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
0 comments:
Post a Comment