1

I have 2 queries

 SELECT *
 FROM sys.dba_indexes
 WHERE Table_Name = 'XDB$RESOURCE'

and

 SELECT *
 FROM sys.dba_tables 
 WHERE Table_Name = 'XDB$RESOURCE'

Are there any reasons why a table wouldn't show in sys.dba_tables? As far as I've read all tables should show here, hence why I'm using this rather than all_tables.

Can an index in Oracle be against a deleted table?

Does sys.dba_tables show data relevant to access you have, and if so is there any table that rather brings back nothing than half the data?

Relatively new to Oracle, any explanation or advice appreciated.

5
+500

You are right. XDB$RESOURCE is not in DBA_TABLES. It is in DBA_OBJECT_TABLES, because it is an object table.

Object tables are listed in {DBA,ALL,USER}_OBJECT_TABLES. All regular (non-object) tables are in {DBA,ALL,USER}_TABLES.

0
2

Does sys.dba_tables show data relevant to access you have ...

No. The DBA_ views should only be accessible to Database Administrators who, by definition, have access to [pretty much] everything.

Can an index in oracle be against a deleted table?

Yes. By default, a dropped table goes first into the Recycle Bin (basically it gets renamed) and only get properly deleted from there so any indexes attached to the original table will go with it, into the Bin.

Why doesn't this one show up in DBA_TABLES?
No idea, I'm sorry to say.

2

A Work colleague pointed out the following to me, for anyone else that stumbles upon this question, use DBA_ALL_TABLES

Explanation Lodewicus thanks for the help :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.