Recreate Undo Tablespace
October 17, 2011 Leave a comment
–Identify undo tablespace name
SQL> SELECT NAME,VALUE FROM v$parameter WHERE name IN ('undo_management','undo_tablespace'); NAME VALUE --------------- ---------- undo_management AUTO undo_tablespace UNDOTBS1
–Define how undo tablespace was created
SQL> SELECT dbms_metadata.get_ddl('TABLESPACE','UNDOTBS1') FROM dual;
CREATE UNDO TABLESPACE "UNDOTBS1" DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF' SIZE 26214400 AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE ALTER DATABASE DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF' RESIZE 28377088
–Create another, substitute undo tablespace
SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS02.DBF' SIZE 26214400 AUTOEXTEND ON NEXT 5242880 MAXSIZE 32767M;
–Change parameter undo_tablespace value to newly created tablespace name. (I have started my DB by spfile)
SQL> ALTER SYSTEM SET undo_tablespace = 'UNDOTBS2' SCOPE=spfile;
–Shutdown database
SQL> shutdown immediate;
–Start the database
SQL> startup;
–Take old undo tbs. into offline mode
SQL> ALTER TABLESPACE UNDOTBS1 OFFLINE;
–Drop undo tbs. including contents and datafiles
SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;
About Mariami Kupatadze
DBA Knowledge Base 