CODEWITHSUNDEEP
oracle-databasedatabase-partitioning
yktoo
yktoo

Reputation: 2986

Partitions and High Water Mark in Oracle

High Water Mark (HWM) for a table in an Oracle Database is the line between blocks that have been used and ones that have never been. Truncating a table resets the HWM to zero.

Now, if I have a partitioned table, I'd like to know if the following is true:

  1. Does each partition maintain its own HWM?
  2. If not, does alter table ... drop partition ... affect table's HWM?

The idea is I'd like to populate partition's tables with insert /*+ append */ (direct path insert), but it only writes data beyond the HWM, so will the space be reused if I recreate the partition? I failed to find information on this specific aspect.

Upvotes: 3

Views: 4213

Answers (2)

Florin Ghita
Florin Ghita

Reputation: 17643

In addition to Dave Costa's answer, response to second question: If you truncate the partition, the HWM will be at zero, so the space will be regained for direct path insert(space will be used). If you drop the partition, the space will be free for any other segment to be used. In particular, for your new partition.

So, in fewer words:

  • if you truncate the partition, space will be available for this partition.
  • if you drop the partition, the space will be free and usable for every segment in the tablespace.

Also, another trick to use if you want to reuse the space is to do an alter table move partition. This will "recreate" the partition, without loosing the data. There are more details, but this is your question about.

Upvotes: 1

Dave Costa
Dave Costa

Reputation: 48121

Each partition is a separate segment, so each would have its own HWM. I presume that truncating the whole table would reset the HWM for all partitions. You can truncate individual partitions as well, which certainly would reset the HWM for the partition.

Upvotes: 4

Related Questions