REM =====================================================================
REM Procedure : tbs01
REM But : Taille et taux de remplissage des tablespaces
REM Lancement : Compte sys
REM =====================================================================
spool tbs01
COL ts FOR A30 HEAD "TABLESPACE" TRUNC
COL vol FOR A10 HEAD "VOLUME" TRUNC
COL vol_o FOR A10 HEAD "VOLUME|OCCUPE" TRUNC
COL vol_r FOR A10 HEAD "VOLUME|RESTANT" TRUNC
COL tx_r FOR A11 HEAD "TAUX DE|REMPLISSAGE" TRUNC
SELECT t.name ts ,
LPAD (
TO_CHAR( SUM(f.blocks)*
t.blocksize *
COUNT(DISTINCT(f.file#))
/(COUNT(*) *
1024 ))||' K',10,' ') vol ,
LPAD (
TO_CHAR( SUM(s.blocks)*
t.blocksize
/(1024 *
COUNT(DISTINCT(f.file#))))||' K',10,' ') vol_o,
LPAD (
TO_CHAR((SUM(f.blocks)*
t.blocksize *
COUNT(DISTINCT(f.file#))
/(COUNT(*) *
1024 ))
- (SUM(s.blocks)*
t.blocksize
/(1024 *
COUNT(DISTINCT(f.file#)))))||' K',10,' ') vol_r,
LPAD (
TO_CHAR(
CEIL (((SUM (s.blocks)*
COUNT(*))
/ (SUM (f.blocks)*
POWER(COUNT(DISTINCT(f.file# )),2)))
*100))||' %',10,' ') tx_r
FROM sys.seg$ s,
sys.ts$ t,
sys.file$ f
WHERE s.ts# (+) = t.ts#
AND f.ts# = t.ts#
AND f.status$ = 2
GROUP BY
t.name ,
t.blocksize
ORDER BY
1
;
SPOOL OFF
exit |