How to create a user or schema in Oracle

User or schema is a description of data in the database. It consists of all database objects related to a particular user. Schema is a collection of logical structures including tables,views,procedures,functions etc. 

A user in a database owns a schema . User and Schema have the same name. Create User Command creates a user, it automatically creates the schema for that user. 

Regarding creation of schema you need a tablespace where your user can store its objects.

Create a Tablespace for a user :-

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
C:\APP\AMIT.RATH\ORADATA\ORCL\SYSTEM01.DBF
C:\APP\AMIT.RATH\ORADATA\ORCL\SYSAUX01.DBF
C:\APP\AMIT.RATH\ORADATA\ORCL\UNDOTBS01.DBF
C:\APP\AMIT.RATH\ORADATA\ORCL\USERS01.DBF
C:\APP\AMIT.RATH\ORADATA\ORCL\EXAMPLE01.DBF

SQL> create tablespace amit datafile 'C:\APP\AMIT.RATH\ORADATA\ORCL\amit01.dbf' size 10m;

Command to create a user :-

1. Create a user having tablespace as default tablespace of database and temporary tablespace as default temporary tablespace of database.

SQL> create user amit identified by amit;

User created.

2. Create a user having different tablespsace as default tablespace of database and different temporary tablespace as default temporary tablespace of database.

SQL> create user amit identified by amit default tablespace test temporary tablespace temporary quota unlimited on test;         ---------- unlimited quota on test tablespace

SQL> create user amit identified by amit default tablespace test temporary tablespace temporary quota 100m on test;               ----------- 100m quota on test tablespace

3. Create a user by assigning a profile other than default profile of database :-

SQL> create user amit identified by amit default tablespace test temporary tablespace temporary quota unlimited on test profile AMIT_PROF; 

Providing Grants to a user :-

SQL> grant connect,resource to amit;

Now your user is ready to connect to database.

I hope this article helped you.

Regards,
Amit Rath