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 tablespace from dictionary managed to Local extent management

[oracle@collabn1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sat Sep 5 11:51:17 2015

Copyright (c) 1982, 2009, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SHAIKDB>select tablespace_name,extent_management from dba_tablespaces;

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


SHAIKDB>alter tablespace users read only;

Tablespace altered.
SHAIKDB>alter tablespace sysaux offline;

Tablespace altered.

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

*
ERROR at line 1:
ORA-10643: Database should be mounted in restricted mode and Exclusive mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 227
ORA-06512: at line 1


SHAIKDB>shut immediate;
Database closed.
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>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.

Remember you cannot convert a tablespace from LOCAL to DICTIONARY unless the system tablespace is already in DICTIONARY managed state. ELSE you will get the below error message.


SHAIKDB>exec dbms_space_admin.tablespace_migrate_from_local('PERM');
BEGIN dbms_space_admin.tablespace_migrate_from_local('PERM'); END;

*
ERROR at line 1:
ORA-10616: Operation not allowed on this tablespace
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 216
ORA-06512: at line 1

 

No comments: