shell 脚本自动巡检查询19c ASM及表空间少于10%

#OS ： Centos 7.6
#DB : ORACLE 19C 
# 作者：jason.wang  2021.07

#!/bin/bash
pdbname=$1
tbsname=$2
#export ORACLE_PDB_SID=$1
su - oracle -c "sqlplus -S / as sysdba"<<eof

SET ECHO        OFF
SET FEEDBACK    6
SET HEADING     ON
SET LINESIZE    180
SET PAGESIZE    50000
SET TERMOUT     ON
SET TIMING      OFF
SET TRIMOUT     ON
SET TRIMSPOOL   ON
SET VERIFY      OFF

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

COLUMN group_name             FORMAT a25           HEAD 'Disk Group|Name'
COLUMN sector_size            FORMAT 99,999        HEAD 'Sector|Size'
COLUMN block_size             FORMAT 99,999        HEAD 'Block|Size'
COLUMN allocation_unit_size   FORMAT 999,999,999   HEAD 'Allocation|Unit Size'
COLUMN state                  FORMAT a11           HEAD 'State'
COLUMN type                   FORMAT a6            HEAD 'Type'
COLUMN total_mb               FORMAT 999,999,999   HEAD 'Total Size (MB)'
COLUMN used_mb                FORMAT 999,999,999   HEAD 'Used Size (MB)'
COLUMN pct_used               FORMAT 999.99        HEAD 'Pct. Used'
COLUMN PDB_NAME               FORMAT a20   HEAD 'PDB_NAME'
COLUMN TABLESPACE                FORMAT a20   HEAD 'TABLESPACE'

SELECT B.NAME PDB_NAME,A.NAME TABLESPACE,A.MAX_PCT_FREE FROM 
(SELECT D.CON_ID,
    D.STATUS                                                      STATUS
  , D.TABLESPACE_NAME                                             NAME
  , D.CONTENTS                                                    TYPE
  , D.EXTENT_MANAGEMENT                                           EXTENT_MGT
  , D.SEGMENT_SPACE_MANAGEMENT                                    SEGMENT_MGT
  , NVL(A.BYTES, 0)/1024/1024                                     TS_SIZE
  , ROUND(A.MAXBYTES/1048576)                                     MAX_MB
  , ROUND(NVL(A.BYTES -  NVL(F.BYTES, 0), 0)/1024/1024,2)         USED
  , ROUND(F.BYTES/1048576)                                        FREE_MB
  , NVL((A.BYTES - NVL(F.BYTES, 0)) / A.BYTES * 100, 0)           PCT_USED
  , ROUND(F.BYTES/A.BYTES * 100 ,2)                               PCT_FREE
  , NVL(ROUND((A.MAXBYTES-A.BYTES+F.BYTES)/ A.MAXBYTES * 100,2),0)       MAX_PCT_FREE
FROM
    CDB_TABLESPACES D
  , ( SELECT TABLESPACE_NAME, SUM(BYTES) BYTES,SUM(DECODE(MAXBYTES, 0, BYTES, MAXBYTES)) MAXBYTES,CON_ID
      FROM CDB_DATA_FILES --WHERE con_id='26'
      GROUP BY TABLESPACE_NAME,CON_ID
    ) A
  , ( SELECT TABLESPACE_NAME, SUM(BYTES) BYTES,CON_ID
      FROM CDB_FREE_SPACE
      GROUP BY TABLESPACE_NAME,CON_ID
    ) F
WHERE
      D.TABLESPACE_NAME = A.TABLESPACE_NAME(+)
  AND D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
  AND D.CON_ID=A.CON_ID(+) AND D.CON_ID=F.CON_ID(+)
  AND NOT (
    D.EXTENT_MANAGEMENT LIKE 'LOCAL'
    AND
    D.CONTENTS LIKE 'TEMPORARY'
  )) A, V\$PDBS B  WHERE A.CON_ID=B.CON_ID AND  A.MAX_PCT_FREE<=10 AND TS_SIZE>0;

 
  select * from (
SELECT
    name                                     group_name
  , sector_size                              sector_size
  , block_size                               block_size
  , allocation_unit_size                     allocation_unit_size
  , state                                    state
  , type                                     type
  , total_mb                                 total_mb
  , (total_mb - free_mb)                     used_mb
  , ROUND((1- (free_mb / total_mb))*100, 2)  pct_used
FROM
    V\$asm_diskgroup
WHERE
    total_mb != 0  
ORDER BY
    name) where pct_used>=90;

  exit
eof