Top 39 Oracle Packages Interview Questions You Must Prepare 23.Jan.2025

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.

• The information is useful for tracing. You can get client information, module, or action information from different module of the application.
• At runtime, you can query different performance views to find out the action performed by application at that specific time.

Alerts are traction based. Whenever, a traction causing event of interest commits, the alert is signaled.

You can use the dbms_scheduler package to create and schedule a database job.

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

• 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.

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 trformations on the output in Extensible Markup Language (XML) or Data Definition Language (DDL) format.

Flashback feature provides the following benefits:

• Flashback Database – you can use this feature to flashback database to a point in past instead of restoring from backup and recovering to the specific point.
• Flashback Standby Database – You can use this feature to flashback standby database to a time prior to point of failure.
• Flashback Re-instantiation – You do not need to re instantiate database following a failover using this feature.
• Flashback Drop – You can restore dropped tables using this feature.
• Flashback Table – You can use this feature to flashback table to a specific point.
• Flashback Row History – This feature gives you ability to view the changes in one or more rows.
• Flashback Traction History – You can use this feature to diagnose problems, perform analysis and audit tractions.

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.

• 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 can resize a datafile by using the ALTER DATABASE DATAFILE <file_name> RESIZE; statement.

Oracle provides a package called dbms_application_info. This package can be used to set information about current session.

A session can register itself for a specific type of alert or a alerts by using the register procedure. Such sessions are called waiting session.

• You can use the dbms_obfuscation_toolkit package to encrypt sensitive information.
• The Data Encryption Standard (DES) or triples DES algorithm can be used to encrypt the data.

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

• Oracle manages database resources through locking mechanism.
• These locks are usually managed by the system and are released on commit or rollback of a traction.
• 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 traction commits or rollbacks. Therefore, a user has to be extremely careful while using user defined locks.

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.

You can use the dbms_profiler package to collect and store profile information about a PL/SQL application.

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

This can be determined by using the fine grained auditing feature for the table.

You can use the fine grained auditing features of Oracle.

• 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.

Flashback feature allows you to use a flashback version of the database, that is, you can query the database from a state in the past based on a specific System Change Number (SCN) or time in the past.

You can use the DBMS_LOGMNR.START_LOGMNR procedure to initialize the log miner.

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.

Oracle provides the dbms_flashback package to use flashback feature.
The package has the following procedures to facilitate this feature:

• Enable_at_time
• Enable_at_system_change_number
• Get_system_change_number
• Disable

The operating system files generated by utl_file are owned by operating system user Oracle.

Yes, you can use the dbms_redefinition package to redefine a table online.

• You can use the utl_file package to communicate with operating system files from PL/SQL code.
• This package provides multiple functions to open, read, write and close the operating system files.

You can use the dbms_redefinition.can_redef_table procedure to determine if a table can be redefined.

Oracle provides a feature called log minner, which can be used to view data changes from previous tractions.

• 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.

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.

Oracle supports asynchronous notification using the dbms_alert package.

• 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 provides a package called dbms_aq package to queue the messages, which can be consumed by another session or application in order.

The dbms_stats procedure performs the following general functions:

• Set or get statistics using the set_xxx and get_xxx subprograms.
• Trfer statistics using the export_xxx and import_xxx subprograms.
• Gather optimizer statistics using the gather_xxx subprograms.

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

• Fine grained auditing can be used to implement auditing at a low level of granularity.
• It allows you to audit a traction when a specific column or row is changed.