Link to home
Start Free TrialLog in
Avatar of AidenA
AidenA

asked on

Create database in Oracle

Hi,

I wanted to create a new database in Oracle but I seem to be making a mess of it and not sure how to correct.

I did the following in the command prompt...

Enter user-name: system
Enter password:

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> create user myDatabase identified by myDatabase
  2
SQL> grant connect,resource,dba to myDatabase
  2
SQL>

Then, I tried to log on via TOAD using myDatabase as username and password...

Actually, now that I look at TOAD, I have another database called RT on the server, and the database seems to be called ORCL while the user / Schema seems to be RT. So, what did I do above... I created another database beside ORCL, when what I really want to do is create another schema? Or do I want to create two totally separate databases?
Avatar of Bajwa
Bajwa
Flag of United States of America image

Just create another schema by

create user new_schema identified by new_schema_password default tablespace ts_name temporary tablespace temp_ts_name;

grant create session to new_schema;

grant create table to new_schema;
grant create procedure to new_schema;

and so forth
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Oracle isn't like SQL Server.  In Oracle you have a 'database' which is the data files and an 'Instance' which is the server processes and memory structures that connects to the database.

Inside thdatabaseae, you have 'users'.  Once the 'user' creates an 'object' like a table it is typically called a 'schema'.

the Instance/database is called ORCL and you are creating users/schemas inside it.
After you login,

you need to
use the CREATE DATABASE statement to create a database manually.
>>to create a database manually.

I would not suggest you manually create a database if you are new to Oracle.  If you truly want a new database complete with separare data files and meomory/processes/etc...

Use the Database Configuration Assistant (DBCA).
Avatar of AidenA

ASKER

Hi thanks,

Yes I saw that DBCA tool and opened it... but wasn't really sure what I was doing with it.

So, can I ask, what did I create above (considering it worked and did something at least) and where is it? If it's not what I want... how do i delete it?

i.e. is it a new user in the database ORCL? and if it is, why can't I connect to it like I can to my other database (or schema I suppose it really is) using the username and password myDatabase?
>>what did I create above (considering it worked and did something at least) and where is it? If it's not what I want... how do i delete it?


You created a 'USER':  create user myDatabase identified by myDatabase.

To undo it:  drop user myDatabase;

I the user owns objects and you want to remove everything:
drop user mydatabsae cascade;

>>. is it a new user in the database ORCL?

Yes.  

>>why can't I connect to it like I can to my other

I'm not a toad person but you should be able to provide the password and apsswrod with the database ORCL.


Just an FYI:  You should not grant DBA to a user unless it absolutely has to have it.  That is a very powerful role.
Avatar of AidenA

ASKER

ok, so I can't log in under toad which means somethings wrong somewhere... (sorry, I'm clearly not an expert on this!)

I logged in under the other database and entered ran the following 'select username from dba_users' and I didn't see my new user... (should I have seen it there or do I have to run under system admin account? - ran that in plsql under that account but just get back the value 2 so wasn't sure how to run sql under an admin account)
>>'select username from dba_users' and I didn't see my new user...

If you can select from dba_users, you should see it.  Are you sure you are connected to the correct 'database', ORCL?

To verify (just one of many ways):  select * from global_name;

>>ran that in plsql under that account but just get back the value 2

I have no idea what this means.  PL/SQL is Oracle's procedural progamming language.


Let's start at the beginning:
What do you 'want' to do?  Do you want multiple schemas or multiple databases?
first you have to establish the connection from Toad to your database (ORCL) i.e

check this below doc for more info

http://www.toadworld.com/Blogs/tabid/67/EntryId/488/TOAD-Database-Connections-Problems.aspx

http://dev.toadfordataanalyst.com/webhelp/Content/Connection_Manager/Create_Oracle_Connections.htm

And once the connection is set up you can use the username and password which you have created for login.
Avatar of AidenA

ASKER

slightwv, good question... well... I don't know exactly. Obviously i want a new database as in you have one database for one project and another database for another. For instance, if it was MS Access, i would have one database for 1 project and an entirely separate database for another... simple.

However, if Oracle does this differently and uses schemas to achieve the same result, then that's fine... I've no problem with that.

praveencpk, As I was saying, I have already a connection through TOAD to another schema on the server. So, I can connect fine, see tables, etc etc. The code I ran above is what I used to create this 'database' in the first place. So, I simply wanted to do the same again and create a second 'database' for a new project. However, when I try to connect to ORCL using the new database username and password (which is presumably 'myDatabase') it does not work (so what I'm saying is that there's nothing wrong with the connection to ORCL since it works fine for the other database. But there is a problem connecting to the new database (called myDatabase))
>>Obviously i want a new database as in you have one database for one project and another database for another

You need to understand the differences before you decide what you actually need.

