实战:oracle巡检脚本v1(六)
atus" green
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cat 36.txt
echo
rm -rf 36.txt
#3.7 Tables and indexes in the same space object
sqlplus -S "${ora_user}/${ora_pass} as sysdba" </dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 37.txt
select a.owner,
a.tablespace_name tbsname ,
a.table_name tname,
b.index_name iname
from
dba_tables a
,dba_indexes b
where
a.tablespace_name=b.tablespace_name
and b.table_name=a.table_name
and a.owner=b.owner
and b.owner NOT in
('SYS','SYSTEM','XDB','WMSYS','SYSMAN',
'ORDSYS','OUTLN','ORDDATA')
and a.tablespace_name not in('SYSTEM','SYSAUX','UNDOTBS1','USERS'
)
order by owner;
spool off
exit;
!01
cechon "3.7 Tables and indexes in the same space object is: " red
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cechon "The result set format:""owner","tablespace_name","tablename","index_name" green
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cat 37.txt
echo
rm -rf 37.txt
#3.8 tablespace Automatic expansion
sqlplus -S "${ora_user}/${ora_pass} as sysdba" </dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 38.txt
select t.tablespace_name,d.file_name,d.autoextensible,d.status
from dba_tablespaces t,dba_data_files d
where t. tablespace_name =d. tablespace_name
order by tablespace_name,file_name;
spool off
exit;
!01
cechon "3.8 tablespace Automatic expansion is: " red
echo
cat 38.txt
echo
rm -rf 38.txt
cechon "***********************************************************************" yellow
echo
cechon "4.Check the database resource usage:" green
echo
cechon "***********************************************************************" yellow
echo
#4.1 tablespace usage
sqlplus -S "${ora_user}/${ora_pass} as sysdba" <
/dev/null #禁止sqlplus执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 41.txt
select f.tablespace_name,a.total,f.free,round((f.free/a.total)*100) "% Free"
from
(select tablespace_name, sum(bytes/(1024*1024)) total from dba_data_files
group by tablespace_name) a,
(select tablespace_name, round(sum(bytes/(1024*1024))) free from dba_free_space
group by tablespace_name) f
WHERE a.tablespace_name = f.tablespace_name(+)
order by "% Free";
spool off
exit;
!01
cechon "4.1 tablespace usage is: " red
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cechon "The result set format:""tablespace_name","total","FREE","%FREE" green
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cat 41.txt
echo
rm -rf 41.txt
#4.2 resource limit
sqlplus -S "${ora_user}/${ora_pass} as sysdba" </dev/null #禁止sqlplus执行结果回显
se