Oracle system package

 

Address: http://www.cnblogs.com/caizhanshu/articles/1153277.html

Web site: http://www.diybl.com/course/7_databases/oracle/Oracleshl/20110315/554478.html? Username = xing_sky & Password = yu900923

 

1. dbms_output
Function: used for input and output information. You can use put and put_lines to send the information to the buffer. The get_line and get_lines processes can display the buffer information.
The details are as follows:
1. Enable
This process is used to activate calls to put, put_line, get_line, and get_lines processes.
Syntax:
Dbms_output.enable (buffer_size in integer default 20000 );
2. Disable
This process is used to prohibit calls to put, put_line, get_line, and get_lines.
Syntax:
Dbms_output.disable;
3. Put and put_line
Put_line is used to write the complete row information into the buffer zone. Put creates row information in blocks. When put_line is used, the row Terminator is automatically appended to the end of the row; when using put, use new_line to append the row Terminator.
Example:
Set serverout on
Begin
Dbms_output.put_line ('great Chinese nation ');
Dbms_output.put ('China ');
Dbms_output.put (', great motherland ');
Dbms_output.new_line;
End;
/
Great Chinese Nation
China, great motherland
4. new_line
This process is used to append the row terminator at the end of the row. When the put process is used, the new_line process must be called to end the row.
5. get_line and get_lines
Get_line is used to obtain the single row information of the buffer, and get_lines is used to obtain the multi-row information of the buffer.
2. dbms_job
Role: it is used to arrange and manage job queues. By using jobs, the Oracle database can regularly execute specific tasks.
1. Submit
Used to create a new job. When creating a job, you need to perform the operations for the job, the next running date and interval of the job.
Syntax:
Dbms_out.submit (
Job out binary_integer, what in varchar2,
Next_date in date default sysdate,
Interval in varchar2 default 'null ',
No_parse in Boolean default false,
Instance in binary_integer default any_instance,
Force in Boolean default false
);
Note: The job is used to specify the job number; what is used to specify the operation to be executed by the job; next_date is used to specify the next running date of the job; interval is used to specify the time interval of running the job; no_parse is used to specify whether to parse job-related processes; instance is used to specify which routine can run the job; force is used to specify whether to forcibly run job-related routines.
Example:
VaR jobno number
Begin
Dbms_job.submit (: jobno,
'Dbms _ DDL. analyze_object (''table '',
''Scott '', ''emp'', ''compute '');',
Sysdate, 'sysdate + 1 ');
Commit;
End;
/
2. Remove
Delete a specific job in a Job Queue
Example:
SQL> exec dbms_job.remove (1 );
3. Change
It is used to change all information related to jobs, including job operations, job running dates, and runtime intervals.
Example:
SQL> exec dbms_job.change (2, null, null, 'sysdate + 2 ');
4. What
Used to change the operations to be performed by a job
Example:
SQL> exec dbms_job.what (
2, 'dbms _ stats. gather_table_stats-> (''scott '', ''emp '');');
5. next_date
Used to change the next running date of a job
Example:
SQL> exec dbms_job.next_date ('2', 'sysdate + 1 ');
6. Instance
Routine used to change a job
Example:
SQL> exec dbms_job.instance (2, 1 );
7. Interval
Used to change the running interval of a job
Example:
SQL & gt; Exec dbms_job.interval (2, 'sysdate + 1/24/60 ');
8. Broken
Used to set the interrupt ID of a job
Example:
SQL> exec dbms_job.broken (2, true, 'sysdate + 1 ');
9. Run
Used to run an existing job
Example:
SQL> exec dbms_job.run (1 );
Iii. dbms_pipe
Purpose: it is used for pipeline communication between different sessions of the same process. Note: If you want to execute the process and function in the dbms_pipe package, you must authorize the user.
SQL> conn sys/Oracle AS sysdba;
SQL> grant execute on dbms_pipe to Scott;
1. create_pipe
This function is used to create a public pipeline or private pipeline. If the parameter private is set to true, a private pipeline is created. If it is set to false, a public pipeline is created.
Example:
Declare
Falg int;
Begin
Flag: = dbms_pipe.create_pipe ('Public _ pipe ', 8192, false );
If flag = 0 then
Dbms_output.put_line ('Public pipeline created successfully ');
End if;
End;
/
2. pack_message
This process is used to write messages to the local message buffer.
3. send_message
This function is used to send content from the local message buffer to the pipeline.
4. receive_message
This function is used to receive pipeline messages.
5. next_item_type
This function is used to determine the Data Type of the next item in the local message buffer. If this function returns 0, it indicates that the pipeline does not have any messages. If it returns 6, it indicates that the data type of the next item is number; if 9 is returned, the Data Type of the next item is varchar2. If 11 is returned, the Data Type of the next item is rowid. If 12 is returned, the data type of the next item is date. If 23 is returned, the Data Type of the next item is raw.
6. unpack_message
This process is used to write the content of the message buffer to the variable.
7. remove_pipe
Delete an existing MPs queue.
8. Purge
This process is used to clear the content in the MPs queue.
9. reset_buffer
This process is used to reset the MPs queue buffer.
10. unique_session_name
This function is used to return a unique name for a specific session, and the maximum length of the name is 30 bytes.
4. dbms_alert
Purpose: Generate and pass the database warning information. If you use the dbms_alert package, you must log on to the system and grant the execution permission to the user.
SQL> conn sys/Oracle AS sysdba
SQL> grant execute on dbms_alert to Scott;
1. Register
Used to register alert events
Example:
SQL> exec dbms_alter.register ('alter1 ');
2. Remove
Deletes alert events that are not required by a session.
3. removeall
Deletes all registered alert events of the current session.
Syntax:
Dbms_alter.removeall;
4. set_defaults
Used to set the interval for detecting alert events. The default interval is 5 seconds.
5. Signal
Used to specify the alert message corresponding to the alert event.
6. waitany
It is used to wait for any alert event of the current session and output the relevant information when the alert event occurs. A commit is implicitly issued before the process is executed.
Syntax:
Dbms_alter.waitany (
Name out varchar2, message out varchar2,
Status out integer, timeout in number default maxwait
);
Note: "status" is used to return the status value. "0" indicates that an alert event has occurred. "1" indicates that an alert event has timed out. "timeout" indicates that an alert event has timed out.
7. waitone
It is used to wait for a specific alert event of the current session and output an alert message when an alert event occurs.
Same syntax as above
V. dbms_transaction
Purpose: it is used to execute SQL transaction processing statements in processes, functions, and packages.
1. read_only
Used to start a read-only transaction. It works exactly the same as the SQL statement set transaction read only.
2. read_write
Used to start read/write transactions, -------------------------------- write -------
3. advise_rollback
It is recommended to roll back distributed transactions of remote databases.
4. advise_nothing
We recommend that you do not process any distributed transactions in the remote database.
5. advise_commit
Recommended for submitting distributed transactions for remote databases
6. user_rollback_segment
Used to specify the rollback segment to be used by the firm
7. commit_comment
Specifies the comment when a transaction is committed.
8. commit_force
Used to force commit distributed transactions.
9. Commit
Used to submit the current transaction
10. savepoint
Used to set the save point
11. rollback
Used to roll back the current transaction
12. rollback_savepoint
Used to roll back to the storage point
13. rollback_force
Used to forcibly roll back distributed transactions
14. begin_discrete_transaction
Used to start the independent transaction mode
15. purge_mixed
Used to clear the mixed transaction results of distributed transactions
16. purge_lost_db_entry
Used to clear the Remote Transaction entry recorded in the local database. The transaction entry operation cannot be completed in the remote database due to remote database issues.
17. local_transaction_id
ID of the transaction used to return the current transaction
18. step_id
Return the unique positive integer used to sort DML transactions.
6. dbms_session
Purpose: provides methods to use PL/SQL to implement the alter session command, set role command, and other session information.
1. set_identifier
The customer ID used to set the session
2. set_context
Used to set application context attributes
3. clear_context
Used to clear attribute settings of application context
4. clear_identifier
Set_client_id used to delete a session.
5. set_role
Used to activate or disable a session role
6. set_ SQL _trace
SQL trace used to activate or disable the current session
Syntax:
Dbms_session.set_ SQL _trace (SQL _strace Boolean );
7. set_nls
Used to set NLS features
Syntax:
Dbms_session.set_nls (Param varchar2, value varchar2 );
8. close_database_link
Used to Close opened database chains
9. reset_package
Resets all packets of the current session and releases the package status.
10. modify_package_state
Used to modify the status of PL/SQL program units of the current session
Syntax:
Dbms_session.modify_package_state (action_flags in pls_integer );
11. unique_session_id
Unique id used to return the current session
12. is_role_enabled
Used to determine whether the current session has activated a specific role.
Syntax:
Dbms_session.is_role_enabled (rolename varchar2)
Return Boolean;
13. is_session_alive
Used to determine whether a specific session is active.
14. set_close_cached_open_cursors
Used to enable or disable close_cached_open_cursors
15. free_unused_user_meory
Used to recycle unused memory after a large internal operation (more than 100 K) is performed
16. set_context
Set the application context Attribute Value
17. list_context
Returns the original namespace and context list of the current session.
18. swith_current_consumer_group
Used to change the resource use group of the current session
VII. dbms_rowid
Purpose: used to obtain information about row identifiers and create rowids in PL/SQL programs and SQL statements. This package can be used to obtain the file number of the row and the data block number of the file where the row is located, the row number of the data block where the row is located, and the database object number.
1. rowid_create
Create a rowid
Syntax:
Dbms_rowid.rowid_create (
Rowid_type in number, object_number in number,
Relative_fno in n umber, block_number in number,
Row_number in number)
Return rowid;
Note: rowid_type is used to specify the rowid type (0: Restricted rowid, 1: Extended rowid); object_number is used to specify the data object number; relative_fno is used to specify the relative file number; block_number is used to specify the data block number in the file; row_number is used to specify the row number in the data block.
2. rowid_info
Used to obtain detailed information about a specific rowid.
3. rowid_type
Returns the type of a specified rowid.
4. rowid_object
Used to obtain the data object number corresponding to a specific rowid
5. rowid_relative_fno
Used to obtain the relative file number corresponding to a specific rowid
6. rowid_block_number
Returns the data block number of a specific rowid in the data file.
7. rowid_row_number
Returns the row number of a specific rowid in the data block.
8. rowid_to_obsolute_fno
Returns the absolute file number corresponding to a specific rowid.
9. rowid_to_extended
Used to convert a restricted rowid to an extended rowid
10. rowid_to_restricted
Used to convert extended rowid to restricted rowid
11. rowid_verify
Check whether the restricted rowid can be converted to the extended rowid
8. dbms_rls
Role: it is only applicable to Oracle Enterprise Edition. It is used to implement fine-grained access control, and fine-grained access control is implemented by dynamically adding predicates (where clauses) in SQL statements. by using the fine-grained access control feature of Oracle, different database users can operate on different data on the same table when executing the same SQL statement.
9. dbms_ddl
Purpose: provides methods to execute DDL statements in PL/SQL blocks, and provides some special DDL management methods.
10. dbms_shared_pool
Function: provides some process and function access to the shared pool. This allows you to display the object size in the Shared Pool, bind the object to the shared pool, and clear the objects bound to the shared pool. to use this package, you must run dbmspool. SQL script to create this package.
11. dbms_random
Function: provides a built-in random number generator to quickly generate random numbers.
12. dbms_logmnr
Purpose: by using the packages dbms_logmnr and dbms_logmnr_d, you can analyze the transaction changes recorded in the redo log and archive log, determine the time of misoperation (such as drop table), and track user transaction operations, trace and restore table DML operations.
13. dbms_flashback
Purpose: The flashback feature used to activate or disable a session. To enable normal users to use the package, they must grant the permission to execute the package to these users.
14. dbms_obfuscation_toolkit
Function: used to encrypt and decrypt data. In addition, you can generate password verification. by encrypting the input data, you can prevent hackers or other users from stealing private data. By combining encryption and password verification, you can prevent hackers from damaging the initial encrypted data. when you use this package to encrypt data, the length of the encrypted data must be an integer multiple of 8 bytes. when data is encrypted using the DES algorithm, the key length cannot be less than 8 bytes. when data is encrypted using the des3 algorithm, the key length cannot be less than 16 bytes.
15th. dbms_space
Role: used to analyze the demand for segment growth and Space
16. dbms_space_admin
Role: provides the function of local tablespace management.
17. dbms_tts
Purpose: Check whether the space set is self-contained. After the check is executed, the information that violates the self-contained rules is written into the temporary table transport_set_violations.
18. dbms_repair
Function: used to detect and repair damaged data blocks on tables and indexes.
19. dbms_resource_manager
Purpose: this command is used to maintain resource plans, resource use groups, and resource plans. The package dbms_resource_manager_privs is used to maintain permissions related to resource management.
20. dbms_stats
Purpose: Collects, views, and modifies the optimization statistics of database objects.
21. utl_file
Function: used to read and write OS files. when using this package to access the OS file, you must create a directory object for the OS directory .. to access files in a specific directory, you must have the permission to read and write directory objects. before using the utl_file package, you should first create a directory object.
22. utl_inaddr
Purpose: Obtain the Host Name and IP address in the LAN or Internet environment.