Enter your email address to subscribe to this blog and receive notifications of new posts by email. It ignores block chaining and row migration completely. There is no single system table which contains the high water mark hwm for a table. Where can one find the high water mark for a table. Handson note about hadoop, cloudera, hortonworks, nosql, cassandra, neo4j, mongodb, oracle, sql server, linux, etc. This option is useful if you have longrunning queries that might span the operation and attempt to read from blocks that have been reclaimed.
The oracle professionals or dbas should know how to accurately predict the highwater mark of ram demands for their database, and fully allocate the ram, reserving only enough to accommodate. Use sql to check datafile highwater mark for resizing. 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. When you specify compact, oracle database defragments the segment space and compacts the table rows but postpones the resetting of the high water mark and the deallocation of the space until a future time. Therefore, we need to check them before we process them. 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. In oracle 9i, 10g and 11g there is actually two hwm a lower high water mark lhwm and a higher high water mark hhwm. This video tutorial is to understand the concept of a high water mark or hvm of a oracle table, also it states the various changes in the hvm when we perform a insert, delete and truncate. My question is regarding high water mark in tablespace. Pdf self tuning of oracle database using sql scripts. Second i ran a loop to shrink each tables on the tablespace withr alter table enable row movement.
There is probably a way to get that high water mark in a cheaper way. Table high water mark tips oracle consulting, oracle. It ignores blocks that were used but arent anymore eg. I expect this will consolidate each table and lower the high water mark.
What is high water markhvm in oracle and how it effects. Posts about resizing tempfile written by pavan dba. Show the hwm for a datafile and whether they can be. Show the hwm for a datafile and whether they can be resized.
487 909 1231 113 1061 174 305 1196 92 478 1070 216 445 674 1199 1129 1547 743 933 472 63 854 472 1394 1267 1047 1006 1478 1509 1520 826 1024 1536 733 617 333 92 132 1069 1454 156 885 1436 1082 355 1404 640