PLSQL

  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 "alte...

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