PL/SQL-related data dictionary and plsql-related dictionary

PL/SQL-related data dictionary and plsql-related dictionary

Data Dictionary related to PL/SQL
Http://www.oracle.com/technetwork/issue-archive/2012/12-nov/o62plsql-1851968.html

Sometimes, we encounter the following problems during PL/SQL development:
1) What database objects does my program depend on?
2) which package calls a subroutine or variable in another package?
3) which of the following child program parameters uses an inappropriate data type?
4) does all of my subprograms use an appropriate optimization level?

The silly way is to search for the code...
Smart people use the following data dictionary views:

[USER_ARGUMENTS]: Contains parameters of all processes and functions in a mode.
[USER_DEPENDENCIES]: Contains dependencies between objects you own. This view is most often used by Oracle to identify invalid database objects (when the objects on which this object depends change ).
[USER_ERRORS]: Contains compilation errors for all the stored objects you own. This view can be read by the SQL * Plus command: SHOW ERRORS.
[USER_IDENTIFIERS]: 11 GB introduced and filled by PL/Scope compilation tools. Once filled, this view will contain all identifiers-program names, variables, and so on.
[USER_OBJECT_SIZE]: contains the object size. In fact, this view shows the size of the original, parsed, and compiled code. Despite being diligently used by compilers and runtime, you can also find large programs in the environment.
[USER_OBJECTS]: contains information about all objects in one mode. You can check whether the object is valid and find all packages containing the EMP name.
[USER_PLSQL_OBJECT_SETTINGS]: Information about the characteristics-such as the optimization level and debug settings-of a PL/SQL object that can be modified through the ALTER and SET DDL commands.
[USER_PROCEDURES]: contains information about stored programs, such as AUTHID settings. A program is defined as a DETERMINISTIC feature.
[USER_SOURCE]: contains the text source code of all objects. A very convenient view, because you can perform various analyses on the source code.
[USER_STORED_SETTINGS]: PL/SQL Compiler flag. Use this view to check which program uses native compilation ).
[USER_TRIGGERS] and [USER_TRIGGER_COLS]: The database triggers you own (including the source code and a description of the triggering event) and any columns identified with the triggers, respectively. you can write programs against USER_TRIGGERS to enable or disable triggers for a special table.

1. Data Dictionary Basics
A data dictionary is composed of tables and views created by multiple instances. You can only query data dictionaries.
The vast majority of data fields consist of three versions:
1) USER view, which starts with "USER _" and contains information about database objects owned by currently logged-on users.
2) ALL views, starting with ALL _, contain information about the database objects read by the currently logged-on user.
3) Administrator view, which starts with DBA _. This type of view contains information about all database objects in an instance. Common users generally do not have access to this view.

For example:
SELECT * FROM user_objects;-information about all database objects that I own
SELECT * FROM all_objects;-I have the right to read the database object information
SELECT * FROM dba_objects;-object information of the entire database that the administrator can access

2. display the storage Object Information USER_OBJECTS
Including columns (English is too simple to translate ):
OBJECT_NAME: Name of the object

OBJECT_TYPE: Type of the object, such as PACKAGE, FUNCTION, or TRIGGER

STATUS: Status of the object-VALID or INVALID

LAST_DDL_TIME: Time stamp indicating the last time this object was changed

Here are several examples:

1) show all tables in my mode:

SELECT object_name FROM user_objects WHERE object_type = 'TABLE' ORDER BY object_name; 

2) display all Invalid object names:

SELECT object_type, object_name FROM user_objects WHERE status = 'INVALID' ORDER BY object_type, object_name; 

3) display all objects modified today:

SELECT object_type, object_name, last_ddl_time FROM user_objects WHERE last_ddl_time >= TRUNC (SYSDATE) ORDER BY object_type, object_name 

3. Search and display the source code USER_SOURCE
Column introduction:
NAME: Name of the object

TYPE: Type of the object (ranging from PL/SQL program units to Java source and trigger source)

LINE: Number of the line of the source code

TEXT: Text of the source code

For example, I need to change the parameter list of the CALC_TOTALS process in the package SALES_MGR. I want to find out where the process is called.

SELECT name, line, text FROM user_source WHERE UPPER (text) LIKE '%SALES_MGR.CALC_TOTALS%' ORDER BY name, line 

Of course, this query may also be identified by comments, and strings that do not conform to the LIKE format will not be retrieved, for example:
SALES_MGR.
CALC_TOTALS

Assuming that our code is relatively standard, this query is still doing a good job.
In addition, for 11g, you can use the PL/Scope feature.

4. storage code compilation settings USER_PLSQL_OBJECT_SETTINGS
PLSQL_OPTIMIZE_LEVEL: The optimization level of the compiled object

PLSQL_CODE_TYPE: Object compilation Mode

PLSQL_DEBUG: Whether or not the object was compiled for debugging Whether the object is compiled for debugging

PLSQL_WARNINGS: Compilation warning settings for compiled objects

NLS_LENGTH_SEMANTICS: NLS length semantics that were used to compile the object compilation object semantic length settings

Find out all program units that are not optimized when effective compilation is used:

SELECT name
FROM user_plsql_object_settings
WHERE plsql_optimize_level <2

Level 0 indicates no optimization is taken. 1 indicates the minimum optimization. Neither of them should exist in the production environment.

Find the programs that disable the compile-time warning.

