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.