Query to check locally managed tablespaces

Below query to identify tablespaces are in locally or dictionary managed tablespaces:

SELECT tablespace_name,extent_management FROM dba_tablespaces;

Sample Output:

SQL> SELECT tablespace_name,extent_management FROM dba_tablespaces;

TABLESPACE_NAME                EXTENT_MAN
------------------------------ ----------
SYSTEM                         LOCAL
SYSAUX                         LOCAL
TEMP                              LOCAL
.............................
.............................
............................
XDB                                LOCAL
APPS_UNDOTS1           LOCAL

15 rows selected.

SQL>

Its possible all tablespaces may be locally managed and below query to find tablespaces are in locally managed.

SELECT tablespace_name,extent_management FROM dba_tablespaces where extent_management='LOCAL';

Same way below query to find dictionary managed tablespaces:

SELECT tablespace_name,extent_management FROM dba_tablespaces where extent_management='DICTIONARY';

Thats it.. Hopw this will help you.. :)

Regards,
Chowdari