Interview Questions.

Q1. What Is Sga? How It Is Different From Ver 6.Zero And Ver 7.0?

The System Global Area in a Oracle database is the location in memory to enables the trfer of facts among customers. It holds the maximum these days asked structural facts between customers. It holds the most currently asked structural records approximately the database. The shape is Database buffers, Dictionary cache, Redo Log Buffer and Shared SQL pool (ver 7.Zero best) place.

Q2. How Will You Monitor Rollback Segment Status?

By the usage of dictionary views known as v$rollstat,dba_rollback_segs.

Q3. Why Query Fails Sometimes?

Due to syntax errors.

Q4. What Is Redo Log Buffer?

Changes made to entries are written to the on line redo log files. So that they can be utilized in roll forward operations during database recoveries. Before writing them into the redo log documents, they'll first delivered to redo log buffers in SGA and LGWR will write into documents frequently.

LOG_BUFFER parameter will decide the dimensions.

Q5. What Is The Use Of Show Option In Imp Command?

A flag to suggest whether file content material ought to be displayed or not.

Q6. What Is The Use Of Analyse ( Ver 7) Option In Exp Command?

A flag to suggest whether statistical records approximately the exported items ought to be written to export sell off record.

Q7. What Is Dictionary Cache?

Dictionary cache is facts about the database items saved in a statistics dictionary table.

Q8. How Will You Estimate The Space Required By A Non-clustered Tables?

Calculate the total header length

Calculate the available dataspace in step with data block

Calculate the mixed column lengths of the common row

Calculate the entire common row length.

Calculate the common number rows which can healthy in a block

Calculate the number of blocks and bytes required for the desk.

After arriving the calculation, add 10 % additional space to calculate the initial volume length for a operating table.

Q9. What Is The Use Of Indexes Option In Exp Command?

A flag to signify whether indexes on tables could be exported.

Q10. How To Implement The Multiple Control Files For An Existing Database?

Edit init.Ora record set control_files parameter with a couple of region

shutdown on the spot

replica manage report to more than one places & confirm from init.Ora contol_files parameter

start the database.

Run this query for changes affirmation - pick name from v$controlfile;

Q11. What Is The Use Of Grant Option In Exp Command?

A flag to suggest whether grants on databse objects may be exported or not. Value is 'Y' or 'N'.

Q12. What Is The Use Of Grant Option In Imp Command?

A flag to indicate whether or not presents on database objects could be imported.

Q13. What Is The Use Of File Option In Imp Command?

The name of the report from which import have to be carried out.

Q14. What Is The Use Of Record Length Option In Exp Command?

Record period in bytes.

Q15. What Is The Use Of Inctype Option In Exp Command?

Type export have to be executed COMPLETE,CUMULATIVE,INCREMENTAL

Q16. What Are The Different Kind Of Export Backups?

Full returned - Complete database

Incremental - Only affected tables from final incremental date/complete backup date.

Cumulative backup - Only affected desk from the last cumulative date/complete backup date.

Q17. What Is The Use Of Full Option In Exp Command?

A flag to indicate whether or not full databse export need to be finished.

Q18. What Is Meant By Recursive Hints?

Number of instances methods again and again question the dictionary desk is known as recursive guidelines. It is due to the statistics dictionary cache is simply too small. By growing the SHARED_POOL_SIZE parameter we can optimize the dimensions of Data Dictionary Cache.

Q19. What Is The Use Of Rows Option In Imp Command?

A flag to indicate whether or not rows ought to be imported. If this is set to 'N' then handiest DDL for database gadgets may be executed.

Q20. It Is Possible To Use Raw Devices As Data Files And What Are The Advantages Over File System Files?

Yes. It is possible to apply raw gadgets as facts documents.

The advantages over file gadget documents:

I/O may be progressed because Oracle is bye-passing the kernnel which writing into disk.

Disk Corruption may be very less.

Q21. What Is A Data Segment?

Data section are the physical regions within a database block wherein the information related to tables and clusters are saved.

Q22. What Is The Functionality Of System Table Space?

To control the database degree tractions along with changes of the statistics dictionary table that document records approximately the unfastened space utilization.

Q23. What Is A Control File?

The Control File is a small binary file essential for the database to start and function efficiently. Each Control document is associated with simplest one Oracle database. Before a database is opened, the manipulate report is examine to determine if the database is in a legitimate kingdom to USE.

The Control report isn't on hand, the database does now not characteristic well.

Q24. What Is Hit Ratio?

Hit Ratio is the ratio of shared SQL and PL/SQL gadgets found in the Library Cache versus physical garage.It can also be described in a mathematical expression as 1 - ((physical reads) / (db block receives + consistent reads)).

Q25. How Will You Swap Objects Into A Different Table Space For An Existing Database?

Export the user

Perform import using the command imp machine/supervisor document=export.Dmp indexfile=newrite.Square. This will create all definitions into newfile.Square.

Drop necessary items.

Run the script newfile.Square after changing the tablespaces.

Import from the backup for the vital items.

