Interview Questions.

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

Answer :

A warm backup is essentially taking a backup of the database at the same time as it's miles still up and jogging and it should be in archive log mode. A bloodless backup is taking a backup of the database while it's far close down and does not require being in archive log mode. The advantage of taking a warm backup is that the database remains available for use while the backup is taking place and you could recover the database to any point in time. The advantage of taking a chilly backup is that it is normally easier to manage the backup and healing manner. In addition, since you take cold backups the database does no longer require being in archive log mode and consequently there will be a moderate overall performance advantage as the database isn't slicing archive logs to disk.

Question 2. You Have Just Had To Restore From Backup And Do Not Have Any Control Files. How Would You Go About Bringing Up This Database?

Answer :

I might create a text based backup manipulate file, stipulating wherein on disk all of the data documents wherein after which trouble the get better command with the using backup manage file clause.

Oracle 10g Interview Questions
Question 3. How Do You Switch From An Init.Ora File To A Spfile?

Answer :

Issue the create spfile from pfile command.

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

Answer :

A statistics block is the smallest unit of logical storage for a database item. As items grow they take chunks of extra storage which are composed of contiguous statistics blocks. These groupings of contiguous records blocks are known as extents. All the extents that an object takes when grouped collectively are considered the phase of the database item.

Oracle 10g Tutorial
Question five. Give Two Examples Of How You Might Determine The Structure Of The Table Dept?

Answer :

Use the describe command or use the dbms_metadata.Get_ddl bundle.

Oracle 9i Interview Questions
Question 6. Where Would You Look For Errors From The Database Engine?

Answer :

In the alert log.

Question 7. Compare And Contrast Truncate And Delete For A Table?

Answer :

Both the truncate and delete command have the preferred outcome of having rid of all the rows in a desk. The distinction among the two is that the truncate command is a DDL operation and just moves the excessive water mark and produces a now rollback. The delete command, then again, is a DML operation, with a view to produce a rollback and consequently take longer to finish.

Oracle 9i Tutorial Oracle 8i Interview Questions
Question 8. Give The Reasoning Behind Using An Index?

Answer :

Faster get right of entry to to facts blocks in a desk.

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

Answer :

Fact tables and measurement tables. A reality desk incorporates measurements whilst dimension tables will incorporate facts in an effort to help describe the fact tables.

Oracle 11g Interview Questions
Question 10. What Type Of Index Should You Use On A Fact Table?

Answer :

Bitmap index.

Oracle 8i Tutorial
Question 11. Give Two Examples Of Referential Integrity Constraints?

Answer :

number one key and a overseas key.

Oracle apps Interview Questions
Question 12. 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?

Answer :

Disable the overseas key constraint to the figure, drop the table, re-create the desk, permit the foreign key constraint.

Oracle 10g Interview Questions
Question thirteen. Explain The Difference Between Archivelog Mode And Noarchivelog Mode And The Benefits And Disadvantages To Each?

Answer :

ARCHIVELOG mode is a mode that you may placed the database in for developing a backup of all transactions that have came about within the database so you can recover to any point in time. NOARCHIVELOG mode is largely the absence of ARCHIVELOG mode and has the drawback of no longer being able to get better to any factor in time.
NOARCHIVELOG mode does have the benefit of no longer having to put in writing transactions to an archive log and hence will increase the overall performance of the database slightly.

Oracle 11g Tutorial
Question 14. What Command Would You Use To Create A Backup Control File?

Answer :

Alter database backup manipulate document to trace.

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

Answer :

STARTUP NOMOUNT – Instance startup
STARTUP MOUNT – The database is installed
STARTUP OPEN – The database is opened
Oracle Apps ERP Interview Questions
Question 16. What Column Differentiates The V$ Views To The Gv$ Views And How?

Answer :

The INST_ID column which shows the example in a RAC surroundings the data got here from.

Question 17. How Would You Go About Generating An Explain Plan?

