Explore topic-wise InterviewSolutions in .

A resource sub-PLAN is a plan, which GETS the resource to allocate from a higher-level plan. It is CREATED in a similar manner as a plan. However, a top-level plan gets to allocate 100% of the resources while a sub-plan can allocate only the resources allocated to it by the top-level plan.

A resource sub-plan is a plan, which gets the resource to allocate from a higher-level plan. It is created in a similar manner as a plan. However, a top-level plan gets to allocate 100% of the resources while a sub-plan can allocate only the resources allocated to it by the top-level plan.

You can specify the directives to cancel long-running SQL queries or to terminate long-running sessions based on the amount of SYSTEM resources CONSUMED. In other words, you can cancel SQL queries or kill sessions if consumer group MEETS certain criteria. The CRITERION is specified using the SWITCH_TIME and SWITCH_ESTIMATE parameters. The option to CANCEL_SQL or KILL_SESSION can be specified by the SWITCH_GROUP parameter.

You can specify the directives to cancel long-running SQL queries or to terminate long-running sessions based on the amount of system resources consumed. In other words, you can cancel SQL queries or kill sessions if consumer group meets certain criteria. The criterion is specified using the SWITCH_TIME and SWITCH_ESTIMATE parameters. The option to CANCEL_SQL or KILL_SESSION can be specified by the SWITCH_GROUP parameter.

It is a type of RESOURCE allocation method USED to automatically switch the CONSUMER GROUP if the switch criterion is met. The switch group is specified USING the SWITCH_GROUP parameter. Switch criteria can be set using the SWITCH_TIME and SWITCH_ESTIMATE parameters.

It is a type of resource allocation method used to automatically switch the consumer group if the switch criterion is met. The switch group is specified using the SWITCH_GROUP parameter. Switch criteria can be set using the SWITCH_TIME and SWITCH_ESTIMATE parameters.

Parallel DEGREE limit is an allocation METHOD for degree of parallelism. It specifies the maximum degree of parallelism for an operation. It can be set using the PARALLEL_DEGREE_LIMIT_P1 parameter.
The default value of this parameter is NULL which INDICATES no limit is imposed.

Parallel degree limit is an allocation method for degree of parallelism. It specifies the maximum degree of parallelism for an operation. It can be set using the PARALLEL_DEGREE_LIMIT_P1 parameter.
The default value of this parameter is null which indicates no limit is imposed.

ORACLE uses eight levels of CPU allocation, namely CPU_P1 to CPU_P8, to prioritize and allocate CPU usage AMONG the COMPETING USER sessions.

Oracle uses eight levels of CPU allocation, namely CPU_P1 to CPU_P8, to prioritize and allocate CPU usage among the competing user sessions.

You can SWITCH users between resource consumer GROUPS by using any of the following THREE procedures:

• dbms_resource_manager.switch_consumer_group_for_sess.
• dbms_resource_manager.switch_consumer_group_for_user.
• dbms_session.switch_current_consumer_group.

You can switch users between resource consumer groups by using any of the following three procedures:

A resource consumer group is used to classify USERS into logical group BASED on their resource consumption requirements.

Following are the two ways to define resource consumer group:

• A resource consumer group is a method of CLASSIFYING users based on their resource consumption requirements or TENDENCIES.
• A resource consumer group is a method of prioritizing database resource usage by classifying users based on their BUSINESS needs.

A resource consumer group is used to classify users into logical group based on their resource consumption requirements.

Following are the two ways to define resource consumer group:

The THREE MAIN COMPONENTS or ELEMENTS of DRM are given as FOLLOWS:

• Resource consumer groups
• Resource plans
• Resource plan directives

The three main components or elements of DRM are given as follows:

• DRM offers a component BASED approach to resource ALLOCATION management.
• It allows you to create resource plans, which SPECIFY resource allocation to various CONSUMER groups. You can group users based on the resource requirement.
• DRM offers an easy-to-use and flexible system by defining distinct independent COMPONENTS.

You can use Database RESOURCE Manager (DRM) to MANAGE OPERATING system RESOURCES.

You can use Database Resource Manager (DRM) to manage operating system resources.

You can query the V$database VIEW. The database_role column provides the RELEVANT information.

You can query the v$database view. The database_role column provides the relevant information.

A PHYSICAL standby DATABASE is PHYSICALLY same as PRODUCTION database, It has same file structure as production database while logical database can have different physical structure as COMPARED to production database.
A physical standby is available only when production database is not available while logical standby can be available for reporting in parallel to production database.

A physical standby database is physically same as production database, It has same file structure as production database while logical database can have different physical structure as compared to production database.
A physical standby is available only when production database is not available while logical standby can be available for reporting in parallel to production database.

A production DATABASE can have maximum of nine STANDBY DATABASES.

A production database can have maximum of nine standby databases.

ROLE transition is the change of role between primary and STANDBY databases.
It can happen in the following two cases:

