ORACLE Tablespace 체크 쿼리

set linesize 209
set pagesize 26
SET FEEDBACK OFF
SET HEADING ON
set timing off
set echo off
SET UNDERLINE =

variable xxx char(200)

column "Tablespace" format A16
column "Initial extent(Mb)" format 999,999
column "Status" format a7
column "Next extent(MB)" format 999,999
column "Max extents" format 99,999,999,999
column "Pct_increase" format 999
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 hh24:mi:ss')||' ***** Tablespace 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(A.tablespace_name,1,16) "Tablespace",
MAX(A.contents) "Type",
MAX(A.status) "Status",
MAX(A.initial_extent)/1024 "Initial extent(Kb)",
MAX(A.next_extent)/1024 "Next extent(Kb)",
MAX(A.max_extents) "Max extents",
MAX(A.pct_increase) "Pct_increase",
(SUM(B.BYTES)*COUNT(DISTINCT B.FILE_ID)/COUNT(B.FILE_ID)/1024/1024)-(ROUND(SUM(C.BYTES)/1024/1024/COUNT(DISTINCT B.FILE_ID))) "USED SIZE(Mb)",
(SUM(B.BLOCKS)*COUNT(DISTINCT B.FILE_ID)/COUNT(B.FILE_ID))-(SUM(C.BLOCKS)/COUNT(DISTINCT B.FILE_ID)) "USED BLOCKS",
TO_CHAR(100-(SUM(C.BLOCKS)*100*COUNT(B.FILE_ID)/(SUM(B.BLOCKS)*COUNT(DISTINCT B.FILE_ID)))/COUNT(DISTINCT B.FILE_ID),'999.99')||'%' "USED USAGE",
ROUND(SUM(C.BYTES)/1024/1024/COUNT(DISTINCT B.FILE_ID)) "FREE SIZE(MB)",
SUM(C.BLOCKS)/COUNT(DISTINCT B.FILE_ID) "FREE BLOCKS",
TO_CHAR((SUM(C.BLOCKS)*100*COUNT(B.FILE_ID)/(SUM(B.BLOCKS)*COUNT(DISTINCT B.FILE_ID)))/COUNT(DISTINCT B.FILE_ID),'999.99')||'%' "FREE USAGE",
SUM(B.BYTES)*COUNT(DISTINCT B.FILE_ID)/COUNT(B.FILE_ID)/1024/1024 "TOTAL SIZE(Mb)",
SUM(B.BLOCKS)*COUNT(DISTINCT B.FILE_ID)/COUNT(B.FILE_ID) "TOTAL BLOCKS"
from dba_tablespaces A,
DBA_DATA_FILES B,
DBA_FREE_SPACE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME
AND A.TABLESPACE_NAME=C.TABLESPACE_NAME
GROUP BY A.TABLESPACE_NAME
order by 1;

TTITLE OFF
BTITLE OFF
SET FEEDBACK ON