by http://webgeektutorials.blogspot.com

Monday, June 4, 2012

Oracle DBA Script : Show data files usage

Script : 
set linesize 188
set pagesize 26
SET FEEDBACK OFF
set heading on
set echo off
set timing off
variable xxx char(150)

column "Tablespace" format a16
COLUMN "File ID" FORMAT 9999999
column "Data file" format a43
column "Mb" format 9,999,999
column "Blocks" format 9,999,999
column "Status" format a10
COLUMN "USED SIZE(Mb)" FORMAT 9,999,999,999
COLUMN "USED BLOCKS" FORMAT 999,999,999
COLUMN "USED USAGE" FORMAT A10
COLUMN "FREE SIZE(Mb)" FORMAT 9,999,999,999
COLUMN "FREE BLOCKS" FORMAT 999,999,999
COLUMN "FREE USAGE" FORMAT A10
COLUMN "TOTAL SIZE(Mb)" FORMAT 99,999,999,999
COLUMN "TOTAL BLOCKS" FORMAT 9999,999,999

break on "Tablespace"
declare
cursor c1 is select 'Print Date:'||to_char(sysdate,'yyyy/mm/dd')||' ***** Datafile Information from DataBase :'||name||' Instance:'||instance||' *****' xxx
from v$database,v$thread
where rownum=1;
begin
open c1;
fetch c1 into :xxx;
close c1;
end;
/

set pagesize 1
print xxx
set pagesize 24

TTITLE RIGHT FORMAT 999 'Page:' SQL.PNO
BTITLE CENTER '================================================================'

SELECT SUBSTR(max(A.TABLESPACE_NAME),1,16) "Tablespace",
A.FILE_ID "File ID",
substr(max(A.file_name),1,43) "Data file",
substr(max(A.status),1,10) "Status",
(MAX(A.BYTES)-nvl(sum(B.BYTES),0))/1024/1024 "USED SIZE(Mb)",
MAX(A.BLOCKS)-nvl(sum(B.BLOCKS),0) "USED BLOCKS",
TO_CHAR((MAX(A.BYTES)-nvl(sum(B.BYTES),0))*100/MAX(A.BYTES),'999.99')||'%' "USED USAGE",
nvl(sum(B.BYTES),0)/1024/1024 "FREE SIZE(Mb)",
nvl(SUM(B.BLOCKS),0) "FREE BLOCKS",
TO_CHAR(nvl(SUM(B.BYTES),0)*100/MAX(A.BYTES),'999.99')||'%' "FREE USAGE",
MAX(A.bytes)/1024/1024 "TOTAL SIZE(Mb)",
MAX(A.blocks) "TOTAL BLOCKS"
from dba_data_files A,
DBA_FREE_SPACE B
WHERE A.FILE_ID=B.FILE_ID(+)
group by a.file_id
order by 1,2;

SPOOL OFF
TTITLE OFF
BTITLE OFF
SET FEEDBACK ON

No comments:

Post a Comment