Interview Questions.

Top 100+ Oracle Packages Interview Questions And Answers

fluid

Top 100+ Oracle Packages Interview Questions And Answers

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

Answer :

The operating gadget documents generated through utl_file are owned by using working system consumer Oracle.

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

Answer :

Flashback characteristic lets in you to use a flashback version of the database, this is, you could query the database from a kingdom in the beyond based totally on a particular System Change Number (SCN) or time in the past.

Oracle Apps ERP Interview Questions
Question 3. How Can You Communicate With Operating System Files From Oracle?

Answer :

You can use the utl_file package to speak with running gadget files from PL/SQL code.
This package deal presents more than one capabilities to open, read, write and near the working gadget documents.
Question four. Does Oracle Provide Any Package To Deal With Materialized Views?

Answer :

You can use the dbms_mview package to deal with materialized perspectives. It is also called dbms_snapshot.

Some of the tactics provided by the dbms_mview applications are as given follows:

BEGIN_TABLE_REORGANIZATION – Performs a procedure to preserve materialized view records needed for refresh.
END_TABLE_REORGANIZATION – Ensures that the materialized view statistics for the master table is legitimate and that the master view or capacity materialized view.
EXPLAIN_MVIEW – Explains what is feasible with a materialized view or ability materialized views.
EXPLAIN_REWRITE – Explains why a query did not rewrite.
Question 5. How Can You Create A User Defined Lock?

Answer :

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

Oracle BPM Interview Questions
Question 6. Does Oracle Support Asynchronous Notification?

Answer :

Oracle helps asynchronous notification the usage of the dbms_alert bundle.

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

Answer :

Oracle offers a function called log minner, which may be used to view statistics modifications from previous transactions.

Oracle BI Publisher Interview Questions
Question 8. Explain The Major Functions Provided By The Dbms_stats Procedure?

Answer :

The dbms_stats process plays the following fashionable features:

Set or get facts the use of the set_xxx and get_xxx subprograms.
Transfer information the usage of the export_xxx and import_xxx subprograms.
Gather optimizer facts the use of the gather_xxx subprograms.
Question nine. What Is The Use Of Recording Information About Current Session?

Answer :

The statistics is beneficial for tracing. You can get purchaser statistics, module, or action facts from specific module of the software.
At runtime, you can question exceptional overall performance perspectives to discover the movement executed through application at that unique time.
Oracle Application Framework Interview Questions
Question 10. What Is A User Defined Lock?

Answer :

Oracle manages database sources thru locking mechanism.
These locks are typically controlled via the gadget and are released on commit or rollback of a transaction.
However, Oracle has furnished this capability to the customers that allows you to create user described locks.
These locks are similar to system created locks in capability but are not launched routinely whilst transaction commits or rollbacks. Therefore, a consumer must be extraordinarily cautious while the usage of consumer described locks.
Question eleven. What Functionality Does Oracle Provide To Secure Sensitive Information?

Answer :

You can use the dbms_obfuscation_toolkit package to encrypt touchy information.
The Data Encryption Standard (DES) or triples DES algorithm can be used to encrypt the data.
Oracle Apps DBA Interview Questions
Question 12. List Some Of The Procedures Provided By The Dbms_aq Package?

Answer :

Some of the tactics provided via the dbms_aq package are enqueue, dequeue, check in and unregister.

Oracle Apps ERP Interview Questions
Question 13. How Can You Grant Privileges On Aq To Other Users?

Answer :

You cannot provide privileges on AQ by using grant statement similar to other Oracle objects.
Grant_system_privilege – Grants AQ machine privileges
revoke_ system_privilege – Revokes AQ gadget 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 presents the dbms_flashback package deal to use flashback characteristic.
The package has the following processes 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 speak between distinct periods.
The information send thru pipe is stored in SGA and is lost when example is shut down.
Oracle SCM Interview Questions
Question sixteen. How Can You Record Information About Current Session?

Answer :

Oracle affords a package deal referred to as dbms_application_info. This package may be used to set data about contemporary session.

Question 17. What Are The Benefits Of Flashback Feature?

Answer :

Flashback function offers the following blessings:

Flashback Database – you can use this feature to flashback database to some extent in past in preference to restoring from backup and convalescing to the particular factor.
Flashback Standby Database – You can use this option to flashback standby database to a time prior to factor of failure.
Flashback Re-instantiation – You do now not want to re instantiate database following a failover the use of this option.
Flashback Drop – You can repair dropped tables using this selection.
Flashback Table – You can use this selection to flashback table to a selected factor.
Flashback Row History – This characteristic offers you capability to view the adjustments in a single or extra rows.
Flashback Transaction History – You can use this selection to diagnose issues, perform evaluation and audit transactions.
RPM Package Manager (RPM) Interview Questions
Question 18. What Do You Understand By Fine Grained Auditing?

Answer :

