Creating a new Database and New Schema

[aldsaldrin]

My Team Lead recently asked me to create a local schema but I this is my first time using Oracle Database so Im really really lost..

How do create a schema? Do I need to create a database first before creating a schema?

Many thanks..

[amitpatel66]

You need to ask that question to your team lead. Check with him whether you want to create a schema in the existing database or in a new database?

[aldsaldrin]

tnx amitpatel66,

I asked her, she said create a schema for a new database.. A follow-up question how can I create a new database in Oracle?

Many thanks..

[debasisdas]

createing a database and a schema are two different things . Which one you want.

[aldsaldrin]

tnx debasisdas,

Creating a database first, pls teach me..

Many thanks..

[debasisdas]

you can use DBCA for the purpose.

[Pilgrim333]

Hi,

Hope this helps you

Create database and manage users

Pilgrim

[aldsaldrin]

database schema

I have created a new database in Oracle 9i using DBCA, how do I create a schema for it?

Many thanks..

[daitaravi2002]

if you have existing dump files then import them into new database, or other wise create users and tables as per your requirement, don't forgot to observe the tablespace sizes...

[aldsaldrin]

if you have existing dump files then import them into new database, or other wise create users and tables as per your requirement, don't forgot to observe the tablespace sizes...

tnx daitaravi2002,

how do I create users and table?

Many thanks..

[Pilgrim333]

Hi,

I posted a link in your previous topic with a link to how to create users in oracle. I'll post you a link to how to it here.

Create users in Oracle

I think your problem is with the schema part. The objects a user owns are collectively called a schema. So create a user, and then you have your schema.

Pilgrim.

[aldsaldrin]

Hi,

I posted a link in your previous topic with a link to how to create users in oracle. I'll post you a link to how to it here.

Create users in Oracle

I think your problem is with the schema part. The objects a user owns are collectively called a schema. So create a user, and then you have your schema.

Pilgrim.

tnx Pilgrim333,

Now Im getting it, follow-up question: Suppose I create a User with this code:

CREATE USER "TEST" PROFILE "DEFAULT" IDENTIFIED BY "test" ACCOUNT UNLOCK;

but now I want to use/access it using toad, what is the database for that user?

[Pilgrim333]

Hi,

the database is the same database as the database you created it in.
If you connected with dba/dba@TEST and you create a user scott with password tiger then you should connect with scott/tiger@TEST.

The database connect string stays the same, just your user and pass change. You give the user space in your database.

Hope this is explained a bit understandable. If not, just let me know.

Pilgrim.

[aldsaldrin]

tnx Pilgrim333,

I got what you said last time, so I created the user using this code:

CREATE USER "TEST" PROFILE "DEFAULT" IDENTIFIED BY "test" ACCOUNT UNLOCK;

and when I tried to use/access it using toad, a prompt appeared saying that:

ORA-01045: user TEST lacks CREATE SESSION privilege

how can I remedy this?

Many thanks..

[Pilgrim333]

Hi,

you have to assign some standard priviliges to the user. Login as dba and issue the following command:

grant create session, connect, resource to <user>

You will need more info on the grants for the user. More info on:

Oracle Grants security

Pilgrim.