This content has been marked as final. Show 18 replies
Assuming you're on 10g:
Did you check the Oracle Documentation @ http://www.oracle.com/pls/db102/homepage ?
create user: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_8003.htm#i2065278
create schema: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_6014.htm#SQLRF01313
Can someone tell me how to create a new schema please ?You create a database user with the CREATE USER statement.To create a user, you must have the CREATE USER system privilege. Because it is a powerful privilege, a DBA or security administrator is normally the only user who has the CREATE USER system privilege.
What i need as user & rights ?CREATE USER jward
IDENTIFIED BY AZ7BC2
DEFAULT TABLESPACE data_ts
QUOTA 100M ON test_ts
QUOTA 500K ON data_ts
TEMPORARY TABLESPACE temp_ts
GRANT create session TO jward;
A newly created user cannot connect to the database until granted the CREATE SESSION system privilege.
thank you for your help.
Can you give some more informations because i 've not dealed with Oracle since 2-3 years..
I'm not sure understanding the procedure step by step
To create the user
-if i don't have neither sys nor system access, can i create the user "jward" with the oracle user ?
To create the schema
- once the new user created and granted,do i have to run the create schema statement under sqlplus ?
-if i don't have neither sys nor system access, can i create the user "jward" with the oracle user ?You need to be connected to the database to a User who already has the CREATE USER Privilege.
- once the new user created and granted,do i have to run the create schema statement under sqlplus ?It's not necessary.
A Schema is a collection of Objects (Tables, Index, ...) so you can create them with their own CREATE
But, you may include these statements into a CREATE SCHEMA statement so that if any error occurs
all the statements are rollbacked.
Please, find enclosed some links about this:
Hope this help.
user469717 wrote:You seem to think that the "schema" is some sort of object in and of itself. It is not. A schema is simply the collection of objects belonging to (owned by) a single user. The CREATE SCHEMA command just gives a short-cut (and transactionally consistent) means of creating both the owning user and the owned objects in one sql statement. The end result of "CREATE SCHEMA ...." and "CREATE USER ... CREATE TABLE ..." is exactly the same and once completed, there is no distinction between the two.
Ok so the Oracle Database automatically creates a schema with the same name when i create a user.
So i just have to run the create tables script with the newly created user under sqlplus and then access to the tables with "user".tables , right ?
thks & best regards
Tablespace is again a logical concept. Logically its the combination of one or more segments. Physically it consists of one or more datafiles
Every user has a default tablespace where he can create his schema objects.
User may also create his schema objects in other tablespaces if he has got quota assigned on those tablespaces.
userSA wrote:"Schemas" are not assigned to a tablespace. A user has a default tablespace, but if that user has quota on other tablespaces he may create objects in those as well. So while a user has a default tablespace, he may actually have objects in multiple tablespaces. That does not mean it is either a good or bad idea to do so, only to point out that there is no inherent relationship between a schema and a TS.
And how about the tablespace ? I can create many schemas in the same tablespace, right ?Yes you can create many schemas in the same tablespace.