Oracle object types & object tables

Question

Are there any tables of interest regarding a connection between object tables & objects types? I mean tables from the dictionary (DICT).

I looked into these 2:

SELECT * FROM USER_OBJECTS
SELECT * FROM USER_OBJECT_TABLES

What I am interested is to find out if object table 'A' contains/can contain object type 'b' instances? Obviously table 'A' is table of object 'a' and 'b' is under 'a'.

Have any ideas? Advices? Should i resume to a query on my table 'A'? And if i should, how do I find out if selected object from table 'A' is a instance of 'a' or 'b'?

Answer 1

I'm not sure what you're trying, but there is the USER_TYPES dictionary view that holds the hierarchy of the objects, so you can write some sql query like (example is not perfect, I know):

select ot.table_name, tt.type_name
  from (
select t.type_name,
       connect_by_root t.supertype_name rkey
  from user_types t
connect by prior t.type_name = t.supertype_name ) tt,
       user_object_tables ot
       where ot.table_type = tt.type_name or ot.table_type = rkey

and find all the types which are under the type of the table