Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Saturday, September 5, 2015

Convert from Dictionary Managed Tablespace to Locally Managed Tablespace:



Convert from Dictionary Managed Tablespace to Locally Managed Tablespace:


SHAIKDB>select tablespace_name,extent_management from dba_tablespaces;

TABLESPACE_NAME            EXTENT_MAN
------------------------------ ----------
SYSTEM                  DICTIONARY
SYSAUX                  LOCAL
UNDOTBS1                  LOCAL
TEMP1                  LOCAL
USERS                  LOCAL

Before migrating the SYSTEM tablespace, the following conditions must be met. These conditions are enforced by the TABLESPACE_MIGRATE_TO_LOCAL procedure, except for the cold backup.
  • The database must have a default temporary tablespace that is not SYSTEM.
  • Dictionary-managed tablespaces cannot have any rollback segments.
  • A locally managed tablespace must have at least one online rollback segment. If you are using automatic undo management, then an undo tablespace must be online.
  • All tablespaces—except the tablespace containing the rollback segment or the undo tablespace—must be read-only.
  • You must have a cold backup of the database.
  • The system must be in restricted mode.



SHAIKDB>shut immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SHAIKDB>startup restrict
ORACLE instance started.

Total System Global Area  471830528 bytes
Fixed Size           2214456 bytes
Variable Size         301991368 bytes
Database Buffers      163577856 bytes
Redo Buffers           4046848 bytes
Database mounted.
Database opened.


SHAIKDB>alter tablespace sysaux offline;

Tablespace altered.

SHAIKDB>exec dbms_space_admin.tablespace_migrate_to_local('SYSTEM');

PL/SQL procedure successfully completed.

SHAIKDB>select tablespace_name,extent_management from dba_tablespaces;

TABLESPACE_NAME            EXTENT_MAN
------------------------------ ----------
SYSTEM                  LOCAL
SYSAUX                  LOCAL
UNDOTBS1                         LOCAL
TEMP1                  LOCAL
USERS                  LOCAL

SHAIKDB>
SHAIKDB>alter tablespace sysaux online;

Tablespace altered.

SHAIKDB>alter tablespace users read write;

Tablespace altered.


SHAIKDB>select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE

No comments: