How to drop undo tablespace in oracle 11g?



SQL> select file_name,tablespace_name from dba_data_files;

FILE_NAME                                     TABLESPACE_NAME
--------------------------------------------- --------------------
/u01/app/oracle/oradata/prim/users01.dbf      USERS
/u01/app/oracle/oradata/prim/undotbs01.dbf    UNDOTBS1
/u01/app/oracle/oradata/prim/sysaux01.dbf     SYSAUX
/u01/app/oracle/oradata/prim/system01.dbf     SYSTEM
/u01/app/oracle/oradata/prim/example01.dbf    EXAMPLE


SQL>  drop tablespace UNDOTBS1 including contents;
 drop tablespace UNDOTBS1 including contents
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

The error shows that the undo tablespace is in use.
Now to drop the current undo tablespace we need to create another undo tablespace and make it a default one.

SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/prim/undotbs02.dbf' size 50M reuse autoextend on maxsize 500M;

Tablespace created.

SQL> alter system set undo_tablespace=undotbs2 scope=both;

System altered.

Now try to drop the old undotablespace.

SQL> drop tablespace UNDOTBS1 including contents;

Tablespace dropped.

SQL> select file_name,tablespace_name from dba_data_files;

FILE_NAME                                     TABLESPACE_NAME
--------------------------------------------- --------------------
/u01/app/oracle/oradata/prim/users01.dbf      USERS
/u01/app/oracle/oradata/prim/undotbs02.dbf    UNDOTBS2
/u01/app/oracle/oradata/prim/sysaux01.dbf     SYSAUX
/u01/app/oracle/oradata/prim/system01.dbf     SYSTEM
/u01/app/oracle/oradata/prim/example01.dbf    EXAMPLE

No comments:

Post a Comment