I have applied for an internship in a company and as a question they have asked me to create a schema for their company with certain requirements and mail them the DDL file. I have installed Oracle database 11g Express edition, but how do I create a new schema in Oracle database 11g? I have searched in the net for a solution but I could not understand what to do. And after creating a schema, which file should I mail them?
Sql – How to create a new schema/new user in Oracle Database 11g
databasedatabase-designoracle11gschemasql
Best Answer
Generally speaking a schema in oracle is the same as an user. Oracle Database automatically creates a schema when you create a user. A file with the DDL file extension is an SQL Data Definition Language file.
Creating new user (using SQL Plus)
Basic SQL Plus commands:
Open SQL Plus and log:
The sysdba is a role and is like "root" on unix or "Administrator" on Windows. It sees all, can do all. Internally, if you connect as sysdba, your schema name will appear to be SYS.
Create an user:
View all users and check if the user johny is there:
If you try to login as johny now you would get an error:
The user to login needs at least create session priviledge so we have to grant this privileges to the user:
Now you are able to connect as the user johny:
To get rid of the user you can drop it:
That was basic example to show how to create an user. It might be more complex. Above we created an user whose objects are stored in the database default tablespace. To have database tidy we should place users objects to his own space (tablespace is an allocation of space in the database that can contain schema objects).
Show already created tablespaces:
Create tablespace:
Create temporary tablespace (Temporaty tablespace is an allocation of space in the database that can contain transient data that persists only for the duration of a session. This transient data cannot be recovered after process or instance failure.):
Create the user:
Grant some privileges:
Login as johny and check what privileges he has:
With create table privilege the user can create tables:
Insert data:
Select:
To get DDL data you can use DBMS_METADATA package that "provides a way for you to retrieve metadata from the database dictionary as XML or creation DDL and to submit the XML to re-create the object.". (with help from http://www.dba-oracle.com/oracle_tips_dbms_metadata.htm)
For table:
Result:
For index:
Result:
More information:
DDL
DBMS_METADATA
Schema objects
Differences between schema and user
Privileges
Creating user/schema
Creating tablespace
SQL Plus commands