Answer :

Create a plan desk with utlxplan.Square.
Use the give an explanation for plan set statement_id = ‘tst1’ into plan_table for a SQL announcement
Look on the give an explanation for plan with utlxplp.Square or utlxpls.Square
Oracle 7.Three Interview Questions
Question 18. How Would You Go About Increasing The Buffer Cache Hit Ratio?

Answer :

Use the buffer cache advisory over a given workload after which query the v$db_cache_advice table. If a trade turned into essential then I might use the modify device set db_cache_size command.

Oracle 9i Interview Questions
Question 19. Explain An Ora-01555?

Answer :

You get this error while you get a image too old inside rollback. It can normally be solved through growing the undo retention or increasing the scale of rollbacks. You should additionally look at the logic concerned inside the software getting the error message.

Question 20. Explain The Difference Between $oracle_home And $oracle_base?

Answer :

ORACLE_BASE is the basis listing for oracle. ORACLE_HOME placed underneath ORACLE_BASE is in which the oracle products live.

Oracle ADF Interview Questions
Question 21. How Would You Determine The Time Zone Under Which A Database Was Operating?

Answer :

pick DBTIMEZONE from dual;

Question 22. Explain The Use Of Setting Global_names Equal To True?

Answer :

Setting GLOBAL_NAMES dictates how you might connect with a database. This variable is either TRUE or FALSE and if it's miles set to TRUE it enforces database links to have the same name because the far off database to which they are linking.

Question 23. What Command Would You Use To Encrypt A Pl/sq. Application?

Answer :

WRAP

Oracle Application Framework Interview Questions
Question 24. Explain The Difference Between A Function, Procedure And Package?

Answer :

A feature and technique are the identical in that they may be intended to be a group of PL/SQL code that contains a unmarried undertaking. While a system does not ought to return any values to the calling software, a function will return a unmarried cost. A bundle alternatively is a collection of capabilities and methods which might be grouped collectively based on their commonality to a commercial enterprise characteristic or utility.

Oracle 8i Interview Questions
Question 25. Explain The Use Of Table Functions?

Answer :

Table features are designed to go back a set of rows thru PL/SQL good judgment however are meant for use as a normal table or view in a SQL statement. They also are used to pipeline information in an ETL procedure.

Question 26. Name Three Advisory Statistics You Can Collect?

Answer :

Buffer Cache Advice
Segment Level Statistics
Timed Statistics
Oracle Apps Functional Interview Questions
Question 27. Where In The Oracle Directory Tree Structure Are Audit Traces Placed?

Answer :

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

Oracle 11g Interview Questions
Question 28. Explain Materialized Views And How They Are Used?

Answer :

Materialized perspectives are items that are reduced units of data that have been summarized, grouped, or aggregated from base tables. They are typically used in records warehouse or selection aid systems.

Question 29. When A User Process Fails, What Background Process Cleans Up After It?

Answer :

PMON

Oracle Apps DBA Interview Questions
Question 30. What Background Process Refreshes Materialized Views?

Answer :

The Job Queue Processes.

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

Answer :

Use of V$SESSION and V$SESSION_WAIT

Question 32. Describe What Redo Logs Are?

Answer :

Redo logs are logical and physical systems which might be designed to keep all of the modifications made to a database and are meant to aid inside the healing of a database.

Oracle Workflow Interview Questions
Question 33. How Would You Force A Log Switch?

Answer :

ALTER SYSTEM SWITCH LOGFILE;

Oracle apps Interview Questions
Question 34. Give Two Methods You Could Use To Determine What Ddl Changes Have Been Made?

Answer :

You may want to use Logminer or Streams

Question 35. What Does Coalescing A Tablespace Do?

Answer :

Coalescing is handiest valid for dictionary-managed tablespaces and de-fragments area by way of combining neighboring loose extents into large single extents.

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

Answer :

