Understanding DELETE vs TRUNCATE In Oracle Database

split_partition

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.

Shoumadip Das

Hi Folks, I am Shoumadip Das hailing from Kolkata, India welcoming you to explore my blog www.oraclemasterpiece.com. I am a self motivated and successful professional working in a leading IT Giant for more than 10 years.My area of expertise includes –

  • Oracle Database Programming (SQL & PL/SQL)
  • Perl Programming
  • Unix Programming

Read More

One thought on “Understanding LOCKING In Database”

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

Leave a Reply

Your email address will not be published. Required fields are marked *