#3.4 all datafile Status

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 34.txt
select name,status from v\$datafile;
spool off
exit;
!01

cechon "3.4 all datafile Status : " red
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cechon "The result set format:""name","status" green
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cat 34.txt
echo
rm -rf 34.txt

#3.5 invalid objects

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 35.txt
select owner,object_name,object_type from dba_objects where status!='VALID' and owner!='SYS' and owner!='SYSTEM';
spool off
exit;
!01

cechon "3.5 invalid objects is : " red
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cechon "The result set format:""owner","object_name","object_type" green
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cat 35.txt
echo
rm -rf 35.txt

#3.6 undo segment status

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 36.txt
select segment_name,status from dba_rollback_segs;
spool off
exit;
!01

cechon "3.6 undo segment status : " red
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cechon "The result set format:""segment_name","status" 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