#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执行结果回显
set heading off;
set feedback off;
set termout off;
set pagesize 0;
set verify off;
set echo off;
spool 42.txt
select resource_name,max_utilization,initial_allocation,limit_value from v\$resource_limit;

spool off
exit;
!01

cechon "4.2 resource limit is: " red
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cechon "The result set format:""resource_name","max_utilization","initial_allocation","limit_value" green
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cat 42.txt
echo
rm -rf 42.txt

#4.3 session 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 43.txt
select sid,serial#,username,program,machine,status from v\$session;
spool off
exit;
!01

cechon "4.3 session status is: " red
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cechon "The result set format:""sid","serial#","username","program" ,"machine","status" green
echo
cechon "-------------------------------------------------------------------------------------------------" yellow
echo
cat 43.txt
echo
rm -rf 43.txt

#4.5 check Non-system tables in system tablespace

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 45.txt
select distinct(owner) from dba_tables
where tablespace_name='SYSTEM' and
owner!='SYS' and owner!='SYSTEM'
union
select distinct(owner) from dba_indexes
where tablespace_name='SYSTEM' and
owner!='SYS' and owner!='SYSTEM';
spool off
exit;
!01