How to drop undo tablespace in oracle 11g?
SYS @ catdb > set pagesize 999SYS @ catdb > set linesize 300
SYS @ catdb > /
FILE_NAME TABLESPACE_NAME
------------------------------------------------------------ ------------------------------
/u01/app/oracle/oradata/catdb/users01.dbf USERS
/u01/app/oracle/oradata/catdb/undotbs01.dbf UNDOTBS1
/u01/app/oracle/oradata/catdb/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/catdb/system01.dbf SYSTEM
/u01/app/oracle/oradata/catdb/example01.dbf EXAMPLE
/u01/app/oracle/oradata/catdb/example04.dbf EXAMPLE
/u01/app/oracle/oradata/catdb/example02.dbf EXAMPLE
/u01/app/oracle/oradata/catdb/tb_name01.dbf TB_NAME
/u01/app/oracle/oradata/catdb/testdb.dbf TESTDB
/u01/app/oracle/oradata/catdb/undotbs02.dbf UNDOTBS1
/u01/app/oracle/oradata/catdb/system02.dbf SYSTEM
11 rows selected.
First create tablespace and then we will see how to drop..
SYS @ catdb > select file_name,tablespace_name from dba_data_files where TABLESPACE_NAME='UNDOTBS1';
FILE_NAME TABLESPACE_NAME
------------------------------------------------------------ ------------------------------
/u01/app/oracle/oradata/catdb/undotbs01.dbf UNDOTBS1
/u01/app/oracle/oradata/catdb/undotbs02.dbf UNDOTBS1
SYS @ catdb > 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.
SYS @ catdb > create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/catdb/undotbs02.dbf' size 10M reuse autoextend on maxsize 50M;
create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/catdb/undotbs02.dbf' size 10M reuse autoextend on maxsize 50M
*
ERROR at line 1:
ORA-01537: cannot add file '/u01/app/oracle/oradata/catdb/undotbs02.dbf' - file already part of database
can not create as we have already datafile of this name.
SYS @ catdb > create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/catdb/undotbs03.dbf' size 10M reuse autoextend on maxsize 50M;
Tablespace created.
SYS @ catdb > alter system set undo_tablespace=undotbs2 scope=both;
System altered.
SYS @ catdb > drop tablespace UNDOTBS1 including contents;^C
SYS @ catdb >
SYS @ catdb > select file_name,tablespace_name from dba_data_files where TABLESPACE_NAME='UNDO%';
no rows selected
SYS @ catdb > select file_name,tablespace_name from dba_data_files where TABLESPACE_NAME like 'UNDO%';
FILE_NAME TABLESPACE_NAME
------------------------------------------------------------ ------------------------------
/u01/app/oracle/oradata/catdb/undotbs01.dbf UNDOTBS1
/u01/app/oracle/oradata/catdb/undotbs02.dbf UNDOTBS1
/u01/app/oracle/oradata/catdb/undotbs03.dbf UNDOTBS2
SYS @ catdb > drop tablespace UNDOTBS1 including contents;
Tablespace dropped.
SYS @ catdb > select file_name,tablespace_name from dba_data_files where TABLESPACE_NAME like 'UNDO%';
FILE_NAME TABLESPACE_NAME
------------------------------------------------------------ ------------------------------
/u01/app/oracle/oradata/catdb/undotbs03.dbf UNDOTBS2
SYS @ catdb > select file_name,tablespace_name from dba_data_files;
FILE_NAME TABLESPACE_NAME
------------------------------------------------------------ ------------------------------
/u01/app/oracle/oradata/catdb/users01.dbf USERS
/u01/app/oracle/oradata/catdb/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/catdb/system01.dbf SYSTEM
/u01/app/oracle/oradata/catdb/example01.dbf EXAMPLE
/u01/app/oracle/oradata/catdb/example04.dbf EXAMPLE
/u01/app/oracle/oradata/catdb/example02.dbf EXAMPLE
/u01/app/oracle/oradata/catdb/tb_name01.dbf TB_NAME
/u01/app/oracle/oradata/catdb/testdb.dbf TESTDB
/u01/app/oracle/oradata/catdb/system02.dbf SYSTEM
/u01/app/oracle/oradata/catdb/shashi.dbf SHASHI
/u01/app/oracle/oradata/catdb/undotbs03.dbf UNDOTBS2
11 rows selected.
SYS @ catdb >
No comments:
Post a Comment