pdb表空间巡检脚本

spool dbtime.txt
set echo off
set termout off
set lines 1000
set pages 1000
col con_name for a15
col tablespace_name for a25
SELECT case D.con_id
         when 1 then
          'CDB$ROOT'
         else
          G.PDB_NAME
       end con_name,
       D.TABLESPACE_NAME,
       ROUND(MAX_SPACE, 2) MAX_SPACE,
       ROUND(SPACE, 2) "SUM_SPACE(M)",
       ROUND(SPACE - NVL(FREE_SPACE, 0), 2) "USED_SPACE(M)",
       ROUND(((SPACE - NVL(FREE_SPACE, 0)) / MAX_SPACE) * 100, 2) "USED_RATE(%)"
  FROM (SELECT /*+ RULE */
         CON_ID,
         TABLESPACE_NAME,
         SUM(MAX_SPACE) MAX_SPACE,
         SUM(SPACE) SPACE,
         SUM(BLOCKS) BLOCKS
          FROM (SELECT CON_ID,
                       FILE_ID,
                       TABLESPACE_NAME,
                       decode(sign(sum(BYTES) - sum(MAXBYTES)),
                              -1,
                              SUM(MAXBYTES) / (1024 * 1024),
                              SUM(BYTES) / (1024 * 1024)) MAX_SPACE,
                       ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,
                       SUM(BLOCKS) BLOCKS
                  FROM CDB_DATA_FILES
                 GROUP BY CON_ID, FILE_ID, TABLESPACE_NAME)
         GROUP BY CON_ID, TABLESPACE_NAME) D,
       (SELECT CON_ID,
               TABLESPACE_NAME,
               SUM(BYTES) / (1024 * 1024) FREE_SPACE
          FROM CDB_FREE_SPACE
         GROUP BY CON_ID, TABLESPACE_NAME) F,
       dba_pdbs G
 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
   AND D.CON_ID = F.CON_ID
   AND D.CON_ID = G.CON_ID(+)
 order by 1 desc,1 desc;
 spool off