Interview Questions.

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

Several tools, such as Oracle Enterprise Manager, and utilities from 1/3 party are to be had for tracking database performance.

However, a lot of these equipment or utilities depend upon statistics accumulated through Oracle, that are to be had thru dynamic overall performance perspectives.

Q2. What Do The Db_file_sequential_read And Db_file_scattered_read Events Indicate?

The db_file_sequential_read occasion usually indicates index utilization and shows an get entry to through rowid at the same time as the db_file_scattered_read occasion indicates complete desk experiment.

A single block is study at one time in the db_file_sequential_read event while more than one blocks are study parallel inside the db_file_scattered_read occasion.

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 report sequential study wait event performs single block read operations towards indexes, tables, manipulate documents, rollback segments and statistics report headers.

It has three parameters:

report#, firstblock# and block be counted.

Q5. What Is An Event?

An event is an Oracle is an occurrence that considerably alters the way your database executes or plays.

There are two kinds of activities in Oracle:

wait events 

OEM described 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 whilst you get a picture too old within rollback. It can commonly be solved by using growing the undo retention or growing the dimensions of rollbacks. You need to also study the common sense concerned in the software getting the error message.

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

A brief tablespace is used for brief gadgets which includes type systems at the same time as everlasting tablespaces are used to store those objects supposed to be used as the real items of the database.

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

ARCHIVELOG mode is a method that you may positioned the database in for growing a backup of all tractions that have took place within the database so that you can recover to any factor in time. NOARCHIVELOG mode is largely the absence of ARCHIVELOG mode and has the downside of no longer being able to recover to any point in time.

NOARCHIVELOG mode does have the advantage of no longer having to jot down tractions to an archive log and as a result increases the overall performance of the database barely.

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

A warm backup is basically taking a backup of the database whilst it is nevertheless up and walking and it ought to be in archive log mode. A bloodless backup is taking a backup of the database even as it's miles shut down and does no longer require being in archive log mode. The advantage of taking a hot backup is that the database is still to be had for use while the backup is happening and you could recover the database to any factor in time. The benefit of taking a cold backup is that it's far generally simpler to administer the backup and healing procedure. In addition, considering you're taking bloodless backups the database does no longer require being in archive log mode and for that reason there will be a moderate performance benefit as the database isn't 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 takes place when a block on a physical disk becomes unreadable or inconsistent to the nation that the records is unusable. Block corruption may be due to many exceptional resources; and consequently, it's miles hard to locate the exact purpose of block corruption.

However, it's miles normally because of human blunders with using software program (patches) firmware, or hardware bugs.

You can keep away from this by using trying out all the hardware and software program patches very well in the check surroundings. In addition, you could use reflected disk to defend your data.

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

You can handle ORA- 01403: no information discovered errors by way of terminating the processing for the choose announcement.

Q14. What Is A Latch? How It Is Used In Oracle?

A latch is a semaphore or an on/off transfer in Oracle database that a method need to get entry to if you want to behavior certain sort of activities. Latches govern the usage of Oracle's inner assets through its techniques.

They put into effect serial get admission to to the sources and limit the quantity of time for which a unmarried manner can use a useful resource.

There are over eighty latches available in Oracle.

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

Oracle statistics the information approximately different form of errors and the procedures in the documents, consisting of ALERT log, consumer technique hint documents and background procedure trace files.

Q16. Give The Reasoning Behind Using An Index?

Faster get admission to to information blocks in a table.

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

A information block is the smallest unit of logical storage for a database item. As items develop they take chunks of additional garage which might be composed of contiguous records blocks. These groupings of contiguous information blocks are known as extents. All the extents that an object takes while grouped together are considered the segment of the database object.

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

SHOW ERRORS

Q19. What Does Coalescing A Tablespace Do?

Coalescing is simplest valid for dictionary-controlled tablespaces and de-fragments space by combining neighboring loose extents into massive single extents.

Q20. How Would You Force A Log Switch?

ALTER SYSTEM SWITCH LOGFILE;

