Maintaining Object Types and Object Views
If you work much with object types, you will learn a number of ways to get information about the types and views that you have created. Once you reach the limits of the SQL*Plus DESCRIBE command, this could involve direct queries from the Oracle data dictionary.
The dictionary term for user-defined types (objects and collections) is simply TYPE. Object type definitions and object type bodies are both found in the USER_SOURCE view (or DBA_SOURCE, or ALL_SOURCE), just as package specifications and bodies are. Table 21-4 lists a number of helpful queries you can use.
| Table 21-4. Data dictionary entries for object types | |
| To answer the question... | Use a query such as |
| What object and collection types have I created? | SELECT * FROM user_types;SELECT * FROM user_objects WHERE object_type = 'TYPE'; |
| What do my object type hierarchies look like? | SELECT RPAD(' ', 3*(LEVEL-1)) || type_name FROM user_types WHERE typecode = 'OBJECT' CONNECT BY PRIOR type_name = supertype_name; |
| What are the attributes of type foo? | SELECT * FROM user_type_attrs WHERE type_name = 'FOO'; |
| What are the methods of type foo? | SELECT * FROM user_type_methods WHERE type_name = 'FOO'; |
| What are the parameters of foo's methods? | SELECT * FROM user_method_params WHERE type_name = 'FOO'; |
| What datatype is returned by foo's method called bar? | SELECT * FROM user_method_results WHERE type_name = 'FOO' AND method_name = 'BAR'; |
| What is the source code for foo, including all ALTER statements? | SELECT text FROM user_source WHERE name = 'FOO' AND type = 'TYPE' /* or 'TYPE BODY' */ ORDER BY line; |
| What are the object tables that implement foo? | SELECT table_name FROM user_object_tables WHERE table_type = 'FOO'; |
| What are all the columns in an object table foo_tab, including the hidden ones? | SELECT column_name, data_type, hidden_column, virtual_column FROM user_tab_cols WHERE table_name = 'FOO_TAB'; |
| What columns implement foo? | SELECT table_name, column_name FROM user_tab_columns WHERE data_type = 'FOO'; |
| What database objects depend on foo? | SELECT name, type FROM user_dependencies WHERE referenced_name = 'FOO'; |
| What object views have I created, using what OIDs? | SELECT view_name, view_type, oid_text FROM user_views WHERE type_text IS NOT NULL; |
| What does my view hierarchy look like? (Requires a temporary table because you can't use a subquery with CONNECT BY) | CREATE TABLE uvtemp AS SELECT v.view_name, v.view_type, v.superview_name, v1.view_type superview_type FROM user_views v, user_views v1 WHERE v.superview_name = v1.view_name (+);SELECT RPAD(' ', 3*(LEVEL-1)) || view_name || ' (' || view_type || ') ' FROM uvtemp CONNECT BY PRIOR view_type = superview_type;DROP TABLE uvtemp; |
| What is the query on which I defined the foo_v view? | SET LONG 1000 -- or greaterSELECT text FROM user_views WHERE view_name = 'FOO_V'; |
| What columns are in view foo_v? | SELECT column_name, data_type_mod, data_type FROM user_tab_columns WHERE table_name = 'FOO_V'; |
One potentially confusing thing Oracle has done in the data dictionary is to make object tables invisible from the USER_TABLES view. Instead, a list of object tables appears in USER_OBJECT_TABLES (as well as in USER_ALL_TABLES).