Friday, March 04, 2016

Find Tablespaces for Oracle Tables

We may run the following query from SYS to find the tablespace names for all tables, including IOTs (index organized table) and partitioned tables (replacing 'DMUSER' with your user name).

select  u.name owner, o.name table_name,t.name tablspace_name 
from
 obj$ o,  ts$ t, sys_objects s, user$ u
where o.obj#=s.object_id and s.ts_number=t.ts#
      and o.owner#=u.user# 
      and o.type#=2
      and u.name='DMUSER'
order by 1,2;
OWNER     TABLE_NAME              TABLSPACE_NAME
-------------------------------- -------------------------------- 
DMUSER     AI_EXPLAIN_OUTPUT         TBS_1
DMUSER     AR_SH_SAMPLE_SETTINGS     TBS_1
DMUSER     DM$P0AR_SH_SAMPLE         TBS_1
DMUSER     DM$P0AR_SH_SAMPLE_2COL    TBS_1
DMUSER     DM$P0EM_SH_CLUS_SAMPLE    TBS_1
DMUSER     DM$P0NB_SH_CLAS_SAMPLE    TBS_1
DMUSER     DM$P0OC_SH_CLUS_SAMPLE    TBS_1
DMUSER     DM$P1EM_SH_CLUS_SAMPLE    TBS_1
DMUSER     DM$P1NB_SH_CLAS_SAMPLE    TBS_1
DMUSER     DM$P1OC_SH_CLUS_SAMPLE    TBS_1

No comments: