Skip to Main Content
  • Questions
  • User's table not shown in DBA_TABLES

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, John.

Asked: March 09, 2016 - 4:17 pm UTC

Last updated: January 08, 2019 - 1:25 am UTC

Version: RDBMS 12.1

Viewed 10K+ times! This question is

You Asked

I have a curious dilemma...
To summarize, I have a user 'ABC' with a table 'POINTS'.
I can query DBA_OBJECTS to see that user's table, but that table does not appear in DBA_TABLES.
Is there some difference between these two system views?
SELECT *
 FROM DBA_TABLES
 WHERE TABLE_NAME='POINTS'
 AND OWNER='ABC';

versus
SELECT *
 FROM DBA_OBJECTS
 WHERE OBJECT_NAME='POINTS'
 AND OWNER='ABC'
 AND OBJECT_TYPE='TABLE';

I thought they ought to be equivalent.

Now, here are the details:
-- The table exists and contains data:
SELECT * FROM ABC.POINTS;

         X          Y
---------- ----------
         0          0
         0          3
         2          3

-- ABC.POINTS does not appear in DBA_TABLES
SELECT * FROM DBA_TABLES
 WHERE TABLE_NAME='POINTS'
 AND OWNER='ABC';

no rows selected


-- But, ABC.POINTS does appear in DBA_OBJECTS as a Table
SELECT * FROM DBA_OBJECTS
 WHERE OBJECT_NAME='POINTS'
 AND OBJECT_TYPE='TABLE'
 AND OWNER='ABC';

OWNER    OBJECT_NAME  SUBOBJECT_NAME   OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
-------- ------------ --------------- ---------- -------------- ----------- 
CREATED   LAST_DDL_ TIMESTAMP           STATUS  T G S 
--------- --------- ------------------- ------- - - -
NAMESPACE EDITION_NAME SHARING       E O
--------- ------------ ------------- - -
ABC      POINTS                           126236         126236 TABLE
09-NOV-15 09-NOV-15 2015-11-09:19:52:22 VALID   N N N
        1              NONE            N


-- There are no other objects in the database with the name POINTS
SELECT * FROM DBA_OBJECTS
 WHERE OBJECT_NAME='POINTS'
 AND OBJECT_ID <> 126236;

no rows selected


Any idea what may explain this?

Thank you for your support of us in the Oracle community. We appreciate your valuable time and sharing of your knowledge.

*** ADDITIONAL REQUESTED INFO ***
Oracle version 12.1.0.2.0

This is a plain vanilla database, single instance, non-CDB. It was originally created under 11.2, and was recently upgraded to 12.1 (this week).

I ran hcheck.sql as requested. This reported one warning on an object that doesn't appear to be related to the issue at hand:

H.Check Version 9i+/hc3.50
---------------------------------------
Catalog Version 12.1.0.2.0 (1201000200)
---------------------------------------

                                   Catalog       Fixed
Procedure Name                     Version    Vs Release      Run
------------------------------ ... ---------- -- ----------   ---
.- BadPublicObjects            ... 1201000200 <=  *All Rel* : Ok

HCKW-0014: Objects owned by PUBLIC
OBJ$ OBJ#=169297 TYPE=111 NAME=NON$CDB

Found 0 potential problem(s) and 1 warning(s)
Contact Oracle Support with the output
to check if the above needs attention or not

The problem object ID is 126236.

Any idea why DBA_Objects show this table, but DBA_Tables does not?

The "problem" doesn't appear to affect the applications running on this database. It was discovered during a routine post-upgrade check when comparing schema items against the test database.

*** MORE ADDITIONAL REQUESTED INFO ***
I ran each of these as sys:
select count(*) from sys.obj$ where obj# = 126236;
select count(*) from sys.tab$ where obj# = 126236;
select count(*) from sys.ts$ where ts# = (
   select t.ts# from sys.tab$ t where t.obj# = 126236 );

