sqlplus -S ${SH_USERNAME}/${SH_PASSWORD}@${ORACLE_SID} << EOF
COLUMN Tablespace FORMAT A20 HEADING 'Tablespace|Name' JUSTIFY left
COLUMN Total_Size FORMAT 999,999 HEADING 'Total|Size(MB)' JUSTIFY left
COLUMN Free_Space FORMAT 999,999 HEADING 'Free(MB)'
COLUMN Percentage FORMAT 90.0 HEADING '%|Used' JUSTIFY left
COLUMN decode FORMAT A14 HEADING '(Space Gauge)'
COLUMN fragdec FORMAT A14 HEADING '(Frag Gauge)'
COLUMN percfrag FORMAT 90.0 HEADING '%|Frag' JUSTIFY left
COLUMN frags FORMAT 999,999 HEADING 'Frags'
COLUMN bigchunk FORMAT 999,999 HEADING 'Big|Chunk(MB)' JUSTIFY left
COLUMN data_files FORMAT 99 HEADING 'Data|Files' JUSTIFY left
select free.tablespace_name Tablespace, tot.total Total_Size,
100 - ((free.free / tot.total) * 100) Percentage,
((ceil(10-(free.free / tot.total) * 10)),
10,'| !! DNGR !! |') decode,
free.free Free_Space, bigchunk, data_files, frags, percfrag,
decode ((ceil(percfrag / 10)) ,
10,'| !! DNGR !! |') fragdec
from (select tablespace_name, ceil(sum(bytes) / 1048576) total, count(*) data_files
group by tablespace_name) tot,
(select tablespace_name, ceil(sum(bytes) / 1048576) free,
ceil(max(bytes) / 1048576) bigchunk, count(*)frags, 100 - (max(bytes) /sum(bytes)) * 100 percfrag
group by tablespace_name) free
where free.tablespace_name = tot.tablespace_name;
Tablespace Total % Big Data %
Name Size(MB) Used (Space Gauge) Free(MB) Chunk(MB) Files Frags Frag (Frag Gauge)
-------------------- -------- ----- -------------- -------- --------- ----- -------- ----- --------------
TBS2 20 0.0 | .......... | 20 20 1 1 0.0 | .......... |
SYSAUX 325 19.1 | **........ | 263 263 1 1 0.0 | .......... |
TBS1 55 5.5 | *......... | 52 29 3 3 44.2 | *****..... |
UNDOTBS 200 6.0 | *......... | 188 185 1 5 1.5 | *......... |
USERS 135 78.5 | ********.. | 29 28 2 3 3.6 | *......... |
TEST 10 0.0 | .......... | 10 10 1 1 0.0 | .......... |
SYSTEM 325 68.3 | *******... | 103 103 1 1 0.0 | .......... |
RCTS 100 0.0 | .......... | 100 100 1 1 0.0 | .......... |
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23718752/viewspace-764433/,如需转载,请注明出处,否则将追究法律责任。