Tuesday, 22 September 2015

Create Schema in Oracle Database 11g

To create a new Schema in Oracle 11g Database follow the below steps:

Step 1: Run SQL Command Line and type

connect /as sysdba;

Step 2: Create user as SUSANTO_TUTORIALS with password as PAUL as shown below:

Step 3: Now we will view all the users and see if the user SUSANTO_TUTORIALS is there. For this type the command as:

Select username from dba_users;

Thus we can see that the user SUSANTO_TUTORIALS is created as shown below:

Step 4: If we try to login now as SUSANTO_TUTORIALS we will get an error saying ORA-01045: user SUSANTO_TUTORIALS lacks CREATE SESSION privilege; login denied.

Step 5: The user to login needs at least create session privilege so we have to grant this privilege to the user that is to SUSANTO_TUTORIALS as shown below. But before that we have to connect again as sysdba as shown in step 1, as in Step 4, the connection got disconnected. After we reconnect as sysdba type the below command.

Grant create session to SUSANTO_TUTORIALS

Step 6: Now we will be able to connect to SUSANTO_TUTORIALS as shown below:

This was the 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 tablespace (Tablespace is an allocation of space in the database that can contains schema objects).

Thanks & Regards,
Susanto Paul