Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: High water marks

Re: High water marks

From: Suresh Bhat <suresh.bhat_at_mitchell-energy.com>
Date: Mon, 08 Jun 1998 14:14:22 GMT
Message-ID: <01bd92f7$a62ebc20$a504fa80@mndnet>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US