# Gather table stats
./do_gatherstats_schema_tab.sh CDB_TNS_NAME PDB_TNS_NAME SCHEMA_NAME TABLE_NAME 8

# Gather schema stats
./do_gatherstats_schema_tab.sh CDB_TNS_NAME PDB_TNS_NAME SCHEMA_NAME 8
    

#!/bin/bash 
# May 2025 Sandeep Reddy Narani
# Version 1

if [ $# -eq 4 ]; then
  export ORACLE_SID=$1
  export SERVICE_NAME=$2
  export SCHEMA_NAME=$3
  export DEGREE=$4
  export ORACLE_HOME=$(grep "^${ORACLE_SID}:" /etc/oratab | cut -d: -f2 -s)
  export PATH=$ORACLE_HOME/bin:$PATH
  export LOGFILE="/tmp/gather_schema_stats_${SCHEMA_NAME}_$(date +%Y%m%d_%H%M%S).log"

  echo "Starting schema stats gather on ${SCHEMA_NAME}..." | mailx -s "START: Schema Stats" $ML

  $ORACLE_HOME/bin/sqlplus -s /nolog <> "$LOGFILE"
CONNECT bmcdba/pgmigrep@${SERVICE_NAME}
SET SERVEROUTPUT ON
BEGIN
  DBMS_STATS.GATHER_SCHEMA_STATS(
    ownname           => '${SCHEMA_NAME}',
    estimate_percent  => DBMS_STATS.AUTO_SAMPLE_SIZE,
    degree            => ${DEGREE},
    granularity       => 'ALL',
    cascade           => TRUE,
    options           => 'GATHER',
    force             => TRUE
  );
  DBMS_OUTPUT.PUT_LINE('Schema stats gathered for ${SCHEMA_NAME}');
END;
/
EXIT;
EOF

  mailx -s "COMPLETED: Schema Stats for ${SCHEMA_NAME}" $ML < "$LOGFILE"

elif [ $# -eq 5 ]; then
  export ORACLE_SID=$1
  export SERVICE_NAME=$2
  export SCHEMA_NAME=$3
  export TABLE_NAME=$4
  export DEGREE=$5
  export ORACLE_HOME=$(grep "^${ORACLE_SID}:" /etc/oratab | cut -d: -f2 -s)
  export PATH=$ORACLE_HOME/bin:$PATH
  export LOGFILE="/tmp/gather_table_stats_${SCHEMA_NAME}_${TABLE_NAME}_$(date +%Y%m%d_%H%M%S).log"

  echo "Starting table stats gather on ${SCHEMA_NAME}.${TABLE_NAME}..." | mailx -s "START: Table Stats" $ML

  $ORACLE_HOME/bin/sqlplus -s /nolog <> "$LOGFILE"
CONNECT dba/dba@${SERVICE_NAME}
SET SERVEROUTPUT ON
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname => '${SCHEMA_NAME}',
    tabname => '${TABLE_NAME}',
    cascade => TRUE,
    degree  => ${DEGREE}
  );
  DBMS_OUTPUT.PUT_LINE('Table stats gathered for ${SCHEMA_NAME}.${TABLE_NAME}');
END;
/
EXIT;
EOF

  mailx -s "COMPLETED: Table Stats for ${SCHEMA_NAME}.${TABLE_NAME}" $ML < "$LOGFILE"

else
  echo "Usage:"
  echo "  Schema Stats: $0 ORACLE_SID SERVICE_NAME SCHEMA_NAME DEGREE"
  echo "  Table Stats : $0 ORACLE_SID SERVICE_NAME SCHEMA_NAME TABLE_NAME DEGREE"
  exit 1
fi