全部博文（323）
分类： Oracle
2008-06-18 13:46:35
SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('USERS');
l How to migrate system tablespace to local
Change SYSTEM tablespaces to locally managed
Before the SYSTEM tablespace can be migrated to locally managed format, you should ensure the following:
The database has a default temporary tablespace which is not SYSTEM
There are not any rollback segments in dictionary managed tablespaces
There is at least one online rollback segment in a locally managed tablespace, or an undo tablespace (if using automatic undo management mode) should be online.
All tablespaces other than the tablespace containing the undo space (undo tablespace or the tablespace containing the rollback segment) and the default temporary tablespace are in read-only mode.
There is a complete backup of the system.
The system is in restricted mode.
Note, that we already have an UNDO Tablespace. The following query determines whether the SYSTEM tablespace is locally managed:
SQL> SELECT ts# FROM ts$ WHERE ts# = 0 AND bitmapped <> 0;
If 0 rows are returned, then the SYSTEM tablespace is dictionary managed. Otherwise, the SYSTEM tablespace is locally managed.
Steps to change SYSTEM tablespaces to locally managed
SQL> shutdown immediate
SQL> startup restrict
SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;
SQL> alter tablespace USERS read only;
SQL> alter tablespace TAB read only;
SQL> alter tablespace IDX read only;
SQL> alter tablespace SYSAUX offline;
SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('SYSTEM');
PL/SQL procedure successfully completed.
Note:
After the SYSTEM tablespace is migrated to locally managed, any dictionary-managed tablespaces in the database cannot be made READ WRITE. If you want to be able to use the dictionary-managed tablespaces in READ-WRITE mode, Oracle recommends that you first migrate these tablespaces to locally managed before migrating the SYSTEM tablespace.
