List all objects in a tablespace

set pages 999
col owner format a15
col owner format a7
col segment_name format a40
col segment_type format a13

select d.tablespace_name, d.owner, d.segment_name, d.segment_type
  from dba_segments d
 where tablespace_name = 'USERS'
   and d.owner = 'HR'
 order by d.owner, d.segment_name
/

Результат:

TABLESPACE_NAME      OWNER   SEGMENT_NAME                             SEGMENT_TYPE
-------------------- ------- ---------------------------------------- -------------
USERS                HR      BIG_ORDERS                               TABLE
USERS                HR      BIN$+0AeybK+SRmxaNEjrULGkw==$0           TABLE
USERS                HR      EMPLOYEES_TEMP                           TABLE
USERS                HR      EMP_TEMP                                 TABLE
USERS                HR      LOCK_HOLDERS                             TABLE
USERS                HR      LOGS_TAB                                 TABLE
USERS                HR      PARTS1                                   TABLE
USERS                HR      PARTS2                                   TABLE
USERS                HR      REJECTED_ORDERS                          TABLE
USERS                HR      VALID_ORDERS                             TABLE
 
10 rows selected

Источник.