Explore topic-wise InterviewSolutions in .

There are four methods for detecting block corruption, which are given as follows:

1. The ANALYZE_TABLE <table_name> VALIDATE STRUCTURE COMMAND
2. The Oracle DBVERIFY utility against the offline datafiles
3. The init.ora parameter DB_BLOCK_CHECKING, WHIHC checks data and index_blocks each time they are CREATED or MODIFIED.
4. THE DBMS_REPAIR PACKAGES used against a table, index or partition.

There are four methods for detecting block corruption, which are given as follows:

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.

Lock modes VARY from 0 to 6 in order of increasing exclusively, GIVEN as follows:

• No lock (0)
• Shared Row Exclusive lock (3)
• Shared lock (4)
• Exclusive lock (6)

You can get the information on modes of TM and TX locks by using the LMODE and REQUEST columns. Both the locks use the same numbering for lock modes.

• DML requires a Shared Row Exclusive lock, that is, TM-3.
• DDL requires an Exclusive lock, that is, TM-6.

Lock modes vary from 0 to 6 in order of increasing exclusively, given as follows:

You can get the information on modes of TM and TX locks by using the LMODE and REQUEST columns. Both the locks use the same numbering for lock modes.

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 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 the wait events for the DATABASE or a specific session from these views and find out the event, which seems too high.

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

User process trace file is USED for performance TUNING because it CONTAINS INFORMATION about execution plan and resource consumption. This information can be used for performance tuning.

These files are located in the directory specified in the BACKGROUND_DUMP_DIRECTORY parameter.

User process trace file is used for performance tuning because it contains information about execution plan and resource consumption. This information can be used for performance tuning.

These files are located in the directory specified in the BACKGROUND_DUMP_DIRECTORY parameter.

• The db_file_sequential_read event GENERALLY INDICATES index USAGE and SHOWS an access by rowid while the db_file_scattered_read event indicates full table scan.
• A single block is READ at one time in the db_file_sequential_read event while multiple blocks are read parallel in the db_file_scattered_read event.

• DB FILE scattered READ indicates a scatter read into multiple discontinuous locations.
• It generally indicates FULL table scan and that multiple blocks are being read into memory.
• Such reads are CALLED scattered read calls, because the blocks are scattered throughout memory.

Block corruption or physical corruption occurs when a block on a physical disk BECOMES unreadable or inconsistent to the STATE that the data is UNUSABLE. Block corruption can be caused by many different sources; and therefore, it is difficult to find the exact reason of block corruption.

However, it is mostly due to human error with the use of software (patches) firmware, or hardware bugs.

You can avoid this by testing all the hardware and software patches thoroughly in the test environment. In addition, you can use mirrored disk to PROTECT your data.

Block corruption or physical corruption occurs when a block on a physical disk becomes unreadable or inconsistent to the state that the data is unusable. Block corruption can be caused by many different sources; and therefore, it is difficult to find the exact reason of block corruption.

However, it is mostly due to human error with the use of software (patches) firmware, or hardware bugs.

You can avoid this by testing all the hardware and software patches thoroughly in the test environment. In addition, you can use mirrored disk to protect your data.

• Oracle offers Oracle ENTERPRISE MANAGER (OEM) to MONITOR performance.
• OEM can also be used to startup and shutdown the instance. In addition, it can be used to manage DATABASE in general.

A user process trace file is a trace file that is produced by user session. HOWEVER, this is an optional file, which is generated if the user wants to generate the file. This file is generated when the value of SQL_TRACE PARAMETER is SET to TRUE for a session.

1. This parameter can be set at DATABASE, instance, or session level.
2. If it is instance level, trace file will be generated for all the connected sessions.
3. If it is set at session level, trace file will be generated only for the specified session.
4. The LOCATION of user process trace file is specified in the USER_DUMP_DEST parameter.
5. The information in user process trace file is generally used for trouble shooting.

A user process trace file is a trace file that is produced by user session. However, this is an optional file, which is generated if the user wants to generate the file. This file is generated when the value of SQL_TRACE parameter is set to TRUE for a session.

• Enqueue is used to queue request for lock on any DB object that can't be served immediately and session is ready to wait.
• Latches are internal locking mechanism of Oracle to provide short term exclusive access to Oracle's internal objects LIKE LIBRARY cache etc.
• Another DIFFERENCE is that ENQUEUES follow first in first out (FIFO) algorithm while latches do not follow any such algorithm.

Background TRACE files are associated with background PROCESSES and are generated when CERTAIN background process experiences an error.

The information in background trace files is GENERALLY used for trouble shooting.

Background trace files are associated with background processes and are generated when certain background process experiences an error.

The information in background trace files is generally used for trouble shooting.

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

