Top 39 Oracle Packages Interview Questions You Must Prepare 08.Dec.2023

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.

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