Each returned count=1.
I did some further digging on my own and found this about the POINTS table:
SELECT BITAND(PROPERTY,1) FROM SYS.TAB$ WHERE OBJ#=126236;

BITAND(PROPERTY,1)
------------------
                 1

According to the definition of system view SYS.DBA_TABLES, this property value disqualifies this table from appearing in the DBA_TABLES.
Furthermore, I queried the data dictionary for all table objects that have this property bit set.
select u.name,
       count(*)
 from sys.user$ u,
      sys.obj$ o,
      sys.tab$ t
 where u.user#=o.owner#
 and   t.obj#=o.obj#
 and   bitand(t.property, 1) = 1
 group by u.name;

NAME                   COUNT(*)
-------------------- ----------
MDSYS                         9
GSMADMIN_INTERNAL             2
XDB                          22
SYS                          14
WMSYS                         2
IEPORTAL                      1


There are a total of 50 objects in the database with this property bit set. From the names, all appear to be internal system objects, other than the one 'POINTS' table that I'm concerned about.

I was unable to find any information regarding the interpretation of the bit-flag SYS.TAB$.PROPERTY, so I'm at a dead end. I don't know whether this is an issue that needs support attention, or whether it might be entirely harmless.

Thanks again for your time helping me understand this anomaly.

and Connor said...

Ah...the penny drops. Bit 1 in property represents object type tables, eg

<code>
SQL> create or replace
2 type my_obj as object ( x int, y int );
3 /

Type created.

SQL> create table my_obj_tab of my_obj;

Table created.

SQL> select * from dba_tables
2 where table_name = 'MY_OBJ_TAB';

no rows selected
<code>

Hope this helps.

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

John Gasch, March 12, 2016 - 9:50 pm UTC

OK. Thanks.

So, just to wrap this up for the record, I now see the system views DBA_OBJECT_TABLES and DBA_TYPES.
SELECT t.OWNER,
       t.TABLE_NAME,
       t.OBJECT_ID_TYPE,
       t.TABLE_TYPE,
       ty.OWNER
   FROM DBA_TYPES ty,
        DBA_OBJECT_TABLES t
   WHERE t.TABLE_NAME='POINTS'
   AND   ty.TYPE_NAME = t.TABLE_TYPE;

OWNER      TABLE_NAME   OBJECT_ID_TYPE    TABLE_TYPE   OWNER
---------- ------------ ----------------- ------------ ----------
ABC        POINTS       SYSTEM GENERATED  POINT_TY     ABC



Now - A few nits for Oracle regarding this issue...

1) DBA_OBJECTS showed this object as being a 'TABLE'. For consistency, that should have returned 'OBJECT TABLE'. Or else there ought to be another field that could disambiguate this.
There is currently nothing I could find in DBA_OBJECTS that suggested that this table 'POINTS' was in fact not technically a "TABLE", which led to the confusion.

2) Of lesser concern - When creating an object table, perhaps the syntax should have been "CREATE OBJECT TABLE ..." versus "CREATE TABLE ...".

Anyway - Over and out. --JOHN

Connor McDonald
March 13, 2016 - 1:26 am UTC

Thanks for getting back to us. Yes, I'm not sure the rationale on why they are not shown in DBA_TABLES, but we are where we are :-)

How to get rid of OBJECT TABLE?

Dieter from Germany, January 07, 2019 - 8:37 am UTC

Hello,

in one of our test databases (Oracle Database 12c Standard Edition Release 12.2.0.1.0), one of my colleagues created approx. 600 types and also object tables.
We were able to drop the types, but we cannot drop the object tables because of ORA-22914: DROP of nested tables not supported.

The object tables show up in user_objects with object_type 'TABLE', but they are not listed in the user_tables view.

TIA
Connor McDonald
January 08, 2019 - 1:25 am UTC

Check out DBA_OBJECT_TABLES

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library