Lock is a mechanism provided by Oracle to reserve a DATABASE object so that different sessions do not interfere in each other's work.

LOCKING helps in ensuring data consistency and maintaining database objects in USABLE state in a multi user environment. However, it can cause one session to block ANOTHER.

Lock is a mechanism provided by Oracle to reserve a database object so that different sessions do not interfere in each other's work.

Locking helps in ensuring data consistency and maintaining database objects in usable state in a multi user environment. However, it can cause one session to block another.

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:

• An Oracle process must acquire relevant latch in order to get resource allocated.
• A latch is REQUIRED for a very short amount of time to ensure that the resource is allocated.

WHENEVER there is a contention for latch, it indicates that there is a performance ISSUE, which may be due to EITHER of the two following REASONS:

• Lack of availability of resource.
• Poor application programming resulting in high number of requests for resource.

Whenever there is a contention for latch, it indicates that there is a performance issue, which may be due to either of the two following reasons:

There are two different types of locks, which are given as follows:

• System locks – Held for a very brief period of time and CONTROLLED by Oracle.
• User locks – CREATED and managed USING dbms_lock package.
  
  Different types of user locks are given as follows:
• The UL lock – Defined with the dbms_lock package.
• The TX lock – ACQUIRED once for every transaction. It is a row transaction lock.
• The TM lock – Acquired once for each object, which is being changed. It is a DML lock. The IDI column identifies the object being modified.

There are two different types of locks, which are given as follows:

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-01555: snapshot too OLD error indicates that the query cannot find the snapshot it is LOOKING for in the rollback SEGMENT.

Rollback segment is designed to hold data blocks that are being CHANGED. It is required to hold old snapshot until the transaction is committed. However, it holds the data until the space is required for other TRANSACTIONS.

The ORA-01555: snapshot too old error indicates that the query cannot find the snapshot it is looking for in the rollback segment.

Rollback segment is designed to hold data blocks that are being changed. It is required to hold old snapshot until the transaction is committed. However, it holds the data until the space is required for other transactions.

You can use the V$SESSION or gv$session view in Real Application Clusters (RAC) environment to get the session information.

You can use the v$session or gv$session view in Real Application Clusters (RAC) environment to get the session information.

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.

The behavior of MERGE JOIN is INFLUENCED by the INITIALIZATION parameters:

• sort_area_size and
• db_file_multiblock_read_count

The behavior of merge join is influenced by the initialization parameters:

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

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

The db file sequential read wait event PERFORMS single block read operations against indexes, TABLES, control files, rollback segments and data file headers.

It has THREE parameters:

file#, firstblock# and block count.

The db file sequential read wait event performs single block read operations against indexes, tables, control files, rollback segments and data file headers.

It has three parameters:

file#, firstblock# and block count.

• The STATSPACK tool is an ORACLE SUPPLIED tool to monitor DATABASE performance.
• It can be used to DIAGNOSE instance WIDE problems both proactively and reactively.

Dynamic performance VIEWS are ALSO called V$ views. These views provided 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 etc.

Dynamic performance views are also called V$ views. These views provided 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 etc.

TNSNAMES.ORA and SQLNET.ORA

TNSNAMES.ORA and SQLNET.ORA

SHOW ERRORS

SHOW ERRORS

You COULD USE Logminer or STREAMS

You could use Logminer or Streams

WRAP

WRAP

You GET this error when you get a snapshot too old within rollback. It can USUALLY be solved by INCREASING the UNDO retention or increasing the size of ROLLBACKS. You should also look at the logic involved in the application getting the error message.

You get this error when you get a snapshot too old within rollback. It can usually be solved by increasing the undo retention or increasing the size of rollbacks. You should also look at the logic involved in the application getting the error message.

• STARTUP NOMOUNT – INSTANCE startup
• STARTUP MOUNT – The database is mounted
• STARTUP OPEN – The database is opened

Fact tables and DIMENSION tables. A fact table contains MEASUREMENTS while dimension tables will contain DATA that will help describe the fact tables.

Fact tables and dimension tables. A fact table contains measurements while dimension tables will contain data that will help describe the fact tables.

USE the DESCRIBE COMMAND or use the dbms_metadata.get_ddl PACKAGE.

Use the describe command or use the dbms_metadata.get_ddl package.

A data block is the smallest UNIT of logical storage for a DATABASE object. As OBJECTS grow they take chunks of ADDITIONAL storage that are composed of contiguous data BLOCKS. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.

A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.

A HOT backup is basically TAKING a backup of the DATABASE while it is STILL up and running and it must be in archive log mode. A cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode. The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can RECOVER the database to any point in time. The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.

A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode. A cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode. The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can recover the database to any point in time. The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.