剩余表空间查询脚本
CLEAR COLUMNS BREAKS COMPUTESset lines 150
set pages 1000
COLUMN STATUS HEADING 'Status' ENTMAP off
COLUMN TABLESPACE_NAME HEADING 'Name' ENTMAP off
COLUMN CONTENTS FORMAT a12 HEADING 'TS Type' ENTMAP off
COLUMN ALL_MB FORMAT 9,999,999,999HEADING 'All Size(MB)' ENTMAP off
COLUMN MAX_MB FORMAT 9,999,999,999HEADING 'Max Size(MB)' ENTMAP off
COLUMN FREE_MB FORMAT 9,999,999,999HEADING 'Free Size(MB)' ENTMAP off
COLUMN FREE_EXT FORMAT 9,999,999,999HEADING 'Max Free' ENTMAP off
COLUMN PCT_FREE FORMAT 999 HEADING 'Pct. Free' ENTMAP off
COLUMN PCT_FREE_EXT FORMAT 999 HEADING 'Max Free%' ENTMAP off
col TABLESPACE_NAME for a20
SELECT T.TABLESPACE_NAME TABLESPACE_NAME,
T.CONTENTS CONTENTS,
ROUND(SUM(A.BYTES) / 1048576) ALL_MB,
ROUND(SUM(DECODE(MAXBYTES, 0, A.BYTES, MAXBYTES)) /1048576) MAX_MB,
ROUND(SUM(NVL(F.BYTES,0)) / 1048576) FREE_MB,
ROUND((SUM(DECODE(MAXBYTES, 0, 0, MAXBYTES-A.BYTES)) + SUM(NVL(F.BYTES, 0))) / 1048576) FREE_EXT,
ROUND(100 * SUM(NVL(F.BYTES, 0)) / SUM(A.BYTES)) PCT_FREE,
ROUND(100 * (SUM(DECODE(MAXBYTES, 0, 0, MAXBYTES-A.BYTES)) + SUM(NVL(F.BYTES, 0))) / SUM(DECODE(A.MAXBYTES, 0, A.BYTES, A.MAXBYTES)))PCT_FREE_EXT
FROM DBA_DATA_FILES A,
DBA_TABLESPACES T,
(SELECT FILE_ID,SUM(BYTES) BYTES FROM DBA_FREE_SPACE GROUP BY FILE_ID) F
WHERE A.FILE_ID = F.FILE_ID(+)
AND A.TABLESPACE_NAME = T.TABLESPACE_NAME
GROUP BY T.TABLESPACE_NAME, T.CONTENTS, T.STATUS
ORDER BY 8,7;
页:
[1]