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
Welcome to the GEEK world, best place where you find topic about latest cutting edge technology on website and mobile. Learn and grow your knowledge with the information and tutorials about Website designing, CSS tutorials, Java Script tutorials, Ruby tutorials, ROR tutorials, HTML tutorials, HTML5 , JQuery, Javascript tutorials, Photoshop tutorials, Flash, games tutorials, Cheat sheets, Design tools, Action Scripts as well as MySql, Oracle and many more in a easy way to use and understand.
No comments:
Post a Comment