A brief tablespace is used for brief gadgets which include kind structures while everlasting tablespaces are used to save those objects supposed to be used because the actual gadgets of the database.

Oracle Apps ERP Interview Questions
Question 37. Name A Tablespace Automatically Created When You Create A Database?

Answer :

The SYSTEM tablespace.

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

Answer :

Grant the CONNECT to the consumer.

Question 39. How Do You Add A Data File To A Tablespace?

Answer :

ALTER TABLESPACE ADD DATAFILE SIZE

Question 40. How Do You Resize A Data File?

Answer :

ALTER DATABASE DATAFILE RESIZE ;

Oracle 7.3 Interview Questions
Question 41. What View Would You Use To Look At The Size Of A Data File?

Answer :

DBA_DATA_FILES

Question forty two. What View Would You Use To Determine Free Space In A Tablespace?

Answer :

DBA_FREE_SPACE

Oracle ADF Interview Questions
Question forty three. How Would You Determine Who Has Added A Row To A Table?

Answer :

Turn on great grain auditing for the desk.

Question 44. How Can You Rebuild An Index?

Answer :

ALTER INDEX REBUILD;

Question 45. Explain What Partitioning Is And What Its Benefit Is?

Answer :

Partitioning is a method of taking huge tables and indexes and splitting them into smaller, extra plausible pieces.

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

Answer :

SHOW ERRORS

Question 47. How Can You Gather Statistics On A Table?

Answer :

The ANALYZE command.

Question 48. How Can You Enable A Trace For A Session?

Answer :

Use the DBMS_SESSION.SET_SQL_TRACE or Use ALTER SESSION SET SQL_TRACE = TRUE;

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

Answer :

These  Oracle utilities are used for loading facts into the database. The distinction is that the import application is based on the records being produced by using every other Oracle utility EXPORT at the same time as the SQL*Loader utility permits records to be loaded that has been produced via other utilities from distinct information sources in order that long as it conforms to ASCII formatted or delimited files.

Question 50. Name Two Files Used For Network Connection To A Database?

Answer :

TNSNAMES.ORA and SQLNET.ORA

Question fifty one. What Are Dynamic Performance Views?

Answer :

Dynamic performance perspectives also are known as V$ perspectives. These views provided data approximately the periods.

Any Oracle person can get information from dynamic overall performance views if the person has the pick out any table privilege. This privilege is usually granted thru the SELECT_CATALOG_ROLE and many others.

Question fifty two. What Is The Statspack Tool?

Answer :

The STATSPACK device is an ORACLE furnished tool to display database overall performance.
It can be used to diagnose example huge problems each proactively and reactively.
Question fifty three. What Is Db File Sequential Read Wait Event?

Answer :

The db file sequential read wait occasion performs single block examine operations towards indexes, tables, manage files, rollback segments and information report headers.

It has three parameters:

record#, firstblock# and block depend.

Question 54. Explain Wait Events?

Answer :

Wait occasion happens whilst a person procedure is kept waiting due to a few trouble, consisting of an I/O bottleneck or a busy CPU.
The data about wait occasion is available in the V$SYSTEM_WAIT and V$SESSION_WAIT dynamic overall performance views.
Question 55. What Is Cost Based Optimizer?

Answer :

Cost primarily based optimizer is the optimizer aspect of the Oracle, which is usually recommended and supported by way of Oracle.
It determines question plans primarily based on common price of utilization of every useful resource to get the first-rate possible plan with appreciate to the aid utilization cost.
It makes use of internal information to determine the best execution plan for the assertion.
Question 56. Which Parameters Affect The Behaviour Of Merge Join?

Answer :

The behavior of merge be a part of is encouraged by way of the initialization parameters:

sort_area_size and
db_file_multiblock_read_count
Question fifty seven. Why Union All Faster Than Union?

Answer :

