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.
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.