Re: HELP: need % of tablespace free
Date: 1996/02/15
Message-ID: <4fuldm$m6u_at_tst.hk.super.net>#1/1
Jonathan,
Hope this sqlplus script helps
rem * This query of the database gives a detailed breakdown of the
rem * fragmentation of each tablespace file within the database.
rem * Last column "Dead" is based on the assumption that any
contiguous block *
rem * smaller than 5 ORACLE blocks cannot be used. That is, no table
or index *
rem * has an INITIAL or NEXT extent size less than 5 ORACLE blocks.
*
rem * *
rem
set lines 132
set pages 60
COL A1 HEADING "Tablespace" FORMAT A20 COL A2 HEADING "Blocks" FORMAT 999,999,999 COL A3 HEADING "Free" FORMAT 999,999,999 COL A4 HEADING "Pieces" FORMAT 999,999,999 COL A5 HEADING "Biggest" FORMAT 999,999,999 COL A6 HEADING "Smallest" FORMAT 999,999,999 COL A7 HEADING "Average" FORMAT 999,999,999 COL A8 HEADING "Dead" FORMAT 999,999
SPOOL tsfrag.lis
SELECT TS.NAME A1, TF.BLOCKS A2, SUM(F.LENGTH) A3, COUNT(*) A4, MAX(F.LENGTH) A5, MIN(F.LENGTH) A6, ROUND(AVG(F.LENGTH)) A7, SUM(DECODE(SIGN(F.LENGTH - 5), -1, F.LENGTH, 0)) A8 FROM SYS.FET$ F, SYS.FILE$ TF, SYS.TS$ TS WHERE TS.TS# = F.TS# AND TS.TS# = TF.TS#
GROUP BY TS.NAME, TF.BLOCKS
/
SPOOL OFF David Kim
Senior Oracle Consultant
email: kimba_at_.HK.Super.Net
jul395_at_prb.mhs.compuserve.com (Jonathan Julian) wrote:
>I need to know what percentage of a tablespace is filled with data. I
>had hoped it was a simple query in SQL, like
>select TOTAL, FREE from USER_TABLESPACE
>where NAME ='tabelspace_name';
>but I cannot find this info in the Oracle docs. Thanks in advance.
>Post here or email.
>Jonathan Julian
>PRB Associates, Inc.
>jul395_at_prb.mhs.compuserve.com
Received on Thu Feb 15 1996 - 00:00:00 CET