Oracle Database
Oracle UNDO tablespace의 datafile 옮기는 방법 2개.
에너자이죠
2010. 1. 7. 15:00
1.
1. Shutdown the database
2. Move the datafile to its new location.
3. Do a "STARTUP MOUNT"
4. ALTER DATABASE RENAME FILE '/old/location' TO '/new/location';
5. ALTER DATABASE OPEN;
2. Move the datafile to its new location.
3. Do a "STARTUP MOUNT"
4. ALTER DATABASE RENAME FILE '/old/location' TO '/new/location';
5. ALTER DATABASE OPEN;
2.
--Create new undo tablespace:
SQL> create undo tablespace UNDOTBS02 datafile '/u03/app/oracle/oradata/ovpi/undo02.dbf' size 1000m;
--Set new tablespace as undo_tablespace:
SQL> alter system set undo_tablespace=undotbs02;
--Drop the old tablespace:
SQL> drop tablespace undotbs including contents;
When dropping the old tablespace, you may encounter an ORA-30013 error.
This essentially indicates that you must wait for any existing transactions using this tablespace to either commit or rollback before the tablespace can be dropped.
SQL> create undo tablespace UNDOTBS02 datafile '/u03/app/oracle/oradata/ovpi/undo02.dbf' size 1000m;
--Set new tablespace as undo_tablespace:
SQL> alter system set undo_tablespace=undotbs02;
--Drop the old tablespace:
SQL> drop tablespace undotbs including contents;
When dropping the old tablespace, you may encounter an ORA-30013 error.
This essentially indicates that you must wait for any existing transactions using this tablespace to either commit or rollback before the tablespace can be dropped.
그럼 undo_retention 까지 기다려야 할까?
아니면 undo_retention은 무시되며 drop 될까? 아마 drop 되지 않을까.. 싶다..
http://www.orafaq.com/forum/t/63723/2/