#!/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,方法可自行搜索