Oracle Interview Questions - Part-4

76. What is the OPTIMAL parameter?
It is used to set the optimal length of a rollback segment.

77. What is the functionality of SYSTEM table space?
To manage the database level transactions such as modifications of the data dictionary table that record information about the free space usage.

78. How will you create multiple rollback segments in a database?
- Create a database, which implicitly creates a SYSTEM rollback segment in a SYSTEM tablespace.
- Create a second rollback segment name R0 in the SYSTEM tablespace.
- Make new rollback segment available (after shutdown, modify init.ora file and start database)
- Create other tablespaces (RBS) for rollback segments.
- Deactivate rollback segment R0 and activate the newly created rollback segments.



79. How the space utilization takes place within rollback segments?
It will try to fit the transaction in a cyclic fashion to all existing extents. Once it found an extent is in use then it forced to acquire a new extent (number of extents is based on the optimal size)

80. Why query fails sometimes?
Rollback segment dynamically extent to handle larger transactions entry loads. A single transaction may wipeout all available free space in the rollback segment tablespace. This prevents other user using rollback segments.

81. How will you monitor the space allocation?
By querying DBA_SEGMENT table/view.

82. How will you monitor rollback segment status?
Querying the DBA_ROLLBACK_SEGS view
IN USE – Rollback Segment is on-line.
AVAILABLE – Rollback Segment available but not on-line.
OFF-LINE – Rollback Segment off-line
INVALID – Rollback Segment Dropped.
NEEDS RECOVERY – Contains data but need recovery or corrupted.
PARTLY AVAILABLE – Contains data from an unresolved transaction involving a distributed database.

83. 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.

84. 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.

85. How will you estimate the space required by a non-clustered tables?
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.

86. 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.

87. 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.

88. 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 file name. Restart the database.

89. 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.

90. 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.

91. What is use of rollback segments in Oracle database?
They allow the database to maintain read consistency between multiple transactions.

92. 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.

93. 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.

94. When will be a segment released?
When Segment is dropped.
When Shrink (RBS only).
When truncated (TRUNCATE used with drop storage option).

95. What are disadvantages of having raw devices?
We should depend on export/import utility for backup/recovery (fully reliable). The tar command cannot be used for physical file backup, instead we can use DD command, which is less flexible and has limited recoveries.

96. List the factors that can affect the accuracy of the estimations?
- The space used transaction entries and deleted records, does not become free immediately after completion due to delayed cleanout.
- Trailing nulls and length bytes are not stored.
- Inserts of, updates to and deletes of rows as well as columns larger than a single data block, can cause fragmentation a chained row pieces.
Database Security & Administration

97. What is user Account in Oracle database?
A user account is not a physical structure in database but it is having important relationship to the objects in the database and will be having certain privileges.

98. How will you enforce security using stored procedures?
Don’t grant user access directly to tables within the application. Instead grant the ability to access the procedures that access the tables. When procedure executed it will execute the privilege of procedures owner. Users cannot access tables except via the procedure.

99. What are the dictionary tables used to monitor a database space?
DBA_FREE_SPACE
DBA_SEGMENTS
DBA_DATA_FILES.



For More -- Please click HERE