Creating a TableSpace

SQL> create tablespace venu

     datafile '/u03/app/oracle/ssss/venu01.dbf' size 10m;

                -->this throws error as there is no directory structure.

root> mkdir -p /u03/app/oracle/ssss

root> chown -R oracle:oinstall /u03/app/oracle/ssss

root> chmod -R 775 /u03/app/oracle/ssss

SQL> create tablespace venu

     datafile '/u03/app/oracle/ssss/venu01.dbf' size 10m;

                Note : we cannot create a tablespace without creating a datafile.

SQL> desc dba_tablespaces;

SQL> select tablespace_name,block_size,status,extent_management,segment_space_management,logging from dba_tablespaces;

SQL> save ts1.sql -- this file contains the previous query, so that we can execute it later.

SQl> !pwd

                /home/oracle

                note : use ! before any OS command to run in SQL prompt.

SQL> get ts1.sql (or) @ts1.sql  (or) start ts1.sql --> to run the queries in the ts1.sql

SQL> desc dba_tablespaces;

SQL> select tablespace_name,block_size,status,contents,bigfile from dba_tablespaces;

Before creating a table space check the path

SQL> select name from v$datafile;

SQL> desc dba_data_files;

SQL> select file_name,file_id,tablespace_name,bytes from dba_data_files;

                by using this view we can find out which datafile is related to which tablespace and its size;