Interview Questions.

Top 100+ Oracle Memory Management Interview Questions And Answers

fluid

Top 100+ Oracle Memory Management Interview Questions And Answers

Question 1. What Is The Significance Of Having Storage Clause?

Answer :

We can plan the storage for a table as how a great deal initial extents are required, how a good deal can be prolonged subsequent, how a whole lot % have to leave loose for managing row updations and so forth.

Question 2. What Is The Functionality Of System Table Space?

Answer :

To control the database level transactions inclusive of modifications of the facts dictionary table that document statistics about the free space utilization.

Oracle 9i Interview Questions
Question three. How Does Space Allocation Table Place Within A Block?

Answer :

Each block consists of entries as follows:

Fixed block header
Variable block header
Row Header,row date (multiple rows may exists)
PCTEREE (% of free area for row updation in destiny).
Question 4. What Is The Role Of Pctfree Parameter Is Storage Clause?

Answer :

This is used to order sure quantity of area in a block for growth of rows.

Oracle 9i Tutorial
Question five. What Is The Optimal Parameter?

Answer :

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

Oracle DBA Interview Questions
Question 6. What Is A Shared Pool?

Answer :

The Shared Pool environment incorporates both constant and variable structures.
The Fixed systems continue to be highly the identical length, while the variable structures develop and shrink based totally on consumer and software requirements.
Used To Store
Most Recently Executed SQL Statements
Most Recently used Data definitions
It Consists of  Key performance - associated reminiscence structures
Library Cache & Data Dictionary Cache
Shared Pool is sized via SHARED_POOL_SIZE
Question 7. What Is Mean By Program Global Area (pga)?

Answer :

It is area in memory that is utilized by a Single Oracle User Process.

Oracle DBA Tutorial Oracle 11g Interview Questions
Question 8. What Is A Data Segment?

Answer :

Data section are the bodily areas inside a database block wherein the records related to tables and clusters are stored.

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

Answer :

Due to inadequate Shared SQL pool length.
Monitor the ratio of the reloads takes vicinity at the same time as executing SQL statements. If the ratio is greater than 1 then boom the SHARED_POOL_SIZE.
Oracle Data Integrator (ODI) Interview Questions
Question 10. What Is Database Buffers?

Answer :

Database buffers are cache inside the SGA used to maintain the records blocks which are study from the facts segments within the database which includes tables, indexes and clusters DB_BLOCK_BUFFERS parameter in INIT.ORA decides the size.

Oracle 11g Tutorial
Question 11. What Is Dictionary Cache?

Answer :

Dictionary cache is facts approximately the database objects stored in a records dictionary table.

Oracle MySQL 5.6 Database Administrator Interview Questions
Question 12. Which Parameter In Storage Clause Will Reduce Number Of Rows Per Block?

Answer :

PCTFREE parameter,
Row size additionally reduces no of rows in line with block.
Oracle 9i Interview Questions
Question thirteen. What Is Meant By Free Extent?

Answer :

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

Question 14. How Will You Force Database To Use Particular Rollback Segment?

Answer :

For precise transaction Alter machine set rollback phase 'name';

For database, we are able to set in pfile. Rollback_segment='name' .

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

Answer :

Store facts in tablespaces to avoid disk contension.Machine tablespace-recursive callsuserdata-customers objectsindex tablespace-for indexesrollback segmnets-undo tablespace or guide rollback segmentsplace software precise data in respective tablespaces.Place most of these tablespaces in saperate disks.Try to implement raid-0 (striping) for better overall performance.

Oracle Exadata Database Interview Questions
Question sixteen. How Will You Swap Objects Into A Different Table Space For An Existing Database?

Answer :

Export the user
Perform import the use of the command imp system/supervisor file=export.Dmp indexfile=newrite.Sq.. This will create all definitions into newfile.Square.
Drop necessary gadgets.
Run the script newfile.Square after changing the tablespaces.
Import from the backup for the essential items.
Question 17. What Is Redo Log Buffer?

Answer :

Changes made to entries are written to the online redo log documents. So that they can be utilized in roll ahead operations at some stage in database recoveries. Before writing them into the redo log documents, they may first brought to redo log buffers in SGA and LGWR will write into files often.

