Re: HELP: need % of tablespace free

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#

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