Re: How to get number of free blocks?

From: Brian P. Mac Lean <brian.maclean_at_teldta.com>
Date: 1996/08/23
Message-ID: <321E85F1.1C0C_at_teldta.com>#1/1


Uwe Goldhammer wrote:
>
> 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

This is in m-bytes not blocks but should give you what your after/need. See the "col" statments as to the output of the script:

REM BEGIN BEGIN BEGIN BEGIN BEGIN BEGIN BEGIN BEGIN BEGIN BEGIN BEGIN BEGIN set verify off
set pagesize 23
set linesize 80
set pause on
set pause 'Hit enter to continue'
set feedback off
set showmode off
set echo off  

PROMPT
ACCEPT USER_INPUT1 CHAR PROMPT 'Please enter a table to describe:>' ACCEPT USER_INPUT2 CHAR PROMPT 'Please enter a owner to describe:>' PROMPT
PROMPT On most systems, this query processes about 1000 rows in a table a second PROMPT Please wait..............
PROMPT

col "Owner"           heading "Owner"           format a10
col "Table Name"      heading "Table|Name"      format a25
col "Tablespace Name" heading "TableSpace"      format a10
col "Total Extents"   heading "#Ext's"          format 999
col "Total Meg"       heading "Meg"             format 999.99
col "Used Meg"        heading "Used|Meg"        format 999.99
col "Percent Used"    heading "Pct|Used"        format 999.99
col "Total Rows"      heading "#Rows"           format 9999999


set termout off
column blsk new_value BLOCK_SIZE_K
select value / 1024 blsk
from v$parameter
where name = 'db_block_size';
set termout on

col owner new_value owner_value noprint col segment_name new_value segment_name_value noprint ttitle left 'OWNER: ' owner_value -

     center 'Percent Used' -
     right 'PAGE:'  format 999 sql.pno skip 1 -
     left 'TABLE NAME: ' segment_name_value -
     center '           ' skip 2

select ds.owner,
       ds.segment_name,
       ds.tablespace_name "Tablespace Name",
       ds.extents "Total Extents",
       (ds.blocks * &BLOCK_SIZE_K) / 1024 "Total Meg",
       (count(distinct substr(t.rowid,1,8) 
               || substr(t.rowid,15,4)) * &BLOCK_SIZE_K) / 1024 "Used Meg",
       ((count(distinct substr(t.rowid,1,8) 
               || substr(t.rowid,15,4)) * &BLOCK_SIZE_K) / 1024) 
           / ((ds.blocks * &BLOCK_SIZE_K) / 1024) * 100 "Percent Used",
       count(t.rowid) "Total Rows"

from &USER_INPUT2..&USER_INPUT1 t, sys.dba_segments ds
where  ds.segment_name = upper('&USER_INPUT1')
  and  ds.owner        = upper('&USER_INPUT2')
  and  ds.segment_type = 'TABLE'

group by ds.owner, ds.segment_name, ds.tablespace_name, ds.extents, (ds.blocks * &BLOCK_SIZE_K) / 1024; exit;
REM END END END END END END END END END END END END END END END END END END END
                       \\|//
                       (0-0)
           +-----oOO----(_)-----------+
           | Brian P. Mac Lean        |
           | Database Analyst         |
           | brian.maclean_at_teldta.com |
           | http://www.teldta.com    |
           +-------------------oOO----+
                      |__|__|
                       || ||
                      ooO Ooo




Be afraid! Be very afraid! Received on Fri Aug 23 1996 - 00:00:00 CEST

Original text of this message