How to create tablespace in oracle database

This document describes us how to create tablespace in oracle database.

Step 1:

Find the name of the database

SQL> select name,open_mode from v$database;

NAME               OPEN_MODE
---------              --------------------
PRIMARY         READ WRITE

Find the datafiles location

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME              FILE_NAME
------------------------------             -------------------------------------------------------
SYSTEM                                  /u02/oradata/PRIMARY/system01.dbf
SYSAUX                                 /u02/oradata/PRIMARY/sysaux01.dbf
EXAMPLE                              /u02/oradata/PRIMARY/example01.dbf
USERS                                    /u02/oradata/PRIMARY/users01.dbf
UNDOTBS1                            /u02/oradata/PRIMARY/undotbs01.dbf


Step 2: Create new tablespace using above datafile location

SQL> create tablespace TEST datafile '/u02/oradata/PRIMARY/test01.dbf' size 50m autoextend on next  10m maxsize unlimited;

Tablespace created.

SQL>

Verify the  newly created tablespace.

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME         FILE_NAME
------------------------------        -------------------------------------------------------
SYSTEM                              /u02/oradata/PRIMARY/system01.dbf
SYSAUX                             /u02/oradata/PRIMARY/sysaux01.dbf
EXAMPLE                          /u02/oradata/PRIMARY/example01.dbf
USERS                                /u02/oradata/PRIMARY/users01.dbf
TEST                                   /u02/oradata/PRIMARY/test01.dbf
UNDOTBS1                         /u02/oradata/PRIMARY/undotbs01.dbf

6 rows selected.

SQL>

Thanks for your patience to view this post.......