(Source Code) Oracle: usage of the tablespaces (permanent and temporary)

Oracle: usage of the tablespaces (permanent and temporary)

 

SET pagesize 10000

SET COLSEP '|'
SET VERIFY off
SET serveroutput ON SIZE 1000000
BREAK ON report

COLUMN tablespace_name format a30 heading 'TABLESPACE'
COLUMN sizegb format 9999999999D9 heading 'SIZE-Gb'
COLUMN usedproc format 999D99 heading 'USED-%'
COLUMN status format a10 heading 'STATUS'
COMPUTE SUM LABEL 'Total size:' OF sizegb ON report

SELECT b.tablespace_name ,
b.bytes/1024/1024/1024 AS sizegb ,
NVL(100-((a.bytes/b.bytes)*100), 100) usedproc,
REPLACE(c.status,' ','_') status
FROM
( SELECT tablespace_name,
SUM(bytes) bytes
FROM dba_free_space
GROUP BY tablespace_name
) a ,
( SELECT tablespace_name,
SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name
) b ,
dba_tablespaces c
WHERE b.tablespace_name = a.tablespace_name (+)
AND b.tablespace_name = c.tablespace_name
UNION
SELECT f.TABLESPACE_NAME,
f.TOTAL_MB/1024 sizegb,
NVL( (u.USED_MB/f.TOTAL_MB)*100, 0 ) usedproc,
'TEMPORARY' status
FROM
(
SELECT f1.TABLESPACE_NAME,SUM( f1.BYTES/1024/1024 ) TOTAL_MB
FROM (
SELECT TABLESPACE_NAME,BYTES
FROM dba_temp_files
UNION ALL
SELECT TABLESPACE_NAME,BYTES
FROM dba_data_files
WHERE TABLESPACE_NAME IN (
SELECT TABLESPACE_NAME
FROM dba_tablespaces
WHERE CONTENTS='TEMPORARY'
)
) f1
GROUP BY f1.TABLESPACE_NAME
) f,
(
SELECT u1.TABLESPACE,
SUM(u1.blocks) * MAX((SELECT VALUE FROM v$parameter WHERE name='db_block_size')/1024/1024) USED_MB
FROM v$sort_usage u1
GROUP BY u1.TABLESPACE
) u
WHERE f.TABLESPACE_NAME = u.TABLESPACE (+)
ORDER BY 1;



comments powered by Disqus