The CREATE TABLESPACE statement is used to create a tablespace in Oracle database where schema objects are stored.
The CREATE TABLESPACE statement can be used to create the 3 kinds of tablespaces:
- Permanent Tablespace
- Temporary Tablespace
- Undo Tablespace
1. Permanent Tablespace
HOW TO CREATE A NEW TABLESPACE
SYS>
SYS> create tablespace NEWTS datafile ‘/u01/TEST/newts01.dbf’ size 100m;
Tablespace created.
HOW TO CHECK THE DATAFILE SIZE IN MB
SYS> select tablespace_name, file_name, file_id, bytes/1024/1024 from dba_data_files;
TABLESPACE FILE_NAME FILE_ID BYTES/1024/1024
———- —————————————- ———- —————
USERS /u01/TEST/users01.dbf 4 5
UNDOTBS1 /u01/TEST/undotbs01.dbf 3 35
SYSAUX /u01/TEST/sysaux01.dbf 2 530
SYSTEM /u01/TEST/system01.dbf 1 710
NEWTS /u01/TEST/newts01.dbf 5 100
HOW TO ADD A DATAFILE TO THE EXISTING TABLESPACE
SYS> alter tablespace NEWTS add datafile ‘/u01/TEST/newts02.dbf’ size 10m;
Tablespace altered.
HOW TO RENAME TABLESPACE
SYS> alter tablespace NEWTS rename to NEWTS1;
Tablespace altered.
HOW TO RENAME A DATAFILE
SYS> select tablespace_name, file_name, file_id, bytes/1024/1024 from dba_data_files where tablespace_name=’NEWTS1′;
TABLESPACE FILE_NAME FILE_ID BYTES/1024/1024
———- —————————————- ———- —————
NEWTS1 /u01/TEST/newts01.dbf 5 100
NEWTS1 /u01/TEST/newts02.dbf 6 10
SYS> ALTER TABLESPACE NEWTS1 offline ;
Tablespace altered.
SYS> !mv /u01/TEST/newts02.dbf /u01/TEST/newts03.dbf
SYS> ALTER TABLESPACE NEWTS1 RENAME DATAFILE ‘/u01/TEST/newts02.dbf’ to ‘/u01/TEST/newts03.dbf’;
Tablespace altered.
SYS> ALTER TABLESPACE NEWTS1 online;
Tablespace altered.
SYS> select tablespace_name, file_name, file_id, bytes/1024/1024 from dba_data_files where tablespace_name=’NEWTS1′;
TABLESPACE FILE_NAME FILE_ID BYTES/1024/1024
———- —————————————- ———- —————
NEWTS1 /u01/TEST/newts01.dbf 5 100
NEWTS1 /u01/TEST/newts03.dbf 6 10
HOW TO MAKE TABLESPACE OFFLINE and ONLINE
SYS> ALTER TABLESPACE NEWTS1 offline;
Tablespace altered.
SYS> ALTER TABLESPACE NEWTS1 online;
Tablespace altered.
RESIZE A DATAFILE
select tablespace_name, file_name, file_id, bytes/1024/1024 from dba_data_files where tablespace_name=’NEWTS1′;
TABLESPACE FILE_NAME FILE_ID BYTES/1024/1024
———- —————————————- ———- —————
NEWTS1 /u01/TEST/newts01.dbf 5 100
NEWTS1 /u01/TEST/newts03.dbf 6 10
SYS> alter database datafile ‘/u01/TEST/newts03.dbf’ resize 50m ;
Database altered.
SYS> select tablespace_name, file_name, file_id, bytes/1024/1024 from dba_data_files where tablespace_name=’NEWTS1′;
TABLESPACE FILE_NAME FILE_ID BYTES/1024/1024
———- —————————————- ———- —————
NEWTS1 /u01/TEST/newts01.dbf 5 100
NEWTS1 /u01/TEST/newts03.dbf 6 50
DROPPING TABLESPACE
SYS> drop tablespace NEWTS1 including contents and datafiles;
Tablespace dropped.
Also read, Oracle 11g Tablespace Management Theory
Words from dbapath
Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestion/feedback.
If you want to be updated with all our articles
please follow us on Facebook | Twitter
Please subscribe to our newsletter.