One of the most used script used
by the DBA in day-to-day work, this script gives the space usage of each
tablespace in the database.
|
SET HEADING ON
SET PAGESIZE 500
SET LINES 400
COLUMN TABLESPACE FORMAT A20 HEADING "Tablespace"
COLUMN AVAIL FORMAT 9,999,999,999,999 HEADING "Avail (MB)"
COLUMN USED FORMAT 9,999,999,999,999 HEADING "Used (MB)"
COLUMN FREE FORMAT 9,999,999,999,999 HEADING "Free (MB)"
COLUMN PCT FORMAT 999 HEADING "%Used"
COMPUTE SUM OF AVAIL USED
FREE ON REPORT
BREAK ON REPORT
SELECT A.TABLESPACE_NAME "Tablespace",
A.AVAIL AVAIL,
A.AVAIL-B.FREE USED,
B.FREE FREE,
ROUND(NVL((A.AVAIL-B.FREE)/A.AVAIL*100,0)) "pct"
FROM
(SELECT
TABLESPACE_NAME, ROUND(SUM(BYTES)/1048576) AVAIL
FROM SYS.DBA_DATA_FILES
GROUP BY
TABLESPACE_NAME
UNION
SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_FREE+BYTES_USED)/1048576)
FROM
V$TEMP_SPACE_HEADER
GROUP BY
TABLESPACE_NAME) A,
(SELECT
TABLESPACE_NAME, ROUND(SUM(BYTES)/1048576) FREE
FROM SYS.DBA_FREE_SPACE
GROUP BY
TABLESPACE_NAME
UNION
SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_FREE)/1048576)
FROM
V$TEMP_SPACE_HEADER
GROUP BY
TABLESPACE_NAME) B
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME (+);
Sample Output:
Tablespace Avail (MB) Used (MB) Free (MB) %Used------------ ---------- ------------------ ------------------ ----- AUDIT_DATA 100 44 56 44 NOTES_DATA 13,196 6,296 6,900 48 SYSAUX 2,048 1,680 368 82 SYSTEM 2,048 733 1,315 36 TEMP 2,048 1,657 391 81 UNDOTBS1 2,048 15 2,033 1 UNDOTBS2 2,048 40 2,008 2 USERS 1,024 1 1,023 0 ---------- ------------------ ------------------------- sum 24,560 10,466 14,094 |
No comments:
Post a Comment