• Article Type: General
• Product: Aleph
• Product Version: 16.02

Description:
How can I tell if my site has "Locally-Managed Tablespace"?

Resolution:
Do this SQL:.
SQL> select tablespace_name, table_name, next_extent from all_tables where owner like 'USM%';.
If the NEXT_EXTENT column is blank, that indicates that it's in a Locally-managed tablespace..
Note: I find that in pre-v16, pre-Oracle-9i installations this SQL will also show you the management for each tablespace ("Dictionary" or "Local"):.
SQL> select substr(tablespace_name,1,20), extent_management from dba_tablespaces;.
I haven't yet found an equivalent query for Oracle 9.

• Article last edited: 10/8/2013