CREATE or ALTER a Tablespace

A tablespaces is made up of one or more database datafiles. Tablespaces are the logical components of a database , datafiles which it's made of are physical componets of database.

To alter a tablespace attributes there are some prerequesites :-

We need to have ALTER TABLESPACE system privilege to alter a tablespace. Having ALTER TABLESPACE system privilege we can perform any operation regarding tablespace, but if we have MANAGE TABLESPACE system privilege then we can only perform ONLINE/OFFLINE a tablespace, BEGIN/END a backup of tablespace, Make the tablespace readonly/readwrite.

To alter SYSAUX tablespace we need SYSDBA privilege.

How to create a tablespace :-

SQL> create tablespace test datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\test01.dbf' size 100m;

SQL> create tablespace test datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\test02.dbf' size 100m autoextend on;

How to rename a tablespace :-

SQL>  alter tablespace test1 rename to test2;

Renaming a datafile of a tablespace :-

To rename a datafile of a tablespace , we have to do that in three steps :-

1. Make the Tablespace Offline :- we cannot make a system tablespace offline, if necessary we have to shut down the database.

SQL> alter tablespace test offline normal;

2. Rename the datafile:-

SQL> alter tablespace test rename datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\test01.dbf' to 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\test05.dbf'; 

3. Make the tablespace online :-

SQL> alter tablespace test online;

How to add a datafile to a Tablespace :-

SQL> alter tablespace test2 add datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\test03.dbf' size 100m;

How to drop a datafile of a tablespace :-

Datafile which you wants to drops has to be empty means no extent allocated to it.

SQL> alter tablespace test2 drop datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\test03.dbf';

SQL> alter database tempfile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\TEMP01.DBF' drop including datafiles;

How to resize a datafile of a tablespace :-

SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\test02.dbf' resize 200m;

Details of tablespaces :-

SQL> select tablespace_name,sum(bytes)/1024/1024/1024 from dba_segments group by tablespace_name;
                                                        OR

SQL> select tablespace_name,sum(bytes)/1024/1024/1024 from dba_data_files group by tablespace_name;
                                                         OR 

SQL> select tablespace_name,sum(bytes)/1024/1024/1024 from dba_free_space group by tablespace_name;
                                                          OR

SQL> select t.tablespace, t.totalspace as " Totalspace(MB)", round((t.totalspace-fs.freespace),2) as "Used Space(MB)", fs.freespace as "Freespace(MB)", round(((t.totalspace-fs.freespace)/t.totalspace)*100,2) as "% Used", round((fs.freespace/t.totalspace)*100,2) as "% Free" from (select round(sum(d.bytes)/(1024*1024)) as totalspace, d.tablespace_name tablespace from dba_data_files d group by d.tablespace_name) t, (select round(sum(f.bytes)/(1024*1024)) as freespace, f.tablespace_name tablespace from dba_free_space f group by f.tablespace_name) fs where t.tablespace=fs.tablespace order by t.tablespace;

Autoextend Parameter :-

SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\TEST01.DBF' autoextend OFF;

SQL> alter database datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\TEST01.DBF' autoextend ON;

Logging Attribute :-

SQL> Alter tablespace TEST1 logging;

SQL> Alter tablespace TEST1 nologging;

How to DROP a tablespace :-

SQL> drop tablespace test including contents and datafiles;

It will drop the tablespace with data and datafiles.

SQL> drop tablespace amit including contents;

It will drop only tablespace and data from database but not that physical file from OS. we can again reuse that file to create a tablespace :-

SQL> create tablespace amit datafile 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\AMIT\amit01.dbf' size 1g reuse;

I hope this article helped you.

Regards,
Amit Rath