View all the tablespaces in a database : dba_tablespaces « System Tables Data Dictionary « Oracle PL/SQL Tutorial






SQL>
SQL> SELECT * FROM dba_tablespaces;

TABLESPACE_NAME                BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS    CONTENTS  LOGGING   FOR EXTENT_MAN ALLOCATIO PLU SEGMEN DEF_TAB_ RETENTION   BIG
------------------------------ ---------- -------------- ----------- ----------- ----------- ------------ ---------- --------- --------- --------- --- ---------- --------- --- ------ -------- ----------- ---
SYSTEM                               8192          65536                       1  2147483645                65536 ONLINE    PERMANENT LOGGING   NO  LOCAL         SYSTEM    NO  MANUAL DISABLED NOT APPLY   NO
UNDO                                 8192          65536                       1  2147483645                65536 ONLINE    UNDO         LOGGING   NO  LOCAL      SYSTEM    NO  MANUAL DISABLED NOGUARANTEE NO
SYSAUX                               8192          65536                       1  2147483645                65536 ONLINE    PERMANENT LOGGING   NO  LOCAL         SYSTEM    NO  AUTO   DISABLED NOT APPLY   NO
TEMP                                 8192        1048576     1048576           1                     0    1048576 ONLINE    TEMPORARY NOLOGGING NO  LOCAL         UNIFORM   NO  MANUAL DISABLED NOT APPLY   NO
USERS                                8192          65536                       1  2147483645                65536 ONLINE    PERMANENT LOGGING   NO  LOCAL         SYSTEM    NO  AUTO   DISABLED NOT APPLY   NO








30.44.dba_tablespaces
30.44.1.View all the tablespaces in a database
30.44.2.select table_name, block_size from dba_tables dt, dba_tablespaces dtbs
30.44.3.Query Tablespace Information