Oracle dba_tables tablespace_name is null

当查询dba_tables的时候,发现有一些表的tablespace为空.

a.哪些表不能显示出表空间?
SQL>select owner,table_name,tablespace_name from dba_tables where owner='HR';
OWNER                TABLE_NAME                     TABLESPACE_NAME
-------------------- ------------------------------ --------------------
HR                   MGMT_TS4                       TSMGMT_TS4
HR                   MGMT_TS1                       TSMGMT_TS1
HR                   T                              USERS
HR                   T_20120607                     USERS
HR                   T_20120607_002                 USERS
HR                   T_20120607_003                 USERS
HR                   TEST_A                         USERS
HR                   MGMT_TS2                       TSMGMT_TS2
HR                   MGMT_TS3                       TSMGMT_TS3
HR                   LEFTTB                         USERS
HR                   D2                             USERS
HR                   TEST                           USERS
HR                   MYTABLE_1                      EXAMPLE
HR                   REGIONS                        EXAMPLE
HR                   LOCATIONS                      EXAMPLE
HR                   DEPARTMENTS                    EXAMPLE
HR                   JOBS                           EXAMPLE
HR                   EMPLOYEES                      EXAMPLE
HR                   JOB_HISTORY                    EXAMPLE
HR                   TEST1                          USERS
HR                   TEST_B                         USERS
HR                   ACCOUNTS                       USERS
HR                   SALES
HR                   COUNTRY
HR                   SYS_TEMP_FBT
HR                   COUNTRIES
26 rows selected.

SQL>select owner,table_name,PARTITIONED,IOT_TYPE,TEMPORARY
    from dba_tables a where tablespace_name is null and owner='HR'
    order by PARTITIONED,IOT_TYPE,TEMPORARY;
OWNER                TABLE_NAME                     PAR IOT_TYPE     T
-------------------- ------------------------------ --- ------------ -
HR                   COUNTRIES                      NO  IOT          N
HR                   COUNTRY                        NO  IOT          N
HR                   SYS_TEMP_FBT                   NO               Y
HR                   SALES                          YES              N

发现IOT表,临时表,分区表是以上这些不能显示tablespace的表.

b. 查询表空间

临时表的表空间为临时表空间;

分区表的表空间可以通过dba_tab_partition查询
SQL>select table_owner,table_name,partition_name,partition_position,tablespace_name from dba_tab_partitions where table_owner='HR';
TABLE_OWNER                    TABLE_NAME                     PARTITION_ PARTITION_POSITION TABLESPACE_NAME
------------------------------ ------------------------------ ---------- ------------------ --------------------
HR                             SALES                          P3                          3 USERS
HR                             SALES                          P2                          2 USERS
HR                             SALES                          P1                          1 USERS
HR                             SALES                          P4                          4 USERS

IOT表无法在dba_segment中找到,它的信息在索引中,索引记录就是表记录,需要单独研究一下.
SQL>select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,bytes/1024/1024||' MB' bytes from dba_segments where owner='HR' and segment_name like '%COUNTR%'
OWNER                SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME      BYTES
-------------------- ------------------------------ ------------------ -------------------- --------------------
HR                   COUNTRY_ID_PK                  INDEX              USERS                .0625 MB//表country主键约束
HR                   LOC_COUNTRY_IX                 INDEX              EXAMPLE              .0625 MB//表countries主键非空约束
HR                   COUNTRY_C_ID_PK                INDEX              EXAMPLE              .0625 MB//表countries主键约束

SQL>select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLE_TYPE,TABLESPACE_NAME from dba_indexes where owner='HR' AND TABLE_NAME='COUNTRIES'
INDEX_NAME      INDEX_TYPE  TABLE_OWNER          TABLE_NAME        TABLE_TYPE  TABLESPACE_NAME
--------------- ----------- -------------------- ----------------- ----------- --------------------
COUNTRY_C_ID_PK IOT - TOP   HR                   COUNTRIES         TABLE       EXAMPLE
这里表countries的记录存储在索引country_c_id_pk这个索引段,即索引就是表,表就是索引.