SELECT name, plsql_warnings FROM user_plsql_object_settings WHERE plsql_warnings LIKE '%DISABLE%'; 

5. Detailed information about procedures and functions USER_PROCEDURES
AUTHID: Shows whether a procedure or a function is defined as an invoker rights (CURRENT_USER) or definer rights (DEFINER) program unit caller permission or definer permission

DETERMINISTIC: Set to YES if the function is defined to be deterministic, which theoretically means that the value returned by the function is determined completely by the function's argument values is DETERMINISTIC

PIPELINED: Set to YES if the function is defined as a pipelined function, which means that it can be executed in parallel as part of a parallel query whether the Pipeline function

OVERLOAD: Set to a positive number if this subprogram is overloaded, which means that there are at least two subprograms with this name in the same package whether to OVERLOAD

Find all the processes and functions that run with the caller permission

SELECT object_name , procedure_name FROM user_procedures WHERE authid = 'CURRENT_USER' ORDER BY object_name, procedure_name 

Show all declared deterministic functions:

SELECT object_name , procedure_name FROM user_procedures WHERE deterministic = 'YES' ORDER BY object_name, procedure_name 

6. Analyze and modify the trigger status USER_TRIGGERS
TRIGGER_NAME: The name of the trigger

TRIGGER_TYPE: A string that shows if this is a BEFORE or AFTER trigger and whether it is a row-or statement-level trigger (in a trigger that is fired before an INSERT statement, for example, the value of this column is before statement)

TRIGGERING_EVENT: The type of SQL operation-such as INSERT, INSERT OR UPDATE, DELETE OR UPDATE-that will cause the trigger to fire

TABLE_NAME: The name of the table on which the trigger is defined

STATUS: The status of the trigger-ENABLED or DISABLED

WHEN_CLAUSE: An optional clause you can use to avoid unnecessary execution of the trigger body

TRIGGER_BODY: The code executed when the trigger fires

Find all disabled triggers:

SELECT * FROM user_triggers WHERE status = 'DISABLED' 

Find all row-level triggers defined on the EMPLOYEES table:

SELECT * FROM user_triggers WHERE table_name = 'EMPLOYEES' AND trigger_type LIKE '%EACH ROW' 

Find all triggers that fire when an UPDATE operation is completed MED:
Find all the triggers triggered by the update operation.

SELECT * FROM user_triggers WHERE triggering_event LIKE '%UPDATE%' 

7. Object dependency analysis USER_DEPENDENCIES
NAME: Name of the object

TYPE: Type of the object

REFERENCED_OWNER: Owner of the referenced object

REFERENCED_NAME: Name of the referenced object Name

REFERENCED_TYPE: Type of the referenced object

Find all objects dependent on the EMPLOYEES table:

SELECT type, name FROM user_dependencies WHERE referenced_name = 'EMPLOYEES' ORDER BY type, name 

Find all the objects that the ORDER_MGR package depends on in the current mode.

SELECT referenced_type , referenced_name FROM user_dependencies WHERE name = 'ORDER_MGR' AND referenced_owner = USER ORDER BY referenced_type, referenced_name 

8. Analyze the parameter information USER_ARGUMENTS
OBJECT_NAME: The name of the procedure or function

PACKAGE_NAME: The name of the package in which the procedure or function is defined

ARGUMENT_NAME: The name of the argument

POSITION: The position of the argument in the parameter list (if 0, this is the RETURN clause of a function)

IN_OUT: The mode of the argument-IN, OUT, or IN OUT

DATA_TYPE: The datatype of the argument

DATA_LEVEL: The nesting depth of the argument for composite types (for example, if one of your arguments 'pes ypes is a record, USER_ARGUMENTS will have a row for this argument with a DATA_LEVEL of 0 and then a row for each field in the record with a DATA_LEVEL of 1)

1) Find all programs that contain the LONG parameter

SELECT object_name , package_name , argument_name FROM user_arguments WHERE data_type = 'LONG' ; 

2) Find all functions with the OUT or in out parameters. Note: experienced programming experts will tell us not to use only IN parameters IN functions,
The reason is that functions with the OUT and in out parameters cannot be called in SQL and cannot be used IN function indexes. If you need the function to return multiple pieces of information, use
A stored procedure or a RECORD type is returned. The following example finds a function that violates this condition:

SELECT ua.object_name, 2 ua.package_name, 3 ua.argument_name, 4 ua.in_out 5 FROM (SELECT * 6 FROM user_arguments 7 WHERE position = 0) funcs, 8 user_arguments ua 9 WHERE ua.in_out IN ('OUT', 'IN OUT') 10 AND ua.position > 0 11 AND ua.data_level = 0 12 AND funcs.object_name = ua.object_name 13 AND funcs.package_name = ua.package_name 14 AND ( funcs.overload = ua.overload 15 OR (funcs.overload IS NULL 16 AND ua.overload IS NULL)); 

9 Conclusion: There is a gold mine
This article merely scratches the surface of the application information that can be mined from the data dictionary views in Oracle Database. PL/SQL editors such as Oracle SQL Developer provide user interfaces to attributes of these views, making it easier to browse their contents.

This article only removes the surface of the application information that can be mined from the Oracle Data Dictionary view. PL/SQL editor for example, Oracle SQL Developer
A view provides user interfaces to more easily browse their content.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.