查询PDB表空间（巡检使用）

#!/bin/bash
export ORACLE_SID=XXXX1
sqlplus -s / as sysdba <<EOF
SPOOL /home/oracle/seltbs.txt;
ALTER SESSION SET CONTAINER=xxx;
set pagesize 60
col name for a30
select NAME from v\$pdbs;
Select tablespace_name name,round(sum(byb),1) total_G, round(Sum(yby),1) use_G,round(Sum(syby),1) free_G,round(round(Sum(yby),1)/round(sum(byb),1),1)*100 rait
From (
select
b.file_id wjm,
b.tablespace_name ,
b.bytes/1024/1024/1024 byb,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024/1024 yby,
sum(nvl(a.bytes,0))/1024/1024/1024 syby,
100 - sum(nvl(a.bytes,0))/(b.bytes)*100 zybfb
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_id,b.bytes
order by b.file_id )
Group By tablespace_name;
conn / as sysdba
ALTER SESSION SET CONTAINER=xxxx;
set pagesize 60
col name for a30
select NAME from v\$pdbs;
Select tablespace_name name,round(sum(byb),1) total_G, round(Sum(yby),1) use_G,round(Sum(syby),1) free_G,round(round(Sum(yby),1)/round(sum(byb),1),1)*100 rait
From (
select
b.file_id wjm,
b.tablespace_name ,
b.bytes/1024/1024/1024 byb,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024/1024 yby,
sum(nvl(a.bytes,0))/1024/1024/1024 syby,
100 - sum(nvl(a.bytes,0))/(b.bytes)*100 zybfb
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_id,b.bytes
order by b.file_id )
Group By tablespace_name;
conn / as sysdba
ALTER SESSION SET CONTAINER=xxxx;
set pagesize 60
col name for a30
select NAME from v\$pdbs;
Select tablespace_name name,round(sum(byb),1) total_G, round(Sum(yby),1) use_G,round(Sum(syby),1) free_G,round(round(Sum(yby),1)/round(sum(byb),1),1)*100 rait
From (
select
b.file_id wjm,
b.tablespace_name ,
b.bytes/1024/1024/1024 byb,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024/1024 yby,
sum(nvl(a.bytes,0))/1024/1024/1024 syby,
100 - sum(nvl(a.bytes,0))/(b.bytes)*100 zybfb
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_id,b.bytes
order by b.file_id )
Group By tablespace_name;
conn / as sysdba
ALTER SESSION SET CONTAINER=xxxx;
set pagesize 60
col name for a30
select NAME from v\$pdbs;
Select tablespace_name name,round(sum(byb),1) total_G, round(Sum(yby),1) use_G,round(Sum(syby),1) free_G,round(round(Sum(yby),1)/round(sum(byb),1),1)*100 rait
From (
select
b.file_id wjm,
b.tablespace_name ,
b.bytes/1024/1024/1024 byb,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024/1024 yby,
sum(nvl(a.bytes,0))/1024/1024/1024 syby,
100 - sum(nvl(a.bytes,0))/(b.bytes)*100 zybfb
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_id,b.bytes
order by b.file_id )
Group By tablespace_name;
conn / as sysdba
ALTER SESSION SET CONTAINER=MINE;
set pagesize 60
col name for a30
select NAME from v\$pdbs;
Select tablespace_name name,round(sum(byb),1) total_G, round(Sum(yby),1) use_G,round(Sum(syby),1) free_G,round(round(Sum(yby),1)/round(sum(byb),1),1)*100 rait
From (
select
b.file_id wjm,
b.tablespace_name ,
b.bytes/1024/1024/1024 byb,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024/1024 yby,
sum(nvl(a.bytes,0))/1024/1024/1024 syby,
100 - sum(nvl(a.bytes,0))/(b.bytes)*100 zybfb
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_id,b.bytes
order by b.file_id )
Group By tablespace_name;
conn / as sysdba
ALTER SESSION SET CONTAINER=TLW;
set pagesize 60
col name for a30
select NAME from v\$pdbs;
Select tablespace_name name,round(sum(byb),1) total_G, round(Sum(yby),1) use_G,round(Sum(syby),1) free_G,round(round(Sum(yby),2)/round(sum(byb),2),1)*100 rait
From (
select
b.file_id wjm,
b.tablespace_name ,
b.bytes/1024/1024/1024 byb,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024/1024 yby,
sum(nvl(a.bytes,0))/1024/1024/1024 syby,
100 - sum(nvl(a.bytes,0))/(b.bytes)*100 zybfb
from dba_free_space a,dba_data_files b
where a.file_id=b.file_id
group by b.tablespace_name,b.file_id,b.bytes
order by b.file_id )
Group By tablespace_name;
spool off;
exit
EOF
exit
EOF