Table missing in DBA_TABLES dictionary view

Today I learned something new about DBA_TABLES and DBA_ALL_TABLES dictionary views.

During some PL/SQL programming I noticed that several tables exist in DBA_SEGMENTS and DBA_OBJECTS views but not in DBA_TABLES.

That confused me a little but I found answer quickly after posting question on OTN forums. User named "admin" helped me with that.


Example:

select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
PL/SQL Release 9.2.0.6.0 - Production
CORE 9.2.0.6.0 Production
TNS for Solaris: Version 9.2.0.6.0 - Production
NLSRTL Version 9.2.0.6.0 - Production
 
5 rows selected.
 
 
select * from dba_segments where segment_name = 'TMP_WEB_DATA';
 
OWNER : TST
SEGMENT_NAME : TMP_WEB_DATA
PARTITION_NAME :
SEGMENT_TYPE : TABLE
TABLESPACE_NAME : TST_DATA
HEADER_FILE : 3
HEADER_BLOCK : 514179
BYTES : 65536
BLOCKS : 8
EXTENTS : 1
INITIAL_EXTENT : 65536
NEXT_EXTENT :
MIN_EXTENTS : 1
MAX_EXTENTS : 2147483645
PCT_INCREASE :
FREELISTS :
FREELIST_GROUPS :
RELATIVE_FNO : 3
BUFFER_POOL : DEFAULT
-----------------


select * from dba_objects where object_name = 'TMP_WEB_DATA';
 
OWNER : TST
OBJECT_NAME : TMP_WEB_DATA
SUBOBJECT_NAME :
OBJECT_ID : 42663
DATA_OBJECT_ID : 42663
OBJECT_TYPE : TABLE
CREATED : 23.05.2007 17:05:20
LAST_DDL_TIME : 19.12.2008 14:05:20
TIMESTAMP : 2007-05-23:17:05:20
STATUS : VALID
TEMPORARY : N
GENERATED : N
SECONDARY : N
-----------------
 

select * from dba_tables where table_name = 'TMP_WEB_DATA';
 
no rows selected
-----------------


select count(*) from TST.TMP_WEB_DATA;
 
COUNT(*)
----------
1

The answer lies in dictionary view DBA_ALL_TABLES.

select * from dba_all_tables where table_name = 'TMP_WEB_DATA';

OWNER                         : TST
TABLE_NAME                    : TMP_WEB_DATA
TABLESPACE_NAME               : TST_DATA
CLUSTER_NAME                  :
IOT_NAME                      :
PCT_FREE                      : 10
PCT_USED                      :
INI_TRANS                     : 1
MAX_TRANS                     : 255
INITIAL_EXTENT                : 65536
NEXT_EXTENT                   :
MIN_EXTENTS                   : 1
MAX_EXTENTS                   : 2147483645
PCT_INCREASE                  :
FREELISTS                     :
FREELIST_GROUPS               :
LOGGING                       : YES
BACKED_UP                     : N
NUM_ROWS                      :
BLOCKS                        :
EMPTY_BLOCKS                  :
AVG_SPACE                     :
CHAIN_CNT                     :
AVG_ROW_LEN                   :
AVG_SPACE_FREELIST_BLOCKS     :
NUM_FREELIST_BLOCKS           :
DEGREE                        :          1
INSTANCES                     :          1
CACHE                         :     N
TABLE_LOCK                    : ENABLED
SAMPLE_SIZE                   :
LAST_ANALYZED                 :
PARTITIONED                   : NO
IOT_TYPE                      :
OBJECT_ID_TYPE                : SYSTEM GENERATED
TABLE_TYPE_OWNER              : TST
TABLE_TYPE                    : PARTNER_WEB_DATA
TEMPORARY                     : N
SECONDARY                     : N
NESTED                        : NO
BUFFER_POOL                   : DEFAULT
ROW_MOVEMENT                  : DISABLED
GLOBAL_STATS                  : NO
USER_STATS                    : NO
DURATION                      :
SKIP_CORRUPT                  : DISABLED
MONITORING                    : NO
CLUSTER_OWNER                 :
DEPENDENCIES                  : DISABLED
COMPRESSION                   : DISABLED
-----------------

When I peeked in documentation for explanation:

- DBA_TABLES describes all relational tables in the database. Its columns are the same as those in ALL_TABLES. To gather statistics for this view, use the ANALYZE SQL statement.

- DBA_ALL_TABLES describes all object tables and relational tables in the database. Its columns are the same as those in ALL_ALL_TABLES.


DDL of TMP_WEB_DATA:

CREATE TABLE "TST"."TMP_WEB_DATA" OF "TST"."PARTNER_WEB_DATA"
OIDINDEX ( PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TST_DATA" )
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "TST_DATA" ;

So TMP_WEB_DATA is object table and this is the reason why it was missing from DBA_TABLES view.

It's nice to know that :)