Saturday 23 February 2013

How To Change A Dictionary Managed Tablespace To A Locally Managed Tablespace

High Level Steps for Migration of  Dictionary managed tablespaces to locally managed tablespaces:

1) Migrate all other tablespaces to Local first
EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TABLESPACE_NAME1');
EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TABLESPACE_NAME2');

2) Check system is not the default temporary tablespace

3) If TEMP is dictionary managed drop it and recreate TEMP tablespace

3) Put database in restricted mode

4) Put SYSAUX tablespace in offline mode

5) Put all other tablespaces in read only mode (except SYSTEM, TEMP and UNDO)

6) Migrate system to local
    EXECUTE DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');

7) Disable restricted session

8) Revert SYSAUX to online

9) Revert all tablespaces to read write

How To Change A Dictionary Managed Tablespace To A Locally Managed Tablespace? [ID 735978.1]

No comments:

Post a Comment