Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]

Search This Blog

Monday, September 12, 2011

Oracle tablespace status report ....

ACCEPT tbsp PROMPT 'Enter the Tablespace name in Alert:'
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES
COLUMN ALLOCATED_MB FORMAT 999,999,990.00
COLUMN FREE_MB FORMAT 99,999,990.00
COLUMN MAX_MB FORMAT 999,999,990.00
COLUMN PCT_FREE FORMAT 90.00
COLUMN PCT_MAX_FREE FORMAT 999,999,990.00
COLUMN AVAILABLE_MB FORMAT 999,999,990.00
COLUMN NEXT_MB FORMAT 999,999,990.00
COLUMN tbsp FORMAT A30 heading "TABLESPACE"
COLUMN file_name FORMAT A70 HEADING "DATA FILE(S)"
COLUMN AUTOEXTENSIBLE FORMAT A7 HEADING "AUTOEXT"
COLUMN BLS NEW_VALUE BLOCK_SIZE
set verify off
SELECT BLOCKSIZE BLS FROM SYS.TS$ WHERE NAME='&tbsp' ;
set lines 136 pages 50

SELECT T.TABLESPACE_NAME tbsp,
ROUND(T.MB_TOTAL,2) "ALLOCATED_MB",
ROUND(F.MB_FREE,2) "FREE_MB",
ROUND(F.MB_FREE/ROUND(T.MB_TOTAL)*100,2) PCT_FREE,
ROUND(T.MB_MAX,2) "MAX_MB",
ROUND((T.MB_MAX-(T.MB_TOTAL-F.MB_FREE))/ROUND(T.MB_MAX)*100,2) PCT_MAX_FREE,
ROUND(F.MB_FREE + T.MB_MAX - T.MB_TOTAL) AVAILABLE_MB
FROM
(
SELECT TABLESPACE_NAME, SUM(BYTES)/(1024*1024) MB_FREE
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME
) F,
(
SELECT TABLESPACE_NAME, SUM(BYTES)/(1024*1024) MB_TOTAL,
SUM(DECODE(MAXBYTES, 0, BYTES, MAXBYTES))/(1024*1024) MB_MAX
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME
) T
WHERE T.TABLESPACE_NAME=F.TABLESPACE_NAME(+)
AND T.TABLESPACE_NAME='&tbsp'
ORDER BY pct_max_free, PCT_FREE, AVAILABLE_MB, ALLOCATED_MB ;
SELECT FILE_NAME, ROUND(BYTES/1024/1024,2) "ALLOCATED_MB", ROUND(MAXBYTES/1024/1024,2) "MAX_MB", AUTOEXTENSIBLE ,
INCREMENT_BY*&&BLOCK_SIZE/1024/1024 "NEXT_MB"
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = '&tbsp' ;

No comments: