Tuesday, December 4, 2012

0 Migrate SYSTEM tablespace from Dictionary to Local management

If the SYSTEM tablespace is dictionary management, we can migrate to local using

DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL

Here are some prerequisite, you can find more detail in here.

If the prerequisite is not meet ( except the cold backup), you may get the error like “ PLS-00201: identifier 'DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL' must bedeclared” as below screenshot. In my case, I didn’t start the database in the restrict mode.

image

  • Has temporary tablespace exist and set to the default. So SYSTEM tablespace is not being used as temporary tablespace.
---- show current default temporary temp tablespace
select *
from DATABASE_PROPERTIES
where property_name='DEFAULT_TEMP_TABLESPACE';
--- create temporary tablespace
create temporary tablespace tempts tempfile;
--- change the default temporary tablespace
alter database default temporary tablespace tempts;

 

  • Startup the database in the restricted mode
startup restrict 
  • Set the SYSAUX offline

If we don’t set the SYSAUX tablespace offline, we would get the ORA-10648 error.

image

alter tablespace sysaux  offline;

  • Use DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL
exec dbms_space_admin.tablespace_migrate_to_local('SYSTEM');

image

Bring the SYSAUX tablespace online and disable the restricted mode.

alter tablespace sysaux  online;
ALTER SYSTEM DISABLE RESTRICTED SESSION;
select TABLESPACE_NAME,EXTENT_MANAGEMENT from DBA_tablespaces;

image

Reference

http://docs.oracle.com/cd/B19306_01/server.102/b14231/tspaces.htm#sthref1279

0 comments:

Post a Comment

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates