Key Metadata Tables/Views In Oracle Applications


The following are some views a PL/SQL developer is most likely to find useful:

USER_DEPENDENCIES
Stores the dependencies to and from the objects a current user owns. This view is mostly used by Oracle to mark objects INVALID when necessary, and also by IDEs to display the dependency information in their object browsers.

USER_ERRORS
Stores the current set of errors for all stored objects a current user owns. This view is accessed by the SHOW ERRORS SQL*Plus command.

USER_OBJECTS
Displays the objects owned by the current user. You can, for instance, use this view to see if an object is marked INVALID, find all the packages that have “DEPT” in their names, etc.

USER_OBJECT_SIZE
Displays the size of the objects owned by the current user. Actually, this view will show you the source, parsed, and compile sizes for your code. Use it to identify the large programs in your environment, good candidates for pinning into the SGA.

USER_PLSQL_OBJECT_SETTINGS
(Introduced in Oracle Database 10g Release 1) Shows Information about the characteristics of a PL/SQL object that can be modified through the ALTER and SET DDL commands, such as the optimization level, debug settings, and more.

USER_PROCEDURES
(Introduced in Oracle9i Database Release 1) Shows Information about stored programs, such as the AUTHID setting, whether the program was defined as DETERMINISTIC, and so on.

USER_SOURCE
Shows the text source code for all objects you own (in Oracle9i Database and above, including database triggers and Java source). This is a very handy view, because you can run all sorts of analysis of the source code against it using SQL and, in particular, Oracle Text.


USER_ARGUMENTS
Shows the arguments (parameters) in all the procedures and functions in your schema.

USER_TRIGGERS and USER_TRIG_COLUMNS
Displays the database triggers owned by current user, and any columns identified with the triggers. You can write programs against this view to enable or disable triggers for a particular table.







No comments:

Post a Comment

How to find all cancel Requisitions

SELECT prha . *   FROM po_Requisition_headers_all prha , po_action_history pah   WHERE      1 = 1        AND pah . object_id ...