Oracle PL/SQL Tutorial/System Tables Data Dictionary/dba tablespaces

Query Tablespace Information

SQL> select * from dba_tablespaces;  -- Tablespace Information
  2

select table_name, block_size from dba_tables dt, dba_tablespaces dtbs

SQL> select table_name, block_size from dba_tables dt, dba_tablespaces dtbs
  2  where dt.tablespace_name = dtbs.tablespace_name and dt.table_name = "ATM_DATA";
no rows selected
SQL>
SQL>
SQL>

View all the tablespaces in a database

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