There are times when multiple schemas are acceptable and times when multiple database are acceptable.  We cannot decide that for you.  Only you know what you are trying to support and the requirements.

For example: If one 'application' is payroll and another is consumer complaints, you likely do not want the data from those two systems in the same database.

If the applications are small department systems say, employee messagsecretaryecratary notes then it might be acceptable to use two schemas in the same database.

>>But there is a problem connecting to the new database (called myDatabase))

What is the problem?
Avatar of AidenA

ASKER

ok well, this is for two different websites and they are totally unrelated. So, in that case, I assume to entirely separate databases is in order?

if that's the case, then I suppose I need to drop this user myDatabase that i created earlier (wherever it went I still don't know) and use the DBCA tool and try this again?
>>they are totally unrelated

Still your call.

Different databases use more memory and resources than a single database.  If a single database, you shut it down, you stop both apps.

If you want no interaction between the apps then separate databases are probably the way to go.

So yes, drop the user and create a new database using dbca.
Avatar of AidenA

ASKER

well i guess it's my call ultimately but i probably don't know enough to be sure of making the correct one!

So, I don't remember the last time I ever had to stop my current database... in fact, that's probably never happened, and given the fact that either site can go down for a period without there being serious consequences I'm sure I could lump them into the same database then.

But, I'd prefer someone who actually knows something about this (like yourself) to help me make that decision. I suppose, from what you're saying, there's probably not any issue for me given the types of sites I'm creating (not mission critical - relatively low user base) that having them in the same database is probably easiest?

so... then... if that's the case! then, I suppose i'd want to keep things the way they are, and try and figure out where 'create user myDatabase identified by myDatabase' has been stored? (i.e. it's not in 'select username from dba_users' when i had logged in under the old database credentials. When I ran 'select * from global_name' I got 'ORCL.REGRESS.RDBMS.DEV.US.ORACLE.COM' which sounds like it is ORCL... and that I can only assume means that perhaps 'myDatabase' user was actually created in something other than ORCL)
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of AidenA

ASKER

damn, thought that was going to work... did the below and got feedback but couldn't log on again, so logged in under the old database and ran the select 'username from dba_users' but still did not see myDatabase

SQL> create user myDatabase identified by myDatabase1234;

User created.

SQL> grant connect,resource,dba to myDatabase ;

Grant succeeded.
>>so logged in under the old database

Are you confusing terms again?

Since you saw 'User Created', it was created in whatever database you were connect to when you issued the command.

You should immediately be able to see it in dba_users.  Are you actually connecting to a different database?
Avatar of AidenA

ASKER

well, when i use TOAD I can't log in under system so i just log in under the other schema (call it 'RT') and then when I'm logged in, I'm running 'username from dba_users'.

So, as I was trying to explain before (badly I think), I was not able to run a select command in sqlplus (it just returned '2') so the only way I could check was the way I normally access ORCL through the RT schema. I couldn't connect through the new 'myDatabase' username and 'myDatabase1234' password so I connected via RT to see if the other username is listed...

well it wasn't anyway... so presumably I'm doing something wrong...

(btw, when I created the 'myDatabase' schema, I was logged in under 'system')
>>I was not able to run a select command in sqlplus (it just returned '2')

OH, same thing, missing a ';', the '2' is not a return value.  It is telling you it is ready for line 2 of input.
Avatar of AidenA

ASKER

ah flip! twice that got me!

ok well we're getting somewhere at least. Ok so I was able to run those commands... and however this happened I'm not sure, but I guess there must be two databases...

My old schema is running under ORCL, but when I created the new user (by logging in under 'system') it has created it in the database MYOLDDAT.REGRESS.RDBMS.DEV.US.ORACLE.COM

So, what I must have done in the beginning was created a new database (myolddatabase) and then decided to ditch that (I remember doing that actually because it wasn't me that created it and I couldn't connect) and then created a new schema under ORCL (myolddatabaseschema). So, I'm using myolddatabaseschema currently, while myolddatabase is not used for anything.

So, why I appear to be logging in under MYOLDDAT instead of ORCL I'm not sure. Maybe you can throw some light on it for me...
Avatar of AidenA

ASKER

ok got it working there... was messing around and this worked:

sqlplus sys/orcl@orcl as sysdba

I was then able to create the user and grant permissions and log on via Toad. Maybe then you can just explain the above situation and I'll award the points... (e.g. why did 'system' log on to MYOLDDAT and not ORCL as was expected)

thanks, Aiden
>>(e.g. why did 'system' log on to MYOLDDAT and not ORCL as was expected)

By 'default' Oracle trys the instance specified by the environment variable ORACLE_SID.  In Windows, this is also set in the registry.

Oracle looks in the Environment first, then the registry for the 'default' instance.
Avatar of AidenA

ASKER

right, so i must have set the environment variable at some point... ok, thanks for the info... appreciated!

Aiden