•  SWITCHOVER, where primary database is switched to standby database and standby database is switched to primary database
•  Failover, where a standby database can be used as a disaster recovery solution in case of a failure in the primary database

Role transition is the change of role between primary and standby databases.
It can happen in the following two cases:

If standby DATABASE is not available and the changes are MADE in primary database, then there will be a GAP in the SEQUENCE of archive logs. The INFORMATION about archive logs in these gaps can be found in the v$archive_gap view.

If standby database is not available and the changes are made in primary database, then there will be a gap in the sequence of archive logs. The information about archive logs in these gaps can be found in the v$archive_gap view.

Standby database requires Fetch ARCHIVE Log (FAL) CLIENT to request and fetch archive log files from primary database, REMOTE file SERVER to receive archived log files from primary database, archiver process to archive redo logs applied to standby database, and Managed Recovery Process (MRP) to apply redo logs to the standby database.

Standby database requires Fetch Archive Log (FAL) client to request and fetch archive log files from primary database, remote file server to receive archived log files from primary database, archiver process to archive redo logs applied to standby database, and Managed Recovery Process (MRP) to apply redo logs to the standby database.

Primary database requires LOG WRITER to generate log information, archiver process to generate archive log files, and fetch archive log server to REQUEST the archive log files from STANDBY database.

Primary database requires log writer to generate log information, archiver process to generate archive log files, and fetch archive log server to request the archive log files from standby database.

Data guard SETUP ensures that another database is available as a copy of production database at almost real time. As a RESULT, we have higher protection for enterprise data and the new database is available very quickly WITHOUT the need to actually restore and recover from backup. THEREFORE, data guard offers an EXCELLENT disaster recovery solution.

Data guard setup ensures that another database is available as a copy of production database at almost real time. As a result, we have higher protection for enterprise data and the new database is available very quickly without the need to actually restore and recover from backup. Therefore, data guard offers an excellent disaster recovery solution.

The UNION operation removes redundancy while UNION ALL does not; therefore, the UNION operation needs to perform SORT. As a RESULT, UNION ALL PERFORMS better as it does not NEED to perform any sort.

The UNION operation removes redundancy while UNION ALL does not; therefore, the UNION operation needs to perform sort. As a result, UNION ALL performs better as it does not need to perform any sort.

You can handle ORA- 01403: no data found ERROR by terminating the PROCESSING for the SELECT statement.

You can handle ORA- 01403: no data found error by terminating the processing for the SELECT statement.

The ORA-01000: MAXIMUM OPEN cursors EXCEEDED ERROR can be handled by checking the parameter setting for OPEN_CURSORS. You can resolve the error by closing the cursors that are no LONGER in use, raising the OPEN__CURSORS parameter within your initialization file, and restarting the Oracle database.

The ORA-01000: maximum open cursors exceeded error can be handled by checking the parameter setting for OPEN_CURSORS. You can resolve the error by closing the cursors that are no longer in use, raising the OPEN__CURSORS parameter within your initialization file, and restarting the Oracle database.

Cost-based optimizer is the optimizer COMPONENT of the Oracle, which is RECOMMENDED and supported by Oracle. It DETERMINES query plans based on overall cost of usage of each resource to get the BEST possible plan with respect to the resource usage cost.
It uses internal statistics to determine the best execution plan for the statement.

Cost-based optimizer is the optimizer component of the Oracle, which is recommended and supported by Oracle. It determines query plans based on overall cost of usage of each resource to get the best possible plan with respect to the resource usage cost.
It uses internal statistics to determine the best execution plan for the statement.

The BEHAVIOR of merge JOIN is INFLUENCED by the initialization PARAMETERS: sort_area_size and db__file_muitiblock_read_count.

The behavior of merge join is influenced by the initialization parameters: sort_area_size and db__file_muitiblock_read_count.

Wait event occurs when a USER PROCESS is kept WAITING because of some problem, such as an I/O bottleneck or a busy CPU.
The information about wait event is available in the V$SYSTEM_WAIT and V$SESSION_WAIT DYNAMIC performance views.

Wait event occurs when a user process is kept waiting because of some problem, such as an I/O bottleneck or a busy CPU.
The information about wait event is available in the V$SYSTEM_WAIT and V$SESSION_WAIT dynamic performance views.

A LATCH is a semaphore or an on/off switch in Oracle DATABASE that a process must access in order to conduct certain type of ACTIVITIES. Latches govern the usage of Oracle's internal resources by its processes. They enforce serial access to the resources and LIMIT the amount of time for which a single process can USE a resource.
There are over 80 latches available in Oracle.

A latch is a semaphore or an on/off switch in Oracle database that a process must access in order to conduct certain type of activities. Latches govern the usage of Oracle's internal resources by its processes. They enforce serial access to the resources and limit the amount of time for which a single process can use a resource.
There are over 80 latches available in Oracle.

