Oracle: lower table high water mark tips

 Question:  I have deleted rows from a large table, but the space has not been released.  What are the different ways that I can lower the table high-water mark and release the disk space to be reused by another table?

Answer:  You are correct, when rows are deleted from a tables, Oracle does not release the free space for other tables, and you must explicitly lower the table high-water mark. 

Oracle leaves the high-water mark alone after rows are deleted, and you can reclaim space at the table level with these techniques, all of which lower the high water mark for the table, thereby freeing-up the space:

  • export-import - For a complete reorganization and space reclamation, export/import allows you to restructure your files and reclaim lost space.
     

  • dbms_redefinition - This procedure will reorganize a table while it remains online for updates.
     

  • "alter table xxx shrink" - - If you were 10g and beyond you could use "alter table xxx shrink space compact;" syntax.

  • You can look at the coalesce table syntax.  Unlike the "deallocate unused space" syntax which removes space above the high-water mark, "coalesce" puts together discontiguous fragmented extents.  There are two type of space fragmentation in Oracle.  First is the honeycomb fragmentation, when the free extents are side by side, and the "Swiss Cheese" fragmentation, when the extents are separated by live segments.
     
    alter table xxx coalesce;

  • You can deallocate unused space - Oracle notes that the "deallocate unused space" clause is used to to explicitly deallocate unused space at "the end" of a segment and makes that space available for other segments within the tablespace. 
     
    alter table xxx deallocate unused space;
    alter index xxx deallocate unused space;
     
    Internally, Oracle deallocates unused space beginning from the end of the objects (allocated space) and moving downwards toward the beginning of the object, continuing down until it reaches the high water mark (HWM).  

  • For indexes, "deallocate unused space" coalesces all leaf blocks within same branch of b-tree, and quickly frees up index leaf blocks for use.

  •  अनुक्रमितों के लिए, "अप्रयुक्त स्थान को निपटाएं" बी-पेड़ की एक ही शाखा के भीतर सभी पत्ती ब्लॉकों को समेटता है, और उपयोग के लिए जल्दी से सूचकांक पत्ती ब्लॉकों को मुक्त करता है।

Comments

Post a Comment

Popular posts from this blog

Table high water mark tips

  Question:  Can you please explain the concept of a table high water mark (HWM)?  I understand that the data is always below the high water mark.  Can you show a script to display the high water mark for a table? Answer:   The high water mark (HWM) for an Oracle table is a construct that shows the table at its greatest size.  Just as a lake has a high-water mark after a draught, an Oracle table has a high water mark that shows the greatest size of the table, the point at which it consumed the most extents. Also see this great script to  display all high water marks . As a table undergoes deletes and updates, rows shrink and table data blocks become empty.  For performance reasons, Oracle keeps the high water mark for a table rather than re-calculate the high water mark after blocks at the "end" of the table (the last extent) becomes empty. For example assume that you have a million row table that takes 30 seconds to read.  After deleting 900...
Read more

Create table of two consecutive months if date range belong to two months

select to_date('01-JUL-2019')+level-1 "JULY", CASE WHEN MONTHS_BETWEEN('09-AUG-2019','01-JUL-19')>1 THEN CASE WHEN LEVEL<=TO_NUMBER(TO_CHAR(TO_DATE('09-AUG-2019'),'DD')) THEN ADD_MONTHS('01-JUL-2019',1)+LEVEL-1 END END "August" FROM DUAL CONNECT BY LEVEL<=CASE WHEN MONTHS_BETWEEN('09-AUG-2019', '01-JUL-19')>1 THEN TO_NUMBER(TO_CHAR(LAST_DAY('01-JUL-2019'),'DD')) ELSE TO_DATE('09-AUG-2019')-TO_DATE('01-JUL-2019') +1 END ----------------------------------- ANOTHER METHOD ----------------------------------- SELECT "JULY", "AUGUST" FROM ( SELECT ROWNUM RN, CASE WHEN TO_CHAR(TO_DATE(MNTH),'MON')='JUL' THEN MONTH END "JULY" FROM ( SELECT TO_DATE('01-JUL-2019')+LEVEL-1 MNTH FROM DUAL CONNECT BY LEVEL<=TO_DATE('09-AUG-19')-T0_DATE('01-JUL-2019')+1 ...
Read more

Fill null values with last non-null amount - Oracle SQL

  I have a table that has 4 columns: Item, Year, Month, Amount. Some of the values for Amount are null and when that happens I want to fill those values in with the previous Amount value that is not null. I can easily do this with the LAG function when there is only one null value but when there are multiple in a row I am not sure how to approach it. Below is an example of what the table might look like with an added column for what I want to add in my query: Item | Year | Month | Amount | New_Amount AAA | 2013 | 01 | 100 | 100 AAA | 2013 | 02 | | 100 AAA | 2013 | 03 | 150 | 150 AAA | 2013 | 04 | 125 | 125 AAA | 2013 | 05 | | 125 AAA | 2013 | 06 | | 125 AAA | 2013 | 07 | | 125 AAA | 2013 | 08 | 175 | 175 I had two ideas which I can't seem to get to work to produce what I want. First I was going to use LAG but then I noticed when there are multiple null values in a row it won't satisfy that. Next I was going...
Read more