Oracle Packages Interview Questions & Answers

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

    Answer :

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

  2. Question 2. What Do You Understand By Flashback Feature Of Oracle?

    Answer :

    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.

  3. Oracle Apps ERP Interview Questions

  4. Question 3. How Can You Communicate With Operating System Files From Oracle?

    Answer :

    • 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.
  5. Question 4. Does Oracle Provide Any Package To Deal With Materialized Views?

    Answer :

    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.
  6. Question 5. How Can You Create A User Defined Lock?

    Answer :

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

  7. Oracle BPM Interview Questions

  8. Question 6. Does Oracle Support Asynchronous Notification?

    Answer :

    Oracle supports asynchronous notification using the dbms_alert package.

  9. Question 7. How Can You Get Actual Data Change Values From Previous Transactions In Oracle?

    Answer :

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

  10. Oracle BI Publisher Interview Questions

  11. Question 8. Explain The Major Functions Provided By The Dbms_stats Procedure?

    Answer :

    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.
  12. Question 9. What Is The Use Of Recording Information About Current Session?

    Answer :

    • 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.
  13. Oracle Application Framework Interview Questions

  14. Question 10. What Is A User Defined Lock?

    Answer :

    • 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.
  15. Question 11. What Functionality Does Oracle Provide To Secure Sensitive Information?

    Answer :

    • 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.
  16. Oracle Apps DBA Interview Questions

  17. Question 12. List Some Of The Procedures Provided By The Dbms_aq Package?

    Answer :

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

  18. Oracle Apps ERP Interview Questions

  19. Question 13. How Can You Grant Privileges On Aq To Other Users?

    Answer :

    • 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.
  20. Question 14. How Can You Enable Flashback Feature?

    Answer :

    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
  21. Question 15. How Does Oracle Facilitate Communication Between Different Sessions?

    Answer :

    • 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.
  22. Oracle SCM Interview Questions

  23. Question 16. How Can You Record Information About Current Session?

    Answer :

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

  24. Question 17. What Are The Benefits Of Flashback Feature?

    Answer :

    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 Transaction History – You can use this feature to diagnose problems, perform analysis and audit transactions.
  25. RPM Package Manager (RPM) Interview Questions

  26. Question 18. What Do You Understand By Fine Grained Auditing?

    Answer :

    • 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.
  27. Oracle BPM Interview Questions

  28. Question 19. What Is The Commonly Used Procedure Of The Dbms_lock Package?

    Answer :

    • 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.
  29. Question 20. Can You Redefine A Table Online?

    Answer :

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

  30. Oracle Approvals Management (AME) Interview Questions

  31. Question 21. How Can You Find Out If A Table Can Be Redefined?

    Answer :

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

  32. Question 22. How Can A Session Indicate Its Interest In Receiving Alerts?

    Answer :

    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.

  33. Question 23. Which Procedures Can You Use To Encrypt And Decrypt Data In Different Formats?

    Answer :

    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.
  34. Question 24. What Procedures Can You Use To Register With An Aq?

    Answer :

    • 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.
  35. Oracle BI Publisher Interview Questions

  36. Question 25. How Can You Implement Fine Grained Auditing?

    Answer :

    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
  37. Question 26. How Can You Manage Optimizer Statistics?

    Answer :

    • 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.
  38. Question 27. How Can You Schedule A Job In Database?

    Answer :

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

  39. Oracle Application Framework Interview Questions

  40. Question 28. Name Some Of The Procedures Available In The Dbms_application_info Package?

    Answer :

    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.

  41. Question 29. List The Log Miner Procedures?

    Answer :

    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.
  42. Question 30. How Can You Process Messages In Order Asynchronously?

    Answer :

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

  43. Question 31. How Would You Determine Who Has Added A Row To A Table?

    Answer :

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

  44. Question 32. How Can You Initialize Log Miner?

    Answer :

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

  45. Question 33. How Can You Get The Information Processed By Log Miner Procedures?

    Answer :

    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.

  46. Oracle Apps DBA Interview Questions

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

    Answer :

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

  48. Question 35. How Can Retrieve Object Definitions From Data Dictionary?

    Answer :

    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.
  49. Question 36. When Does An Alert Gets Signaled?

    Answer :

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

  50. Oracle SCM Interview Questions

  51. Question 37. How Can You Create An Advanced Queuing (aq)?

    Answer :

    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
  52. Question 38. Suppose You Want To Audit Specific Activities On Sensitive Data. How Can You Achieve That?

    Answer :

    You can use the fine grained auditing features of Oracle.

  53. Question 39. How Do You Resize A Datafile?

    Answer :

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