Oracle表空间&asm巡检脚本

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

SET ECHO OFF
SET FEEDBACK OFF
SET FLUSH OFF
SET HEADING OFF
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

复制