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

Наши рекомендации