Show All Tablespaces

if you want to get a list of all tablespaces used in the current database instance, you can use the DBA_TABLESPACES view as shown in the following SQL script example:

SQL> connect SYSTEM/fyicenter
Connected.

SQL> SELECT TABLESPACE_NAME, STATUS, CONTENTS
  2  FROM USER_TABLESPACES;
TABLESPACE_NAME                STATUS    CONTENTS
------------------------------ --------- ---------
SYSTEM                         ONLINE    PERMANENT
UNDO                           ONLINE    UNDO
SYSAUX                         ONLINE    PERMANENT
TEMP                           ONLINE    TEMPORARY
USERS                          ONLINE    PERMANENT

SQL> select owner, table_name, tablespace_name
  2        from dba_tables
  3       where owner in ('SCOTT', 'HR')
  4       order by owner, tablespace_name, table_name;

OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ ----------------
HR                             DEPARTMENTS                    USERS
HR                             EMPLOYEES                      USERS
HR                             JOBS                           USERS
HR                             JOB_HISTORY                    USERS
HR                             LOCATIONS                      USERS
HR                             REGIONS                        USERS
HR                             COUNTRIES

7 rows selected.