Blog dedicated to Oracle Applications (E-Business Suite) Technology; covers Apps Architecture, Administration and third party bolt-ons to Apps

Monday, February 18, 2008

Query to identify locally managed tablespaces

Here's the query to identify locally managed tablespaces and dictionary managed tablespaces in your instance.

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
Ari Kaplan has written an excellent article describing the process of migrating dictionary managed tablespaces to locally managed.

Its possible that all your tablespaces may be locally managed. But it doesn't hurt to run this query and check. In one of our non-OATM environments I found these tablespaces still being dictionary managed:

1 SELECT tablespace_name,extent_management
2 FROM dba_tablespaces
3* where extent_management='DICTIONARY'
SQL> /

TABLESPACE_NAME EXTENT_MAN
------------------------------ ----------
SYSTEM DICTIONARY
AMWD DICTIONARY
AMWX DICTIONARY
ASND DICTIONARY
ASNX DICTIONARY
FPAD DICTIONARY
FPAX DICTIONARY
FUND DICTIONARY
FUNX DICTIONARY
GCSD DICTIONARY
GCSX DICTIONARY
IAD DICTIONARY
IAX DICTIONARY
LNSD DICTIONARY
ZPBX DICTIONARY
ZXD DICTIONARY
ZXX DICTIONARY
LNSX DICTIONARY
MSTD DICTIONARY
MSTX DICTIONARY
XLED DICTIONARY
XLEX DICTIONARY
ZPBD DICTIONARY

23 rows selected.

In OATM enabled instance only SYSTEM tablespace was dictionary managed:

SQL> SELECT tablespace_name,extent_management
FROM dba_tablespaces
where extent_management='DICTIONARY'
/
2 3 4
TABLESPACE_NAME EXTENT_MAN
------------------------------ ----------
SYSTEM DICTIONARY

Ari's article describes in detail how to migrate SYSTEM tablespace from dictionary managed to local.

No comments: