Interview Questions.

Question 1. Explain Briefly About Oracle Database Architecture?

Answer :

Oracle database structure is a combination of example and database. Instance is a aggregate of reminiscence structures and heritage tactics which helps in studying and writing the facts to/from the database .

Question 2. Which Background Process Is Used During User Connectivity?

Answer :

PMON.

Oracle 10g Interview Questions
Question three. What Are Base Tables? When And How They Will Get Created?

Answer :

Base tables are dictionary statistics of the database. They will be created at the time of database introduction the use of SQL.BSQ script.

Question four. What Are Different Views Dba Uses? Which Script Will Create Them?

Answer :

We use records dictionary perspectives to investigate permanent records approximately the database and dynamic overall performance perspectives to get ongoing moves in the database. Both the perspectives will be created after database introduction the use of catalog.Square script.

Oracle 10g Tutorial
Question five. Why To Execute Catproc.Sq. Script?

Answer :

It will create vital applications and techniques which DBA use for certain movements.

Oracle 9i Interview Questions
Question 6. Explain The Phases Of Sql Execution?

Answer :

SQL execution incorporates 3 stages:

Parsing – in which syntax checking, semantic checking and dividing the assertion into literals will be completed
Execution – wherein parsed announcement gets transformed into ASCII layout and could be completed
Fetch – in which facts might be fetched either from database buffer cache or database.
Question 7. What Is Mean By Semantic Checking? Which Component Helps In That?

Answer :

Semantic checking approach checking for the privileges for the person or in other phrases authorizing the consumer. Base tables or dictionary will assist in doing this.

Oracle 9i Tutorial Oracle 8i Interview Questions
Question 8. What Is Server Process?

Answer :

It is a process created to assist the user technique either in studying/writing the data in the database.

Question 9. What Is The Difference Between Physical And Logical Read?

Answer :

If we fetch information from database buffer cache, then its known as logical examine. If we fetch it from database, its known as physical study as it consists of an I/O operation.

Oracle 11g Interview Questions
Question 10. Why To Maintain A Copy Of Data In Database Buffer Cache?

Answer :

When the same question is ran by way of identical or extraordinary consumer, statistics can be picked from buffer cache thus heading off I/O and improving overall performance.

Oracle 8i Tutorial
Question 11. Why Server Process Will Not Start Searching From Mru End?

Answer :

If server procedure starts searching at MRU quit,  there's a danger that records may additionally get flush from buffer cache with the aid of the ime it reaches LRU cease. In that case once more to fetch statistics, we want to do an I/O which is highly-priced. So oracle designed its structure that server procedure will search best from LRU stop.

Oracle apps Interview Questions
Question 12. What Are The Logical Structures Of The Database?

Answer :

Tablespace, section, volume and oracle statistics block are logical systems.

Oracle 10g Interview Questions
Question thirteen. What Are The 4 Mandatory Table Spaces Required To Run The Database?

Answer :

SYSTEM.
SYSAUX.
TEMP.
UNDO.
Oracle 11g Tutorial
Question 14. Can I Have A Database Without Sysaux Tablespace In 10g?

Answer :

Yes, but load may be greater on device tablespace.

Question 15. If We Have Physical Structures, Why Do We Need Logical Structures?

Answer :

Logical systems are defined to offer the easiness in upkeep.

Oracle Apps ERP Interview Questions
Question 16. Explain The Difference Between A Block, Extent And Segment?

Answer :

Block is a fundamental storage unit where as volume is a small reminiscence region allocated to a desk and segment is a item which occupies space.

Question 17. What Are Redo Entries? When And Where They Will Be Created?

Answer :

A unmarried atomic danger occurred to the database is referred to as redo entry. They could be created while we run any DML or DDL instructions or when any adjustments are performed inside the database. They can be created in PGA.

Oracle 7.Three Interview Questions
Question 18. What Is Different Status For A Block In Database Buffer Cache?

Answer :

UNUSED
FREE
PINNED
DIRTY.
Oracle 9i Interview Questions
Question 19. What Is Write-beforehand Protocol?

Answer :

LGWR writing earlier than DBWR is called write-in advance protocol.

Question 20. Will There Be Any Undo Generated In Case Of Ddl Statement Processing? If So Why?

Answer :

Yes. Reason is even tough it is a DDL externally, it is going to be DML to base tables and to rollback them usually oracle calls for undo.

Oracle ADF Interview Questions
Question 21. What Is Pga?

Answer :

PGA is a reminiscence location that's used to keep user’s unique information like session statistics and helps in executing a query.

Question 22. What Is The Difference Between Memory Allocation In 8i And 9i For Pga?

Answer :

In 8i, we want to set extraordinary man or woman parameters wherein as in 9i putting handiest one parameter PGA_AGGREGATE_TARGET might be sufficient.

Question 23. Which Is Correct? Sorting Will Take Place In Pga Or Temp Tablespace?

Answer :

Both. If the information which to be taken care of is more than sort vicinity length of PGA, then temp tablespace can be used.

Oracle Application Framework Interview Questions
Question 24. How You Will Define Instance And Database?

Answer :

INSTANCE is a aggregate of memory systems and historical past methods which allows in analyzing/writing the data. DATABASE is mix of physical and logical structures which allows in storing user information.

Oracle 8i Interview Questions
Question 25. What Is Sga?

Answer :

SGA is a aggregate of different reminiscence systems which facilitates in numerous actions inside the database.

Question 26. What Are The Responsibilities Of Smon?

Answer :

It will do example recovery, coalesces the tablespace and could release the temp segments occupied by means of a transaction while it is completed.

Oracle Apps Functional Interview Questions
Question 27. Why Oracle Maintains 2 Redolog Files?

Answer :

To avoid any area constraints.

Oracle 11g Interview Questions
Question 28. What Is Scn And Which Process Writes That?

Answer :

SCN is a unique range assigned to a transaction which modifies data inside the database. CKPT manner will constantly update this SCN to each datafiles header and manage record.

Question 29. What Are The Other New Background Processes In 10g That You Know?

Answer :

MMAN – memory supervisor which helps in keeping ASMM
RVWR – allows in producing flashback logs incase flashback is turned on
CTWR – hepls incremental backup in RMAN
MMNL – memory reveal mild, which helps in AWR records series
MMON – managebility screen, which helps in computerized trouble detection and self-tuning.
Oracle Apps DBA Interview Questions
Question 30. What Information Control File Contains?

Answer :

It contains cutting-edge SCN, all places and sizes of datafiles and redolog documents, database advent date and timestamp and controlfile parameters.

Question 31. What Is Server Parameter File And How It Is Different From Parameter File?

Answer :

Spfile is a binary document and it helps in converting parameters effectively than a pfile.

Question 32. What Is Ifile And When It Is Used?

Answer :

Ifile is a index document that could help database while pfile or spfile aren't in default location.

Oracle Workflow Interview Questions
Question 33. How To Recover If I Lost Parameter Pfile Or Spfile?

Answer :

We can recover from alert log document which includes non-default parameters.

Oracle apps Interview Questions
Question 34. If We Have Both Pfile And Spfile In Place, Which File Oracle Will Use During Startup?

Answer :

Spfile.

Question 35. What Are Hidden Parameters And Their Use?

Answer :

Hidden parameters are for use most effective on advice of oracle help and now and again they help us in supplying paintings around to any severe problem in the database.

Question 36. What Is The Purpose Of Password File?

Answer :

It is used to authenticate any consumer is connecting as SYSDBA from a remote gadget.

Oracle Apps ERP Interview Questions
Question 37. Even Though There Is A Password File, Still I Observed That Any User Is Being Able To Connect As Sysdba From Remote Machine Without A Password. What Went Wrong?

Answer :

REMOTE_LOGIN_PASSWORD isn't always set to different.

Question 38. While Creating Password File, What Is The Use Of Entries?

Answer :

To specify what number of users with sysdba position can hook up with the database remotely.

Question 39. What Is Asmm? Which Background Process Helps In That?

Answer :

Setting SGA_TARGET parameter is called ASMM. This helps in handling all SGA components mechanically and MMAN is the background process facilitates in that.

Question forty. How Database Will Behave When You Have Both Asmm And Individual Parameters Are Configured?

Answer :

Values of character parameters will act as minimum and ASMM as maximum.

Oracle 7.3 Interview Questions
Question 41. You Increase The Sga_max_size Parameter To A Higher Value. But When Trying To Increase Sga_target, Its Throwing Error That “it Cannot Increase”. What Might Be The Reason?

Answer :

Kernel parameter SHMMAX may be reached. We should growth that first.

Question 42. Which Parameter Helps Asmm To Be Affective? What Is The Value It Should Be Set To?

Answer :

SGA_TARGET and fee is dependent on no of transactions within the database.

Oracle ADF Interview Questions
Question forty three. What Is Amm In 11g?

Answer :

Managing each SGA and PGA routinely via oracle is known as Automatic Memory Management (AMM).

Question forty four. My Database Size Is 1 Tb, How Much Sga Will You Configure?

Answer :

