Create user/schema 1

[evergrean100]

I just installed and created a new Oracle 9i database on my Windows Workstation. I am using Sys to log in as sysdba.
I see there are many example schemas that were already built but I now want to create my own user/schema.

Please advise what the commands are to do this?

I assume this is correct but dont I need to create the user account first and also need priviledges to create, modify, delete tables in the new schema:

CREATE SCHEMA myfirst AUTHORIZATION myfirst;

 

[SantaMufasa]

Evergrean,

Strangely, CREATE SCHEMA... is not the command you want to use in Oracle to create a schema. Here are sample commands that I use (in SQL*Plus) to create a user schema:

create user <username> identified by <password>
default tablespace <tablespace name of DBA's choice>
temporary tablespace <tablespace name for sorting, typically TEMP>
quota unlimited on <default tablespace name, above>
/
grant connect, resource to <username>
/
grant CREATE VIEW to <username> -- for Oracle 10g
/

Here are the effects of GRANTing the above roles or privileges:

1) CONNECT, allows the new user to log into Oracle;
2) RESOURCE, allows the new user to CREATE objects under their user's ownership
3) CREATE VIEW, used to be part of the RESOURCE, but beginning with Oracle 10g, that privilege requires an explicit GRANT of its own.

Let us know if you have additional questions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[evergrean100]

Thanks, it works.

Intially I could see the Schema using SQL Plus and not in the OEM. Then after I created a table in the new Schema it showed up in the OEM. I assume that is normal?

 

[SantaMufasa]

Evergrean,

I am not the most experienced OEM aficionado, but it may be that OEM treats schemas like the old proverb, "If there is not yet a tree in the forest, can you actually see a forest?"

Regardless of how OEM sees (or doesn't see) it, once you create an Oracle user, regardless of the absence of objects, you still have a schema by the same name.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jimirvine]

I am not the most experienced OEM aficionado, but it may be that OEM treats schemas like the old proverb, "If there is not yet a tree in the forest, can you actually see a forest?"

Yep this is the case for 9i OEM (I'm not sure about the 10g OEM) Evergreen, you WILL be able to see that a USER has been created in the OEM.