Handson note about hadoop, cloudera, hortonworks, nosql, cassandra, neo4j, mongodb, oracle, sql server, linux, etc. Enter your email address to subscribe to this blog and receive notifications of new posts by email. Table high water mark tips oracle consulting, oracle. Second i ran a loop to shrink each tables on the tablespace withr alter table enable row movement.
Since oracle knows that blocks beyond the high water mark dont have data, it only reads blocks up to the high water mark in a full table scan. The low high water mark lhwm is similar to the older high water mark in releases of oracle before 9i, in that all of the blocks below it have been formated for use by oracle. What is high water markhvm in oracle and how it effects. 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. This option is useful if you have longrunning queries that might span the operation and attempt to read from blocks that have been reclaimed. Just like after a flood you can never reduce the high water mark on a building, unless you rip the building down and build a new one. There is no single system table which contains the high water mark hwm for a table. There is probably a way to get that high water mark in a cheaper way. 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. How to lower down high water mark of a data file oracle. My question is regarding high water mark in tablespace. Therefore, we need to check them before we process them. Pdf self tuning of oracle database using sql scripts.
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. Show the hwm for a datafile and whether they can be. Where can one find the high water mark for a table. It ignores blocks that were used but arent anymore eg. Show the hwm for a datafile and whether they can be resized oracle database.
1262 798 1052 687 1599 949 380 414 99 141 580 689 921 810 351 1158 1155 1307 1206 914 1110 1146 1218 503 487 928 450 1355 147 1555 1402 922 1514 109 5 370 1105 858 744 570 1038 1115 1496 687