SGA length is not dependant on database size, it relies upon on how many transactions taking place in the database.

Question forty five. What Is Alert Log? How It Is Helpful For Dba?

Answer :

It is file which helps in diagnosing all of the mistakes took place in the database.

Question 46. What Are The Contents Of Alert Log File?

Answer :

Database startup and shutdown times, non-default parameters, any logical or physical structural modifications and alloracle mistakes and so on.

Question forty seven. What Happens If We Remove Alert Log File When Database Is In Use?

Answer :

No effect on the database functionality. Oracle will create a new alert log report.

Question 48. If We Have Alert Log File To Diagnose The Problem, Why We Need Trace Files? What Are They?

Answer :

Alert log itself can not offer complete statistics about the error, in which case it's going to generate hint record. Depends on the mistake, it's going to generate heritage, core or consumer hint documents.

Question forty nine. Which Background Process Is Responsible For Writing Into Alert Log File?

Answer :

All the history procedures are responsible.

Question 50. Which Process Will Start First When Instance Is Started?

Answer :

PSP0, technique spawner. This heritage manner will begin different methods like PMON, SMON etc. But in alert log first it's going to show PMON.

Question fifty one. Which File Is Required To Place Database In Nomount State And What Happens In That Phase?

Answer :

Pfile or spfile is needed to convey database to NOMOUNT kingdom and example might be started out (backgroung processes could be began and memory can be allocated to SGA from RAM) in this phase.

Question fifty two. Which File Is Required To Bring Into Mount Phase And What Is The Use Of This Phase?

Answer :

Control report is required and it is for protection of a few database movements.

Question 53. What Files Are Needed To Open The Database And How Oracle Knows The Locations Of Them?

Answer :

Datafiles and redolog files are required to open the database and oracle will get that records from controlffiles.

Question fifty four. What You Will Do When Shutdown Immediate Command Hanging For Last 30 Min?

Answer :

We can open every other terminal and problem shut abort. Then all over again startup and do close instant.

Question fifty five. What Could Be Reason For Shutdown Immediate Command Hanging For Long Time?

Answer :

Might be there is a transaction which is huge and rollback is taking place for the same.

Question fifty six. What Is Server Result Cache? What Benefit We Get Out Of It?

Answer :

It is a brand new issue added in 11g. It will keep the rows of a query immediately this avoids the want of executing a assertion.

Question 57. What Parameters To Be Used To Make 11g Database Use Server Result Cache?

Answer :

SERVER_RESULT_CACHE.

Question 58. How To Increase The Size Of Server Result Cache?

Answer :

By increasing the dimensions specific by SERVER_RESULT_CACHE or by means of growing MEMORY_TARGET parameter if AMM is used.

Question fifty nine. Where All The Trace Files Located In 11g?

Answer :

It is in a unmarried place defined by means of DIAG_DEST parameter.

Question 60. Which Background Process Clears Sessions And Releases Locks When System Fails?

Answer :

SMON (right here system method database).

Question 61. What Is Ofa? Do You Recommend To Implement It?

Answer :

It is a rule which says database associated documents must split throughout multiple disks and sure i'm able to recommend to put into effect it.

Question sixty two. Why Oracle Recommends Ofa? How It Is Related To Os?

Answer :

As one disk may have handiest one I/O header, it is going to be burden to the database if we location all the documents in single hard disk. So oracle recommends OFA.

Question 63. What Is Dispatcher In Shared Server Architecture?

Answer :

It is a provider which server more than one consumer requests.

Question 64. As A Dba, When You Will Take Decision To Enable Shared Server Architecture?

Answer :

When we found ORA-04030 or ORA-04031 errors very frequently in alert log document.

Question sixty five. You Configured All The Parameters To Enable Shared Server Architecture, But Still Users Facing Memory Allocation Issues. What You Would Do?

Answer :

We want to check if SERVER=SHARED is cited in patron TNS access or no longer.

Question sixty six. What Are The Parameters You Use To Configure Shared Server Architecture?

Answer :

DISPATCHERS
MAX_DISPATCHERS
SHARED_SERVER_PROCESSES
MAX_SHARED_SERVER_PROCESSES.
Question sixty seven. How Many Slave Process We Can Have For Dbwr And Smon?

Answer :

DBWR – 20, SMON – sixteen .

Question 68. I Connected To Database As Sysdba, But Its Not Allowing Me To Shutdown The Database, What May Be The Reason?

Answer :

The connection would had been shared server connection wherein case oracle will now not permit to shutdown/startup the database.

Question sixty nine. What Is Sga? Define Structure Of Shared Pool Component Of Sga?

