Explore topic-wise InterviewSolutions in .

ORACLE provides the dbms_aqadm package to CREATE an advanced queue. This package can be used to create, alter, and DROP AQ.

The procedure that provides these FUNCTIONS are given as follows:

• Create_queue_table
• Alter_queue_table
• Drop_queue_table
• Create_queue
• Alter_queue
• Drop_queue
• Start_queue
• Stop_queue

Oracle provides the dbms_aqadm package to create an advanced queue. This package can be used to create, alter, and drop AQ.

The procedure that provides these functions are given as follows:

You can use the dbms_metadata package to retrieve object definitions from data dictionary by SPECIFYING the following:

• Types of object, such as TABLES, indexes or procedures.
• Optional SELECTION criteria, such as owner or name.
• Parse items (attributes of the returned objects that are to be PARSED and returned separately)
• Optional transformations on the output in Extensible Markup Language (XML) or Data Definition Language (DDL) format.

You can use the dbms_metadata package to retrieve object definitions from data dictionary by specifying the following:

The information PROCESSED by log miner is stored in the V$logmnr_contents view.

You can be use the dbms_logmnr.mine_value and dbms_logmnr.column_present procedures with the data in the v$logmnr_contents view to REVIEW the information.

The information processed by log miner is stored in the v$logmnr_contents view.

You can be use the dbms_logmnr.mine_value and dbms_logmnr.column_present procedures with the data in the v$logmnr_contents view to review the information.

Log MINER procedures are given as follows:

• ADD_LOGFILE – Adds a file to the existing or newly created list of archive files to process.
• START_LOGMNR – Initializes the LogMiner utility.
• END_LOGMNR – Finishes a LogMiner session.

The following two functions can also be used with log MINERS:

• MINE_VALUE (function) – RETURNS undo or redo VALUE for the column specified as parameter BASED on the data present in the v$logmnr_contents view.
• COLUMN_PRESENT ( function) – Verifies whether undo or redo values is present in the v$logmnr _contents view for the column specified as parameter.

Log miner procedures are given as follows:

The following two functions can also be used with log miners:

The PROCEDURES AVAILABLE in the dbms_application_info PACKAGE are set_module, sect_action, read_module, set_client_info, read_client_info and SET set_session_longtops.

The procedures available in the dbms_application_info package are set_module, sect_action, read_module, set_client_info, read_client_info and set set_session_longtops.

• You can manage optimizer statistics through the dbms_stats package.
• This package OFFERS multiple functions or procedures to gather, modify and REMOVE statistics.
• Statistics stored in the dictionary have an impact on the COST BASED optimizer. You can also use the dbms_stats PROCEDURE to gather statistics in parallel.

Oracle has PROVIDED a package called dbms_fga to IMPLEMENT FINE GRAINED auditing.

It contains the following procedures:

• Add_policy
• Drop_policy
• Enable_policy
• Disable_policy

Oracle has provided a package called dbms_fga to implement fine grained auditing.

It contains the following procedures:

• The user of an AQ is called a SUBSCRIBER, which can be added through the add_subscriber PROCEDURE.
• It can be altered USING the alter_subscriber procedure and removed using the drop_subscriber procedure of the dbms_aqadm package.
• Oracle also provides procedures to schedule propagation of MESSAGE in an AQ.
• This procedure is called schedule_propagation.
• It can be UNSCHEDULED using the unscheduled_propagation procedure.

Following procedures can be used to encrypt or decrypt data in DIFFERENT formats:

• DESEncrypt – GENERATES the encrypted FORM of the input data.
• DESDecrypt – Generates the decrypted form of the input data.
• DES3Encrypt – Generates the encrypted form of the input data by PASSING it through the triple DES encryption ALGORITHM.
• DES3Decrypt – Generates the decrypted form of the input data.

Following procedures can be used to encrypt or decrypt data in different formats:

• The dbms_lock.sleep procedure is the most COMMONLY used procedure from the dbms_lock package.
• It suspends a session for specified time interval.
• It takes a PARAMETER in seconds.
• The SMALLEST increment can be entered in HUNDRED of a SECOND.

• Fine grained AUDITING can be used to implement auditing at a low LEVEL of granularity.
• It ALLOWS you to AUDIT a transaction when a specific column or row is changed.

• You can USE the dbms_pipe package to COMMUNICATE between different SESSIONS.
• The information send through pipe is stored in SGA and is lost when instance is SHUT down.

• You cannot grant privileges on AQ by using grant STATEMENT SIMILAR to other Oracle objects.
• grant_system_privilege – Grants AQ SYSTEM privileges
• revoke_ system_privilege – Revokes AQ system privileges
• grant_queue_privilege – Grants privileges on AQ
• revoke_queue_privilege – Revokes privileges on AQ.

Some of the PROCEDURES provided by the dbms_aq PACKAGE are ENQUEUE, dequeue, REGISTER and unregister.

Some of the procedures provided by the dbms_aq package are enqueue, dequeue, register and unregister.

• Oracle manages database resources through LOCKING mechanism.
• These locks are usually managed by the system and are released on commit or rollback of a transaction.
• HOWEVER, Oracle has provided this FUNCTIONALITY to the users so that they can CREATE user defined locks.
• These locks are similar to system created locks in functionality but are not released automatically when transaction commits or rollbacks. Therefore, a user has to be EXTREMELY careful while using user defined locks.

The dbms_stats procedure performs the FOLLOWING general FUNCTIONS:

• Set or get STATISTICS using the set_xxx and get_xxx subprograms.
• Transfer statistics using the export_xxx and import_xxx subprograms.
• GATHER optimizer statistics using the gather_xxx subprograms.

The dbms_stats procedure performs the following general functions:

ORACLE SUPPORTS asynchronous notification USING the dbms_alert PACKAGE.

Oracle supports asynchronous notification using the dbms_alert package.

The dbms_lock PACKAGE can be used to create USER DEFINED LOCKS.

The dbms_lock package can be used to create user defined locks.

You can use the dbms_mview PACKAGE to deal with materialized views. It is ALSO called dbms_snapshot.

Some of the procedures provided by the dbms_mview packages are as given follows:

• BEGIN_TABLE_REORGANIZATION – Performs a process to preserve materialized view data NEEDED for refresh.
• END_TABLE_REORGANIZATION – Ensures that the materialized view data for the MASTER table is valid and that the master view or potential materialized view.
• EXPLAIN_MVIEW – Explains what is possible with a materialized view or potential materialized views.
• EXPLAIN_REWRITE – Explains why a QUERY failed to rewrite.

You can use the dbms_mview package to deal with materialized views. It is also called dbms_snapshot.

Some of the procedures provided by the dbms_mview packages are as given follows: