Create a new schema/new user in oracle 11g

Generally 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.

Show already created tablespaces:

SQL> select tablespace_name from dba_tablespaces;

 Create tablespace:

SQL> create tablespace edcfc_tabspace
     datafile 'edcfc_tabspace.dat'
     size 10M autoextend on;
   

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 temporary tablespace :-

SQL> create temporary tablespace edcfc_tabspace_temp
     tempfile 'edcfc_tabspace_temp.dat'
     size 5M autoextend on;

Create User:-

 SQL > create user eDCFC
       identified by edcfc
       default tablespace EDCFC_TABSPACE
       temporary tablespace EDCFC_TABSPACE_TEMP; 

Grant some privileges:- 

  SQL > grant create session to edcfc;
        grant create table to edcfc;
        grant unlimited tablespace to edcfc;

Login as edcfc and check what privileges he has:- 

  SQL > select * from session_privs;

       PRIVILEGE
     ----------------------------------------
      CREATE SESSION
      UNLIMITED TABLESPACE
      CREATE TABLE 

With create table privilege the user can create tables:

SQL > CREATE TABLE "EMP" (emp_id .............);

SQL > INSERT INTO EMP VALUES (...........) ;

More information:

DDL

• http://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_1001.htm

DBMS_METADATA

• http://www.dba-oracle.com/t_1_dbms_metadata.htm
• http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_metada.htm#ARPLS026
• http://docs.oracle.com/cd/B28359_01/server.111/b28310/general010.htm#ADMIN11562

Schema objects

• http://docs.oracle.com/cd/B19306_01/server.102/b14220/schema.htm

Differences between schema and user

• http://dba.stackexchange.com/questions/37012/difference-between-database-vs-user-vs-schema
• difference between a User and a Schema in Oracle?

Privileges

• http://docs.oracle.com/cd/E11882_01/timesten.112/e21642/privileges.htm#TTSQL338

Creating user/schema

• http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_8003.htm
• http://www.techonthenet.com/oracle/schemas/create_schema.php

Creating tablespace

• http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7003.htm#i2231734

SQL Plus commands

• http://ss64.com/ora/syntax-sqlplus.html