DATABASE & SQL/PLSQL

Oacle interview Questions And Answer
Previous Home Next

List the sequence of events when a large transaction that exceeds beyond its optimal value when an entry wraps and causes the rollback segment to expand into another extend.

Transaction Begins.An entry is made in the RES header for new transactions entry Transaction acquires blocks in an extent of RBS The entry attempts to wrap into second extent. None is available, so that the RBS must extent. The RBS checks to see if it is part of its OPTIMAL size. RBS chooses its oldest inactive segment. Oldest inactive segment is eliminated. RBS extents The data dictionary tables for space management are updated. Transaction Completes.

How can we plan storage for very large tables?

Limit the number of extents in the table Separate table from its indexes. Allocate sufficient temporary storage.

How will you estimate the space required by a non-clustered table?
  • Calculate the total header size
  • Calculate the available data space per data block
  • Calculate the combined column lengths of the average row
  • Calculate the total average row size.
  • Calculate the average number rows that can fit in a block
  • Calculate the number of blocks and bytes required for the table.
  • After arriving the calculation, add 10 % additional space to calculate the initial extent size for a working table.

It is possible to use raw devices as data files and what are the advantages over file system files?

Yes.

The advantages over file system files are that I/O will be improved because Oracle is bye-passing the kernel which writing into disk. Disk corruption will be very less.

What is a Control file?

Database's overall physical architecture is maintained in a file called control file. It will be used to maintain internal consistency and guide recovery operations. Multiple copies of control files are advisable.

How to implement the multiple control files for an existing database? Shutdown the database

Copy one of the existing control file to new location Edit Config ora file by adding new control filename Restart the database.

What is redo log file mirroring? How can be achieved?

Process of having a copy of redo log files is called mirroring. This can be achieved by creating group of log files together, so that LGWR will automatically writes them to all the members of the current on-line redo log group. If any one group fails then database automatically switch over to next group. It degrades performance.

What is advantage of having disk shadowing / mirroring?

Shadow set of disks save as a backup in the event of disk failure. In most operating systems if any disk failure occurs it automatically switchover to place of failed disk. Improved performance because most OS support volume shadowing can direct file I/O request to use the shadow set of files instead of the main set of files. This reduces I/O load on the main set of disks.

What is use of rollback segments in Oracle database?

They allow the database to maintain read consistency between multiple transactions.

What is a rollback segment entry?

It is the set of before image data blocks that contain rows that are modified by a transaction. Each rollback segment entry must be completed within one rollback segment. A single rollback segment can have multiple rollback segment entries.

What is hit ratio?

It is a measure of well the data cache buffer is handling requests for data. Hit Ratio = (Logical Reads - Physical Reads - Hits Misses)/ Logical Reads.

When will be a segment released?

  • When Segment is dropped.
  • When Shrink (RBS only)
  • When truncated (TRUNCATE used with drop storage option)
Previous Home Next