Oracle interview Questions
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Oracle Concepts and Architecture
Memory Management
Qus. . What is meant by free extent?
Ans: A free extent is a collection of continuous free blocks in tablespace.
When a segment is dropped its extents are reallocated and are marked as free.
Qus. .Which parameter in Storage clause will reduce number of rows per block?
Ans: PCTFREE parameter
Row size also reduces no of rows per block.
Qus. . What is the significance of having storage clause?
Ans: We can plan the storage for a table as how much initial extents are
required, how much can be extended next, how much % should leave free for
managing row updating, etc.,
Qus. . How does Space allocation table place within a block?
Ans: Each block contains entries as follows
Fixed block header
Variable block header
Row Header, row date (multiple rows may exists)
PCTEREE (% of free space for row updating in future)
Qus. . What is the role of PCTFREE parameter is storage clause?
Ans: This is used to reserve certain amount of space in a block for
expansion of rows.
Qus. . What is the OPTIMAL parameter?
Ans: It is used to set the optimal length of a rollback segment.
Qus. . What is the functionality of SYSTEM table space?
Ans: To manage the database level transactions such as modifications of the
data dictionary table that record information about the free space usage.
Qus. . 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.
Qus. . How the space utilization takes place within rollback segments?
Ans: 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)
Qus. . Why query fails sometimes?
Ans: 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.
Qus. . How will you monitor the space allocation?
Ans: By querying DBA_SEGMENT table/view
Qus. . How will you monitor rollback segment status?
Ans: 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.
1 2 3 4 5 6 7 8 9 10 11 12 13
14 15