Fine grained auditing can be used to put into effect auditing at a low stage of granularity.
It lets in you to audit a transaction whilst a selected column or row is modified.
Oracle BPM Interview Questions
Question 19. What Is The Commonly Used Procedure Of The Dbms_lock Package?

Answer :

The dbms_lock.Sleep technique is the maximum typically used method from the dbms_lock package.
It suspends a session for unique time c language.
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 could use the dbms_redefinition package to redefine a table on-line.

Oracle Approvals Management (AME) Interview Questions
Question 21. How Can You Find Out If A Table Can Be Redefined?

Answer :

You can use the dbms_redefinition.Can_redef_table system to decide if a desk may be redefined.

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

Answer :

A consultation can check in itself for a selected form of alert or a signals via the usage of the register procedure. Such periods are called waiting consultation.

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

Answer :

Following strategies can be used to encrypt or decrypt statistics in extraordinary codecs:

DESEncrypt – Generates the encrypted form of the input information.
DESDecrypt – Generates the decrypted form of the enter statistics.
DES3Encrypt – Generates the encrypted shape of the enter data by way of passing it through the triple DES encryption set of rules.
DES3Decrypt – Generates the decrypted shape of the input facts.
Question 24. What Procedures Can You Use To Register With An Aq?

Answer :

The person of an AQ is known as a subscriber, which can be brought via the add_subscriber method.
It may be altered the usage of the alter_subscriber process and eliminated using the drop_subscriber technique of the dbms_aqadm package deal.
Oracle also gives methods to time table propagation of message in an AQ.
This technique is known as schedule_propagation.
It can be unscheduled using the unscheduled_propagation process.
Oracle BI Publisher Interview Questions
Question 25. How Can You Implement Fine Grained Auditing?

Answer :

Oracle has furnished a package deal called dbms_fga to implement fine grained auditing.

It includes the following tactics:

Add_policy
Drop_policy
Enable_policy
Disable_policy
Question 26. How Can You Manage Optimizer Statistics?

Answer :

You can manipulate optimizer statistics thru the dbms_stats package.
This package gives more than one functions or methods to gather, regulate and get rid of information.
Statistics stored within the dictionary have an impact on the price based totally optimizer. You also can use the dbms_stats manner to accumulate records in parallel.
Question 27. How Can You Schedule A Job In Database?

Answer :

You can use the dbms_scheduler bundle to create and time table a database process.

Oracle Application Framework Interview Questions
Question 28. Name Some Of The Procedures Available In The Dbms_application_info Package?

Answer :

The methods available within the dbms_application_info package deal 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 tactics are given as follows:

ADD_LOGFILE – Adds a report to the present or newly created listing of archive files to process.
START_LOGMNR – Initializes the LogMiner application.
END_LOGMNR – Finishes a LogMiner session.
The following  features can also be used with log miners:

MINE_VALUE (feature) – Returns undo or redo cost for the column targeted as parameter based totally at the facts present in the v$logmnr_contents view.
COLUMN_PRESENT ( function) – Verifies whether or not undo or redo values is gift in the v$logmnr _contents view for the column exact as parameter.
Question 30. How Can You Process Messages In Order Asynchronously?

Answer :

Oracle affords a package deal referred to as dbms_aq package deal to queue the messages, which may be ate up through every other session or software so as.

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

Answer :

This may be determined with the aid of the use of the excellent grained auditing feature for the table.

Question 32. How Can You Initialize Log Miner?

Answer :

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

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

Answer :

The records processed with the aid of log miner is saved within the v$logmnr_contents view.

You may be use the dbms_logmnr.Mine_value and dbms_logmnr.Column_present techniques with the statistics within the v$logmnr_contents view to review the data.

Oracle Apps DBA Interview Questions
Question 34. How Can You Generate Profile Of Pl/square Applications To Identify Performance Bottlenecks?

Answer :

You can use the dbms_profiler bundle to gather and store profile information about a PL/SQL software.

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

Answer :

You can use the dbms_metadata package to retrieve item definitions from statistics dictionary by using specifying the following:

Types of object, which include tables, indexes or procedures.
Optional choice criteria, together with owner or call.
Parse items (attributes of the back objects which are to be parsed and again one at a time)
Optional transformations on the output in Extensible Markup Language (XML) or Data Definition Language (DDL) layout.
Question 36. When Does An Alert Gets Signaled?

Answer :

Alerts are transaction based. Whenever, a transaction inflicting event of hobby commits, the alert is signaled.

Oracle SCM Interview Questions
Question 37. How Can You Create An Advanced Queuing (aq)?

Answer :

Oracle offers the dbms_aqadm package to create an advanced queue. This bundle may be used to create, modify, and drop AQ.

The process that offers those capabilities 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 first-class grained auditing capabilities of Oracle.

Question 39. How Do You Resize A Datafile?

Answer :

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




CFG