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.

• 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.

alter tablespace sysaux  offline;

• Use DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL

exec dbms_space_admin.tablespace_migrate_to_local('SYSTEM');

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;

Reference

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