Segment and storage structures
PCTFREE = specifies what percentage of block should be allocated as free space for future updates (default 10)
PCTUSED = specifies when the block can be considered for adding new rows (default 40)
PCTFREE + PCTUSED <= 100
Blocks are smallest logical unit of storage in Oracle database.
An extent is logical storage unit made of contiguous data blocks.
Segment is logical storage unit made up of one or more extents.
Types of segments are – table, table partition, cluster, nested table, index, index organized table, index partition, temporary, LOB, undo, bootstrap.
PCTUSED = specifies when the block can be considered for adding new rows (default 40)
PCTFREE + PCTUSED <= 100
Blocks are smallest logical unit of storage in Oracle database.
An extent is logical storage unit made of contiguous data blocks.
Segment is logical storage unit made up of one or more extents.
Types of segments are – table, table partition, cluster, nested table, index, index organized table, index partition, temporary, LOB, undo, bootstrap.
Undo segment
When a user performs an update or deletes operation, the earlier data is saved to undo segments and then actual data is modified to new value. In case of insert operation, rowid of new rows are stored in undo segments.
Undo data is not deleted immediately after commit or rollback. How long it will stay in undo segment depends on UNDO_RETENTION parameter in initialization file.
When a transaction is rolled back, Oracle restores the earlier data from undo segments.
From Oracle 9i, undo management can be automatically controlled.
To use automatic undo management, set following parameters in initialization
file.
UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=table space name
Undo data is not deleted immediately after commit or rollback. How long it will stay in undo segment depends on UNDO_RETENTION parameter in initialization file.
When a transaction is rolled back, Oracle restores the earlier data from undo segments.
From Oracle 9i, undo management can be automatically controlled.
To use automatic undo management, set following parameters in initialization
file.
UNDO_MANAGEMENT=AUTO
UNDO_TABLESPACE=table space name
Creating undo segment
CREATE UNDO TABLESPACE undo DATAFILE
‘/oradata/mydb/undo01.dbf’ SIZE 20M
To specify different table space as undo table space dynamically – issue
ALTER SYSTEM SET UNDO_TABLESPACE=undo02
‘/oradata/mydb/undo01.dbf’ SIZE 20M
To specify different table space as undo table space dynamically – issue
ALTER SYSTEM SET UNDO_TABLESPACE=undo02
Extent or segment related data dictionary views
DBA_EXTENTS
DBA_FREE_SPACE
DBA_SEGMENTS
V$SORT_SEGMENT
DBA_ROLLBACK_SEGS
V$ROLLNAME
V$ROLLSTAT
V$UNDOSTAT
DBA_FREE_SPACE
DBA_SEGMENTS
V$SORT_SEGMENT
DBA_ROLLBACK_SEGS
V$ROLLNAME
V$ROLLSTAT
V$UNDOSTAT
No comments:
Post a Comment