출처는 모르겠고, 예전회사에서 쓰던 Tablespace 사용량 확인하는 스크립트이다.
set pagesize 50
COLUMN tablespace_name FORMAT a15 HEADING "TableSPace(KB)"
COLUMN total_size_kb FORMAT 999,999,999 HEADING "Total(KB)"
COLUMN free_size_kb FORMAT 99,999,999 HEADING "Free(KB)"
COLUMN used_size_kb FORMAT 999,999,999 HEADING "Used(KB)"
COLUMN usedpercentage FORMAT 999.99 HEADING "Used %"
COLUMN files FORMAT 99999 HEADING "Files"
COLUMN largest FORMAT 99,999,999 HEADING "Largest"
select
T.tablespace_name,
TS."total_size_b"/1024 as "total_size_kb",
NVL(FS."free_size_b"/1024, 0) as "free_size_kb",
NVL((TS."total_size_b"-FS."free_size_b")/1024, 0) as "used_size_kb",
NVL(Round(100 * ((TS."total_size_b"-FS."free_size_b") / TS."total_size_b"), 2), 0) as "usedpercentage",
TS."filecnt" as "files",
FS."maxfree" as "largest"
from
dba_tablespaces T,
(select tablespace_name, round (sum(bytes), 0) as "total_size_b",
count(file_name) as "filecnt"
from dba_data_files
group by tablespace_name) TS,
(select tablespace_name, round(sum(bytes), 0) as "free_size_b",
max(bytes)/1024 as "maxfree"
from dba_free_space
group by tablespace_name) FS
where
T.tablespace_name = TS.tablespace_name and
T.tablespace_name = FS.tablespace_name (+)
order by "usedpercentage";
간략하게 쓰자면, tsp.sql로 저장해놓고 sqlplus 에서 @tsp.sql 하면 됩니다. ^^
'Tech_Info > Database' 카테고리의 다른 글
SQL Server Errorlog archive 개수 늘리기 (0) | 2006.06.26 |
---|---|
xp_cmdshell 삭제하기 (0) | 2006.06.26 |