Answer:
Please see these scripts
to display the high water mark for a table. The following code
examples will display the high water mark for a table.
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('&&1'),'ALL',a.table_name,Upper('&&1'))
AND
a.owner = Upper('&&2');
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;
/
SET VERIFY ON
In this version of an Oracle table high-water mark script, you pass the table_name as an argument to the code:
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;
/
Here is another script to display the HWM fr an Oracle
table. Note that you must specify the table_name in
the script:
set linesize 300
set serveroutput on
set verify off
declare
l_fs1_bytes number;
l_fs2_bytes number;
l_fs3_bytes number;
l_fs4_bytes number;
l_fs1_blocks
number;
l_fs2_blocks number;
l_fs3_blocks number;
l_fs4_blocks number;
l_full_bytes number;
l_full_blocks number;
l_unformatted_bytes number;
l_unformatted_blocks number;
begin
dbms_space.space_usage(
segment_owner => user,
segment_name => 'TEST',
segment_type => 'TABLE',
fs1_bytes => l_fs1_bytes,
fs1_blocks => l_fs1_blocks,
fs2_bytes => l_fs2_bytes,
fs2_blocks => l_fs2_blocks,
fs3_bytes => l_fs3_bytes,
fs3_blocks => l_fs3_blocks,
fs4_bytes => l_fs4_bytes,
fs4_blocks => l_fs4_blocks,
full_bytes => l_full_bytes,
full_blocks => l_full_blocks,
unformatted_blocks => l_unformatted_blocks,
unformatted_bytes => l_unformatted_bytes
);
dbms_output.put_line(' FS1 Blocks = '||l_fs1_blocks||' Bytes
= '||l_fs1_bytes);
dbms_output.put_line(' FS2 Blocks =
'||l_fs2_blocks||' Bytes = '||l_fs2_bytes);
dbms_output.put_line(' FS3 Blocks = '||l_fs3_blocks||' Bytes
= '||l_fs3_bytes);
dbms_output.put_line(' FS4 Blocks =
'||l_fs4_blocks||' Bytes = '||l_fs4_bytes);
dbms_output.put_line('Full Blocks = '||l_full_blocks||'
Bytes = '||l_full_bytes);
end;
/
Also
see these related notes to the HWM for a table:
Using dbms_space_usage
|
|
|
Oracle Training from Don Burleson
The best on site
"Oracle
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
|
|
|
|
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
|