• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1774
  • Last Modified:

Convert To Locally Managed Tablespaces

Oracle 9.2.0.5
What is the quickest way to convert from dictionary managed tablespaces to locally managed tablespaces with the least amount of downtime?
0
jdh0650
Asked:
jdh0650
  • 2
2 Solutions
 
paquicubaCommented:
SQL> shutdown immediate
SQL> startup restrict

SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('SYSTEM');  -- execute this procedure for all those tablespaces DM, before SYSTEM
SQL> alter database open
0
 
helpneedCommented:
hi

Locally managed tablespaces can have uniform extent sizes or variable extent sizes that are determined by the system. Any of the options, UNIFORM or AUTOALLOCATE can be mentioned while creating the tablespace. For UNIFORM extents you can specify an extent size. The default size is 1MB. For AUTOALLOCATE extents you can specify the size of the initial extent and Oracle determines the optimal size of the additional extents, with a minimum extent size of 64KB. That is why these are called system-managed extents.

Check extent management of the tablespaces with following command:

SQL> SELECT tablespace_name,extent_management
       FROM dba_tablespaces;

TABLESPACE_NAME                EXTENT_MAN
------------------------------ ----------
SYSTEM                         DICTIONARY
TEMP                           LOCAL
USERS                          DICTIONARY
TAB                            DICTIONARY
IDX                            DICTIONARY
SYSAUX                         LOCAL
UNDO                           LOCAL


First change first all dictionary managed tablespaces except tablespace SYSTEM to locally managed with the following procedure:

SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('USERS');
PL/SQL procedure successfully completed.

The tablespace must be kept online and read/write during migration. Note that temporary tablespaces cannot be migrated.

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.

Notr, 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> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('SYSTEM');

ERROR at line 1:
ORA-10644: SYSTEM tablespace cannot be default temporary tablespace
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 227
ORA-06512: at line 1

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('SYSTEM');

ERROR at line 1:
ORA-10647: Tablespace other than SYSTEM, UNDO, TEMP not
found in read only mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 227
ORA-06512: at line 1

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
TEMP
USERS
TAB
IDX
SYSAUX
UNDO

SQL> alter tablespace USERS read only;
SQL> alter tablespace TAB read only;
SQL> alter tablespace IDX  read only;

SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('SYSTEM');

ERROR at line 1:
ORA-10648: Tablespace SYSAUX is not offline
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 227
ORA-06512: at line 1

SQL> alter tablespace SYSAUX offline;

SQL> execute DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL ('SYSTEM');
PL/SQL procedure successfully completed.


regards
0
 
jdh0650Author Commented:
So you can migrate all user tablespaces in uptime(read write), and you only need downtime for the system tablespace
0
 
helpneedCommented:
hi

just read it carefully

regards
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now