About Me

My photo
Bangalore, India
I am an Oracle Certified Professional working in SAP Labs. Everything written on my blog has been tested on my local environment, Please test before implementing or running in production. You can contact me at amit.rath0708@gmail.com.

Tuesday, March 5, 2013

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;

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

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

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.

Amit Rath

No comments:

Post a Comment