Rebuild Tables and Indexes

Rebuild Tables and Indexes from crontab

CREATE DBA USER

CREATE USER SH_USER IDENTIFIED BY XXXXXX;

GRANT CONNECT, RESOURCE TO SH_USER;

GRANT DBA TO SH_USER;

ALTER USER SH_USER DEFAULT TABLESPACE IGT_TABLE;

crontab
20 7 * * * /software/oracle/oracle/scripts/space/rebuild_tables_and_indexes.sh

rebuild_tables_and_indexes.sh

#!/bin/bash

. ~/.bash_profile

. /etc/sh/orash/oracle_login.sh igt

ORACLE_SID=igt

ORACLE_HOME=/software/oracle/1910

WORK_DIR=/software/oracle/oracle/scripts/space/rebuild_tables_and_indexes

RUN_DATE=`date +"%Y%m%d"_"%H%M%S"`

cd ${WORK_DIR}

echo "Start Rebuild Indexes at $RUN_DATE" >> rebuild_tables_and_indexes.log

sqlplus SH_USER/XXXXXXXX@orainst @rebuild_tables_and_indexes.sql

sqlplus SH_USER/XXXXXXXX@orainst @log_connection.sql

rebuild_tables_and_indexes.sql

----------------------

--USER_A

----------------------

ALTER TABLE USER_A.GA_W_COUNTERS_HISTORY ENABLE ROW MOVEMENT;

ALTER TABLE USER_A.GA_W_COUNTERS_HISTORY SHRINK SPACE;

ALTER TABLE USER_A.GA_W_COUNTERS_HISTORY DEALLOCATE UNUSED;

DROP INDEX USER_A.GWCH_PROCESSED_STATIC_IDX;

CREATE INDEX USER_A.GWCH_PROCESSED_STATIC_IDX ON USER_A.GA_W_COUNTERS_HISTORY (is_processed, static_id) TABLESPACE IGT_INDEX;

DROP INDEX USER_A.GWCH_TS_LAST_MODIFIED_IDX;

CREATE INDEX USER_A.GWCH_TS_LAST_MODIFIED_IDX ON USER_A.GA_W_COUNTERS_HISTORY (ts_last_modified) TABLESPACE IGT_INDEX;

----------------------

--USER_B

----------------------

ALTER TABLE USER_B.GA_W_COUNTERS_HISTORY ENABLE ROW MOVEMENT;

ALTER TABLE USER_B.GA_W_COUNTERS_HISTORY SHRINK SPACE;

ALTER TABLE USER_B.GA_W_COUNTERS_HISTORY DEALLOCATE UNUSED;

DROP INDEX USER_B.GWCH_PROCESSED_STATIC_IDX;

CREATE INDEX USER_B.GWCH_PROCESSED_STATIC_IDX ON USER_B.GA_W_COUNTERS_HISTORY (is_processed, static_id) TABLESPACE IGT_INDEX;

DROP INDEX USER_B.GWCH_TS_LAST_MODIFIED_IDX;

CREATE INDEX USER_B.GWCH_TS_LAST_MODIFIED_IDX ON USER_B.GA_W_COUNTERS_HISTORY (ts_last_modified) TABLESPACE IGT_INDEX;

log_connection.sql

SET LINESIZE 140

SET PAGESIZE 0

SET HEADING OFF

SET FEEDBACK OFF

SET VERIFY OFF

SET ECHO OFF

SET NEWPAGE NONE

SET SHOW OFF

SET TRIMSPOOL ON

spool log_connection.log APPEND

SELECT '=======================' FROM DUAL;

SELECT 'Start Rebuild Tables and Indexes at '||TO_CHAR(SYSDATE,'YYYYMMDD hh24:mi:ss') FROM DUAL;

SELECT '    ' FROM DUAL;

spool off

exit;