PL/SQL Related data dictionary

PL/SQL Related data dictionary
Http://www.oracle.com/technetwork/issue-archive/2012/12-nov/o62plsql-1851968.html

Sometimes, we encounter the following issues during the PL/SQL development process:
1) What database objects does my program depend on?
2) which package calls a subroutine or variable in one of the other packages?
3) Which of my sub-program parameters use inappropriate data types?
4) Do all my subroutines use the appropriate level of optimization?

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

"User_arguments": Contains parameters for all procedures and functions in a pattern.
"User_dependencies": Contains dependencies that you have between objects. This view is most commonly used by Oracle to identify failed database objects (when the object on which it depends has changed).
"User_errors": Contains compilation errors for all stored objects that you have. This view can be read by the Sql*plus command: SHOW errors.
"User_identifiers": 11g is introduced and populated by the Pl/scope compilation tool. Once populated, this view will contain all identifiers-program names, variables, and so on.
"User_object_size": Contains the size of the object. In fact, this view shows the size of the original, parsed, and compiled code. Although it is used by the compiler and the runtime, you can also use it to find large programs in your environment.
"User_objects": Contains information about all objects in one mode. You can see if the object is valid, find all packages that contain the EMP name, and so on.
"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 the stored program, such as the Authid setting, which is defined as a deterministic feature.
"User_source": Contains text source code for all objects. A very handy view, because you can perform various analyses of the source code.
"user_stored_settings": PL/SQL compiler tag. Use this view to see which program is using native compilation (native compilation).
"User_triggers" and "User_trigger_cols": The Database TRIGGERS you own (including the source code and a description of the Tri Ggering event) and any columns identified with the triggers, respectively. You can write programs against User_triggers to enable or disable TRIGGERS for a particular table.

1. Data Dictionary Basics
A data dictionary is a table and a view that is created by multiple instances, and the user typically has only access to the data dictionary query.
The vast majority of data fields are made up of three versions:
1) User view, beginning with User_, contains database object information owned by the currently logged in user.
2) Full view, beginning with All_, contains the database object information that the currently logged-on user has read.
3) Administrator view, beginning with Dba_, such views contain all database object information in an instance, which is usually not accessible to ordinary users.

For example:
SELECT * from User_objects; – All database object information I have
SELECT * from All_objects; – Database object information I have permission to read
SELECT * from Dba_objects; – Object information for the entire database that the administrator can access

2, display the information of the storage object user_objects
Contains a description of the column (English too Simple not translated):
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 is changed

Take a look at some examples:

1) Show all the tables in my mode:

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

2) Displays all invalid object names:

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

3) Show 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 source code User_source
Column Description:
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 to call the process.

SELECTtextFROMWHERE UPPER (textLIKE‘%SALES_MGR.CALC_TOTALS%‘ ORDERBY

Of course, this query may even be checked for comments, and a string that does not conform to the like format will not be retrieved, for example:
Sales_mgr.
Calc_totals

So assuming that our code is relatively standard, this query does a good job.
Also, for 11g, you can use the Pl/scope feature.

4, the compilation of storage code settings user_plsql_object_settings
Plsql_optimize_level: Optimization level for compiled objects

Plsql_code_type: The compilation mode of the object

Plsql_debug:whether or not the object is compiled for debugging object is compiled for debugging

Plsql_warnings: Compiling warning settings for compiled objects

Nls_length_semantics:nls length semantics that were used to compile the object's semantics for compiling objects are set

Find all the program units that are not optimized for effective compile time:

SELECT Name
From User_plsql_object_settings
WHERE Plsql_optimize_level < 2

Level 0 indicates that no optimizations have been taken. 1 represents a minimal optimization. 2 of people should not exist in the production environment.

Identify those programs that have a compile-time warning disabled.

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

5. More 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 permissions or definition permissions

Deterministic:set to YES if the function was defined to being deterministic, which theoretically means that the value Returne D by the function was determined completely by the function ' s argument values are deterministic

Pipelined:set to YES if the function was 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 was overloaded, which means that there was at least, Subprograms W ITH this name in the same package is overloaded

Find all procedures and functions that run under the caller's authority

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

Displays all functions declared as deterministic:

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

6. Analyze and modify the trigger state user_triggers
Trigger_name:the NAME of the TRIGGER

Trigger_type:a string that shows if the is a before or after TRIGGER and whether it's a row-or statement-level TRIGGER (In a trigger that's 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 would 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 out all the triggers that are disabled:

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 this fire when a UPDATE operation is performed:
Find all triggers that contain the update action

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

7. Object-dependent Analysis user_dependencies
Name:name of the object

Type:type of the object

Referenced_owner:owner of the referenced object to be referenced by the owner

Referenced_name:name of the referenced object referenced by name

Referenced_type:type of the type of the referenced object being referenced

Find all objects that depend on the Employees table:

SELECTtypeFROMWHERE‘EMPLOYEES‘ORDERBYtype

Identify all objects that are dependent on the Order_mgr package 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, analysis 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 ' datatypes are a R Ecord, user_arguments'll has 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 a long parameter

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

2) Find all functions with out or in parameters. This place should be noted: experienced programming experts will tell us not to use the in parameter only in the function,
The reason is that the function with out and in arguments cannot be called in SQL and cannot be used in the function index. If you need a function to return multiple pieces of information, use the
A stored procedure or returns a record type. The following example identifies a function that violates this condition:

SELECTUa.object_name,2Ua.package_name,3Ua.argument_name,4Ua.in_out5  from(SELECT*6  fromUser_arguments7 WHEREPosition =0) Funcs,8User_arguments UA9 WHEREUa.in_outinch(' Out ',' in Out ')Ten  andUa.position >0  One  andUa.data_level =0  A  andFuncs.object_name = Ua.object_name -  andFuncs.package_name = Ua.package_name -  and(Funcs.Overload= UA.Overload  the OR(Funcs.Overload  isNull -  andUa.Overload  isNULL));

9 Summary: There's a gold mine.
This article merely scratches the surface of the application information the can is mined from the data dictionary views In Oracle Database. PL/SQL editors such as Oracle SQL Developer provide user interfaces to many of these views, making it easier to browse the IR contents.

This article just brushed off a layer of application information that can be mined from the Oracle data dictionary view. The PL/SQL editor such as Oracle Developer for many
Views provide a user interface that makes it easier to browse their content.

Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.

PL/SQL Related data dictionary