Top 50 Oracle Dba Troubleshooting Interview Questions You Must Prepare 23.Jul.2024

Q1. How Can You Monitor Performance Of The Database Proactively?

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

Q2. What Do The Db_file_sequential_read And Db_file_scattered_read Events Indicate?

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

Q3. What Type Of Index Should You Use On A Fact Table?

Bitmap index.

Q4. What Is Db File Sequential Read Wait Event?

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.

Q5. What Is An Event?

An event is an Oracle is an occurrence that substantially alters the way your database executes or performs.

There are two types of events in Oracle:

  • wait events 
  • OEM defined events.

Q6. Give The Stages Of Instance Start Up To A Usable State Where Normal Users May Access It?

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

Q7. Explain An Ora-01555?

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.

Q8. What Is The Difference Between A Temporary Tablespace And A Permanent Tablespace?

A temporary tablespace is used for temporary objects such as sort structures while permanent tablespaces are used to store those objects meant to be used as the true objects of the database.

Q9. Explain The Difference Between Archivelog Mode And Noarchivelog Mode And The Benefits And Disadvantages To Each?

ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all tractions that have occurred in the database so that you can recover to any point in time. NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any point in time.
NOARCHIVELOG mode does have the advantage of not having to write tractions to an archive log and thus increases the performance of the database slightly.

Q10. Explain The Difference Between A Hot Backup And A Cold Backup And The Benefits Associated With Each?

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.

Q11. What View Would You Use To Determine Free Space In A Tablespace?

DBA_FREE_SPACE

Q12. What Is The Main Reason For Block Corruption?

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.

Q13. How Do You Handle Ora-01403: No Data Found Error?

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

Q14. What Is A Latch? How It Is Used 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.

Q15. Name A Few Places You Will Look To Get More Details On A Performance Issue?

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.

Q16. Give The Reasoning Behind Using An Index?

Faster access to data blocks in a table.

Q17. Explain The Difference Between A Data Block, An Extent And A Segment.?

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.

Q18. You Have Just Compile A Pl/sql Package But Got Errors, How Would You View The Errors?

SHOW ERRORS

Q19. What Does Coalescing A Tablespace Do?

Coalescing is only valid for dictionary-managed tablespaces and de-fragments space by combining neighboring free extents into large single extents.

Q20. How Would You Force A Log Switch?

ALTER SYSTEM SWITCH LOGFILE;

Q21. Explain Wait Events?

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

Q22. How Do You Add A Data File To A Tablespace?

ALTER TABLESPACE ADD DATAFILE SIZE

Q23. How Would You Go About Increasing The Buffer Cache Hit Ratio?

Use the buffer cache advisory over a given workload and then query the v$db_cache_advice table. If a change was necessary then I would use the alter system set db_cache_size command.

Q24. Give Two Examples Of How You Might Determine The Structure Of The Table Dept?

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

Q25. Describe The Oracle Wait Interface?

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

Q26. Where In The Oracle Directory Tree Structure Are Audit Traces Placed?

In unix $ORACLE_HOME/rdbms/audit, in Windows the event viewer

Q27. How Can You Gather Statistics On A Table?

The ANALYZE command.

Q28. Where Would You Look For Errors From The Database Engine?

In the alert log.

Q29. What Command Would You Use To Create A Backup Control File?

Alter database backup control file to trace.

Q30. What Is The Difference Between The Sql*loader And Import Utilities?

These two Oracle utilities are used for loading data into the database. The difference is that the import utility relies on the data being produced by another Oracle utility EXPORT while the SQL*Loader utility allows data to be loaded that has been produced by other utilities from different data sources just so long as it conforms to ASCII formatted or delimited files.

Q31. What Is The Significance Of Latches With Respect To Performance Tuning?

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

Q32. Why Union All Faster Than Union?

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.

Q33. When Creating A User, What Permissions Must You Grant To Allow Them To Connect To The Database?

Grant the CONNECT to the user.

Q34. Give Two Examples Of Referential Integrity Constraints?

primary key and a foreign key.

Q35. Explain Materialized Views And How They Are Used?

Materialized views are objects that are reduced sets of information that have been summarized, grouped, or aggregated from base tables. They are typically used in data warehouse or decision support systems.

Q36. What Is The Use Of Alert Log 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.

Q37. What Background Process Refreshes Materialized Views?

The Job Queue Processes.

Q38. A Table Is Classified As A Parent Table And You Want To Drop And Re-create It. How Would You Do This Without Affecting The Children Tables?

Disable the foreign key constraint to the parent, drop the table, re-create the table, enable the foreign key constraint.

Q39. Which Trace File Is Used For Performance Tuning And Why?

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.

Q40. Give The Two Types Of Tables Involved In Producing A Star Schema And The Type Of Data They Hold?

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

Q41. How Would You Determine The Time Zone Under Which A Database Was Operating?

select DBTIMEZONE from dual;

Q42. Which Parameters Affect The Behaviour Of Merge Join?

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

  • sort_area_size and
  • db_file_multiblock_read_count

Q43. How Would You Determine What Sessions Are Connected And What Resources They Are Waiting For?

Use of V$SESSION and V$SESSION_WAIT

Q44. When A User Process Fails, What Background Process Cle Up After It?

PMON

Q45. How Do You Switch From An Init.ora File To A Spfile?

Issue the create spfile from pfile command.

Q46. What Is A Lock?

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.

Q47. How Can You Rebuild An Index?

ALTER INDEX REBUILD;

Q48. Explain The Difference Between A Function, Procedure And Package?

A function and procedure are the same in that they are intended to be a collection of PL/SQL code that carries a single task. While a procedure does not have to return any values to the calling application, a function will return a single value. A package on the other hand is a collection of functions and procedures that are grouped together based on their commonality to a business function or application.

Q49. Explain What Partitioning Is And What Its Benefit Is?

Partitioning is a method of taking large tables and indexes and splitting them into smaller, more manageable pieces.

Q50. What Is The Difference Between Latches And Enqueues?

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