Answer :

The device global area is a collection of shared memory vicinity this is committed to oracle example. All oracle method makes use of the SGA to maintain statistics. The SGA is used to store incoming facts and internal control records that is needed with the aid of the database. 

You can manipulate the SGA reminiscence by using placing the parameter db_cache_size, shared_pool_size and log_buffer.

Shared pool element include 3 main area:  Library cache (parse SQL announcement, cursor facts and execution plan), records dictionary cache (contain cache, user account data, privilege person records, segments and extent information, statistics buffer cache for parallel execution message and manipulate structure.

Question 70. What Is The Difference Between Pga And Uga?

Answer :

When you are strolling dedicated server then procedure information stored within the procedure international vicinity (PGA) and while you are the usage of shared server then the method facts stored internal user worldwide region (UGA).

Question 71. What Is The Difference Between Data Block/extent/section?

Answer :

A information block is the smallest unit of logical storage for a database item. As items develop they take chunks of extra garage which are composed of contiguous records blocks. These groupings of contiguous statistics blocks are referred to as extents. All the extents that an item takes when grouped collectively are taken into consideration the section of the database object.

Question 72. What Is Difference Between Oracle Sid And Oracle Service Name?

Answer :

Oracle SID is the precise call that uniquely identifies your instance/database where because the service name is the TNS alias may be equal or different as SID.

Question 73. What Are Bind Variables?

Answer :

With bind variable in SQL, oracle can cache queries in a unmarried time in the SQL cache location. This avoids a tough parse on every occasion, which saves on diverse locking and latching useful resource we use to check item life and so on.

Question seventy four. What Is Log Switch?

Answer :

The point at which oracle ends writing to 1 online redo log report and starts writing to any other is called a log transfer.  Sometimes you may pressure the log switch.

ALTER SYSTEM SWITCH LOGFILE;

Question 75. What Are Log File States?

Answer :

CURRENT nation method that redo statistics are presently being written to that institution. It might be till a log switch happens. At a time there may be most effective one redo group current.

If a redo institution containing redos of a grimy buffer that redo organization is said to be ACTIVE state. As we recognize log document hold modifications made to the facts blocks then data blocks are modified in buffer cache (grimy blocks). These grimy blocks need to be written to the disk (RAM to everlasting media).

And when a redo log group incorporates no redo records belonging to a dirty buffer it's miles in an "INACTIVE" country. These inactive redo log can be overwritten.

One more state UNUSED initially while you create new redo log group its log report is empty on that time it's far unused. Later it can be any of the above stated nation.

Question 76. What Does Database Do During The Mounting Process?

Answer :

While mounting the database oracle reads the statistics from manipulate record that's used for verifying bodily database documents at some stage in sanity test. Background procedures are began earlier than mounting the database only.

Question 77. What Is The Use Of Large Pool, Which Case You Need To Set The Large Pool?

Answer :

You want to set huge pool in case you are the use of: MTS (Multi thread server) and RMAN Backups. Large pool prevents RMAN MTS from competing with other sub gadget for the same memory. RMAN uses the massive pool for backup restore when you set the DBWR_IO_SLAVES or BACKUP_TAPE_IO_SLAVES parameters to simulate asynchronous I/O. If neither of those parameters is enabled, then Oracle allocates backup buffers from neighborhood technique memory in preference to shared reminiscence. Then there's little need of large pool.

Question 78. What Happens When We Fire Sql Statement In Oracle?

Answer :

First it's going to take a look at the syntax and semantics in library cache, after that it will create execution plan. 

If already statistics is in buffer cache it's going to directly go back to the purchaser. 

If now not it's going to fetch the records from datafiles and write to the database buffer cache after that it'll ship server and finally server ship to the client.

Question 79. What Is The Main Purpose Of Checkpoint In Oracle Database? How Do You Automatically Force The Oracle To Perform A Checkpoint?

Answer :

A checkpoint is a database event, which synchronize the database blocks in reminiscence with the datafiles on disk. It has  primary functions: To set up a records consistency and enable quicker database Recovery.

The following are the parameter with a view to be utilized by DBA to adjust time or c language of how often its checkpoint should arise in database.

LOG_CHECKPOINT_TIMEOUT = 3600; # Every one hour 
LOG_CHECKPOINT_INTERVAL = a thousand; # variety of OS blocks.

Question eighty. What Is A System Change Number (scn)?

Answer :

SCN is a price this is incremented each time a grimy examine occurs.
SCN is incremented each time a impasse occurs. 
SCN is a fee that continues tune of explicit locks. 
SCN is a cost this is incremented every time database changes are made.