LOG_BUFFER parameter will decide the dimensions.

Oracle DBA Troubleshooting Interview Questions
Question 18. What Is Meant By Recursive Hints?

Answer :

Number of instances procedures repeatedly question the dictionary desk is referred to as recursive suggestions. It is due to the statistics dictionary cache is just too small. By growing the SHARED_POOL_SIZE parameter we will optimize the dimensions of Data Dictionary Cache.

Oracle DBA Interview Questions
Question 19. How Can We Plan Storage For Very Large Tables?

Answer :

Limit the range of extents within the table
Separate Table from its indexes.
Allocate Sufficient brief garage.
Question 20. How Will You Estimate The Space Required By A Non-clustered Tables?

Answer :

Calculate the total header size
Calculate the available dataspace in keeping with facts block
Calculate the combined column lengths of the common row
Calculate the full common row size.
Calculate the average number rows that can in shape in a block
Calculate the variety of blocks and bytes required for the desk.
After arriving the calculation, add 10 % extra area to calculate the preliminary quantity size for a operating table.

Oracle Demantra Interview Questions
Question 21. It Is Possible To Use Raw Devices As Data Files And What Are The Advantages Over File System Files?

Answer :

Yes. It is viable to use uncooked gadgets as records documents.

The advantages over report machine files:

I/O can be advanced because Oracle is bye-passing the kernnel which writing into disk.
Disk Corruption may be very much less.
Question 22. What Is A Control File?

Answer :

The Control File is a small binary file necessary for the database to begin and operate efficiently. Each Control record is related to handiest one Oracle database. Before a database is opened, the control file is read to determine if the database is in a valid kingdom to USE.

The Control document isn't handy, the database does not characteristic nicely.

Question 23. How Will You Monitor Rollback Segment Status?

Answer :

By the use of dictionary views referred to as v$rollstat,dba_rollback_segs.

Oracle Access Manager Interview Questions
Question 24. How Will You Monitor The Space Allocation?

Answer :

This can be monitored in DB_data_files.

Oracle 11g Interview Questions
Question 25. Why Query Fails Sometimes?

Answer :

Due to syntax errors.

Question 26. How The Space Utilization Takes Place Within Rollback Segments?

Answer :

By effectively fitting most advantageous length.

Oracle Data Guard Interview Questions
Question 27. How Will You Create Multiple Rollback Segments In A Database?

Answer :

create rollback segment roll1tablespace roll1.

Oracle Data Integrator (ODI) Interview Questions
Question 28. What Is A Rollback Segment Entry?

Answer :

When ever modifications befell to the database previous trade could be there within the rollback section.

Question 29. What Is Hit Ratio?

Answer :

Hit Ratio is the ratio of shared SQL and PL/SQL items located within the Library Cache as opposed to bodily storage.It can also be defined in a mathematical expression as 1 - ((bodily reads) / (db block receives + steady reads)).

Oracle Core Dba Interview Questions
Question 30. What Are Disadvantages Of Having Raw Devices?

Answer :

We need to rely on export/import software for backup/healing (completely dependable) The tar command can not be used for physical record backup, alternatively we can use dd command that's less flexible and has limited recoveries.

Question 31. What Is Use Of Rollback Segments In Oracle Database?

Answer :

When a user updated a particular table (for example 100 rows) the old cost can be retained within the roll lower back segments(Oracle eight) and now it's miles Undo section (oracle 9i). If the person issue a rollback command the vintage fee can be taken from the rollback section(that too if undo_retention parameter set properly within the parameter document).

Question 32. What Is Advantage Of Having Disk Shadowing / Mirroring?

Answer :

Shadow set of disks keep as a backup within the event of disk failure. In maximum Operating System if any disk failure occurs it robotically switchover to place of failed disk.

Improved performance because maximum OS help volume shadowing can direct file I/O request to apply the shadow set of documents instead of the primary set of documents. This reduces I/O load on the primary set of disks.

Oracle D2K Interview Questions
Question 33. How Redo Logs Can Be Achieved?

Answer :

LGWR system writes all trade vectors from the redo log buffer to on line redo log report sequentially.

Oracle MySQL five.6 Database Administrator Interview Questions
Question 34. What Is Redo Log File Mirroring?

