一键查询所有pdb表空间使用脚本

1.首先需要创建一个全局用户

create user c##tbs identified by tbs;

2.登录pdb，授予pdb下用户c##tbs权限

这里为了方便，直接授予的dba权限
alter session set container=xxxx;
grant dba to c##tbs;

3.具体脚本如下

具体脚本可以根究实际情况进行修改

#!/bin/bash
instance_name=`lsnrctl  status|grep Service |awk '{print $2}'|grep -v [0~9\SX]`
for i in $instance_name;
do
echo "*********************************************************************************************"
echo "***********************************$i pdb空间大小如下***********************************"
echo "*********************************************************************************************"
echo ""
sqlplus -s c##tbs/"tbs"@10.115.xx.xx:1521/$i <<EOF
col name for a20
col "Used %" for a12
set linesize 200
set pagesize 999
SELECT "Name",status,"Size (M)" ,"Used (M)","Used %", "Free (M)" , "free %" from
 (SELECT d.tablespace_name "Name", d.status,
           NVL(a.bytes / 1024 / 1024, 0) "Size (M)",
           NVL(a.bytes - NVL(f.bytes, 0), 0) / 1024 / 1024 "Used (M)",
           TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Used %",
           NVL(a.bytes / 1024 / 1024, 0) - NVL(a.bytes - NVL(f.bytes, 0), 0) / 1024 / 1024 "Free (M)" ,
          100 - TO_CHAR(NVL((a.bytes - NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "free %"
      FROM sys.dba_tablespaces d,
           (select tablespace_name, sum(bytes) bytes
              from dba_data_files
             group by tablespace_name) a,
           (select tablespace_name, sum(bytes) bytes
              from dba_free_space
             group by tablespace_name) f
     WHERE d.tablespace_name = a.tablespace_name(+)
       AND d.tablespace_name = f.tablespace_name(+)
       AND NOT
            (d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
    UNION ALL
    SELECT d.tablespace_name "Name", d.status,
           NVL(a.bytes / 1024 / 1024, 0) "Size (M)",
           NVL(t.bytes, 0) / 1024 / 1024 "Used (M)",
           TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %",
           NVL(a.bytes / 1024 / 1024, 0) - NVL(t.bytes, 0) / 1024 / 1024 "Free (M)" ,
           100 - TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "free %"
      FROM sys.dba_tablespaces d,
           (select tablespace_name, sum(bytes) bytes
              from dba_temp_files
             group by tablespace_name) a,
           (select tablespace_name, sum(bytes_cached) bytes
              from v\$temp_extent_pool
             group by tablespace_name) t
    WHERE d.tablespace_name = a.tablespace_name(+)
       AND d.tablespace_name = t.tablespace_name(+)
       AND d.extent_management like 'LOCAL'
       AND d.contents like 'TEMPORARY' )
 -- where "Used %" > 80
 ORDER BY 5;
quit;
EOF
done