Top 39 Oracle Packages Interview Questions You Must Prepare 24.Jun.2024

Q1. List The Log Miner Procedures?

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.

Q2. What Is The Use Of Recording Information About Current Session?

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

Q3. When Does An Alert Gets Signaled?

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

Q4. How Can You Schedule A Job In Database?

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

Q5. How Can You Implement Fine Grained Auditing?

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

Q6. What Is The Commonly Used Procedure Of The Dbms_lock Package?

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

Q7. How Can Retrieve Object Definitions From Data Dictionary?

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.

Q8. What Are The Benefits Of Flashback Feature?

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.

Q9. Which Procedures Can You Use To Encrypt And Decrypt Data In Different Formats?

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.

Q10. How Does Oracle Facilitate Communication Between Different Sessions?

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

Q11. How Do You Resize A Datafile?

You can resize a datafile by using the ALTER DATABASE DATAFILE <file_name> RESIZE; statement.

Q12. How Can You Record Information About Current Session?

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

Q13. How Can A Session Indicate Its Interest In Receiving Alerts?

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.

Q14. What Functionality Does Oracle Provide To Secure Sensitive Information?

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

Q15. How Can You Create A User Defined Lock?

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

Q16. What Is A User Defined Lock?

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

Q17. How Can You Get The Information Processed By Log Miner Procedures?

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.

Q18. How Can You Generate Profile Of Pl/sql Applications To Identify Performance Bottlenecks?

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

Q19. How Can You Create An Advanced Queuing (aq)?

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

Q20. How Would You Determine Who Has Added A Row To A Table?

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

Q21. Suppose You Want To Audit Specific Activities On Sensitive Data. How Can You Achieve That?

You can use the fine grained auditing features of Oracle.

Q22. What Procedures Can You Use To Register With An Aq?

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

Q23. What Do You Understand By Flashback Feature Of Oracle?

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.

Q24. How Can You Initialize Log Miner?

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

Q25. Name Some Of The Procedures Available In The Dbms_application_info Package?

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.

Q26. How Can You Enable Flashback Feature?

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

Q27. Who Owns The Operating System Files Created By The Utl_file Package?

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

Q28. Can You Redefine A Table Online?

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

Q29. How Can You Communicate With Operating System Files From Oracle?

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

Q30. How Can You Find Out If A Table Can Be Redefined?

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

Q31. How Can You Get Actual Data Change Values From Previous Tractions In Oracle?

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

Q32. How Can You Grant Privileges On Aq To Other Users?

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

Q33. Does Oracle Provide Any Package To Deal With Materialized Views?

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.

Q34. Does Oracle Support Asynchronous Notification?

Oracle supports asynchronous notification using the dbms_alert package.

Q35. How Can You Manage Optimizer Statistics?

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

Q36. How Can You Process Messages In Order Asynchronously?

Oracle provides a package called dbms_aq package to queue the messages, which can be consumed by another session or application in order.

Q37. Explain The Major Functions Provided By The Dbms_stats Procedure?

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.

Q38. List Some Of The Procedures Provided By The Dbms_aq Package?

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

Q39. What Do You Understand By Fine Grained Auditing?

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