Q21. Explain Wait Events?

Wait event takes place when a user method is saved waiting because of some problem, along with an I/O bottleneck or a hectic CPU.

The information about wait occasion is to be had in the V$SYSTEM_WAIT and V$SESSION_WAIT dynamic overall 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 question the v$db_cache_advice table. If a change become essential then I would use the adjust 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 bundle.

Q25. Describe The Oracle Wait Interface?

The Oracle Wait Interface is the set of records dictionary tables that keep facts about wait occasions.

Oracle offers multiple perspectives to present data approximately wait activities, along with v$system_event and v$session_event.

You can get the statistics approximately the wait events for the database or a specific consultation from those perspectives and find out the event, which appears too high.

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

In unix $ORACLE_HOME/rdbms/audit, in Windows the occasion 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 manage report to hint.

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

These two Oracle utilities are used for loading statistics into the database. The distinction is that the import software is based at the records being produced with the aid of another Oracle application EXPORT while the SQL*Loader application allows information to be loaded that has been produced via other utilities from one-of-a-kind records sources in order that long because it conforms to ASCII formatted or delimited files.

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

An Oracle technique should gather relevant latch a good way to get aid allocated.

A latch is needed for a totally brief amount of time to make certain that the aid is allotted.

Whenever there is a rivalry for latch, it indicates that there is a overall performance problem, which can be due to both of the 2 following reasons:

Lack of availability of useful resource.

Poor software programming ensuing in high wide variety of requests for aid.

Q32. Why Union All Faster Than Union?

The UNION operation gets rid of redundancy whilst UNION ALL does now not; consequently, the UNION operation needs to carry out sort. As a result, UNION ALL plays higher as it does not need to perform any kind.

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

Grant the CONNECT to the consumer.

Q34. Give Two Examples Of Referential Integrity Constraints?

Number one key and a overseas key.

Q35. Explain Materialized Views And How They Are Used?

Materialized perspectives are objects which might be decreased units of statistics which have been summarized, grouped, or aggregated from base tables. They are typically used in information warehouse or decision guide structures.

Q36. What Is The Use Of Alert Log File?

The ALERT log is a log file that facts database huge occasions. The information in the ALERT log document is commonly used for problem capturing.

Following occasions are recorded in the ALERT log record:

Database shutdown and startup statistics

All non default parameters

Oracle internal (ORA – 600) errors

Information about a modified control file

At log switch

The place of ALERT log report is special inside 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 figure, drop the table, re-create the table, permit the foreign key constraint.

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

User process hint report is used for performance tuning because it incorporates statistics about execution plan and aid intake. This information may be used for overall performance tuning.

These documents are located in the listing certain 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 reality table incorporates measurements even as dimension tables will incorporate records that will assist describe the fact tables.

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

Pick DBTIMEZONE from twin;

Q42. Which Parameters Affect The Behaviour Of Merge Join?

The conduct of merge join is prompted by means of 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 supplied by means of Oracle to reserve a database item so that exceptional periods do no longer intrude in each other's paintings.

Locking helps in ensuring facts consistency and preserving database items in usable state in a multi user surroundings. However, it can motive one consultation to dam every other.

Q47. How Can You Rebuild An Index?

ALTER INDEX REBUILD;

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

A function and process are the same in that they are meant to be a collection of PL/SQL code that contains a unmarried challenge. While a method does no longer have to go back any values to the calling utility, a characteristic will go back a single price. A bundle on the other hand is a set of capabilities and approaches which might be grouped collectively primarily based on their commonality to a business characteristic or software.

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

Partitioning is a way of taking big tables and indexes and splitting them into smaller, greater attainable pieces.

Q50. What Is The Difference Between Latches And Enqueues?

Enqueue is used to queue request for lock on any db object that can not be served immediately and consultation is ready to attend.

Latches are internal locking mechanism of Oracle to provide short term one of a kind access to Oracle's internal objects like library cache etc.

Another difference is that enqueues observe first in first out (FIFO) algorithm while latches do no longer observe such a algorithm.