The UNION operation gets rid of redundancy while UNION ALL does not; therefore, the UNION operation wishes to carry out type. As a end result, UNION ALL performs better because it does not want to perform any type.

Question 58. How Can You Get More Details About The Blocking Session?

Answer :

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

Question 59. What Is The Ora-01555: Snapshot Too Old Error? How Can It Be Avoided?

Answer :

The ORA-01555: snapshot too old errors suggests that the query can't discover the photograph it is seeking out in the rollback section.

Rollback phase is designed to keep statistics blocks that are being modified. It is needed to preserve old photo till the transaction is dedicated. However, it holds the facts until the gap is needed for other transactions.

Question 60. How Do You Handle Ora – 01403: No Data Found Error?

Answer :

You can manage ORA- 01403: no facts discovered mistakes by terminating the processing for the SELECT statement.

Question 61. What Are Different Types Of Locks?

Answer :

There are two one-of-a-kind forms of locks, which can be given as follows:

System locks – Held for a completely quick period of time and controlled by means of Oracle.
User locks – Created and controlled using dbms_lock package.

Different forms of consumer 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 as soon as for every item, that's being modified. It is a DML lock. The IDI column identifies the object being modified.
Question 62. What Is The Significance Of Latches With Respect To Performance Tuning?

Answer :

An Oracle system have to collect applicable latch in an effort to get aid allocated.
A latch is required for a totally short amount of time to make certain that the resource is allocated.
Whenever there's a contention for latch, it suggests that there is a overall performance trouble, which can be because of either of the 2 following motives:

Lack of availability of resource.
Poor software programming ensuing in excessive number of requests for resource.
Question 63. What Is The Use Of Alert Log File?

Answer :

The ALERT log is a log file that facts database wide activities. The information in the ALERT log record is usually used for trouble shooting.

Following events are recorded within the ALERT log report:

Database shutdown and startup records
All non default parameters
Oracle inner (ORA – six hundred) errors
Information approximately a changed control record
At log transfer
The location of ALERT log record is distinctive within the BACKGROUND_DUMP_DEST parameter.
Question 64. What Is A Lock?

Answer :

Lock is a mechanism supplied with the aid of Oracle to order a database object in order that extraordinary sessions do not interfere in every other's paintings.

Locking facilitates in ensuring information consistency and maintaining database gadgets in usable state in a multi consumer surroundings. However, it could purpose one session to block another.

Question sixty five. What Is A Latch? How It Is Used In Oracle?

Answer :

A latch is a semaphore or an on/off switch in Oracle database that a technique ought to access in an effort to behavior positive kind of activities. Latches govern using Oracle's inner resources by its procedures.
They implement serial get right of entry to to the assets and restriction the amount of time for which a single process can use a resource.
There are over 80 latches to be had in Oracle.
Question sixty six. What Are The Background Trace Files?

Answer :

Background hint files are associated with background processes and are generated whilst sure history system stories an error.

The information in historical past trace files is commonly used for problem shooting.

Question sixty seven. What Is The Difference Between Latches And Enqueues?

Answer :

Enqueue is used to queue request for lock on any db object that can not be served immediately and session is ready to attend.
Latches are inner locking mechanism of Oracle to offer quick term different get right of entry to to Oracle's internal objects like library cache and so on.
Another difference is that enqueues comply with first in first out (FIFO) algorithm whilst latches do now not follow one of these set of rules.
Question sixty eight. What Is A User Process Trace File?

Answer :

A person manner hint report is a trace file this is produced by way of person consultation. However, this is an optionally available record, that is generated if the person desires to generate the report. This report is generated whilst the value of SQL_TRACE parameter is about to TRUE for a consultation.

This parameter can be set at database, instance, or session stage.
If it is example degree, trace document may be generated for all of the connected periods.
If it is set at consultation degree, trace document could be generated best for the desired consultation.
The place of consumer system trace record is special within the USER_DUMP_DEST parameter.
The facts in consumer manner trace report is normally used for trouble shooting.
Question 69. Which Tools Are Available To Monitor Performance?

