Find High Water Mark in ORACLE database
Information
~~~~~~~~~~~~
Each table is made up of extents and each extent is made up of oracle blocks – a common block size is 8k.
When any table creates the high water mark will be in the ‘starting’ position.
The high water mark keep moving forward as data get saved into the database.
Scenario - Inserting data move High Water Mark forward, but When you delete data, HWM may becomes empty but high water mark will stay as it is.
For example:- Suppose you load the table with 1Million rows. Now you will have suppose the high water mark as 1 GB.
then delete all the rows, now the high water mark will be remain same as 1 GB.
The only way to reduce the HWM is to rebuild the able or use truncate table. Oracle Truncate table reduce the High water mark.
Why do we need to check HWM ?
The high water mark (HWM) has a function that comes into play with tables that have heavy insert, update and delete activity.
Every time data is changed in a table, the HWM moves to a setting in each table that shows how much free space is left in terms of blocks used and free in the segment object.
To resize any datafile to reclaim some space on the datafile. Need to check for the High water mark usage and based on that Water mark we may have to resize the respective datafile to get the space reclaimed.
We had an critical space issue on the datawarehouse environment to reclaim the space identified the datafiles using below query and resized the respective datafiles where we can get some space through this process.
STEPS to find the HWM
Step1:Check the current data file size.
SET TERMOUT OFF;
COLUMN current_instance NEW_VALUE current_instance NOPRINT;
SELECT rpad(instance_name, 17) current_instance FROM v$instance;
SET TERMOUT ON;
PROMPT
PROMPT +------------------------------------------------------------------------+
PROMPT | Report : Data File Report (all physical files) |
PROMPT | Instance : ¤t_instance |
PROMPT +------------------------------------------------------------------------+
SET ECHO OFF
SET FEEDBACK 6
SET HEADING ON
SET LINESIZE 180
SET PAGESIZE 50000
SET TERMOUT ON
SET TIMING OFF
SET TRIMOUT ON
SET TRIMSPOOL ON
SET VERIFY OFF
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN tablespace FORMAT a35 HEADING 'Tablespace Name / File Class'
COLUMN filename FORMAT a40 HEADING 'Filename'
COLUMN filesize FORMAT 9999999999999 HEADING 'File Size MB'
COLUMN autoextensible FORMAT a4 HEADING 'Auto'
COLUMN increment_by FORMAT 999999999999 HEADING 'Next in MB'
COLUMN maxbytes FORMAT 999999999999 HEADING 'Max Size MB'
BREAK ON report
COMPUTE sum OF filesize ON report
COMPUTE sum OF maxbytes ON report
SELECT /*+ ordered */
d.tablespace_name tablespace
, d.file_name filename
, d.bytes/1024/1024 filesize
, d.autoextensible autoextensible
, (d.increment_by * e.value)/1024/1024 increment_by
, d.maxbytes/1024/1024 maxbytes
FROM
sys.dba_data_files d
, v$datafile v
, (SELECT value
FROM v$parameter
WHERE name = 'db_block_size') e
WHERE
(d.file_name = v.name)
UNION
SELECT
d.tablespace_name || ' **TEMP**' tablespace
, d.file_name filename
, d.bytes/1024/1024 filesize
, d.autoextensible autoextensible
, (d.increment_by * e.value)/1024/1024 increment_by
, d.maxbytes/1024/1024 maxbytes
FROM
sys.dba_temp_files d
, (SELECT value
FROM v$parameter
WHERE name = 'db_block_size') e
UNION
SELECT
'[ ONLINE REDO LOG ]'
, a.member
, b.bytes/1024/1024
, null
, TO_NUMBER(null)
, TO_NUMBER(null)
FROM
v$logfile a
, v$log b
WHERE
a.group# = b.group#
UNION
SELECT
'[ STANDBY REDO LOG ]'
, a.member
, b.bytes/1024/1024
, null
, TO_NUMBER(null)
, TO_NUMBER(null)
FROM
v$logfile a
, v$standby_log b
WHERE
a.group# = b.group#
UNION
SELECT
'[ CONTROL FILE ]'
, a.name
, TO_NUMBER(null)
, null
, TO_NUMBER(null)
, TO_NUMBER(null)
FROM
v$controlfile a
ORDER BY 1,2;
Step 2:Will suggest a new size based on HWM.
set lines 180 pages 200
select 'alter database datafile'||' '''||file_name||''''||' resize '||round(highwater+100)||' '||'m'||';' from ( select /*+ rule */
a.tablespace_name,
a.file_name,
a.bytes/1024/1024 file_size_MB,
(b.maximum+c.blocks-1)*d.db_block_size/1024/1024 highwater
from dba_data_files a ,
(select file_id,max(block_id) maximum
from dba_extents
group by file_id) b,
dba_extents c,
(select value db_block_size
from v$parameter
where name='db_block_size') d
where a.file_id= b.file_id
and c.file_id = b.file_id
and c.block_id = b.maximum
order by a.tablespace_name,a.file_name);
Step 3:Find High Water Mark in particular Table.
SQL> SET LINESIZE 300
SET SERVEROUTPUT ON
SET VERIFY OFF
DECLARE
CURSOR cu_tables IS
SELECT a.owner,
a.table_name
FROM all_tables a
WHERE a.table_name = Decode(Upper('&&Table_Name'),'ALL',a.table_name,Upper('&&Table_Name'))
AND a.owner = Upper('&&Table_Owner')
AND a.partitioned='NO'
AND a.logging='YES'
order by table_name;
op1 NUMBER;
op2 NUMBER;
op3 NUMBER;
op4 NUMBER;
op5 NUMBER;
op6 NUMBER;
op7 NUMBER;
BEGIN
Dbms_Output.Disable;
Dbms_Output.Enable(1000000);
Dbms_Output.Put_Line('TABLE UNUSED BLOCKS TOTAL BLOCKS HIGH WATER MARK');
Dbms_Output.Put_Line('------------------------------ --------------- --------------- ---------------');
FOR cur_rec IN cu_tables LOOP
Dbms_Space.Unused_Space(cur_rec.owner,cur_rec.table_name,'TABLE',op1,op2,op3,op4,op5,op6,op7);
Dbms_Output.Put_Line(RPad(cur_rec.table_name,30,' ') ||
LPad(op3,15,' ') ||
LPad(op1,15,' ') ||
LPad(Trunc(op1-op3-1),15,' '));
END LOOP;
END;
/
REAL TIME TESTING SAMPLE
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> DELETE FROM emp WHERE eno<=25000;
25000 rows deleted.
Run the script now.
SQL> truncate table emp;
Table truncated.
Run the script again will change the high water mark:
TO RESET HIGH WATER MARK IN BELOW POSSIBLE WAYS:
Export /Import the table
Alter tablespace move
Truncate and insert table
Analyze the table
