Re: free space in a tablespace

From: Oracleguru <oracleguru_at_mailcity.com>
Date: Mon, 27 Jul 1998 14:25:20 GMT
Message-ID: <01bdb979$dc5e7080$a504fa80@mndnet>

Robert, 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;
.
/

Robert Eisenhardt <Robert.Eisenhardt_at_t-online.de> wrote in article <6pdgv4$ecr$1_at_news02.btx.dtag.de>...

> Hello,
> 
> I have to reorganize a tablespace. In this tablespace a lot of records
> were deleted. So how can I get the real free space in this tablespace
> to reduce the size when reorganizing it.
> 
> Thanks a lot
> 
> Robert Eisenhardt
> ---
> email: Robert.Eisenhardt_at_t-online.de
> 

Received on Mon Jul 27 1998 - 09:25:20 CDT