10.6 How do I…List information about stored objects in the database?Problem
I want to list information about stored objects owned by a particular user account. I also need to view the size of subprograms and the associated source code. To ensure that they are compiled in the right order, I need to know dependencies between them. How do I list information about stored objects in the database?
Technique
The USER_OBJECTS, USER_OBJECT_SIZE, USER_SOURCE, USER_DEPEND-ENCIES, and USER_ERRORS data dictionary views supply a lot of information about stored objects created by a user account. To query objects (to which you have the necessary access privileges) created by a different user account, replace USER_ with ALL_ for the view name in your query and specify the OWNER in the WHERE clause of the query.
The STATUS column of the USER_OBJECTS view tells you whether a given object is valid. Query the USER_OBJECT_SIZE view to show all stored objects in your schema and their sizing data. The TEXT column of the USER_SOURCE view contains a row for each line of source code in the stored module, and the LINE column represents the line number in the module. As the name suggests, a query on the USER_DEPENDENCIES view provides information about object dependencies in the system. From the REFERENCED_NAME column, a list of names of all objects that are referenced by a particular stored object can be found. The USER_ERRORS view can be queried to look up error information for modules that failed to compile. In all these views (except the USER_OBJECTS view), the NAME column represents the name of the stored object, and the TYPE column denotes the type of stored module. Object types for stored modules are PROCEDURE, FUNCTION, PACKAGE, and PACKAGE BODY. In the USER_OBJECTS view, the corresponding columns are named OBJECT_NAME and OBJECT_TYPE.
Steps
1. Run SQL*Plus and connect as the WAITE user account. It is assumed that you have run the files from the previous How-To’s in this chapter. CHP10_21.SQL, as shown in Figure 10.21, queries the USER_OBJECTS data dictionary view to display stored object information if it is a function, procedure, package specification, or a package body. Columns of the USER_OBJECTS view are listed in Table 10.5.
Line 1 specifies that the name of the object, its type, and state to be listed for each record are returned by the query. Line 2 specifies that the USER_OBJECTS view is the data source. The WHERE clause in line 3 specifies that object type should be FUNCTION, PROCEDURE, PACKAGE, or PACKAGE BODY.
Column Description OBJECT_NAME Name of the object SUBOBJECT_NAME Name of the subobject, if it is a subobject OBJECT_ID A unique object number DATA_OBJECT_ID Same as the object number for TABLE and INDEX object types OBJECT_TYPE Type of object: FUNCTION, PROCEDURE, PACKAGE, PACKAGE BODY, TRIGGER, TABLE, INDEX, SEQUENCE, SYNONYM CREATED Timestamp telling when the object was created as a DATE column LAST_DDL_TIME Timestamp telling when a DDL command was last applied to the object TIMESTAMP Timestamp for creation of the object as a VARCHAR2 column STATUS Object status: VALID or INVALID TEMPORARY Indicates whether the object is temporary (Y or N) GENERATED Indicates whether the object was generated by the system (Y or N) 2. Run the CHP10_22.SQL file in SQL*Plus. The size of the source code, parsed code, compiled code, and error code (if there is a compilation error) is queried from the USER_OBJECT_SIZE view. The code and results are shown in Figure 10.22. Columns of the USER_OBJECT_SIZE view are listed in Table 10.6.
Lines 1 and 2 return all size columns for each record returned by the query and also return the total of all size columns as a separate column. Line 3 specifies that USER_OBJECT_SIZE view is the data source. The total amount of space in bytes taken up by the stored module in the USER_DATA table space is summed up under the TOTAL_SIZE column.
Table 10.6 Columns of the USER_OBJECT_SIZE view
Column Description NAME Name of the object TYPE Type of the object: FUNCTION, PROCEDURE, PACKAGE, PACKAGE BODY SOURCE_SIZE Size of the source code (in bytes) PARSED_SIZE Size of the parsed code (in bytes) CODE_SIZE Size of the compiled code (in bytes) ERROR_SIZE Size of the error messages (in bytes) 3. The CHP10_23.SQL file contains a query to the USER_SOURCE data dictionary view to display the source code of the sample stored procedure created in step 1 of How-To 10.3. The USER_SOURCE view contains a row for each line of source code in the TEXT column. Run the query to display the results, as shown in Figure 10.23. Columns of the USER_SOURCE view are listed in Table 10.7.
Line 1 returns the line number and a single line of code for each record returned by the query. Line 2 specifies that USER_SOURCE view is the data source. The WHERE clause in line 3 specifies that source code for the sample stored procedure created in step 1 of How-To 10.3 will be returned.
Table 10.7 Columns of the USER_SOURCE view.
The code contained in the sample stored procedure is displayed exactly as it was entered. The case of the characters and the code indentation is preserved in the USER_SOURCE data dictionary view.
Column Description NAME Name of the object TYPE Type of the object: FUNCTION, PROCEDURE, PACKAGE, PACKAGE BODY LINE Line number in the source code TEXT Text of a line in the source code. 4. Run the CHP10_24.SQL file in SQL*Plus. The file first creates a stored procedure that references a stored procedure created in Step 1 of How-To 10.3, and then a SELECT statement to query the USER_DEPENDENCIES table and to list all objects referenced by a dependent object specified by the user in SQL*Plus. The code and results are shown in Figure 10.24. Columns of the USER_DEPENDENCIES view are listed in Table 10.8.
TYPE Type of the object: FUNCTION, PROCEDURE, PACKAGE, PACKAGE BODY REFERENCED_OWNER Owner of the referenced object REFERENCED_NAME Name of the referenced object REFERENCED_TYPE Object type of the referenced object REFERENCED_LINK_NAME Name of link to the remote referenced object SCHEMAID Identity of the schema of the object How It Works
The USER_OBJECTS view lists information about all objects in the user’s schema. Step 1 queries the USER_OBJECTS view to list the name, type, and status of PL/SQL modules created in the previous How-To’s. Step 2 uses the USER_OBJECT_SIZE view to find out the code size of stored modules. Step 3 queries the USER_SOURCE view to display the source code of a stored module. Step 4 lists objects referenced by a particular object by querying the USER_DEPENDENCIES view.
Comments
As mentioned earlier in a note in How-To 10.3, if a referenced object has changed, the dependent object is invalidated. Oracle automatically tries to recompile an invalid module before referencing it. This runtime compilation of invalidated modules can result in degradation of performance. To avoid automatic recompilation of invalid modules, you can manually compile them beforehand by using information from the USER_OBJECTS view. How-To 10.13 presents a technique used to recompile stored modules.
When a stored module is called, Oracle checks to see whether it is present in the SGA. If not, it loads the compiled code in the SGA. If the available space in the SGA is insufficient, Oracle swaps some modules existing in the SGA disk to make room for loading the called stored module. If you have large modules in the system, frequent swapping might occur, which can severely affect the performance of the system because of increased I/O. Occasionally, you might run out of buffer space while compiling immoderately sized source code. Care should be taken to identify overly large modules and modularize them into smaller units. The USER_OBJECT_SIZE comes in handy if you need to find out information about the size of modules in the system.
The source code contained in the data dictionary always contains the same code used during the latest compilation of a module. If you have questions about the validity of the source files used to create the stored modules, query the USER_SOURCE view to compare the source code. The SQL*Plus LIST command can display the source code of the last module compiled in the current session, as long as you have not executed any other SQL statements after the compilation.
To keep track of dependencies between stored objects in the database, the USER_DEPENDENCIES view can be used to see any objects that reference or depend on a particular object. In a complex system, this information can be used to build DDL scripts that compile all dependent stored modules whenever a referenced stored module is modified.
The SQL*Plus SHOW ERRORS command shows errors only for the most recent stored module that you attempted to compile. If a SQL script is used to create or compile stored modules, the command will not show errors generated for all modules. Thus, it is a good programming practice to have a SHOW ERRORS command in the script after each module compilation. The USER_ERRORS view can also be queried to show errors for all modules that were compiled.