Saturday, 23 April 2011

how to drop a tablespace

DROP TABLESPACE <tablespace name> INCLUDING CONTENTS AND DATAFILES;

If you don’t specify “AND DATAFILES”, Oracle will not drop the physical datafile after the DROP TABLESPACE command, but you can always delete the files from operating system shell (If the data files are locked, restart the server).
But always perform a backup of the database, as the “DROP TABLESPACE” command is irreversible. It’s also a good practice to check and identify how many datafiles a tablespace actually has before performing the drop action in order to avoid mistake.

SQL> select name from v$database;
NAME
---------
PNDW
SQL>
SQL>
SQL> select STATUS from v$datafile where NAME like '%xxtne%';
STATUS
-------
OFFLINE
OFFLINE
OFFLINE
OFFLINE
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces where tablespace_name in ('XXTNED','XXTNEX');
TABLESPACE_NAME                STATUS
------------------------------ ---------
XXTNED                         OFFLINE
XXTNEX                         OFFLINE

SQL> select file_name from dba_data_files where tablespace_name in ('XXTNED','XXTNEX');
FILE_NAME
--------------------------------------------------------------------------------
/aa_gen_dvd/oradata02/pndw/xxtned01.dbf
/aa_gen_dvd/oradata02/pndw/xxtnex01.dbf
/aa_gen_dvd/oradata02/pndw/xxtned02.dbf
/aa_gen_dvd/oradata02/pndw/xxtnex02.dbf
pndw$bdf /aa_gen_dvd/oradata02/
Filesystem          kbytes    used   avail %used Mounted on
/dev/vgaa_gen_dvd/lvol4
                   131072000 123009269 7581899   94% /aa_gen_dvd/oradata02

SQL> select name from v$database;
NAME
---------
PNDW
SQL> DROP TABLESPACE XXTNED INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
SQL>
SQL>
SQL> DROP TABLESPACE XXTNEX INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
SQL>
SQL> select file_name from dba_data_files where tablespace_name in ('XXTNED','XXTNEX');
no rows selected
SQL> select TABLESPACE_NAME,STATUS from dba_tablespaces where tablespace_name in ('XXTNED','XXTNEX');
no rows selected
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
pndw$bdf /aa_gen_dvd/oradata02/
Filesystem          kbytes    used   avail %used Mounted on
/dev/vgaa_gen_dvd/lvol4
                   131072000 102529205 26781960   79% /aa_gen_dvd/oradata02

No comments:

Post a Comment