HWM

[sudip]

Hi!
From which table will I get HWM for a table?
Thanx

[ngwh]

wat's a HWM ???

[sudip]

HWM - High Water Mark

[jjongman]

v$license

desc v$license
Name
---------------------------------
SESSIONS_MAX
SESSIONS_WARNING
SESSIONS_CURRENT
SESSIONS_HIGHWATER
USERS_MAX

[sudip]

But I want HWM value for a specific Table.

[kgb]

I think you are talking about this:

'Bytes' - number of bytes which table takes.
It is difference betwen "Rollback segment" and "Table segment" as second one can't shrink so I think HWM for a table that is number of bytes reserved for a table.

select segment_name,bytes from dba_segments where segment_type = 'TABLE';

Best wishes!

[vinit]

kgb, I think that will give u only the space occupied by the table object and not the high water mark.

Cheers

[ngwh]

you can use the DBMS_SPACE package, which contains a procedure (UNUSED_SPACE) that returns information about the position of the
high water mark and the amount of unused space in a segment.

Within a segment, the high water mark indicates the amount of used space, or space that had been formatted to receive data.You cannot release space below the
high water mark (even if there is no data in the space you wish to deallocate). However, if the segment is completely empty, you can release space using the
TRUNCATE...DROP STORAGE statement.

[kgb]

So it looks like a just number of bytes reserved by segment.
Who mind?

[bmycroft]

The thing about overly high - high water marks (HWM) is that any full table scans read all those empty unused block up to the high water mark. You might get a high HWM if a lot of data has been deleteed from a table.

The following script will show you the segments with a high - high HWM.

rem hwm8.sql
rem Description:
rem This script lists all of the tables specified by owner,
rem where the High Water Mark is say 20% larger than the actual
rem data in the tables.
rem This will indicate which tables require a rebuild.
rem
rem History
rem
rem Date Who Description
rem 31/05/2001 JAD Created
rem
rem
rem Notes:
rem This script generates another script(hwm_get_sql.lst), which
rem it calls at the end. The hwm_get_sql.lst file is NOT deleted
rem when it is finished.
rem You do NOT need to run an analyze on the tables before running
rem this script
rem The ROWID format changed between Oracle 7 and Oracle 8.
rem ################################
rem THIS SCRIPT IS FOR ORACLE 8 ONLY
rem ################################
rem
set echo off
set heading off
set pagesize 0
set feedback off
set linesize 1000
set trimspool on
set wrap on
set verify off
rem Get the variables
accept table_owner char prompt 'Enter the table owner: '
accept percentage_larger number default 20 prompt 'Enter the percentage larger threshold the HWM can be [default 20]: '
prompt This may take a while...
rem
set termout off
rem
spool hwm_get_sql.lst
prompt set echo off
prompt set heading off
prompt set termout on
prompt set verify off

rem Step 1 - First generate the script to calc hwm and data for each table in schema.

select 'select owner,segment_name,hwm,actual_data from'||chr(10)||
' (SELECT round((COUNT(DISTINCT '||chr(10)||
'SUBSTR(rowid,1,15))+1)*'||vp.value/1024||'/1024) as Actual_Data FROM '||chr(10)||
ds.owner||'.'||ds.segment_name||') ad,'||chr(10)||
'(select s.owner,s.segment_name,round(s.BLOCKS*'||vp.value/1024||'/1024) as HWM '||chr(10)||
'from dba_segments s, dba_tables t where s.owner='''||ds.owner||''' '||chr(10)||
'and s.segment_name='''||ds.segment_name||''' and t.owner=s.owner and t.table_name=s.segment_name) hw'||chr(10)||
' where hw.hwm>(ad.actual_data*&&percentage_larger/100)+ad.actual_data'||
' and ad.actual_data >0;'
from dba_segments ds, dba_tables dt, v$parameter vp
where ds.owner=upper('&&Table_Owner') and
ds.segment_name=dt.table_name and
ds.owner=dt.owner
and vp.name='db_block_size'
order by segment_name
/
spool off

rem STEP 2 - Now Generate the output

rem spool hwm.lst
set termout on
column Owner format A10 heading Owner
column segment_name format a40
column hwm format 999,999,999
column actual_data format 999,999,999

prompt High Water Mark Report - This will indicate tables that require rebuilding.
prompt Owner Table HWM(Mb) Data(Mb)
start hwm_get_sql.lst
REM spool off (hwm.lst)