Oracle Packages Interview Questions & Answers

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.

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.

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.

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.

Question 5. How Can You Create A User Defined Lock?

Answer :

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

Question 6. Does Oracle Support Asynchronous Notification?

Answer :

Oracle supports asynchronous notification using the dbms_alert package.

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.

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

Question 20. Can You Redefine A Table Online?

Answer :

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

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.

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.

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.

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.

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

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.

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.

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.

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.

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.

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.

Question 32. How Can You Initialize Log Miner?

Answer :

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

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.

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.

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.

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.

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

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.

Question 39. How Do You Resize A Datafile?

Answer :

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