Database growth
#!/bin/bash
# Log file
LOGFILE="db_growth_check.log"
echo "Checking yearly database growth on all running Oracle databases..." > $LOGFILE
echo "DATABASE_NAME | YEAR | GROWTH_GB" >> $LOGFILE
echo "---------------------------------" >> $LOGFILE
# Find running Oracle instances (excluding ASM and APX)
ps -ef | grep pmon | grep -v grep | grep -Ev "(\+ASM|APX)" | awk -F'_' '{print $3}' | while read -r ORACLE_SID
do
if [[ -n "$ORACLE_SID" ]]; then
echo "Processing database: $ORACLE_SID"
# Set Oracle environment
export ORACLE_SID=$ORACLE_SID
export ORAENV_ASK=NO
. oraenv > /dev/null 2>&1
# Run SQL query to check yearly database growth
SQL_OUTPUT=$(sqlplus -s / as sysdba <<EOF
SET HEADING OFF;
SET FEEDBACK OFF;
SELECT '$ORACLE_SID' AS DATABASE_NAME,
TO_CHAR(s.begin_interval_time, 'YYYY') AS YEAR,
ROUND((SUM(max_size) - SUM(min_size)) / 1024 / 1024 / 1024, 2) AS GROWTH_GB
FROM (
SELECT
d.snap_id,
d.tablespace_id,
MAX(d.tablespace_size) AS max_size,
MIN(d.tablespace_size) AS min_size
FROM
DBA_HIST_TBSPC_SPACE_USAGE d
GROUP BY
d.snap_id, d.tablespace_id
) ts_usage
JOIN DBA_HIST_SNAPSHOT s ON ts_usage.snap_id = s.snap_id
GROUP BY TO_CHAR(s.begin_interval_time, 'YYYY')
ORDER BY TO_CHAR(s.begin_interval_time, 'YYYY');
EXIT;
EOF
)
# Append result to log file
echo "$SQL_OUTPUT" >> $LOGFILE
fi
done
echo "Database growth check completed. Log saved in $LOGFILE"
***********
#!/bin/bash
# Log file
LOGFILE="db_segment_growth_check.log"
echo "Checking yearly segment-level database growth on all running Oracle databases..." > $LOGFILE
echo "DATABASE_NAME | YEAR | GROWTH_GB" >> $LOGFILE
echo "---------------------------------" >> $LOGFILE
# Find running Oracle instances (excluding ASM and APX)
ps -ef | grep pmon | grep -v grep | grep -Ev "(\+ASM|APX)" | awk -F'_' '{print $3}' | while read -r ORACLE_SID
do
if [[ -n "$ORACLE_SID" ]]; then
echo "Processing database: $ORACLE_SID"
# Set Oracle environment
export ORACLE_SID=$ORACLE_SID
export ORAENV_ASK=NO
. oraenv > /dev/null 2>&1
# Run SQL query to check yearly segment-level database growth
SQL_OUTPUT=$(sqlplus -s / as sysdba <<EOF
SET LINESIZE 400;
SET PAGESIZE 100;
SET HEADING OFF;
SET FEEDBACK OFF;
SELECT '$ORACLE_SID' AS DATABASE_NAME,
TO_CHAR(B.END_INTERVAL_TIME, 'YYYY') AS YEAR,
ROUND(SUM(A.SPACE_ALLOCATED_DELTA) / 1024 / 1024 / 1024, 2) AS GROWTH_GB
FROM DBA_HIST_SEG_STAT A
JOIN DBA_HIST_SNAPSHOT B ON A.SNAP_ID = B.SNAP_ID
GROUP BY TO_CHAR(B.END_INTERVAL_TIME, 'YYYY')
ORDER BY TO_CHAR(B.END_INTERVAL_TIME, 'YYYY');
EXIT;
EOF
)
# Append result to log file
echo "$SQL_OUTPUT" >> $LOGFILE
fi
done
echo "Database segment growth check completed. Log saved in $LOGFILE"
Comments
Post a Comment