Solved

Create database in Oracle

Posted on 2012-04-03
23
563 Views
Last Modified: 2012-04-04
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?
0
Comment
Question by:AidenA
  • 10
  • 10
  • +2
23 Comments
 
LVL 5

Expert Comment

by:Bajwa
ID: 37800947
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
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37800950
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.
0
 
LVL 13

Expert Comment

by:Ashok
ID: 37800970
After you login,

you need to
use the CREATE DATABASE statement to create a database manually.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37800981
>>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).
0
 

Author Comment

by:AidenA
ID: 37801031
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?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37801076
>>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.
0
 

Author Comment

by:AidenA
ID: 37801197
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)
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37801219
>>'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?
0
 
LVL 12

Expert Comment

by:praveencpk
ID: 37801235
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.
0
 

Author Comment

by:AidenA
ID: 37801333
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))
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37801399
>>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?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:AidenA
ID: 37801421
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?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37801472
>>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.
0
 

Author Comment

by:AidenA
ID: 37801641
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)
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 37801683
>>But, I'd prefer someone who actually knows something about this (like yourself) to help me make that decision.

I don't know your systems, organization, etc...  You might have security polocies in your company that dictate the correct approach.

Do you now have a DBA team that handles these things for your company?

>>try and figure out where 'create user myDatabase identified by myDatabase' has been stored

It is in the database you were connect to at the time you issed the create user statement.

I see the issue:
create user myDatabase identified by myDatabase
  2


You need a ';' at the end of the command to actually 'execute' it.

If you did not see feedback: User Created, it never was.

create user myDatabase identified by myDatabase ;
grant connect,resource,dba to myDatabase;
0
 

Author Comment

by:AidenA
ID: 37801892
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.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37801929
>>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?
0
 

Author Comment

by:AidenA
ID: 37802011
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')
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37802184
>>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.
0
 

Author Comment

by:AidenA
ID: 37804867
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...
0
 

Author Comment

by:AidenA
ID: 37805039
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
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37805750
>>(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.
0
 

Author Comment

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

Aiden
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now