linux shell脚本监控多台oracle 表空间使用

#!/bin/bash

# Define the list of Oracle databases
databases=("abc" "bcd"  "cde" "def" )

# Define the SQL query
sql_query=$(cat <<'EOF'
SET PAGESIZE 0
SET LONG 50000
SET FEEDBACK OFF
SET HEADING ON
SET LINESIZE 120
SET PAGESIZE 50000
SELECT a.tablespace_name,
  ROUND(((a.bytes_alloc - NVL(b.bytes_free, 0)) / maxbytes) * 100) || '%' Pct_Used_Max
FROM
    (SELECT f.tablespace_name,
            sum(f.bytes) bytes_alloc,
            sum(DECODE(f.autoextensible, 'YES',f.maxbytes,'NO', f.bytes)) maxbytes
     FROM dba_data_files f
     GROUP BY tablespace_name) a,
    (SELECT f.tablespace_name,
             sum(f.bytes)  bytes_free
     FROM dba_free_space f
     GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name (+)
ORDER BY 1;
EOF
)

# Create a temporary file to store the consolidated results
temp_file="tablespaces.txt"

# Loop through each database connection and append the results to the temporary file
for db in "${databases[@]}"; do
            {
         echo "--------------------------------------------------------"
         echo "Tablespaces Report for $db"
         echo "--------------------------------------------------------"
         sqlplus -s system/123456@$db <<EOF
        $(eval "echo \"$sql_query\"")
EOF
    } >> "$temp_file"
    done


 

 # Send email 
 mail -s "Daily Tablespaces Report" test@test.com < $temp_file
   # 删除临时txt

 rm $temp_file

1、将databases=("abc" "bcd"  "cde" "def" ) 里的修改为你要远程连接的tnsname（前提需要添加tnsname.ora）

2、sqlplus -s system/123456@$db <<EOF    这里修改为你的连接密码

3、修改为你的邮件地址，前提也需要在linux上配置好mailx，方法可自行搜索