Oracle – Create tablespace and User / Schema

Creating a tablespace and user is a very common task after creating an oracle database. Before going further must read the post Schema vs. User

In this post we will see how to create tablespace and user with example

Tablespace

Each tablespace in an Oracle database consists of one or more files called data files.

A permanent tablespace contains persistent schema objects. Objects in permanent tablespaces are stored in data files.

Script 1: Create Tablespace

CREATE TABLESPACE "test01" 
    LOGGING 
    DATAFILE 'D:\myTBS\ test01.ora' SIZE 10M AUTOEXTEND ON NEXT 10M
    EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

A temporary tablespace contains schema objects only for the duration of a session. Objects in temporary tablespaces are stored in tempfiles.

Script 2: Create Temporary Tablespace

CREATE TEMPORARY TABLESPACE "test01_TEMP" 
TEMPFILE 'D:\myTBS\ test01 _TEMP.ora' 
SIZE 10M REUSE AUTOEXTEND ON NEXT  10M EXTENT MANAGEMENT LOCAL 
UNIFORM SIZE 1024K;

Script 3: Create User

CREATE USER " testuser"  PROFILE "DEFAULT" 
    IDENTIFIED BY "TEST123" DEFAULT TABLESPACE " test01" 
    TEMPORARY TABLESPACE " test01_TEMP" 
    ACCOUNT UNLOCK;

Using the GRANT statement one can authorize database users to do the specific task on the system.

Script 4 : Grant to User

GRANT "CONNECT" TO " testuser ";
GRANT "RESOURCE" TO " testuser ";
GRANT "DBA" TO " testuser ";
GRANT create any table to testuser;

Similarly one can give the other required grants as per the requirement .