Answer :

Multiplexing Redo log record referred to as Mirroring. ( Keeping multiple copies in exceptional disks)

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

Answer :

Edit init.Ora document set control_files parameter with multiple region
shutdown immediately
reproduction manipulate file to a couple of locations & affirm from init.Ora contol_files parameter
begin the database.
Run this query for changes confirmation - pick call from v$controlfile;
Question 36. What Is Sga? How It Is Different From Ver 6.Zero And Ver 7.Zero?

Answer :

The System Global Area in a Oracle database is the vicinity in reminiscence to helps the transfer of records among customers. It holds the maximum currently requested structural facts between users. It holds the maximum these days requested structural information approximately the database. The shape is Database buffers, Dictionary cache, Redo Log Buffer and Shared SQL pool (ver 7.Zero handiest) place.

Oracle Exadata Database Interview Questions
Question 37. What Is A Shared Sql Pool?

Answer :

The records dictionary cache is saved in a place in SGA referred to as the Shared SQL Pool. This will allow sharing of parsed SQL statements amongst concurrent customers.

Question 38. List The Factors That Can Affect The Accuracy Of The Estimations?

Answer :

The space used transaction entries and a deleted record does no longer grow to be free without delay after of entirety due to delayed cleanout.

Trailing nulls and length bytes are not saved. Inserts of, updates to and deletes of rows as well as columns large than a single datablock, can motive fragmentation and chained row pieces.

Question 39. What Are The Different Kind Of Export Backups?

Answer :

Full returned - Complete database
Incremental - Only affected tables from remaining incremental date/complete backup date.
Cumulative backup - Only affected desk from the final cumulative date/complete backup date.
Question forty. What Is Cold Backup? What Are The Elements Of It?

Answer :

Cold backup is taking backup of all bodily files after ordinary shutdown of database. We want to take.

All Data documents.
All Control files.
All on-line redo log files.
The init.Ora file (Optional)
Oracle DBA Troubleshooting Interview Questions
Question forty one. What Is A Logical Backup?

Answer :

Logical backup involves analyzing a hard and fast of database information and writing them into a document. Export software is used for taking backup and Import software is used to recover from backup.

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

Answer :

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

Oracle Demantra Interview Questions
Question 43. What Is The Use Of File Option In Exp Command?

Answer :

To provide the export report call.

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

Answer :

A flag to signify whether grants on databse objects might be exported or no longer. Value is 'Y' or 'N'.

Question forty five. What Is The Use Of Indexes Option In Exp Command?

Answer :

A flag to indicate whether or not indexes on tables will be exported.

Question 46. What Is The Use Of Rows Option In Exp Command?

Answer :

Flag to signify whether desk rows should be exported. If 'N' only DDL statements for the database gadgets will be created.

Question forty seven. What Is The Use Of Parfile Option In Exp Command?

Answer :

Name of the parameter document to be surpassed for export.

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

Answer :

A flag to indicate whether or not statistical facts about the exported gadgets should be written to export sell off document.

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

Answer :

A flag to suggest whether complete databse export must be carried out.

Question 50. What Is The Use Of Owner Option In Exp Command?

Answer :

List of desk money owed have to be exported.

Question fifty one. What Is The Use Of Tables Option In Exp Command?

Answer :

List of tables must be exported.

Question fifty two. What Is The Use Of Record Length Option In Exp Command?

Answer :

Record duration in bytes.

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

Answer :

Type export ought to be achieved COMPLETE,CUMULATIVE,INCREMENTAL

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

Answer :

For Incremental exports, the flag indirects whether or not a record will be stores facts dictionary tables recording the export.

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

Answer :

A flag to suggest whether rows should be imported. If that is set to 'N' then simplest DDL for database items will be accomplished.

Question fifty six. What Is The Use Of Indexes Option In Imp Command?

Answer :

A flag to signify whether import need to import index on tables or not.

Question fifty seven. What Is The Use Of Grant Option In Imp Command?

Answer :

A flag to signify whether or not grants on database objects will be imported.

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

Answer :

A flag to indicate whether report content must be displayed or now not.

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

Answer :

The call of the report from which import must be performed.

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

Answer :

The call of the file which log of the export can be written.




CFG