检查库中每个表空间的使用情况,表空间的大小,使用的百分比,剩余空间等。
在这有两个(来自互联网)一个用于检查当前各个表空间的情况。一个检查哪些表空间的剩余空间小于10%,这个可以做报警使用。
一、检查当前各个表空间的情况
[oracle@xn-test ~]$ cat ./ck-ts-used.sh
#!/bin/bash
output=`sqlplus -s "/ as sysdba" << EOF
set feed off
set linesize 100
set pagesize 200
spool tablespaceused.alert
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS,SPACE-NVL(FREE_SPACE,0) "USED_SPACE(M)",
ROUND((1-NVL(FREE_SPACE,0)/SPACE)*100,2) "USED_RATE(%)",FREE_SPACE "FREE_SPACE(M)"
FROM 
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",BLOCKS SUM_BLOCKS, 
USED_SPACE "USED_SPACE(M)",ROUND(NVL(USED_SPACE,0)/SPACE*100,2) "USED_RATE(%)",
NVL(FREE_SPACE,0) "FREE_SPACE(M)"
FROM 
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/(1024*1024),2) SPACE,SUM(BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES_USED)/(1024*1024),2) USED_SPACE,
ROUND(SUM(BYTES_FREE)/(1024*1024),2) FREE_SPACE
FROM V\\$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE  D.TABLESPACE_NAME = F.TABLESPACE_NAME(+);
spool off
exit
EOF
`
echo $output
[oracle@xn-test ~]$ cat tablespaceused.alert
TABLESPACE_NAME                SUM_SPACE(M) SUM_BLOCKS USED_SPACE(M) USED_RATE(%) FREE_SPACE(M)    
------------------------------ ------------ ---------- ------------- ------------ -------------    
UNDOTBS1                                 30       3840          10.5           35          19.5    
SYSAUX                                  360      46080        354.94        98.59          5.06    
USERS                                     5        640          3.06         61.2          1.94    
SYSTEM                                  500      64000        492.87        98.57          7.13    
EXAMPLE                                 100      12800         68.25        68.25         31.75    
SP_CATALOG                              300      38400          8.44         2.81        291.56    
TEMP                                    214      27392           214          100             0    
 
二、检查哪些表空间剩余小于10%
[oracle@xn-test ~]$ cat analyze_table.sh
#!/bin/bash
output=`sqlplus -s "/ as sysdba" << EOF
set feed off
set linesize 100
set pagesize 200
spool little10%.alert
select f.tablespace_name,
to_char ((t.total_space - f.free_space),'999,999') "used (mb)",
to_char (f.free_space, '999,999') "free (mb)",
to_char (t.total_space, '999,999') "total (mb)",
to_char ((round ((f.free_space/t.total_space)*100)),'999')||' %' per_free
from (
select tablespace_name,
round (sum (blocks*(select value/1024
from v\\$parameter
where name = 'db_block_size')/1024)
) free_space
from dba_free_space
group by tablespace_name
) f,
(
select tablespace_name,
round (sum (bytes/1048576)) total_space
from dba_data_files
group by tablespace_name
) t
where f.tablespace_name = t.tablespace_name
and (round ((f.free_space/t.total_space)*100)) < 10;
spool off
exit
EOF
`
echo $output
[oracle@xn-test ~]$ cat little10%.alert 
TABLESPACE_NAME                used (mb free (mb total (m PER_FR                                   
------------------------------ -------- -------- -------- ------                                   
SYSAUX                              355        5      360    1 %                                   
SYSTEM                              493        7      500    1 %                                   
[oracle@xn-test ~]$
 
来自网络,大家随便用。
不要找我要版权啊,要也不给了。