Clever Geek Handbook

Sections and high water mark in Oracle

The high water mark (HWM) for a table in an Oracle database is the line between used blocks and those that never were. Truncating the table resets the HWM to zero.

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

  • Does each section support its own HWM?
  • If not, does it alter table ... drop partition ...

    affect the HWM table?

The idea is that I would like to populate the partition tables with insert /*+ append */

(direct path insertion), but it only writes data outside of the HWM, so the space will be reused if I recreate the partition? I was unable to find information on this particular aspect.

+3


source to share


2 answers


Each section is a separate segment, so each will have its own HWM. I am guessing that truncating the entire table will reset the HWM for all partitions. You can also trim individual partitions, which will definitely reset the HWM for the partition.



+4


source


In addition to Dave Costa's answer, the answer to the second question is, if you truncate the partition, the HWM will be at zero, so the space will be reclaimed to insert the forward path (the space will be used). If you delete a partition, the space is free for any other segment that will be used. Specifically for your new section.

So, fewer words:



  • If you truncate a section, space will be available for that section.
  • If you release the partition, the space is free for every segment in the tablespace as well.

Also, another trick to use if you want to reuse space is to do this alter table move partition

. This "recreates" the partition without losing data. There is more detailed information, but this is your question.

+1


source






More articles:


All Articles