In Oracle, Segment Space Management is managing the space within a segment. In a segment generally you have USED block & UNUSED blocks.
USED blocks are blocks where a insert operation took place.
UNUSED blocks are blocks which have never used in the segment for any DML operations.
If that be the case how does Oracle identify from which point there are unused blocks ?
There is where something called HIGH WATER MARK (HWM) which comes into picture.
- HWM is the point at which data has been inserted into the table.
- All blocks beyond the HWM in a segment are unused.
- When a full table scan occurs, the optimizer read the block till HWM for that segment. —–> PERFORMANCE TIPS.
- HWM demarcates between used blocks & unused blocks in a segment.
SQL UPDATE hr.employees SET salary=salary; 107 rows updated. SQL SELECT XID, STATUS FROM V$TRANSACTION; XID STATUS —————- —————- 0800090033000000 ACTIVE SQL ROLLBACK; Rollback complete. SQL SELECT XID FROM V$TRANSACTION; no rows selected SQL UPDATE hr.employees SET last_name=last_name; 107 rows updated. SQL SELECT XID, STATUS FROM V$TRANSACTION; XID STATUS —————- —————- 0900050033000000 ACTIVE Oracle Database supports statement-level atomicity, which means that a SQL statement is an atomic unit of work and either completely succeeds or completely fails.