Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: High water marks
I am not complaining, but I think this is a great newsgroup. I have been a
regular of this group for almost 4 months and I have learned a lot from
this newsgroup rather than from Oracle manuals.
But many times I feel that many of us, because of our work or whatever, do not spend enough time to post the scripts that we may have, which is what the original post kind of requested. I wish we all do post our scripts. Why let someone re-invent the wheel spending his/her valuable time?
To that end I always enjoy and save some of Thomas Kyte's posts, because he
always has scripts
to go with his posts. He does a great job on many Oracle aspects/products.
Of course he works for Oracle !!! If there is any Hall of Fame for this
newsgroup, I would like to nominate him.
Colin, here is the PL/SQL that I wrote that gives the information that you seek for certain tables and indexes. You may need to modify it to suit your needs.
This version does not create a procedure but executes the PL/SQL block in SQL*Plus. Some of the HOST commands are for UNIX.
I have snipped it from one of my SQL script. I hope I have not missed anyting.
Good luck !!!
Here we go:
set pause off termout off verify off wrap on serveroutput on size 1000000 set newpage 0 pagesize 58 linesize 80
clear breaks
break on station skip 1
column today new_value today noprint column time new_value time noprint
/*
Remove report file if exists, otherwise in case of problem, you may
receive a print out of an old file.
*/
host rm -f $EFM_HOME/rep/analyze_efm_hist_tablespace_usage.lst
spool $EFM_HOME/rep/analyze_efm_hist_tablespace_usage.lst
set feedback off
ttitle today center 'EFM_HIST TABLE AND INDEX SPACE USAGE' -
right 'Page ' format 990 sql.pno skip 1 -
time -
skip 2 -
analyze_efm_hist_tablespace_usage.sql skip 1
select to_char(sysdate, 'dd-MON-yyyy') today,
to_char(sysdate, 'HH:MI:SS AM') time
from dual
/
set feedback on
/*
Compute EFM_HIST and EFM_HIST_IDX total and unused disk space. */
DECLARE
total_blocks number; total_bytes number; unused_blocks number; unused_bytes number;/* Above High water mark */ last_used_extent_file_id number; last_used_extent_block_id number; last_used_block number; free_space number;/* Below High water mark */ user_id all_tables.owner%type; object_name all_tables.table_name%type; object_type varchar2 ( 5);--
cursor c1
is
select owner, table_name, 'TABLE'
from all_tables
where ( table_name like 'EFM_FA_HIST%' )
union
select owner, index_name, 'INDEX'
from all_indexes
where ( table_name like 'EFM_FA_HIST%' );
BEGIN
open c1;
LOOP
fetch c1 into user_id, object_name, object_type; exit when c1%notfound; dbms_space.unused_space
( user_id,
object_name, object_type, total_blocks, total_bytes, unused_blocks, unused_bytes, last_used_extent_file_id, last_used_extent_block_id, last_used_block ); dbms_space.free_blocks
( user_id,
object_name, object_type, 0, free_space);
dbms_output.put_line
( object_type||' Name = '||object_name );
dbms_output.put_line
( '**********' );
dbms_output.put_line
( 'Total Bytes = '||to_char(total_bytes, '999,999,990')||
' Free Bytes above Water Mark = '|| to_char(unused_bytes,'999,999,990') ); dbms_output.put_line-------');
( 'Bytes to W.Mark= '||to_char(total_bytes -
unused_bytes,'999,999,990')|| ' Free Bytes below Water Mark = '|| to_char(free_space*8192, '999,999,990') ); dbms_output.put_line ( '-------------------------------------------------------------------------
END LOOP;
END;
.
/
Colin Polykett <colin_at_daemon-computing.demon.co.uk> wrote in article <343767eb.6243919_at_news.demon.co.uk>...
> Folks, > > Is there any way to find out what the current high water mark is for > table ? > > I have no real need to know this, except that it's bugging me that the > high water mark can make such a difference in execution speed of a > query and yet there's no obvious way to find out what it is (not > obvious to me anyway !). > > > TIA. > > Colin Polykett. >Received on Mon Jun 08 1998 - 09:14:22 CDT