Answer :

Oracle gives Oracle Enterprise Manager (OEM) to monitor overall performance.
OEM can also be used to startup and shutdown the example. In addition, it is able to be used to control database in popular.
Question 70. What Is An Event?

Answer :

An occasion is an Oracle is an prevalence that substantially alters the way your database executes or performs.

There are two varieties of activities in Oracle:

wait occasions 
OEM described activities.
Question seventy one. What Is The Main Reason For Block Corruption?

Answer :

Block corruption or physical corruption takes place while a block on a physical disk turns into unreadable or inconsistent to the country that the data is unusable. Block corruption can be due to many exclusive assets; and therefore, it is difficult to discover the exact reason of block corruption.

However, it's far on the whole due to human error with the use of software (patches) firmware, or hardware bugs.

You can keep away from this by using testing all the hardware and software patches thoroughly in the test environment. In addition, you may use mirrored disk to protect your facts.

Question 72. What Do You Understand By Db File Scattered Read?

Answer :

Db report scattered study shows a scatter read into a couple of discontinuous places.
It generally shows complete desk test and that a couple of blocks are being read into reminiscence.
Such reads are known as scattered read calls, due to the fact the blocks are scattered all through memory.
Question seventy three. What Do The Db_file_sequential_read And Db_file_scattered_read Events Indicate?

Answer :

The db_file_sequential_read occasion usually suggests index usage and suggests an get right of entry to with the aid of rowid whilst the db_file_scattered_read event shows complete table experiment.
A unmarried block is examine at one time within the db_file_sequential_read event while more than one blocks are read parallel in the db_file_scattered_read event.
Question seventy four. Which Trace File Is Used For Performance Tuning And Why?

Answer :

User technique hint record is used for overall performance tuning because it carries statistics approximately execution plan and resource intake. This data may be used for performance tuning.

These documents are placed inside the directory certain within the BACKGROUND_DUMP_DIRECTORY parameter.

Question 75. How Can You Monitor Performance Of The Database Proactively?

Answer :

Several tools, along with Oracle Enterprise Manager, and utilities from 1/3 celebration are to be had for monitoring database overall performance.
However, these types of equipment or utilities rely upon information collected by Oracle, that are to be had through dynamic overall performance perspectives.
Question 76. Describe The Oracle Wait Interface?

Answer :

The Oracle Wait Interface is the set of information dictionary tables that save information approximately wait events.
Oracle gives multiple views to provide information about wait occasions, consisting of v$system_event and v$session_event.
You can get the records approximately the wait activities for the database or a selected consultation from those views and find out the event, which appears too excessive.
Question 77. Name A Few Places You Will Look To Get More Details On A Performance Issue?

Answer :

Oracle records the statistics approximately distinctive type of mistakes and the tactics in the documents, which includes ALERT log, person process trace files and history technique hint files.

Question 78. What Are The Different Types Of Locking Modes?

Answer :

Lock modes range from zero to 6 so as of growing completely, given as follows:

No lock (0)
Shared Row Exclusive lock (3)
Shared lock (4)
Exclusive lock (6)
You can get the facts 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 calls for a Shared Row Exclusive lock, this is, TM-three.
DDL requires an Exclusive lock, that is, TM-6.
Question seventy nine. How Do You Handle Ora-01403: No Data Found Error?

Answer :

You can manage ORA- 01403: no data located errors with the aid of terminating the processing for the pick assertion.

Question 80. How Can You Detect Block Corruption?

Answer :

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

The ANALYZE_TABLE <table_name> VALIDATE STRUCTURE command
The Oracle DBVERIFY application in opposition to the offline datafiles
The init.Ora parameter DB_BLOCK_CHECKING, whihc checks records and index_blocks whenever they may be created or changed.
THE DBMS_REPAIR applications used towards a desk, index or partition.