Re: How to get number of free blocks?

From: Ekkehard Schulz <schulz_e_at_bln.sel.alcatel.de>
Date: 1996/08/23
Message-ID: <4vk5tb$6ed_at_slbh00.bln.sel.alcatel.de>#1/1

Uwe Goldhammer <106111.1074_at_compuserve.com> writes:

>We use a Oracle7 database with 100 table (size of database is 20 GB).
>But only three tables are very big (1.6 to 6.0 GB).
>We use big initial extents because these tables are 75..100% filled.
>Storage Parameters:
> initial extent 1000M
> next extent 200M
> pctincrease 0
>How can I get the number of free blocks in the big extents?
>The data dictionary view DBA_FREE_SPACE tells me the free
>space in a tablespace, not in a extent.
>The view ALL_TABLES is usable only after analyzing the tables,
>which takes for the big ones 7 to 8 hours a table.
>Is there another way to get this information?
>Regards
>Uwe

Hi Uwe, some years ago I've written a script which gives for all tables of an owner the information Number of blocks of the table
Number of used blocks (contain rows)
Average number of rows in all used blocks Min and Max number of rows in all used blocks.

Today it could be better written in PL/SQL, but nevertheless try it and modify it for your needs.
Regards Ekkehard

• Snip ---- set echo off set term on accept _owner char prompt 'Please give the Object-Owner: ' set term off set heading off set pagesize 0 set feedback off set verify off

spool stor.sql

select 'set heading off' from dual;
select
'select ',

'''Table                 Blocks    Used     Avg    Min    Max'' from dual;'

from dual;
select

   'select '''

   || rpad(substr(segment_name,1,20),20)
   || ' '
   || to_char(blocks,'999999')
   || '''' ,
   ',decode(sum(1),NULL,''      0'',to_char(sum(1),''999999'')),',
   'to_char(round(avg(count(*))),''999999'')',

   ',to_char(min(count(*)),''99999''),to_char(max(count(*)),''99999'')',    ' from '
   || '&&_owner..' || rpad(substr(segment_name,1,30),30),    'group by substr(rowid,1,8)||substr(rowid,15,4);' from dba_segments
where segment_type='TABLE'

      and owner=upper('&&_owner');
spool off
set term on
spool sto.log
_at_stor
spool off
!/bin/rm -f stor.log
prompt There is a spool file sto.log with the same contents.

--
Ekkehard Schulz, Alcatel SEL AG Berlin, Department KB/EF3
Colditzstrasse 34-36, D 12099 Berlin, Germany           
Phone: +49-30-7002-3469  Fax: +49-30-7002-3667
Email: ekkehard.schulz_at_bln.sel.alcatel.de

Received on Fri Aug 23 1996 - 00:00:00 CEST