After you get ID1 from the V$lock view for locked OBJECT, you can query the dba_objects view to get the NAME of the object.

After you get ID1 from the v$lock view for locked object, you can query the dba_objects view to get the name of the object.

The Oracle Wait INTERFACE is the set of data dictionary tables that STORE information about wait events. Oracle offers multiple views to GIVE information about wait events, such as v$system_event and v$session_event.
You can get the information about wait events for the database or a specific SESSION from these views and find out the event, which seems too high. Upon further analysis, you can determine the CAUSE of such events and then resolve the issue.

The Oracle Wait Interface is the set of data dictionary tables that store information about wait events. Oracle offers multiple views to give information about wait events, such as v$system_event and v$session_event.
You can get the information about wait events for the database or a specific session from these views and find out the event, which seems too high. Upon further analysis, you can determine the cause of such events and then resolve the issue.

DYNAMIC performance VIEWS are also called V$ views. These views provide INFORMATION about the sessions.
Any Oracle USER can get information from dynamic performance views if the user has the select any table privilege. This privilege is generally granted through the SELECT_CATALOG_ROLE role.

Dynamic performance views are also called V$ views. These views provide information about the sessions.
Any Oracle user can get information from dynamic performance views if the user has the select any table privilege. This privilege is generally granted through the SELECT_CATALOG_ROLE role.

Several tools, such as ORACLE Enterprise Manager, and UTILITIES from third party are available for monitoring database PERFORMANCE. HOWEVER, all these tools or utilities depend on statistics gathered by Oracle, which are available through dynamic performance VIEWS.

Several tools, such as Oracle Enterprise Manager, and utilities from third party are available for monitoring database performance. However, all these tools or utilities depend on statistics gathered by Oracle, which are available through dynamic performance views.

You can USE the TKPROF utility to MAKE user process TRACE file more readable. TKPROF CONVERTS raw INFORMATION into formatted output in user process trace file.

You can use the TKPROF utility to make user process trace file more readable. TKPROF converts raw information into formatted output in user process trace file.

The ALERT log is a log file that records database-wide events. The information in the ALERT log file is generally USED for TROUBLE SHOOTING.
FOLLOWING events are recorded in the ALERT log file:

• Database shutdown and startup information
• All non-default parameters
• Oracle internal (ORA-600) errors
• Information about a modified control file
• At log switch

The LOCATION of ALERT log file is specified in the BACKGROUND_DUMP_DEST parameter.

The ALERT log is a log file that records database-wide events. The information in the ALERT log file is generally used for trouble shooting.
Following events are recorded in the ALERT log file:

The location of ALERT log file is specified in the BACKGROUND_DUMP_DEST parameter.

ORACLE records the information about DIFFERENT KIND of errors and the processes in the files, such as ALERT LOG, user PROCESS trace files, and background process trace files.

Oracle records the information about different kind of errors and the processes in the files, such as ALERT log, user process trace files, and background process trace files.

The OPERATING system FILES generated by utl_file are OWNED by operating system USER ORACLE.

The operating system files generated by utl_file are owned by operating system user 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.

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_profiler PACKAGE to collect and store PROFILE INFORMATION about a PL/SQL application.

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

You can USE the dbms__obfuscation_toolkit package to encrypt sensitive information. The Data ENCRYPTION STANDARD (DES) or triple DES ALGORITHM can be USED to encrypt the data.

You can use the dbms__obfuscation_toolkit package to encrypt sensitive information. The Data Encryption Standard (DES) or triple DES algorithm can be used to encrypt the data.

You can USE the DBMS_LOGMNR.START_LOGMNR PROCEDURE to INITIALIZE the LOG MINER.

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

Oracle provides a feature called log MINER, which can be used to VIEW DATA changes from PREVIOUS transactions.

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

You can use the dbms_scheduler package to CREATE and schedule a DATABASE JOB.

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

The dbms_lock PACKAGE can be USED to CREATE user-defined LOCKS.

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

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.

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

Oracle provides the dbms_flashback package to use flashback feature. The package has the following procedures to facilitate this 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 Transaction History —You can use this feature to diagnose problems, perform analysis, and audit transactions

Flashback feature provides the following benefits:

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.

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.

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

Oracle has provided a package called dbms_fga to implement fine-grained auditing. It contains the following procedures:

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 a row is changed.

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 a row is changed.

You can USE the fine-grained AUDITING FEATURE of ORACLE.

You can use the fine-grained auditing feature of Oracle.

ORACLE provides a PACKAGE called dbms_aq package to queue the messages, which can be consumed by ANOTHER SESSION or application in ORDER.

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

ALERTS are transaction-based. Whenever, a transaction CAUSING event of INTEREST commits, the alert is SIGNALED.

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

A session can register itself for a specific TYPE of alert or all alerts by USING the register procedure. Such sessions are CALLED WAITING sessions.

A session can register itself for a specific type of alert or all alerts by using the register procedure. Such sessions are called waiting sessions.