Q26. Which Parameter In Storage Clause Will Reduce Number Of Rows Per Block?

PCTFREE parameter,

Row size also reduces no of rows per block.

Q27. What Is The Use Of Record Option In Exp Command?

For Incremental exports, the flag indirects whether or not a document can be shops facts dictionary tables recording the export.

Q28. How Redo Logs Can Be Achieved?

LGWR manner writes all exchange vectors from the redo log buffer to on-line redo log record sequentially.

Q29. What Is Use Of Log (ver 7) Option In Exp Command?

The name of the report which log of the export can be written.

Q30. What Is Redo Log File Mirroring?

Multiplexing Redo log report called Mirroring. ( Keeping more than one copies in one-of-a-kind disks)

Q31. What Is A Shared Sql Pool?

The statistics dictionary cache is saved in a place in SGA called the Shared SQL Pool. This will permit sharing of parsed SQL statements among concurrent customers.

Q32. How Can We Plan Storage For Very Large Tables?

Limit the wide variety of extents in the table

Separate Table from its indexes.

Allocate Sufficient transient garage.

Q33. What Is The Use Of Tables Option In Exp Command?

List of tables should be exported.

Q34. List The Factors That Can Affect The Accuracy Of The Estimations?

The space used traction entries and a deleted report does no longer turn out to be free right now after of completion due to behind schedule cleanout.

Trailing nulls and period bytes are not saved. Inserts of, updates to and deletes of rows as well as columns larger than a unmarried datablock, can reason fragmentation and chained row pieces.

Q35. What Is Mean By Program Global Area (pga)?

It is location in memory this is utilized by a Single Oracle User Process.

Q36. What Is A Logical Backup?

Logical backup includes studying a set of database statistics and writing them right into a document. Export software is used for taking backup and Import utility is used to recover from backup.

Q37. How The Space Utilization Takes Place Within Rollback Segments?

By efficiently fitting surest size.

Q38. What Is The Significance Of Having Storage Clause?

We can plan the garage for a desk as how an awful lot preliminary extents are required, how a great deal can be prolonged next, how an awful lot % ought to go away loose for handling row updations and so on.

Q39. What Is Use Of Rollback Segments In Oracle Database?

When a person up to date a selected desk (as an example a hundred rows) the antique price could be retained inside the roll lower back segments(Oracle eight) and now it's miles Undo section (oracle 9i). If the user issue a rollback command the vintage price could be taken from the rollback section(that too if undo_retention parameter set well inside the parameter report).

Q40. What Is Advantage Of Having Disk Shadowing / Mirroring?

Shadow set of disks save as a backup in the occasion of disk failure. In maximum Operating System if any disk failure occurs it mechanically switchover to region of failed disk.

Improved performance due to the fact most OS assist extent shadowing can direct report I/O request to use the shadow set of files in place of the main set of documents. This reduces I/O load on the primary set of disks.

Q41. What Is The Optimal Parameter?

To keep away from the gap wastage we use OPTIMAL parameter.

Q42. What Is Cold Backup? What Are The Elements Of It?

Cold backup is taking backup of all bodily documents after ordinary shutdown of database. We need to take.

All Data documents.

All Control documents.

All on-line redo log files.

The init.Ora record (Optional)

Q43. What Is Hot Backup And How It Can Be Taken?

Taking backup of archive log files when database is open. For this the ARCHIVELOG mode ought to be enabled. The following documents want to be backed up. All records files. All Archive log, redo log files. All manipulate files.

Q44. What Is The Use Of Parfile Option In Exp Command?

Name of the parameter report to be exceeded for export.

Q45. What Are Disadvantages Of Having Raw Devices?

We have to rely on export/import application for backup/healing (fully dependable) The tar command can't be used for physical file backup, rather we can use dd command that's less bendy and has constrained recoveries.

Q46. How Does Space Allocation Table Place Within A Block?

Each block contains entries as follows:

Fixed block header

Variable block header

Row Header,row date (a couple of rows may exists)

PCTEREE (% of unfastened area for row updation in future).

Q47. How Can We Organize The Tablespaces In Oracle Database To Have Maximum Performance?

Store information in tablespaces to avoid disk contension.System tablespace-recursive callsuserdata-customers objectsindex tablespace-for indexesrollback segmnets-undo tablespace or manual rollback segmentsplace software unique records in respective tablespaces.Place these types of tablespaces in saperate disks.Try to put in force raid-0 (striping) for higher performance.

Q48. What Are The Factors Causing The Re Parsing Of Sql Statements In Sga?

Due to insufficient Shared SQL pool length.

Monitor the ratio of the reloads takes place while executing SQL statements. If the ratio is greater than 1 then growth the SHARED_POOL_SIZE.

Q49. What Is Meant By Free Extent?

A unfastened extent is a collection of continuous free blocks in tablespace. When a segment is dropped its extents are reallocated and are marked as loose.

Q50. What Is A Rollback Segment Entry?

When ever changes happened to the database previous alternate might be there inside the rollback segment.