Monday, September 28, 2020

DBA BUNDLE 5.8

September's release of the DBA Bundle is now available: (It's a combination of Aug+Sep releases)

dbdailychk.sh [For generating a database Health Check Report]:
- Added Memory utilization check feature, combining both RAM and SWAP in the calculation:
The following parameters need to be edited by the user to match the environment requirements:
 CHECK_MEMORY=Y          # REPORT LOW MEMORY USING BELOW TWO DEFINED THRESHOLDS COMBINED [OS]
 USEDPHYSICALMEM_PCT=95 # THRESHOLD FOR RAM %UTILIZATION                                [OS]
 USEDSWAPMEM_PCT=75     # THRESHOLD FOR SWAP SPACE %UTILIZATION                         [OS]

How it works?
 
Low memory will get reported if both; RAM %USED is >=95% and SWAP space %USED is >=75% 
 
Above thresholds are generic; feel free to change them as per your experience with your systems.
e.g. if you feel your system start to get slow when the RAM %USED reach 85% and SWAP space utilization hit 10%, then you edit those parameters like this:
USEDPHYSICALMEM_PCT=85
USEDSWAPMEM_PCT=10
 
If you are not interested in getting notified about Memory high utilization, just disable this function by setting:
CHECK_MEMORY=N
 
- Reorganized the script's sections to make it easier for the user to edit and understand.
 
rebuild_table.sh [For rebuilding a table online]:
- Added the feature of rebuilding the table on a different tablespace.
- Enhanced the formatting of the outputs and the logfile.
For a detailed discussion on this script new features please visit: http://dba-tips.blogspot.com/2020/09/major-update-to-rebuild-table-script.html 
 
parameter_val.sh [For viewing Normal & Hidden Parameters]
- User can view all NON-DEFAULT parameters.
- The user can list all the parameters on the instance.
 
user_ddl.sh [For generating User creation DDL statement]
- If the database version is 12c+ the script will automatically use dbms_metadata.get_ddl to generate the DDL (User creation + Privileges + showing quotas on tablespaces).

 
- Minor functionality enhancements and bug fixes included in these scripts:
 
export_data.sh   [For exporting data]
- Fixed a directory creation bug.
 
dbalarm.sh [For database monitoring]
- Excluded the reporting of ORA-235 which triggers during the unlocking of the control file" as advised by MOS 2312580.1
 
rebuild_table.sh  [For rebuilding a table online]:
- Correction of DBA_REDEFINITION_ERRORS query. 
 
object_size.sh [For calculating the total object size]
- Enhanced the formatting of the outputs. 
 
aliases_DBA_BUNDLE.sh [The main Bundle deployment script]
- Removed the constraint of extracting the bundle under User's home directory, now the user is free to extract the bundle anywhere.

 
If you are new to the DBA Bundle, please visit below link to understand its functions:
http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html
Posted by No comments:

Thursday, September 17, 2020

Major Update to Rebuild Table Script

 Few months back:

I've shared a script to rebuild tables in Oracle using online rebuild options like [(DBMS_REDEFINITION (9i+) & ALTER TABLE MOVE ONLINE (12.2+)] if those features are not available in the database (i.e. database edition is Standard Edition) the script will utilize the legacy "ALTER TABLE MOVE" command along with rebuilding indexes.

The original post link: http://dba-tips.blogspot.com/2019/05/rebuild-table-script-and-reclaim-wasted.html

What is new?

I received many enhancements requests from readers along with bug fixing and here is a summary of new features:

Major Enhancements:

- When using ALTER TABLE MOVE ONLINE or the legacy one, you will be prompted to choose whether to keep the table in its original tablespace or you want to move the table to another tablespace:


Leaving it blank will rebuild the table on its original tablespace.

Of course, if you choose a different tablespace the script will automatically calculate the free space on the tablespace and make sure it's sufficient to host the table after the rebuild.

Note: This feature is not added to DBMS_REDEFINITION, although it's available starting with 12.2,  it will need the user to specify the tablespace for each index separately, I thought it will be better to not add this feature to not confuse the user, anyways the user can still use "ALTER TABLE MOVE ONLINE" option which is already available on 12.2 which will give similar result.

- Bug fixes along with reorganizing the steps to make it more simple for the user.

Minor Enhancements:

- User can enter blank value when asked for Parallelism Degree to utilize the maximum CPU power on server without the need to calculate it. "For those who are lazy to check CPU count on the machine 😊"

- Entering 0 value to Parallelism Degree will be accepted to disable the parallelism.


By the way, the info statement shown above "don't exceed the current number of CPUs" is my advice, of course you can exceed the number of CPUs and it may be faster for you, but in most cases I studied it won't help much, better you validate that yourself on your hardware!

- More notifications, messages added to the script to keep the user updated with what is going on.

- Added more tolerance for user's wrong inputs. 

- If the table selected for rebuild is being replicated by goldengate or streams, the user will get an info message that target table may miss some data during the table rebuild (this took me days of testing ad building case scenarios till I figured out this limitation!). By the way, this is not a script limitation, it's how the Oracle rebuild technology works internally so far.

- changed the color of important messages from green to yellow to give it more appearance.

 

To download the script:

https://www.dropbox.com/s/bmgbc0u76okokcs/rebuild_table.sh?dl=0


GitHub version is updated as well

# #########################################################################################################################################################
# Ver 3.2
# SCRIPT TO REBUILD A GIVEN TABLE AND ITS INDEXES USING ONLINE FEATURES DBMS_REDEFINITION/ONLINE REBUILD IF AVAILABLE OR OFFLINE REBUILD AS A FINAL RESORT
# #########################################################################################################################################################
#
# # # #
# Author: Mahmmoud ADEL # # # # ###
# Created: 16-06-14 # # # # #
#
# Modified: 17-06-14 Inform the user if the DB is in Force Logging Mode.
# 16-09-14 Added Search Feature.
# 04-01-16 Added DEGREE OF PARALLELISM calculation.
# 11-04-19 Added ONLINE table rebuild for Oracle 12.2.
# 15-04-19 Added pctfree option to the rebuild command.
# 16-04-19 Added DBMS_REDEIFINITION to the list of rebuild options.
# 18-04-19 Added ALERTLOG notification when the DBMS_REDEIFINITION process reach to the Final Step: [Manual Mode]
# 21-04-19 Added COMPRESSION option to ALTER TABLE REBUILD option.
# 25-04-19 Added the check for the free space on the underlying tablespace.
# 05-05-19 Added PCTFREE option for DBMS_REDEIFINITION process
# 05-05-19 Fix the bug of not showing UNUSABLE INDEXES after the rebuild by using the outer join.
# 05-05-19 Increased DDL LOCK timeout to 5 minutes to avoid the failure of the rebuild operation due to failure to aquire a lock.
# 09-05-19 Included "WHENEVER SQLERROR EXIT" to the SQLPLUS script to terminate the rebuild if any stage return errors.
# 25-08-19 Fix a bug of non integer index/lob size when login.sql is active.
# 13-02-20 Excluded "ENABLE NOVALIDATE" from error log [After rebuild report] as it's not an actual error.
# 13-09-20 Correction of DBA_REDEFINITION_ERRORS query.
# 15-09-20 Added Rebuild Table to another Tablespace feature (available only for ALTER TABLE MOVE options).
# 16-09-20 Enhance the formatting of the outputs.
# 23-05-22 Using NULLIF(x,0) to workaround error "ORA-01476: divisor is equal to zero" when displaying table details post rebuild.
# #########################################################################################################################################################
SCRIPT_NAME="rebuild_table"
# ###########
# VARIABLES:
# ###########
LIST_TOP_FRAG_TABS=Y # LIST TOP 50 FRAGMENTED TABLE EACH TIME SCRIPT RUN [Y/N]
CHECK_SPACE=Y # CHECK IF THE UNDERLYING TABLESPACE HAS SUFFICENT SPACE FOR THE REBUILD OPERATION [Y/N]
export LIST_TOP_FRAG_TABS
export CHECK_SPACE
# ###########
# Description:
# ###########
echo
echo "============================================"
echo "This script REBUILDS A TABLE and its INDEXES ..."
echo "============================================"
echo ""
echo "It will check first if the table can be REBUILD using DBMS_REDEIFINITION [Very Minimal Downtime will happen on the table]."
echo "It will check if ALTER TABLE MOVE ONLINE can be used for the rebuild, if you are on 12.2 or higher [Very Minimal Downtime will happen on the table]."
echo "If NO ONLINE TABLE REBUILD Options are available, it will use ALTER TABLE MOVE and rebuild its INDEXES. [A DOWNTIME will be mandatory on the table]"
echo "Lastly, You will be prompted to utilize options like Parallelism/Compression/Gather Statistics if they are available in your DB Edition."
echo ""
echo "Note: It's highly recommended to take a FULL DB BACKUP before the start of any table rebuild."
echo "Note: UNDO TABLESPACE and ARCHIVELOG LOCATION (or FRA) must be big enough to hold the data generated by the rebuild process."
echo
sleep 3
# #######################################
# Excluded INSTANCES:
# #######################################
# Here you can mention the instances the script will IGNORE and will NOT run against:
# Use pipe "|" as a separator between each instance name.
# e.g. Excluding: -MGMTDB, ASM instances:
EXL_DB="\-MGMTDB|ASM|APX" #Excluded INSTANCES [Will not get reported offline].
# ###########################
# Listing Available Databases:
# ###########################
# Count Instance Numbers:
INS_COUNT=$( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|wc -l )
# Exit if No DBs are running:
if [ $INS_COUNT -eq 0 ]
then
echo "No Database is Running !"
echo
return
fi
# If there is ONLY one DB set it as default without prompt for selection:
if [ $INS_COUNT -eq 1 ]
then
export ORACLE_SID=$( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk '{print $NF}'|sed -e 's/ora_pmon_//g'|grep -v sed|grep -v "s///g" )
# If there is more than one DB ASK the user to select:
elif [ $INS_COUNT -gt 1 ]
then
echo
echo "Select the ORACLE_SID:[Enter the number]"
echo "---------------------"
select DB_ID in $( ps -ef|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk '{print $NF}'|sed -e 's/ora_pmon_//g'|grep -v sed|grep -v "s///g" )
do
integ='^[0-9]+$'
if ! [[ ${REPLY} =~ ${integ} ]] || [ ${REPLY} -gt ${INS_COUNT} ] || [ ${REPLY} -eq 0 ]
then
echo
echo "Error: Not a valid number!"
echo
echo "Enter a valid NUMBER from the displayed list !: i.e. Enter a number from [1 to ${INS_COUNT}]"
echo "----------------------------------------------"
else
export ORACLE_SID=$DB_ID
echo
printf "`echo "Selected Instance: ["` `echo -e "\033[33;5m${DB_ID}\033[0m"` `echo "]"`\n"
echo
break
fi
done
fi
# Exit if the user selected a Non Listed Number:
if [ -z "${ORACLE_SID}" ]
then
echo "You've Entered An INVALID ORACLE_SID"
exit
fi
# #########################
# Getting ORACLE_HOME
# #########################
ORA_USER=`ps -ef|grep ${ORACLE_SID}|grep pmon|grep -v grep|egrep -v ${EXL_DB}|awk '{print $1}'|tail -1`
USR_ORA_HOME=`grep ${ORA_USER} /etc/passwd| cut -f6 -d ':'|tail -1`
# SETTING ORATAB:
if [ -f /etc/oratab ]
then
ORATAB=/etc/oratab
export ORATAB
## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]
then
ORATAB=/var/opt/oracle/oratab
export ORATAB
fi
# ATTEMPT1: Get ORACLE_HOME using pwdx command:
export PGREP=`which pgrep`
export PWDX=`which pwdx`
if [[ -x ${PGREP} ]] && [[ -x ${PWDX} ]]
then
PMON_PID=`pgrep -lf _pmon_${ORACLE_SID}|awk '{print $1}'`
export PMON_PID
ORACLE_HOME=`pwdx ${PMON_PID} 2>/dev/null|awk '{print $NF}'|sed -e 's/\/dbs//g'`
export ORACLE_HOME
fi
# ATTEMPT2: If ORACLE_HOME not found get it from oratab file:
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
## If OS is Linux:
if [ -f /etc/oratab ]
then
ORATAB=/etc/oratab
ORACLE_HOME=`grep -v '^\#' ${ORATAB} | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
export ORACLE_HOME
## If OS is Solaris:
elif [ -f /var/opt/oracle/oratab ]
then
ORATAB=/var/opt/oracle/oratab
ORACLE_HOME=`grep -v '^\#' ${ORATAB} | grep -v '^$'| grep -i "^${ORACLE_SID}:" | perl -lpe'$_ = reverse' | cut -f3 | perl -lpe'$_ = reverse' |cut -f2 -d':'`
export ORACLE_HOME
fi
fi
# ATTEMPT3: If ORACLE_HOME is in /etc/oratab, use dbhome command:
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`dbhome "${ORACLE_SID}"`
export ORACLE_HOME
fi
# ATTEMPT4: If ORACLE_HOME is still not found, search for the environment variable: [Less accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`env|grep -i ORACLE_HOME|sed -e 's/ORACLE_HOME=//g'`
export ORACLE_HOME
fi
# ATTEMPT5: If ORACLE_HOME is not found in the environment search user's profile: [Less accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
ORACLE_HOME=`grep -h 'ORACLE_HOME=\/' ${USR_ORA_HOME}/.bash_profile ${USR_ORA_HOME}/.*profile | perl -lpe'$_ = reverse' |cut -f1 -d'=' | perl -lpe'$_ = reverse'|tail -1`
export ORACLE_HOME
fi
# ATTEMPT6: If ORACLE_HOME is still not found, search for orapipe: [Least accurate]
if [ ! -f ${ORACLE_HOME}/bin/sqlplus ]
then
if [ -x /usr/bin/locate ]
then
ORACLE_HOME=`locate -i orapipe|head -1|sed -e 's/\/bin\/orapipe//g'`
export ORACLE_HOME
fi
fi
export LD_LIBRARY_PATH=${ORACLE_HOME}/lib
# ########################################
# Exit if the user is not the Oracle Owner:
# ########################################
CURR_USER=`whoami`
if [ ${ORA_USER} != ${CURR_USER} ]; then
echo ""
echo "You're Running This Sctipt with User: \"${CURR_USER}\" !!!"
echo "Please Run This Script With The Right OS User: \"${ORA_USER}\""
echo "Script Terminated!"
exit
fi
# #####################
# Getting DB Block Size:
# #####################
VAL302=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
set pages 0 feedback off;
prompt
select value from v\$parameter where name='db_block_size';
exit;
EOF
)
blksize=`echo ${VAL302}|perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'|cut -f1 -d '.'`
# ###################
# Checking DB Version:
# ###################
VAL311=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
set pages 0 feedback off;
prompt
select version from v\$instance;
exit;
EOF
)
DB_VER=`echo ${VAL311}|perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'|cut -f1 -d '.'`
DB_RELEASE=`echo ${VAL311}|perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'|cut -f2 -d '.'`
CHK_IDX_ONLINE_OPTION_RAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set pages 0 feedback off echo off;
SELECT count(*) from v\$option where parameter='Online Index Build' and value='TRUE';
exit;
EOF
)
CHK_IDX_ONLINE_OPTION=`echo ${CHK_IDX_ONLINE_OPTION_RAW} | awk '{print $NF}'`
if [ ${CHK_IDX_ONLINE_OPTION} -eq 1 ]
then
export ONLINE_REBUILD="ONLINE"
fi
# ONLINE OPTION TO BE USED ON 12.2 and higher:
if [ ${DB_VER} -eq 12 ]
then
if [ ${DB_RELEASE} -gt 1 ]
then
if [ ${CHK_IDX_ONLINE_OPTION} -eq 1 ]
then
export ORACLE12_ONLINE="ONLINE UPDATE INDEXES"
export ORACLE12_HASH="--"
fi
fi
fi
if [ ${DB_VER} -gt 12 ]
then
if [ ${CHK_IDX_ONLINE_OPTION} -eq 1 ]
then
export ORACLE12_ONLINE="ONLINE UPDATE INDEXES"
export ORACLE12_HASH="--"
fi
fi
# ###############################
# Checking CDB Architecture:
# ###############################
# Check if the CBD Architecture is implemented if the DB Version is 12c or higher:
if [ ${DB_VER} -ge 12 ]
then
export ORACLE_PDB_SID=""
CDBACTIVERAW=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
select count(*) from v\$containers where open_mode='READ WRITE';
EOF
)
CDBACTIVE=`echo ${CDBACTIVERAW} | awk '{print $NF}'`
# If only one PDB found then set the ORACLE_PDB_SID variable to that PDB without the need to prompt the user:
if [ ${CDBACTIVE} -eq 2 ]
then
echo ""
echo "CBD Architecture detected!"
export ORACLE_PDB_SID=""
ORACLE_PDB_SID_RAW=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
select name from v\$containers where name not in ('CDB\$ROOT','PDB\$SEED') and open_mode='READ WRITE';
EOF
)
export ORACLE_PDB_SID=`echo ${ORACLE_PDB_SID_RAW} | awk '{print $NF}'`
export SET_CONTAINER="alter session set container = ${ORACLE_PDB_SID};"
echo "Setting ORACLE_PDB_SID to the only available PDB [${ORACLE_PDB_SID}]"
echo ""
fi
# If more than one PDB found then prompt the user to select from the PDBs list:
if [ ${CDBACTIVE} -gt 2 ]
then
echo ""
echo "CBD Architecture detected!"
export ORACLE_PDB_SID=""
PDB_COUNT_RAW=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
set pages 0 feedback off echo off
select count(*) from v\$containers where name not in ('CDB\$ROOT','PDB\$SEED') and open_mode='READ WRITE';
EOF
)
PDB_COUNT=`echo ${PDB_COUNT_RAW} | awk '{print $NF}'`
echo ""
echo "Select one PDB from the list:[Enter the number]"
echo "----------------------------"
echo "[For CDB Enter 0]"
echo
select PDB_DB_ID in $(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
set pages 0 feedback off echo off
select name from v\$containers where name not in ('CDB\$ROOT','PDB\$SEED') and open_mode='READ WRITE';
EOF
)
do
integ='^[1-9]+$'
# If input is 0 set the DB to CDB:
if [ ${REPLY} -eq 0 ]
then
export ORACLE_PDB_SID=""; export SET_CONTAINER=""
echo
echo "Setting CDB as default ..."
echo
fi
# For other input show PDB DBs:
if ! [[ ${REPLY} =~ ${integ} ]] || [ ${REPLY} -gt ${PDB_COUNT} ] || [ -z "${REPLY}" ]
then
echo
echo "Error: Not a valid number!"
echo
echo "Enter a valid NUMBER from the displayed list!: i.e. Enter a number from [1 to ${PDB_COUNT}]"
echo "---------------------------------------------"
else
export ORACLE_PDB_SID=${PDB_DB_ID}; export SET_CONTAINER="alter session set container = ${PDB_DB_ID};"
echo
printf "`echo "Selected PDB: ["` `echo -e "\033[33;5m${PDB_DB_ID}\033[0m"` `echo "]"`\n"
echo
break
fi
done
fi
fi
# ##############################
# LIST TOP 50 FRAGMENTED TABLES:
# ##############################
case ${LIST_TOP_FRAG_TABS} in
y|Y|yes|YES|Yes)
${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
set pages 1000 lines 169 feedback off
col owner for a30
col table_name for a35
col "%RECLAIMABLE_SPACE" for 99
col "%FRAGMENTED_SPACE" for a17
col LAST_ANALYZED for a13
PROMPT
PROMPT LIST OF TOP FRAGMENTED TABLES ON THE DB: [The accuracy of this list depends on the existance of recent STATISTICS on tables]
PROMPT ----------------------------------------
select * from (select owner,table_name,to_char(LAST_ANALYZED, 'DD-MON-YYYY') LAST_ANALYZED,
round(blocks * ${blksize}/1024/1024) "FULL_SIZE_MB",
round(num_rows * avg_row_len/1024/1024) "ACTUAL_SIZE_MB",
round(blocks * ${blksize}/1024/1024) - round(num_rows * avg_row_len/1024/1024) "FRAGMENTED_SPACE_MB",
round(((round((blocks * ${blksize}/1024/1024)) - round((num_rows * avg_row_len/1024/1024))) / round((blocks * ${blksize}/1024/1024))) * 100)||'%' "%FRAGMENTED_SPACE"
from dba_tables
where
owner <> 'SYS'
and blocks>10
and round(blocks * ${blksize}/1024/1024) > 10
-- List only the tables with Fragmented Space > 30%:
and ((round((blocks * ${blksize}/1024/1024)) - round((num_rows * avg_row_len/1024/1024))) / round((blocks * ${blksize}/1024/1024))) * 100 > 30
order by "FRAGMENTED_SPACE_MB" desc) where rownum<51;
PROMPT
EOF
echo "";;
esac
# Checking FORCE LOGGING mode:
# ###########################
VAL1=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
select force_logging from v\$database;
EOF
)
VAL2=`echo ${VAL1}| awk '{print $NF}'`
case ${VAL2} in
YES) echo
echo "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^"
echo "INFO: THE DATABASE IS IN FORCE LOGGING MODE."
echo "vvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvvv"
echo;sleep 2;;
*);;
esac
echo
echo "Enter the OWNER of The Table:"
echo "============================="
while read OWNER
do
case ${OWNER} in
"")echo
echo "Enter the OWNER of the Table:"
echo "============================";;
*)
VAL11=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
SELECT COUNT(*) FROM DBA_USERS WHERE USERNAME=upper('${OWNER}');
EOF
)
VAL22=`echo ${VAL11}| awk '{print $NF}'`
case ${VAL22} in
0) echo;echo "ERROR: USER [${OWNER}] IS NOT EXIST ON DATABASE [${ORACLE_SID}] !"
echo; echo "Searching For Users Match The Provided String ..."; sleep 1
${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
set linesize 143
set pagesize 1000
set feedback off
set trim on
set echo off
select username "Users Match Provided String" from dba_users where username like upper ('%${OWNER}%');
EOF
echo;echo "Enter A Valid Table Owner:"
echo "=========================";;
*) break;;
esac
esac
done
echo
echo "Enter the TABLE Name:"
echo "===================="
while read OBJECT_NAME
do
case ${OBJECT_NAME} in
"")echo
echo "Enter the TABLE NAME:"
echo "====================";;
*)
VAL11=$(${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
SELECT COUNT(*) FROM DBA_TABLES WHERE OWNER=upper('${OWNER}') AND TABLE_NAME=UPPER('${OBJECT_NAME}');
EOF
)
VAL22=`echo ${VAL11}| awk '{print $NF}'`
case ${VAL22} in
0) echo;echo "INFO: TABLE [${OBJECT_NAME}] IS NOT EXIST UNDER SCHEMA [${OWNER}] !"
echo;echo "Searching for tables match the provided string ..."; sleep 1
${ORACLE_HOME}/bin/sqlplus -s '/ as sysdba' << EOF
set linesize 143
set pagesize 1000
set feedback off
set trim on
set echo off
select table_name "Tables Match Provided String:" from dba_tables where owner=upper('${OWNER}') and table_name like upper ('%${OBJECT_NAME}%');
EOF
echo;echo "Enter A VALID TABLE NAME:"
echo "========================";;
*) break;;
esac
esac
done
VALLASTANALYZED=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
set pages 0 feedback off lines 1000;
prompt
select LAST_ANALYZED from DBA_TABLES where OWNER= upper('${OWNER}') and TABLE_NAME= upper('${OBJECT_NAME}');
exit;
EOF
)
LASTANALYZED=`echo ${VALLASTANALYZED}|perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'|cut -f1 -d '.'`
VALTABUPDATES=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
set pages 0 feedback off lines 1000;
prompt
select UPDATES from DBA_TAB_MODIFICATIONS where TABLE_OWNER= upper('${OWNER}') and TABLE_NAME= upper('${OBJECT_NAME}');
exit;
EOF
)
TABUPDATES=`echo ${VALTABUPDATES}|perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'|cut -f1 -d '.'`
# ###################
# Getting DB Version:
# ###################
VAL311=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
set pages 0 feedback off;
prompt
select version from v\$instance;
exit;
EOF
)
DB_VER=`echo ${VAL311}|perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'|cut -f1 -d '.'`
# ###################
# Getting TABLE SIZE:
# ###################
case ${CHECK_SPACE} in
y|Y|yes|YES|Yes)
echo ""
echo "[Calculating Table Size] ..."
# TABLE SIZE:
SINGLETABLESIZE_RAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set pages 0 feedback off lines 1000;
prompt
select /*+RULE*/ ROUND(SUM(BYTES)/1024/1024) FROM DBA_SEGMENTS where owner=upper('${OWNER}') and SEGMENT_NAME=upper('${OBJECT_NAME}');
exit;
EOF
)
export SINGLETABLESIZE=`echo ${SINGLETABLESIZE_RAW} |perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'|cut -f1 -d '.'`
# LOB SIZE:
LOBSIZE_RAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set pages 0 feedback off lines 1000;
prompt
SELECT /*+RULE*/ ROUND(SUM(BYTES)/1024/1024) FROM DBA_SEGMENTS WHERE SEGMENT_NAME IN
(SELECT /*+RULE*/ SEGMENT_NAME FROM DBA_LOBS WHERE owner=upper('${OWNER}') AND table_name=UPPER('${OBJECT_NAME}'));
exit;
EOF
)
export LOBSIZE=`echo ${LOBSIZE_RAW} |perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'|cut -f1 -d '.'`
# Zeriong an empty lob size:
if [ -z ${LOBSIZE} ]
then
export LOBSIZE=0
fi
# Zeriong a non integer LOB size:
INT='^[0-9]+$'
if ! [[ ${LOBSIZE} =~ $INT ]]
then
export LOBSIZE=0
fi
# INDEXES SIZE:
INDEXESSIZE_RAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set pages 0 feedback off lines 1000;
prompt
SELECT /*+RULE*/ round(SUM(BYTES/1024/1024)) FROM SYS.DBA_SEGMENTS WHERE OWNER = upper('${OWNER}')
AND SEGMENT_NAME in (select index_name from dba_indexes where owner=upper('${OWNER}') and table_name=UPPER('${OBJECT_NAME}'));
exit;
EOF
)
export INDEXESSIZE=`echo ${INDEXESSIZE_RAW} |perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'|cut -f1 -d '.'`
# Zeriong an empty index size:
if [ -z ${INDEXESSIZE} ]
then
export INDEXESSIZE=0
fi
# Zeriong a non integer index size:
INT='^[0-9]+$'
if ! [[ $INDEXESSIZE =~ $INT ]]
then
export INDEXESSIZE=0
fi
# TABLE + LOBS + INDEXES SIZE:
#export TABLESIZE=$(echo "${SINGLETABLESIZE} + ${LOBSIZE} + ${INDEXESSIZE}" | bc)
export TABLESIZE=$(awk "BEGIN {print ${SINGLETABLESIZE} + ${LOBSIZE} + ${INDEXESSIZE}}")
echo "[TABLE + INDEXES SIZE= ${TABLESIZE} MB]"
export SAFE_MARGIN="1.25"
#export TABLESIZEPLUSMARGIN=$(echo "${TABLESIZE} * ${SAFE_MARGIN}" | bc)
export TABLESIZEPLUSMARGIN=$(awk "BEGIN {print ${TABLESIZE} * ${SAFE_MARGIN}}")
# Convert TABLESIZEPLUSMARGIN from float number to integer:
export TABLESIZEPLUSMARGIN=${TABLESIZEPLUSMARGIN%.*}
# ##########################################
# Check the underlying TABLESPACE FREE SPACE:
# ##########################################
# Check the underlying tablespace:
UNDERLYINGTBS_RAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set pages 0 feedback off lines 1000;
prompt
select tablespace_name from dba_tables where owner=upper('${OWNER}') and table_name=upper('${OBJECT_NAME}');
prompt
exit;
EOF
)
UNDERLYINGTBS=`echo ${UNDERLYINGTBS_RAW} |perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'|cut -f1 -d '.'`
echo ""
echo "[Checking if [${UNDERLYINGTBS}] tablespace has SUFFICIENT FREE SPACE for this REBUILD operation] ..."
# Check the FREE SPACE on the underlying tablespace:
UNDERLYINGTBS_FREE_RAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set pages 0 feedback off lines 1000;
prompt
select /*+RULE*/ round(((tablespace_size*${blksize})/(1024*1024)) - ((used_space*${blksize})/(1024*1024)))
from dba_tablespace_usage_metrics where tablespace_name=upper('${UNDERLYINGTBS}');
prompt
exit;
EOF
)
UNDERLYINGTBS_FREE=`echo ${UNDERLYINGTBS_FREE_RAW} | awk '{print $NF}'`
# EXIT if the underlying tablespace FREE SPACE is less than TABLE SIZE + The SAFE MARGIN:
if [ ${UNDERLYINGTBS_FREE} -lt ${TABLESIZEPLUSMARGIN} ]
then
#REQUIREDSPACE=$(echo "${TABLESIZE} - ${UNDERLYINGTBS_FREE}" | bc)
REQUIREDSPACE=$(awk "BEGIN {print ${TABLESIZE} - ${UNDERLYINGTBS_FREE}}")
echo ""
echo -e "\033[33;5mThe underlying tablespace [${UNDERLYINGTBS}] has NO SUFFICIENT FREE SPACE to cater to this operation!\033[0m"
echo "TABLE + INDEXES SIZE= ${TABLESIZE} MB"
echo "TABLESPACE FREE SPACE= ${UNDERLYINGTBS_FREE} MB"
echo "You need to add at least ${REQUIREDSPACE} MB of extra space to [${UNDERLYINGTBS}] tablespace and then re-run the rebuild script again."
echo "If you think [${UNDERLYINGTBS}] tablespace has sufficient space and you want to skip this check, set the parameter CHECK_SPACE=N inside the script."
echo ""
echo "SCRIPT TERMINATED!"
echo ""
exit 1
else
echo "[TABLESPACE FREE SPACE= ${UNDERLYINGTBS_FREE} MB]"
echo ""
echo "[Tablespace [${UNDERLYINGTBS}] has SUFFICIENT FREE SPACE.]"
fi
;;
esac
# ########################################################
# Check if the supplement logging is enabled on the table:
# ########################################################
CHECK_REPLICATION_RAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
set pages 0 feedback off lines 1000;
prompt
select count(*) from DBA_LOG_GROUPS where OWNER= upper('${OWNER}') and TABLE_NAME= upper('${OBJECT_NAME}');
exit;
EOF
)
CHECK_REPLICATION=`echo ${CHECK_REPLICATION_RAW} | awk '{print $NF}'`
if [ ${CHECK_REPLICATION} -gt 0 ]
then
echo ""
echo -e "\033[33;5mWARNING WARNING WARNING!\033[0m"
echo "IS THIS TABLE IS GETTING REPLICATED TO ANOTHER DATABASE USING A REPLICATION TOOL i.e. Goldengate/Streams?"
echo "If this is the case; Then please Note that target table MAY miss some live data during the final SWAP step."
echo "Be in the safe side and execute the final SWAP command manually while the APPLICATION is DOWN."
echo ""
echo -e "\033[33;5m...\033[0m"
sleep 10
fi
# ####################
# PARALLEL DEGREE:
# ####################
# Computing the default PARALLEL DEGREE based on CPU count:
case `uname` in
Linux ) export PARALLEL_DEGREE=`cat /proc/cpuinfo| grep processor|wc -l`;;
AIX ) export PARALLEL_DEGREE=`lsdev -C|grep Process|wc -l`;;
SunOS ) export PARALLEL_DEGREE=`kstat cpu_info|grep core_id|sort -u|wc -l`;;
HP-UX) export PARALLEL_DEGREE=`lsdev -C|grep Process|wc -l`;;
esac
if [ ! -z "${PARALLEL_DEGREE##[0-9]*}" ]
then
export PARALLEL_DEGREE=1
fi
echo ""
echo "Number of CPUs detected on this machine is: ${PARALLEL_DEGREE}"
echo ""
echo "Enter the PARALLELISM DEGREE to be used for this rebuild operation? [Blank value means use the MAX CPU resources where PARALLELISM=${PARALLEL_DEGREE}]"
echo "==================================================================="
echo "[The HIGHER the PARALLELISM DEGREE the FASTER the REBUILD, but don't exceed the current number of CPUs [${PARALLEL_DEGREE}] on your system.]"
echo "[Note: 1 means NO Parallelism.]"
while read ENTERED_DEGREE
do
integ='^[0-9]+$'
case ${ENTERED_DEGREE} in
0) export ENTERED_DEGREE=1; break;;
"") export ENTERED_DEGREE=${PARALLEL_DEGREE};break;;
esac
if ! [[ ${ENTERED_DEGREE} =~ ${integ} ]] ; then
echo "Error: Not a valid number !"
echo
echo "Please Enter a VALID NUMBER for PARALLELISM DEGREE:"
echo "---------------------------------------------------"
else
export PARALLEL_DEGREE=${ENTERED_DEGREE}
echo ""
echo "DEGREE OF PARALLELISM IS SET TO [${ENTERED_DEGREE}]"
break
fi
done
# ##########################################################
# Check if the table can be REBUILD using DBMS_REDEFINITION:
# ##########################################################
echo ""
echo "[Checking if DBMS_REDEFINITION can be used to rebuild the table] ..."
CHK_ONLINE_REDEFINITION_OPTION_RAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set pages 0 feedback off echo off;
SELECT count(*) from v\$option where parameter='Online Redefinition' and value='TRUE';
exit;
EOF
)
CHK_ONLINE_REDEFINITION_OPTION=`echo ${CHK_ONLINE_REDEFINITION_OPTION_RAW} | awk '{print $NF}'`
if [ ${CHK_ONLINE_REDEFINITION_OPTION} -eq 1 ]
then
USEREDEFINITIONRAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
set pages 0 lines 1000;
prompt
EXEC DBMS_REDEFINITION.can_redef_table(upper('${OWNER}'),upper('${OBJECT_NAME}'),options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);
exit;
EOF
)
USEREDEFINITION=`echo ${USEREDEFINITIONRAW}|perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'|cut -f1 -d '.'`
case ${USEREDEFINITIONRAW} in
*'currently being redefined'*)
echo "Looks you already ran the script against the same table and didn't complete."
echo -e "\033[33;5mPlease run the following command to CLEANUP the STATE of the previous execution then run rebuild_table script again:\033[0m"
echo ""
echo "EXEC DBMS_REDEFINITION.abort_redef_table(upper('${OWNER}'), upper('${OBJECT_NAME}'), upper('<INTERIM_TABLE_NAME>'));"
echo ""
echo "SCRIPT TERMINATED!"
echo ""
exit 1;;
esac
case ${USEREDEFINITION} in
*completed*)
echo ""
echo -e "\033[33;5mVoila! TABLE [${OBJECT_NAME}] is eligible to be rebuild ONLINE using DBMS_REDEFINITION [with a Very Minimal Downtime on the table].\033[0m"
echo ""
echo "Do you want to use DBMS_REDEFINITION for ONLINE Table REBUILD with a MINIMAL DOWNTIME on the table? [Y|N] Y"
echo "==================================================================================================="
echo "Enter NO in case you want to use ALTER TABLE MOVE option."
while read ANS
do
case $ANS in
""|y|Y|yes|YES|Yes)
# Creating the INTERIM TABLE:
export OBJECT_NAME_INT=${OBJECT_NAME}_INT
# Check if the INTERIM TABLE is already exist:
INTERIM_TABLE_EXIST_RAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set pages 0 feedback off echo off;
SELECT count(*) from dba_tables where owner=upper('${OWNER}') and table_name=upper('${OBJECT_NAME_INT}');
exit;
EOF
)
INTERIM_TABLE_EXIST=`echo ${INTERIM_TABLE_EXIST_RAW} | awk '{print $NF}'`
if [ ${INTERIM_TABLE_EXIST} -eq 1 ]
then
echo -e "\033[33;5mThe INTERIM table [${OWNER}.${OBJECT_NAME_INT}] IS ALREADY EXIST!\033[0m"
echo "Changing the INTERIM TABLE NAME to [${OBJECT_NAME}_XNT]."
export OBJECT_NAME_INT=${OBJECT_NAME}_XNT
# Second Attempt of checking the existance of INTERIM table:
INTERIM_TABLE_EXIST2_RAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set pages 0 feedback off echo off;
SELECT count(*) from dba_tables where owner=upper('${OWNER}') and table_name=upper('${OBJECT_NAME_INT}');
exit;
EOF
)
INTERIM_TABLE_EXIST2=`echo ${INTERIM_TABLE_EXIST2_RAW} | awk '{print $NF}'`
if [ ${INTERIM_TABLE_EXIST2} -eq 1 ]
then
echo -e "\033[33;5mAgain! A table with same name of the INTERIM table [${OBJECT_NAME_INT}] IS ALREADY EXIST!\033[0m"
echo "Most probably you ran this script multiple times against the same table, if this is the case; then please DROP the old INTERIM tables|MATERIALIZED VIEWS."
echo "SCRIPT TERMINATED!"
echo ""
exit 1
fi
fi
# SETTING PCT_FREE FOR THE TABLE AFTER THE REBUILD:
# #################################################
VALDEFAULTPCTFREE=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
set pages 0 feedback off lines 1000;
prompt
select PCT_FREE from DBA_TABLES where owner=upper('${OWNER}') and table_name=upper('${OBJECT_NAME}');
exit;
EOF
)
DEFAULTPCTFREE=`echo ${VALDEFAULTPCTFREE}|perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'|cut -f1 -d '.'`
echo ""
echo "INFO: Current PCTFREE: ${DEFAULTPCTFREE}"
echo "INFO: Number of \"UPDATE\" transactions on the table since the last Statistics Gathered on [${LASTANALYZED}]: ${TABUPDATES}"
echo
echo
echo "Specify the table PCTFREE after the rebuild: [How much %Free space will be left in each block for future updates | Current ${DEFAULTPCTFREE}%]"
echo "==========================================="
echo "Note: The SMALLER the PCTFREE the SMALLER the table size after the rebuild. [Recommended for Archival/Datawarehouse Tables]"
echo "Note: If the table is highly updated it's recommended to keep the current PCTFREE: ${DEFAULTPCTFREE}"
echo "Leave it BLANK and hit Enter to keep the default PCTFREE."
while read TABPCTFREEVAL
do
case ${TABPCTFREEVAL} in
"") export TABPCTFREE=${DEFAULTPCTFREE}
break;;
*[!0-9]*) echo "Please enter a valid NUMBER for PCTFREE:";;
*) export TABPCTFREE=${TABPCTFREEVAL}
break;;
esac
done
# INTERIM TABLE CREATION:
INTERIMTABLECREATIONRAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
set pages 0 lines 1000;
prompt
CREATE TABLE ${OWNER}.${OBJECT_NAME_INT} PCTFREE ${TABPCTFREE} AS SELECT * FROM ${OWNER}.${OBJECT_NAME} WHERE 1=2;
exit;
EOF
)
INTERIMTABLECREATION=`echo ${INTERIMTABLECREATIONRAW}|perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'|cut -f1 -d '.'`
case ${INTERIMTABLECREATION} in
*created*)
echo "The INTERIM table [${OBJECT_NAME_INT}] created successfully."
echo ""
echo "Do you want to run the FINAL step of SWAPPING to the new table MANUALLY? [Where the minimal DOWNTIME will occur] [Y|N] Y"
echo "======================================================================="
echo "Enter [NO] to let the script run all the steps AUTOMATICALLY for you."
while read MANUALRUN
do
case $MANUALRUN in
""|y|Y|yes|YES|Yes)export HASHNOTIFY="";export HASHSWAP="--";export HASHSTATS="--"; break;;
n|N|no|NO|No) export HASHNOTIFY="--";export HASHSWAP="";export HASHSTATS=""
echo ""
echo "Do you want to GATHER NEW STATISTICS after table rebuild [Recommended]? [Y|N] Y"
echo "======================================================================"
echo "Enter NO to keep the old statistics after the rebuild."
while read ANS2
do
case $ANS2 in
""|y|Y|yes|YES|Yes) export HASHSTATS=""; break;;
n|N|no|NO|No) export HASHSTATS="--"; break;;
*) echo "Please enter a VALID answer [Y|N]" ;;
esac
done
break;;
*) echo "Please enter a VALID answer [Y|N]" ;;
esac
done
# Creating the REBUILD script:
if [ ! -d ${USR_ORA_HOME} ]
then
export USR_ORA_HOME=/tmp
fi
REBUILDTABLESCRIPT=${USR_ORA_HOME}/REBUILDTABLESCRIPT-${OWNER}.${OBJECT_NAME}.sql
REBUILDTABLESCRIPTRUNNER=${USR_ORA_HOME}/REBUILDTABLESCRIPT-${OWNER}.${OBJECT_NAME}.sh
REBUILDTABLESPOOL=${USR_ORA_HOME}/REBUILDTABLESCRIPT-${OWNER}.${OBJECT_NAME}.log
echo "spool ${REBUILDTABLESPOOL}" > ${REBUILDTABLESCRIPT}
echo "PROMPT THIS OPERATION IS LOGGED IN: [${REBUILDTABLESPOOL}]" >> ${REBUILDTABLESCRIPT}
echo "PROMPT" >> ${REBUILDTABLESCRIPT}
echo "set pages 0 feedback off lines 168" >> ${REBUILDTABLESCRIPT}
echo "EXEC DBMS_SESSION.set_identifier('REBUILDING_${OWNER}.${OBJECT_NAME}');" >> ${REBUILDTABLESCRIPT}
echo "PROMPT ***************" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "PROMPT Session Details:" >> ${REBUILDTABLESCRIPT}
echo "PROMPT ***************" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "select 'OSPID: '||p.spid from v\$session s, v\$process p where s.sid=(select sid from v\$mystat where rownum=1) and s.paddr=p.addr;" >> ${REBUILDTABLESCRIPT}
echo "select 'SID: '||sid from v\$mystat where rownum = 1;" >> ${REBUILDTABLESCRIPT}
echo "select 'Serial#: '||s.serial# from v\$session s where s.sid = (select sid from v\$mystat where rownum = 1);" >> ${REBUILDTABLESCRIPT}
echo "PROMPT" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "PROMPT You can KILL this session using this command:" >> ${REBUILDTABLESCRIPT}
echo "PROMPT ********************************************" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "select 'ALTER SYSTEM DISCONNECT SESSION '''||sid||','||serial#||''' IMMEDIATE;' from v\$session where sid = (select sid from v\$mystat where rownum = 1);" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "PROMPT" >> ${REBUILDTABLESCRIPT}
echo "PROMPT" >> ${REBUILDTABLESCRIPT}
echo "PROMPT TABLE INFO BEFORE THE REBUILD:" >> ${REBUILDTABLESCRIPT}
echo "PROMPT *****************************" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "set linesize 168 pages 1000 heading on feedback on" >> ${REBUILDTABLESCRIPT}
echo "col \"OWNER.TABLE\" for a35" >> ${REBUILDTABLESCRIPT}
echo "col tablespace_name for a20" >> ${REBUILDTABLESCRIPT}
echo "col \"READONLY\" for a8" >> ${REBUILDTABLESCRIPT}
echo "col \"%RECLAIM\" for 999" >> ${REBUILDTABLESCRIPT}
echo "col LAST_ANALYZED for a13" >> ${REBUILDTABLESCRIPT}
echo "col COMPRESSION for a8" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "select /*+RULE*/ t.owner||'.'||t.table_name \"OWNER.TABLE\",t.TABLESPACE_NAME,d.extents,t.LOGGING" >> ${REBUILDTABLESCRIPT}
echo ",t.COMPRESSION,t.READ_ONLY \"READONLY\",o.created,to_char(t.LAST_ANALYZED, 'DD-MON-YYYY') LAST_ANALYZED" >> ${REBUILDTABLESCRIPT}
echo ",round(d.bytes/1025/1024) SIZE_MB," >> ${REBUILDTABLESCRIPT}
echo "round((t.blocks * ${blksize}/1024/1024), 2) - round((t.num_rows * t.avg_row_len/1024/1024), 2) \"FRAGMENTED_SPACE_MB\"," >> ${REBUILDTABLESCRIPT}
echo "((round((t.blocks * ${blksize}/1024/1024), 2) - round((t.num_rows * t.avg_row_len/1024/1024), 2)) /" >> ${REBUILDTABLESCRIPT}
echo "round((t.blocks * ${blksize}/1024/1024), 2)) * 100 \"%RECLAIM\"" >> ${REBUILDTABLESCRIPT}
echo "from dba_tables t, dba_objects o, dba_segments d" >> ${REBUILDTABLESCRIPT}
echo "where t.owner= upper('${OWNER}')" >> ${REBUILDTABLESCRIPT}
echo "and t.table_name = upper('${OBJECT_NAME}')" >> ${REBUILDTABLESCRIPT}
echo "and o.owner=t.owner" >> ${REBUILDTABLESCRIPT}
echo "and o.object_name=t.table_name" >> ${REBUILDTABLESCRIPT}
echo "and o.owner=d.owner" >> ${REBUILDTABLESCRIPT}
echo "and t.table_name=d.SEGMENT_NAME;" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "PROMPT LOBS:" >> ${REBUILDTABLESCRIPT}
echo "PROMPT -----" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "col COMPRESSION for a8" >> ${REBUILDTABLESCRIPT}
echo "col SEGMENT_NAME for a35" >> ${REBUILDTABLESCRIPT}
echo "col INDEX_NAME for a35" >> ${REBUILDTABLESCRIPT}
echo "col SECUREFILE for a10" >> ${REBUILDTABLESCRIPT}
echo "col COLUMN_NAME for a40" >> ${REBUILDTABLESCRIPT}
echo "select SEGMENT_NAME,COLUMN_NAME,TABLESPACE_NAME,INDEX_NAME,SECUREFILE,COMPRESSION from dba_lobs" >> ${REBUILDTABLESCRIPT}
echo "where owner=upper('${OWNER}') and table_name = upper('${OBJECT_NAME}');" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "PROMPT INDEXES BEFORE THE REBUILD:" >> ${REBUILDTABLESCRIPT}
echo "PROMPT ---------------------------" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "COLUMN OWNER FORMAT A25 heading \"Index Owner\"" >> ${REBUILDTABLESCRIPT}
echo "COLUMN INDEX_NAME FORMAT A30 heading \"Index Name\"" >> ${REBUILDTABLESCRIPT}
echo "COLUMN COLUMN_NAME FORMAT A25 heading \"On Column\"" >> ${REBUILDTABLESCRIPT}
echo "COLUMN COLUMN_POSITION FORMAT 9999 heading \"Pos\"" >> ${REBUILDTABLESCRIPT}
echo "COLUMN \"INDEX\" FORMAT A35" >> ${REBUILDTABLESCRIPT}
echo "COLUMN INDEX_TYPE FOR A16" >> ${REBUILDTABLESCRIPT}
echo "SELECT /*+RULE*/ IND.OWNER||'.'||IND.INDEX_NAME \"INDEX\"," >> ${REBUILDTABLESCRIPT}
echo " IND.INDEX_TYPE," >> ${REBUILDTABLESCRIPT}
echo " COL.COLUMN_NAME," >> ${REBUILDTABLESCRIPT}
echo " COL.COLUMN_POSITION," >> ${REBUILDTABLESCRIPT}
echo " IND.TABLESPACE_NAME," >> ${REBUILDTABLESCRIPT}
echo " IND.STATUS," >> ${REBUILDTABLESCRIPT}
echo " IND.UNIQUENESS," >> ${REBUILDTABLESCRIPT}
echo " IND.LAST_ANALYZED,round(d.bytes/1024/1024) SIZE_MB" >> ${REBUILDTABLESCRIPT}
echo "FROM SYS.DBA_INDEXES IND," >> ${REBUILDTABLESCRIPT}
echo " SYS.DBA_IND_COLUMNS COL," >> ${REBUILDTABLESCRIPT}
echo " DBA_SEGMENTS d" >> ${REBUILDTABLESCRIPT}
echo "WHERE IND.TABLE_NAME = upper('${OBJECT_NAME}')" >> ${REBUILDTABLESCRIPT}
echo "AND IND.TABLE_OWNER = upper('${OWNER}')" >> ${REBUILDTABLESCRIPT}
echo "AND IND.TABLE_NAME = COL.TABLE_NAME" >> ${REBUILDTABLESCRIPT}
echo "AND IND.OWNER = d.OWNER(+)" >> ${REBUILDTABLESCRIPT}
echo "AND IND.OWNER = COL.INDEX_OWNER" >> ${REBUILDTABLESCRIPT}
echo "AND IND.TABLE_OWNER = COL.TABLE_OWNER" >> ${REBUILDTABLESCRIPT}
echo "AND IND.INDEX_NAME = COL.INDEX_NAME" >> ${REBUILDTABLESCRIPT}
echo "AND IND.INDEX_NAME = d.SEGMENT_NAME(+);" >> ${REBUILDTABLESCRIPT}
echo "PROMPT" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "PROMPT CONSTRAINTS BEFORE THE REBUILD:" >> ${REBUILDTABLESCRIPT}
echo "PROMPT -------------------------------" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "col CONSTRAINT_NAME for a30" >> ${REBUILDTABLESCRIPT}
echo "col R_CONSTRAINT_NAME for a30" >> ${REBUILDTABLESCRIPT}
echo "select CONSTRAINT_NAME,CONSTRAINT_TYPE,R_CONSTRAINT_NAME,STATUS,DEFERRED,INDEX_NAME from dba_constraints where">> ${REBUILDTABLESCRIPT}
echo "OWNER=upper('${OWNER}') and TABLE_NAME=upper('${OBJECT_NAME}') order by CONSTRAINT_TYPE;" >> ${REBUILDTABLESCRIPT}
echo "PROMPT" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "PROMPT [Setting the PARALLELISM FACTOR to ${PARALLEL_DEGREE}] ..." >> ${REBUILDTABLESCRIPT}
echo "alter session enable parallel dml;" >> ${REBUILDTABLESCRIPT}
echo "alter session enable parallel ddl;" >> ${REBUILDTABLESCRIPT}
echo "alter session enable parallel query;" >> ${REBUILDTABLESCRIPT}
echo "alter session force parallel dml parallel ${PARALLEL_DEGREE};" >> ${REBUILDTABLESCRIPT}
echo "alter session force parallel ddl parallel ${PARALLEL_DEGREE};" >> ${REBUILDTABLESCRIPT}
echo "alter session force parallel query parallel ${PARALLEL_DEGREE};" >> ${REBUILDTABLESCRIPT}
echo "PROMPT [Setting the SESSION DDL WAIT FOR LOCK to 5 minutes] ..." >> ${REBUILDTABLESCRIPT}
echo "alter session set ddl_lock_timeout=300;" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "PROMPT [Setting the SESSION RESUMABLE TIMEOUT to 6 Hours] ..." >> ${REBUILDTABLESCRIPT}
echo "ALTER SESSION ENABLE RESUMABLE TIMEOUT 21600 NAME 'REBUILD_OF_TABLE_${OBJECT_NAME}';" >> ${REBUILDTABLESCRIPT}
echo "PROMPT" >> ${REBUILDTABLESCRIPT}
echo "PROMPT [Starting REBUILDING the table using DBMS_REDEFINITION] ..." >> ${REBUILDTABLESCRIPT}
echo "PROMPT" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "PROMPT At anytime you can abort the REBUILD process by executing this command:" >> ${REBUILDTABLESCRIPT}
echo "PROMPT ----------------------------------------------------------------------" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "PROMPT exec dbms_redefinition.abort_redef_table(upper('${OWNER}'), upper('${OBJECT_NAME}'), upper('${OBJECT_NAME_INT}'));;" >> ${REBUILDTABLESCRIPT}
echo "PROMPT" >> ${REBUILDTABLESCRIPT}
echo "PROMPT REBUILDING TABLE PROCEDURE WILL START WITHIN 5 Seconds ..." >> ${REBUILDTABLESCRIPT}
echo "PROMPT -------------------------------------------------------" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "SET FEEDBACK OFF" >> ${REBUILDTABLESCRIPT}
echo "exec dbms_lock.sleep(4);" >> ${REBUILDTABLESCRIPT}
echo "PROMPT [5]" >> ${REBUILDTABLESCRIPT}
echo "exec dbms_lock.sleep(1);" >> ${REBUILDTABLESCRIPT}
echo "PROMPT [4]" >> ${REBUILDTABLESCRIPT}
echo "exec dbms_lock.sleep(1);" >> ${REBUILDTABLESCRIPT}
echo "PROMPT [3]" >> ${REBUILDTABLESCRIPT}
echo "exec dbms_lock.sleep(1);" >> ${REBUILDTABLESCRIPT}
echo "PROMPT [2]" >> ${REBUILDTABLESCRIPT}
echo "exec dbms_lock.sleep(1);" >> ${REBUILDTABLESCRIPT}
echo "PROMPT [1]" >> ${REBUILDTABLESCRIPT}
echo "exec dbms_lock.sleep(1);" >> ${REBUILDTABLESCRIPT}
echo "SET FEEDBACK ON" >> ${REBUILDTABLESCRIPT}
echo "PROMPT" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "PROMPT Keep checking the progress using this statement:" >>${REBUILDTABLESCRIPT}
echo "PROMPT ------------------------------------------------" >>${REBUILDTABLESCRIPT}
echo "" >>${REBUILDTABLESCRIPT}
echo "PROMPT col OPERATION for a21" >>${REBUILDTABLESCRIPT}
echo "PROMPT col \"%DONE\" for 999.999" >>${REBUILDTABLESCRIPT}
echo "PROMPT col \"STARTED|MIN_ELAPSED|REMAIN\" for a26" >>${REBUILDTABLESCRIPT}
echo "PROMPT col MESSAGE for a90" >>${REBUILDTABLESCRIPT}
echo "PROMPT col \"USERNAME| SID,SERIAL#\" for a28" >>${REBUILDTABLESCRIPT}
echo "PROMPT select USERNAME||'| '||SID||','||SERIAL# \"USERNAME| SID,SERIAL#\",SQL_ID,round(SOFAR/TOTALWORK*100,2) \"%DONE\"" >>${REBUILDTABLESCRIPT}
echo "PROMPT ,to_char(START_TIME,'DD-Mon HH24:MI')||'| '||trunc(ELAPSED_SECONDS/60)||'|'||trunc(TIME_REMAINING/60) \"STARTED|MIN_ELAPSED|REMAIN\" ,MESSAGE" >>${REBUILDTABLESCRIPT}
echo "PROMPT from v\$session_longops" >>${REBUILDTABLESCRIPT}
echo "PROMPT where TARGET LIKE UPPER('%${OBJECT_NAME}%') and TOTALWORK <> '0' and SOFAR/TOTALWORK*100 <>'100'" >>${REBUILDTABLESCRIPT}
echo "PROMPT order by \"STARTED|MIN_ELAPSED|REMAIN\" desc, \"USERNAME| SID,SERIAL#\";;" >>${REBUILDTABLESCRIPT}
echo "" >>${REBUILDTABLESCRIPT}
echo "PROMPT" >>${REBUILDTABLESCRIPT}
echo "--Exit the script if any failure encountered at any stage:" >>${REBUILDTABLESCRIPT}
echo "" >>${REBUILDTABLESCRIPT}
echo "set serveroutput on" >>${REBUILDTABLESCRIPT}
echo "WHENEVER SQLERROR EXIT SQL.SQLCODE;" >>${REBUILDTABLESCRIPT}
echo "" >>${REBUILDTABLESCRIPT}
echo "PROMPT START DUPLICATING THE TABLE TO [${OBJECT_NAME_INT}]: [The execution time is dependant on the size of the table and its indexes]" >>${REBUILDTABLESCRIPT}
echo "PROMPT ---------------------------------------------------------" >>${REBUILDTABLESCRIPT}
echo "" >>${REBUILDTABLESCRIPT}
echo "PROMPT EXEC DBMS_REDEFINITION.start_redef_table(upper('${OWNER}'), upper('${OBJECT_NAME}'), upper('${OBJECT_NAME_INT}'),options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);;" >>${REBUILDTABLESCRIPT}
echo "PROMPT" >>${REBUILDTABLESCRIPT}
echo "${HASHSWAP} PROMPT [AN ORA-NOTIFICATION MESSAGE WILL BE WRITTEN TO THE ALERTLOG ONEC THE REBUILD OPERATION IS COMPLETED.]" >>${REBUILDTABLESCRIPT}
echo "PROMPT" >>${REBUILDTABLESCRIPT}
echo "PROMPT NOTE: Feel free to exit from this session as the script is running in the BACKGROUND." >>${REBUILDTABLESCRIPT}
echo "PROMPT" >>${REBUILDTABLESCRIPT}
echo "SET TIMING ON" >>${REBUILDTABLESCRIPT}
echo "EXEC DBMS_REDEFINITION.start_redef_table(upper('${OWNER}'), upper('${OBJECT_NAME}'), upper('${OBJECT_NAME_INT}'),options_flag => DBMS_REDEFINITION.CONS_USE_ROWID);" >>${REBUILDTABLESCRIPT}
echo "SET TIMING OFF" >>${REBUILDTABLESCRIPT}
echo "PROMPT" >>${REBUILDTABLESCRIPT}
echo "PROMPT [INTERIM TABLE INITIAL COPY COMPLETED.]" >>${REBUILDTABLESCRIPT}
echo "" >>${REBUILDTABLESCRIPT}
echo "PROMPT" >>${REBUILDTABLESCRIPT}
echo "PROMPT Creating Dependant Objects [Including Indexes] ..." >>${REBUILDTABLESCRIPT}
echo "PROMPT ----------------------------------------------" >>${REBUILDTABLESCRIPT}
echo "" >>${REBUILDTABLESCRIPT}
echo "PROMPT" >>${REBUILDTABLESCRIPT}
echo "PROMPT DECLARE" >>${REBUILDTABLESCRIPT}
echo "PROMPT num_errors PLS_INTEGER;;" >>${REBUILDTABLESCRIPT}
echo "PROMPT BEGIN" >>${REBUILDTABLESCRIPT}
echo "PROMPT DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS (upper('${OWNER}'), upper('${OBJECT_NAME}'), upper('${OBJECT_NAME_INT}')," >>${REBUILDTABLESCRIPT}
echo "PROMPT DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors, TRUE);;" >>${REBUILDTABLESCRIPT}
echo "PROMPT END;;" >>${REBUILDTABLESCRIPT}
echo "PROMPT /" >>${REBUILDTABLESCRIPT}
echo "" >>${REBUILDTABLESCRIPT}
echo "SET TIMING ON" >>${REBUILDTABLESCRIPT}
echo "DECLARE" >>${REBUILDTABLESCRIPT}
echo "num_errors PLS_INTEGER;" >>${REBUILDTABLESCRIPT}
echo "BEGIN" >>${REBUILDTABLESCRIPT}
echo "DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS (upper('${OWNER}'), upper('${OBJECT_NAME}'), upper('${OBJECT_NAME_INT}')," >>${REBUILDTABLESCRIPT}
echo "DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors, TRUE);" >>${REBUILDTABLESCRIPT}
echo "END;" >>${REBUILDTABLESCRIPT}
echo "/" >>${REBUILDTABLESCRIPT}
echo "SET TIMING OFF" >>${REBUILDTABLESCRIPT}
echo "PROMPT" >>${REBUILDTABLESCRIPT}
echo "PROMPT [DEPENDANT OBJECTS CREATED.]" >>${REBUILDTABLESCRIPT}
echo "" >>${REBUILDTABLESCRIPT}
echo "PROMPT" >>${REBUILDTABLESCRIPT}
echo "PROMPT [Checking the ERROR Log:]" >>${REBUILDTABLESCRIPT}
echo "PROMPT SELECT OBJECT_NAME,DDL_TEXT FROM DBA_REDEFINITION_ERRORS WHERE BASE_TABLE_NAME=UPPER('${OBJECT_NAME}');;" >>${REBUILDTABLESCRIPT}
echo "set long 200" >>${REBUILDTABLESCRIPT}
echo "col DDL_TXT for a150" >>${REBUILDTABLESCRIPT}
echo "select object_name, ddl_txt from DBA_REDEFINITION_ERRORS where base_table_name=upper('${OBJECT_NAME}') and ddl_txt not like '%NULL ENABLE NOVALIDATE%';" >>${REBUILDTABLESCRIPT}
echo "" >>${REBUILDTABLESCRIPT}
echo "PROMPT" >>${REBUILDTABLESCRIPT}
echo "PROMPT Running the FINAL SYNC of the INTERIM TABLE Before the Actual SWAP:" >>${REBUILDTABLESCRIPT}
echo "PROMPT ------------------------------------------------------------------">>${REBUILDTABLESCRIPT}
echo "" >>${REBUILDTABLESCRIPT}
echo "PROMPT EXEC DBMS_REDEFINITION.sync_interim_table(upper('${OWNER}'), upper('${OBJECT_NAME}'), upper('${OBJECT_NAME_INT}'));;">>${REBUILDTABLESCRIPT}
echo "" >>${REBUILDTABLESCRIPT}
echo "SET TIMING ON" >>${REBUILDTABLESCRIPT}
echo "EXEC DBMS_REDEFINITION.sync_interim_table(upper('${OWNER}'), upper('${OBJECT_NAME}'), upper('${OBJECT_NAME_INT}'));" >>${REBUILDTABLESCRIPT}
echo "SET TIMING OFF" >>${REBUILDTABLESCRIPT}
echo "" >>${REBUILDTABLESCRIPT}
echo "PROMPT" >>${REBUILDTABLESCRIPT}
echo "PROMPT Running a COMPARISON between the OLD and NEW TABLE:" >>${REBUILDTABLESCRIPT}
echo "PROMPT ***************************************************" >>${REBUILDTABLESCRIPT}
echo "PROMPT" >> ${REBUILDTABLESCRIPT}
echo "PROMPT TABLES:" >> ${REBUILDTABLESCRIPT}
echo "PROMPT ------" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "set linesize 168 pages 1000 heading on feedback off" >> ${REBUILDTABLESCRIPT}
echo "col \"TABLE_NAME\" for a55" >> ${REBUILDTABLESCRIPT}
echo "col COMPRESSION for a8" >> ${REBUILDTABLESCRIPT}
echo "col tablespace_name for a20" >> ${REBUILDTABLESCRIPT}
echo "col \"READONLY\" for a8" >> ${REBUILDTABLESCRIPT}
echo "col DEGREE for a7" >> ${REBUILDTABLESCRIPT}
echo "col \"%RECLAIM\" for 999" >> ${REBUILDTABLESCRIPT}
echo "COLUMN TABLE_NAME FORMAT A40 heading \"Table Name\"" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "select /*+RULE*/ 'ORIGINAL_TABLE: '||t.table_name \"TABLE_NAME\",t.TABLESPACE_NAME,d.extents," >> ${REBUILDTABLESCRIPT}
echo "t.COMPRESSION,t.READ_ONLY \"READONLY\"" >> ${REBUILDTABLESCRIPT}
echo ",round(d.bytes/1025/1024) SIZE_MB," >> ${REBUILDTABLESCRIPT}
echo "round((t.blocks * ${blksize}/1024/1024), 2) - round((t.num_rows * t.avg_row_len/1024/1024), 2) \"FRAGMENTED_SPACE_MB\"," >> ${REBUILDTABLESCRIPT}
echo "((round((t.blocks * ${blksize}/1024/1024), 2) - round((t.num_rows * t.avg_row_len/1024/1024), 2)) /" >> ${REBUILDTABLESCRIPT}
echo "round((t.blocks * ${blksize}/1024/1024), 2)) * 100 \"%RECLAIM\"" >> ${REBUILDTABLESCRIPT}
echo "from dba_tables t, dba_objects o, dba_segments d" >> ${REBUILDTABLESCRIPT}
echo "where t.owner= upper('${OWNER}')" >> ${REBUILDTABLESCRIPT}
echo "and t.table_name = upper('${OBJECT_NAME}')" >> ${REBUILDTABLESCRIPT}
echo "and o.owner=t.owner" >> ${REBUILDTABLESCRIPT}
echo "and o.object_name=t.table_name" >> ${REBUILDTABLESCRIPT}
echo "and o.owner=d.owner" >> ${REBUILDTABLESCRIPT}
echo "and t.table_name=d.SEGMENT_NAME" >> ${REBUILDTABLESCRIPT}
echo "union" >> ${REBUILDTABLESCRIPT}
echo "select /*+RULE*/ 'NEW_TABLE: '||t.table_name TABLE_NAME,t.TABLESPACE_NAME,d.extents" >> ${REBUILDTABLESCRIPT}
echo ",t.COMPRESSION,t.READ_ONLY \"READONLY\"" >> ${REBUILDTABLESCRIPT}
echo ",round(d.bytes/1025/1024) SIZE_MB" >> ${REBUILDTABLESCRIPT}
echo ",round((t.blocks * ${blksize}/1024/1024), 2) - round((t.num_rows * t.avg_row_len/1024/1024), 2) \"FRAGMENTED_SPACE_MB\"" >> ${REBUILDTABLESCRIPT}
echo ",((round((t.blocks * ${blksize}/1024/1024), 2) - round((t.num_rows * nullif(t.avg_row_len,0)/1024/1024), 2)) /" >> ${REBUILDTABLESCRIPT}
echo "round((t.blocks * ${blksize}/1024/1024), 2)) * 100 \"%RECLAIM\"" >> ${REBUILDTABLESCRIPT}
echo "from dba_tables t, dba_objects o, dba_segments d" >> ${REBUILDTABLESCRIPT}
echo "where t.owner= upper('${OWNER}')" >> ${REBUILDTABLESCRIPT}
echo "and t.table_name = upper('${OBJECT_NAME_INT}')" >> ${REBUILDTABLESCRIPT}
echo "and o.owner=t.owner" >> ${REBUILDTABLESCRIPT}
echo "and o.object_name=t.table_name" >> ${REBUILDTABLESCRIPT}
echo "and o.owner=d.owner" >> ${REBUILDTABLESCRIPT}
echo "and t.table_name=d.SEGMENT_NAME" >> ${REBUILDTABLESCRIPT}
echo "order by 1 desc;" >> ${REBUILDTABLESCRIPT}
echo "PROMPT" >> ${REBUILDTABLESCRIPT}
echo "set pages 0" >> ${REBUILDTABLESCRIPT}
echo "select 'ORIGINAL_TABLE NUMBER OF ROWS: '||count(*) from ${OWNER}.${OBJECT_NAME};" >> ${REBUILDTABLESCRIPT}
echo "select 'NEW_TABLE NUMBER OF ROWS: '||count(*) from ${OWNER}.${OBJECT_NAME_INT};" >> ${REBUILDTABLESCRIPT}
echo "set pages 1000" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "PROMPT" >> ${REBUILDTABLESCRIPT}
echo "PROMPT LOBS:" >> ${REBUILDTABLESCRIPT}
echo "PROMPT -----" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "col SEGMENT_NAME for a35" >> ${REBUILDTABLESCRIPT}
echo "col INDEX_NAME for a35" >> ${REBUILDTABLESCRIPT}
echo "col COMPRESSION for a8" >> ${REBUILDTABLESCRIPT}
echo "col SECUREFILE for a10" >> ${REBUILDTABLESCRIPT}
echo "col COLUMN_NAME for a35" >> ${REBUILDTABLESCRIPT}
echo "select 'ORIGINAL_TABLE: '||TABLE_NAME,SEGMENT_NAME,COLUMN_NAME,TABLESPACE_NAME,INDEX_NAME,SECUREFILE,COMPRESSION from dba_lobs" >> ${REBUILDTABLESCRIPT}
echo "where owner=upper('${OWNER}') and table_name = upper('${OBJECT_NAME}')" >> ${REBUILDTABLESCRIPT}
echo "union" >> ${REBUILDTABLESCRIPT}
echo "select 'NEW_TABLE: '||TABLE_NAME,SEGMENT_NAME,COLUMN_NAME,TABLESPACE_NAME,INDEX_NAME,SECUREFILE,COMPRESSION from dba_lobs" >> ${REBUILDTABLESCRIPT}
echo "where owner=upper('${OWNER}') and table_name = upper('${OBJECT_NAME_INT}');" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "PROMPT" >>${REBUILDTABLESCRIPT}
echo "PROMPT INDEXES:" >>${REBUILDTABLESCRIPT}
echo "PROMPT --------" >>${REBUILDTABLESCRIPT}
echo "" >>${REBUILDTABLESCRIPT}
echo "COLUMN TABLE_NAME FORMAT A45 heading \"Table Name\"" >>${REBUILDTABLESCRIPT}
echo "COLUMN INDEX_NAME FORMAT A30 heading \"Index Name\"" >>${REBUILDTABLESCRIPT}
echo "COLUMN COLUMN_NAME FORMAT A25 heading \"On Column\"" >>${REBUILDTABLESCRIPT}
echo "COLUMN COLUMN_POSITION FORMAT 9999 heading \"Pos\"" >>${REBUILDTABLESCRIPT}
echo "COLUMN \"INDEX\"FORMAT A35" >>${REBUILDTABLESCRIPT}
echo "COLUMN TABLESPACE_NAME FOR A25" >>${REBUILDTABLESCRIPT}
echo "COLUMN INDEX_TYPE FOR A14" >>${REBUILDTABLESCRIPT}
echo "SELECT /*+RULE*/ 'ORIGINAL_TABLE: '||I.TABLE_NAME \"TABLE_NAME\", I.INDEX_NAME \"INDEX\",I.STATUS,I.INDEX_TYPE,C.COLUMN_NAME,C.COLUMN_POSITION," >>${REBUILDTABLESCRIPT}
echo "I.UNIQUENESS \"UNIQUE\",round(d.bytes/1024/1024) SIZE_MB FROM DBA_INDEXES I, DBA_IND_COLUMNS C, DBA_SEGMENTS d" >>${REBUILDTABLESCRIPT}
echo "WHERE I.TABLE_NAME = upper('${OBJECT_NAME}') AND I.TABLE_OWNER = upper('${OWNER}') AND I.TABLE_NAME = C.TABLE_NAME" >>${REBUILDTABLESCRIPT}
echo "AND I.OWNER = d.OWNER AND I.OWNER = C.INDEX_OWNER AND I.TABLE_OWNER = C.TABLE_OWNER AND I.INDEX_NAME = C.INDEX_NAME" >>${REBUILDTABLESCRIPT}
echo "AND I.INDEX_NAME = d.SEGMENT_NAME" >>${REBUILDTABLESCRIPT}
echo "union" >>${REBUILDTABLESCRIPT}
echo "SELECT 'NEW_TABLE: '||I.TABLE_NAME \"TABLE_NAME\", I.INDEX_NAME \"INDEX\",I.STATUS,I.INDEX_TYPE,C.COLUMN_NAME,C.COLUMN_POSITION," >>${REBUILDTABLESCRIPT}
echo "I.UNIQUENESS \"UNIQUE\",round(d.bytes/1024/1024) SIZE_MB FROM DBA_INDEXES I, DBA_IND_COLUMNS C, DBA_SEGMENTS d" >>${REBUILDTABLESCRIPT}
echo "WHERE I.TABLE_NAME = upper('${OBJECT_NAME_INT}') AND I.TABLE_OWNER = upper('${OWNER}') AND I.TABLE_NAME = C.TABLE_NAME" >>${REBUILDTABLESCRIPT}
echo "AND I.OWNER = d.OWNER AND I.OWNER = C.INDEX_OWNER AND I.TABLE_OWNER = C.TABLE_OWNER AND I.INDEX_NAME = C.INDEX_NAME" >>${REBUILDTABLESCRIPT}
echo "AND I.INDEX_NAME = d.SEGMENT_NAME AND COLUMN_NAME <> 'M_ROW\$\$'" >>${REBUILDTABLESCRIPT}
echo "order by 1 desc;" >>${REBUILDTABLESCRIPT}
echo "" >>${REBUILDTABLESCRIPT}
echo "PROMPT" >>${REBUILDTABLESCRIPT}
echo "PROMPT Dependant Objects:" >>${REBUILDTABLESCRIPT}
echo "PROMPT ------------------" >>${REBUILDTABLESCRIPT}
echo "" >>${REBUILDTABLESCRIPT}
echo "select 'ORIGINAL_TABLE: '||NAME \"TABLE_NAME\",REFERENCED_OWNER,REFERENCED_NAME,REFERENCED_TYPE from DBA_DEPENDENCIES where">>${REBUILDTABLESCRIPT}
echo "OWNER=upper('${OWNER}') and NAME=upper('${OBJECT_NAME_INT}')" >>${REBUILDTABLESCRIPT}
echo "union" >>${REBUILDTABLESCRIPT}
echo "select 'NEW_TABLE: '||NAME \"TABLE_NAME\",REFERENCED_OWNER,REFERENCED_NAME,REFERENCED_TYPE from DBA_DEPENDENCIES where">>${REBUILDTABLESCRIPT}
echo "OWNER=upper('${OWNER}') and NAME=upper('${OBJECT_NAME}')" >>${REBUILDTABLESCRIPT}
echo "order by 1 desc;" >>${REBUILDTABLESCRIPT}
echo "PROMPT" >>${REBUILDTABLESCRIPT}
echo "PROMPT CONSTRAINTS:" >>${REBUILDTABLESCRIPT}
echo "PROMPT ------------" >>${REBUILDTABLESCRIPT}
echo "" >>${REBUILDTABLESCRIPT}
echo "col CONSTRAINT_NAME for a30" >> ${REBUILDTABLESCRIPT}
echo "col R_CONSTRAINT_NAME for a30" >> ${REBUILDTABLESCRIPT}
echo "select 'ORIGINAL_TABLE: '||TABLE_NAME \"TABLE_NAME\",CONSTRAINT_NAME,CONSTRAINT_TYPE,R_CONSTRAINT_NAME,STATUS,DEFERRED,INDEX_NAME from dba_constraints where">>${REBUILDTABLESCRIPT}
echo "OWNER=upper('${OWNER}') and TABLE_NAME=upper('${OBJECT_NAME_INT}')" >>${REBUILDTABLESCRIPT}
echo "union" >>${REBUILDTABLESCRIPT}
echo "select 'NEW_TABLE: '||TABLE_NAME \"TABLE_NAME\",CONSTRAINT_NAME,CONSTRAINT_TYPE,R_CONSTRAINT_NAME,STATUS,DEFERRED,INDEX_NAME from dba_constraints where">>${REBUILDTABLESCRIPT}
echo "OWNER=upper('${OWNER}') and TABLE_NAME=upper('${OBJECT_NAME}')" >>${REBUILDTABLESCRIPT}
echo "order by 1 desc,CONSTRAINT_TYPE;" >>${REBUILDTABLESCRIPT}
echo "PROMPT" >>${REBUILDTABLESCRIPT}
echo "PROMPT [Checking the ERROR Log ...]" >>${REBUILDTABLESCRIPT}
echo "select object_name, ddl_txt from DBA_REDEFINITION_ERRORS where base_table_name=upper('${OBJECT_NAME}');" >>${REBUILDTABLESCRIPT}
echo "" >>${REBUILDTABLESCRIPT}
echo "${HASHSWAP} PROMPT" >>${REBUILDTABLESCRIPT}
echo "${HASHSWAP} PROMPT ***********************" >>${REBUILDTABLESCRIPT}
echo "" >>${REBUILDTABLESCRIPT}
echo "${HASHSWAP} PROMPT Running the ACTUAL SWAP [FINAL STAGE]:" >>${REBUILDTABLESCRIPT}
echo "${HASHSWAP} PROMPT ***********************" >>${REBUILDTABLESCRIPT}
echo "${HASHSWAP} PROMPT [AN ORA-NOTIFICATION MESSAGE WILL BE WRITTEN TO THE ALERTLOG ONEC THE SWAP IS COMPLETED.]" >>${REBUILDTABLESCRIPT}
echo "" >>${REBUILDTABLESCRIPT}
echo "PROMPT" >>${REBUILDTABLESCRIPT}
echo "${HASHSWAP} PROMPT EXEC DBMS_REDEFINITION.finish_redef_table(upper('${OWNER}'), upper('${OBJECT_NAME}'), upper('${OBJECT_NAME_INT}'));;">>${REBUILDTABLESCRIPT}
echo "" >>${REBUILDTABLESCRIPT}
echo "${HASHSWAP} SET TIMING ON" >>${REBUILDTABLESCRIPT}
echo "${HASHSWAP} EXEC DBMS_REDEFINITION.finish_redef_table(upper('${OWNER}'), upper('${OBJECT_NAME}'), upper('${OBJECT_NAME_INT}'));" >>${REBUILDTABLESCRIPT}
echo "${HASHSWAP} SET TIMING OFF" >>${REBUILDTABLESCRIPT}
echo "${HASHSWAP} SET FEEDBACK OFF" >>${REBUILDTABLESCRIPT}
echo "${HASHSWAP} exec dbms_system.ksdwrt(3,'ORA-NOTIFICATION: THE REBUILD OF TABLE [${OWNER}.${OBJECT_NAME}] USING DBMS_REDEFINITION COMPLETED.');">>${REBUILDTABLESCRIPT}
echo "${HASHSWAP} SET FEEDBACK On" >>${REBUILDTABLESCRIPT}
echo "" >>${REBUILDTABLESCRIPT}
echo "PROMPT" >>${REBUILDTABLESCRIPT}
echo "${HASHSWAP} PROMPT [THE SWAP BETWEEN THE INTERIM AND ACTUAL TABLE IS COMPLETED.]" >>${REBUILDTABLESCRIPT}
echo "${HASHSWAP} exec dbms_system.ksdwrt(3,'ORA-NOTIFICATION: ALL REBUILD STEPS OF TABLE [${OWNER}.${OBJECT_NAME}] COMPLETED. PLEASE CHECK THE LOGFILE: [${REBUILDTABLESPOOL}]');" >>${REBUILDTABLESCRIPT}
echo "" >>${REBUILDTABLESCRIPT}
echo "PROMPT" >>${REBUILDTABLESCRIPT}
echo "" >>${REBUILDTABLESCRIPT}
echo "${HASHNOTIFY} PROMPT ALL REBUILD STEPS ARE COMPLETED EXCEPT THE ACTUAL SWAP BETWEEN THE ORIGINAL AND INTERIM TABLE." >>${REBUILDTABLESCRIPT}
echo "${HASHNOTIFY} PROMPT" >>${REBUILDTABLESCRIPT}
echo "${HASHNOTIFY} PROMPT **************************************************" >>${REBUILDTABLESCRIPT}
echo "" >>${REBUILDTABLESCRIPT}
echo "${HASHNOTIFY} PROMPT EXECUTE THE FOLLOWING ACTUAL SWAP COMMAND MANUALLY: [Minimal DOWNTIME will happen]" >>${REBUILDTABLESCRIPT}
echo "${HASHNOTIFY} PROMPT **************************************************" >>${REBUILDTABLESCRIPT}
echo "" >>${REBUILDTABLESCRIPT}
echo "${HASHNOTIFY} PROMPT EXEC DBMS_REDEFINITION.finish_redef_table(upper('${OWNER}'), upper('${OBJECT_NAME}'), upper('${OBJECT_NAME_INT}'));;">>${REBUILDTABLESCRIPT}
echo "${HASHNOTIFY} PROMPT" >>${REBUILDTABLESCRIPT}
echo "${HASHNOTIFY} exec dbms_system.ksdwrt(3,'ORA-NOTIFICATION: ALL PRE-STEPS OF REBUILDING TABLE [${OWNER}.${OBJECT_NAME}] ARE COMPLETED PLEASE RUN THE FINAL SWAP COMMAND ASAP TO FINALISE THE REBUILD OPERATION: EXEC DBMS_REDEFINITION.finish_redef_table(upper(''${OWNER}''), upper(''${OBJECT_NAME}''), upper(''${OBJECT_NAME_INT}''));');" >>${REBUILDTABLESCRIPT}
echo "${HASHNOTIFY} PROMPT" >>${REBUILDTABLESCRIPT}
echo "${HASHNOTIFY} PROMPT [Note: It's recommended to GATHER NEW STATISTICS on the table after the execution of the above SWAP command.]">>${REBUILDTABLESCRIPT}
echo "${HASHNOTIFY} PROMPT" >>${REBUILDTABLESCRIPT}
echo "${HASHNOTIFY} PROMPT BEGIN" >>${REBUILDTABLESCRIPT}
echo "${HASHNOTIFY} PROMPT DBMS_STATS.GATHER_TABLE_STATS (" >>${REBUILDTABLESCRIPT}
echo "${HASHNOTIFY} PROMPT ownname => upper('${OWNER}')," >>${REBUILDTABLESCRIPT}
echo "${HASHNOTIFY} PROMPT TABNAME => upper('${OBJECT_NAME}')," >>${REBUILDTABLESCRIPT}
echo "${HASHNOTIFY} PROMPT DEGREE => DBMS_STATS.AUTO_DEGREE," >>${REBUILDTABLESCRIPT}
echo "${HASHNOTIFY} PROMPT CASCADE => TRUE," >>${REBUILDTABLESCRIPT}
echo "${HASHNOTIFY} PROMPT estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);;" >>${REBUILDTABLESCRIPT}
echo "${HASHNOTIFY} PROMPT END;;" >>${REBUILDTABLESCRIPT}
echo "${HASHNOTIFY} PROMPT /" >>${REBUILDTABLESCRIPT}
echo "${HASHNOTIFY} PROMPT" >>${REBUILDTABLESCRIPT}
echo "${HASHSTATS} PROMPT [GATHERING STATISTICS on the NEW TABLE] ..." >>${REBUILDTABLESCRIPT}
echo "" >>${REBUILDTABLESCRIPT}
echo "${HASHSTATS} SET TIMING ON" >>${REBUILDTABLESCRIPT}
echo "${HASHSTATS} BEGIN" >>${REBUILDTABLESCRIPT}
echo "${HASHSTATS} DBMS_STATS.GATHER_TABLE_STATS (" >>${REBUILDTABLESCRIPT}
echo "${HASHSTATS} ownname => upper('${OWNER}')," >>${REBUILDTABLESCRIPT}
echo "${HASHSTATS} TABNAME => upper('${OBJECT_NAME}')," >>${REBUILDTABLESCRIPT}
echo "${HASHSTATS} DEGREE => DBMS_STATS.AUTO_DEGREE," >>${REBUILDTABLESCRIPT}
echo "${HASHSTATS} CASCADE => TRUE," >>${REBUILDTABLESCRIPT}
echo "${HASHSTATS} estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);" >>${REBUILDTABLESCRIPT}
echo "${HASHSTATS} END;" >>${REBUILDTABLESCRIPT}
echo "${HASHSTATS} /" >>${REBUILDTABLESCRIPT}
echo "${HASHSTATS} SET TIMING OFF" >>${REBUILDTABLESCRIPT}
echo "${HASHSTATS} PROMPT" >>${REBUILDTABLESCRIPT}
echo "" >>${REBUILDTABLESCRIPT}
echo "" >>${REBUILDTABLESCRIPT}
echo "PROMPT FINALLY: PLEASE DOUBLE CHECK THE PROCESS, IF YOU ARE SATISFIED, DROP THE OLD TABLE USING THIS COMMAND:" >>${REBUILDTABLESCRIPT}
echo "PROMPT -------- --------------------------------------------------------------------------------------------" >>${REBUILDTABLESCRIPT}
echo "" >>${REBUILDTABLESCRIPT}
echo "PROMPT [DROP TABLE ${OWNER}.${OBJECT_NAME_INT} CASCADE CONSTRAINTS PURGE;]">>${REBUILDTABLESCRIPT}
echo "" >>${REBUILDTABLESCRIPT}
echo "PROMPT" >>${REBUILDTABLESCRIPT}
echo "PROMPT [SCRIPT COMPLETED.]" >>${REBUILDTABLESCRIPT}
echo "PROMPT" >>${REBUILDTABLESCRIPT}
echo "PROMPT SCRIPT: [${REBUILDTABLESCRIPT}]" >>${REBUILDTABLESCRIPT}
echo "PROMPT LOGFILE: [${REBUILDTABLESPOOL}]" >>${REBUILDTABLESCRIPT}
echo "PROMPT" >>${REBUILDTABLESCRIPT}
echo "EXIT" >>${REBUILDTABLESCRIPT}
echo "#!/bin/bash" > ${REBUILDTABLESCRIPTRUNNER}
echo "nohup ${ORACLE_HOME}/bin/sqlplus \"/ as sysdba\" @${REBUILDTABLESCRIPT} | tee ${REBUILDTABLESPOOL} 2>&1 &" >>${REBUILDTABLESCRIPTRUNNER}
chmod 740 ${REBUILDTABLESCRIPTRUNNER}
echo ""
echo -e "\033[33;5mFeel free to EXIT from this session as the REBUILD operation is running in the BACKGROUND.]\033[0m"
echo ""
source ${REBUILDTABLESCRIPTRUNNER}
exit 1 ;;
esac
break ;;
n|N|no|NO|No) echo "Moving to Other REBUILD Options..."; break ;;
*) echo "Please enter a VALID answer [Y|N]" ;;
esac
done
;;
*object*) echo "The INTERIM TABLE [${OWNER}.${OBJECT_NAME_INT}] is already exist!";echo "Please check and run the script again.";echo "Script Terminated!"; exit 1;;
*) echo "";echo -e "\033[33;5mDBMS_REDEIFINITION cannot be used: The INTERIM table [${OWNER}.${OBJECT_NAME_INT}] cannot be created! Moving to 'ALTER TABLE MOVE' option ...\033[0m";;
esac
else
echo ""
echo -e "\033[33;5mDBMS_REDEIFINITION is NOT AVAILABLE in this Database Edition!\033[0m"
fi
# ########################################
# SQLPLUS: TABLE REBUILD:
# ########################################
echo ""
echo "Exploring \"ALTER TABLE MOVE\" Option ..."
case ${ORACLE12_ONLINE} in
"ONLINE UPDATE INDEXES") echo -e "\033[33;5mVoila! The Database Version & Edition Support The Online Table Rebuild [Minimal Downtime On The Table!]\033[0m"
echo ""
echo "Entering ALTER TABLE MOVE ONLINE Mode ..."
echo "";;
*) echo ""
echo "ALTER TABLE MOVE ONLINE is NOT available in this DB Edition!"
echo ""
printf "`echo "Entering ALTER TABLE MOVE ["` `echo -e "\033[33;5mLegacy Mode\033[0m"` `echo "] ..."`\n"
echo "";;
esac
# Checking if the table has any of NON SUPPORTED data types for ALTER TABLE MOVE operation:
VALDATATYPENOSUPPORTRAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
set pages 0 feedback off lines 1000;
prompt
select count(*) from dba_tab_columns where owner=upper('${OWNER}') and table_name=upper('${OBJECT_NAME}') and DATA_TYPE in ('LONG','LONG RAW');
exit;
EOF
)
VALDATATYPENOSUPPORT=`echo ${VALDATATYPENOSUPPORTRAW}|perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'|cut -f1 -d '.'`
if [ ${VALDATATYPENOSUPPORT} -eq 1 ]
then
echo -e "\033[33;5m[Table [${OWNER}.${OBJECT_NAME}] Contains UN-SUPPORTED \"LONG\" DATA TYPE for \"ALTER TABLE MOVE\" operation.]\033[0m"
echo ""
echo "Script Terminated!"
echo ""
exit 1
fi
# Checking DEFAULT LOGGING option:
VALDEFAULTLOGGINGRAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
set pages 0 feedback off lines 1000;
prompt
select LOGGING from DBA_TABLES where owner=upper('${OWNER}') and table_name=upper('${OBJECT_NAME}');
exit;
EOF
)
VALDEFAULTLOGGING=`echo ${VALDEFAULTLOGGINGRAW}|perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'|cut -f1 -d '.'`
case ${VALDEFAULTLOGGING} in
NO) echo "[INFO: LOGGING is NOT enabled on table [${OWNER}.${OBJECT_NAME}]. Preserving the NOLOGGING setting.]";export DISABLELOGGING="--";;
esac
# Checking ORIGINAL PARALLELIESM DEGREE:
VALTABPARALLELDEGREERAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
set pages 0 feedback off lines 1000;
prompt
select DEGREE from DBA_TABLES where owner=upper('${OWNER}') and table_name=upper('${OBJECT_NAME}');
exit;
EOF
)
VALTABPARALLELDEGREE=`echo ${VALTABPARALLELDEGREERAW}|perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'|cut -f1 -d '.'`
if [ ${VALTABPARALLELDEGREE} -gt 1 ]
then
echo "[INFO: TABLE's DEFAULT PARALLELISM DEGREE is: ${VALTABPARALLELDEGREE}]"; export VALTABPARALLELDEGREE
sleep 1
fi
# NEW TABLESPACE SELECTION:
# ########################
echo
echo "Enter the NEW TABLESPACE name if you want to move this table to a different TABLESPACE: [Leave it BLANK if you want to leave it on same TABLESPACE]"
echo "======================================================================================"
while read NEW_TABLESPACE_ANSWER
do
case ${NEW_TABLESPACE_ANSWER} in
"") export TABLESPACE_NAME=''; break;;
*)
TBS_EXIST_RAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set pages 0 feedback off lines 1000;
prompt
select count(*) from dba_tablespaces where tablespace_name=upper('${NEW_TABLESPACE_ANSWER}');
exit;
EOF
)
TBS_EXIST=`echo ${TBS_EXIST_RAW} | awk '{print $NF}'`
case ${TBS_EXIST} in
0) echo; echo "The Provided tablespace is NOT EXIST!."
echo ""
echo "Please Enter a VALID TABLESPACE NAME:"
echo "------------------------------------";;
1)
echo ""
echo "Tablespace exist."
echo ""
echo "Checking if the new tablespace has sufficent space to host the table ..."
echo ""
TBS_FREE_RAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set pages 0 feedback off lines 1000;
prompt
select /*+RULE*/ round(((tablespace_size*${blksize})/(1024*1024)) - ((used_space*${blksize})/(1024*1024)))
from dba_tablespace_usage_metrics where tablespace_name=upper('${NEW_TABLESPACE_ANSWER}');
prompt
exit;
EOF
)
TBS_FREE=`echo ${TBS_FREE_RAW} | awk '{print $NF}'`
if [ ${TBS_FREE} -lt ${TABLESIZEPLUSMARGIN} ]
then
REQUIREDSPACE=$(awk "BEGIN {print ${TABLESIZE} - ${TBS_FREE}}")
echo ""
echo -e "\033[33;5mThe underlying tablespace [${NEW_TABLESPACE_ANSWER}] has NO SUFFICIENT FREE SPACE to cater to this operation!\033[0m"
echo "TABLE + INDEXES SIZE= ${TABLESIZE} MB"
echo "TABLESPACE FREE SPACE= ${TBS_FREE} MB"
echo "You need to add at least ${REQUIREDSPACE} MB of extra space to [${NEW_TABLESPACE_ANSWER}] tablespace and then re-run the rebuild script again."
echo ""
echo "Please Enter a TABLESPACE NAME that has sufficent space:"
echo "-------------------------------------------------------";
else
echo "Tablespace [${NEW_TABLESPACE_ANSWER}] has sufficent free space."
export TABLESPACE_NAME="TABLESPACE ${NEW_TABLESPACE_ANSWER}"
break;
fi
esac
esac
done
# Checking if COMPRESSION option is enabled:
COMPRESSION_OPTION_RAW=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" << EOF
set pages 0 feedback off echo off;
SELECT count(*) from v\$option where parameter='Basic Compression' and value='TRUE';
exit;
EOF
)
COMPRESSION_OPTION=`echo ${COMPRESSION_OPTION_RAW} | awk '{print $NF}'`
if [ ${COMPRESSION_OPTION} -eq 1 ]
then
echo
echo "Do you want to use BASIC COMPRESSION option while rebuilding the table?: [Y|N] N [Compression Will Save much of space]"
echo "========================================================================"
while read COMPRESS_ANSWER
do
case ${COMPRESS_ANSWER} in
y|Y|yes|YES|Yes)export COMPRESS="COMPRESS"; break;;
""|n|N|no|NO|No)export COMPRESS=""; break;;
*) echo "Please enter a VALID answer [Y|N]" ;;
esac
done
else
echo ""
echo -e "\033[33;5mBASIC COMPRESSION option is NOT available in this Database Edition!\033[0m"
echo ""
fi
# #############################################
# SETTING PCT_FREE FOR TABLE AFTER THE REBUILD:
# #############################################
VALDEFAULTPCTFREE=$(${ORACLE_HOME}/bin/sqlplus -S "/ as sysdba" <<EOF
set pages 0 feedback off lines 1000;
prompt
select PCT_FREE from DBA_TABLES where owner=upper('${OWNER}') and table_name=upper('${OBJECT_NAME}');
exit;
EOF
)
DEFAULTPCTFREE=`echo ${VALDEFAULTPCTFREE}|perl -lpe'$_ = reverse' |awk '{print $1}'|perl -lpe'$_ = reverse'|cut -f1 -d '.'`
echo ""
echo "INFO: Current PCTFREE: ${DEFAULTPCTFREE}"
echo "INFO: Number of \"UPDATE\" transactions on the table since the last Statistics Gather on [${LASTANALYZED}]: ${TABUPDATES}"
echo
echo
echo "Specify the PCTFREE for the New Table: [How much %Free space will be left in each block for future updates | Current ${DEFAULTPCTFREE}%]"
echo "====================================="
echo "Note: The SMALLER the PCTFREE the SMALLER the table size after the rebuild. [Recommended for Archival/Datawarehouse Tables]"
echo "Note: If the table is highly updated it's recommended to keep the current PCTFREE: ${DEFAULTPCTFREE}"
echo "Leave it BLANK and hit Enter to keep the default PCTFREE."
while read TABPCTFREEVAL
do
case ${TABPCTFREEVAL} in
"") export TABPCTFREE=${DEFAULTPCTFREE}
break;;
*[!0-9]*) echo "Please enter a valid NUMBER:";;
*) export TABPCTFREE=${TABPCTFREEVAL}
break;;
esac
done
echo
echo "Specify the PCTFREE for the Table's Indexes: [How much %Free space will be left in each block for future updates]"
echo "==========================================="
echo "Note: The SMALLER the PCTFREE the SMALLER the index size after the rebuild."
echo "Note: If the table is highly updated it's recommended to keep the current PCTFREE: ${DEFAULTPCTFREE}"
echo "Leave it BLANK and hit Enter to accept the default PCTFREE=10"
while read IDXPCTFREEVAL
do
case ${IDXPCTFREEVAL} in
"") export IDXPCTFREE=10
break;;
*[!0-9]*) echo "Please enter a valid NUMBER:";;
*) export IDXPCTFREE=${IDXPCTFREEVAL}
break;;
esac
done
# INFO AND REBUILD PROCEDURE:
# ##########################
REBUILDTABLESCRIPT=${USR_ORA_HOME}/REBUILDTABLESCRIPT-${OWNER}.${OBJECT_NAME}.sql
REBUILDINDEXSCRIPT=${USR_ORA_HOME}/REBUILDINDEXSCRIPT-${OWNER}.${OBJECT_NAME}.sql
REBUILDTABLESCRIPTRUNNER=${USR_ORA_HOME}/REBUILDTABLESCRIPT-${OWNER}.${OBJECT_NAME}.sh
REBUILDTABLESPOOL=${USR_ORA_HOME}/REBUILDTABLESCRIPT-${OWNER}.${OBJECT_NAME}.log
REBUILDINDEXSPOOL=${USR_ORA_HOME}/REBUILDINDEXSCRIPT-${OWNER}.${OBJECT_NAME}.log
echo "spool ${REBUILDTABLESPOOL}" > ${REBUILDTABLESCRIPT}
echo "PROMPT THIS OPERATION IS LOGGED IN: [${REBUILDTABLESPOOL}]" >> ${REBUILDTABLESCRIPT}
echo "PROMPT" >> ${REBUILDTABLESCRIPT}
echo "${ORACLE12_HASH} PROMPT ********************************************************************************">> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "${ORACLE12_HASH} PROMPT It's HIGHLY RECOMMENDED to run this script within a DOWNTIME WINDOW, ">> ${REBUILDTABLESCRIPT}
echo "${ORACLE12_HASH} PROMPT To AVOID INTERRUPTING long running queries against the table during the rebuild.">> ${REBUILDTABLESCRIPT}
echo "${ORACLE12_HASH} PROMPT ********************************************************************************">> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "${ORACLE12_HASH} exec dbms_lock.sleep(9);" >> ${REBUILDTABLESCRIPT}
echo "set pages 0 feedback off lines 168" >> ${REBUILDTABLESCRIPT}
echo "EXEC DBMS_SESSION.set_identifier('REBUILDING_${OWNER}.${OBJECT_NAME}');" >> ${REBUILDTABLESCRIPT}
echo "PROMPT ***************" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "PROMPT Session Details:" >> ${REBUILDTABLESCRIPT}
echo "PROMPT ***************" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "select 'OSPID: '||p.spid from v\$session s, v\$process p where s.sid=(select sid from v\$mystat where rownum=1) and s.paddr=p.addr;" >> ${REBUILDTABLESCRIPT}
echo "select 'SID: '||sid from v\$mystat where rownum = 1;" >> ${REBUILDTABLESCRIPT}
echo "select 'Serial#: '||s.serial# from v\$session s where s.sid = (select sid from v\$mystat where rownum = 1);" >> ${REBUILDTABLESCRIPT}
echo "PROMPT" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "PROMPT You can KILL this session using this command:" >> ${REBUILDTABLESCRIPT}
echo "PROMPT ********************************************" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "select 'ALTER SYSTEM DISCONNECT SESSION '''||sid||','||serial#||''' IMMEDIATE;' from v\$session where sid = (select sid from v\$mystat where rownum = 1);" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "PROMPT" >> ${REBUILDTABLESCRIPT}
echo "PROMPT" >> ${REBUILDTABLESCRIPT}
echo "set feedback on" >> ${REBUILDTABLESCRIPT}
echo "PROMPT TABLE INFO BEFORE THE REBUILD:" >> ${REBUILDTABLESCRIPT}
echo "PROMPT *****************************" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "set linesize 168 pages 1000 heading on" >> ${REBUILDTABLESCRIPT}
echo "col \"OWNER.TABLE\" for a35" >> ${REBUILDTABLESCRIPT}
echo "col tablespace_name for a20" >> ${REBUILDTABLESCRIPT}
echo "col \"READONLY\" for a8" >> ${REBUILDTABLESCRIPT}
echo "col \"%RECLAIM\" for 999" >> ${REBUILDTABLESCRIPT}
echo "col LAST_ANALYZED for a13" >> ${REBUILDTABLESCRIPT}
echo "col COMPRESSION for a8" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "select /*+RULE*/ t.owner||'.'||t.table_name \"OWNER.TABLE\",t.TABLESPACE_NAME,d.extents,t.logging," >> ${REBUILDTABLESCRIPT}
echo "t.COMPRESSION,t.READ_ONLY \"READONLY\",o.created,to_char(t.LAST_ANALYZED, 'DD-MON-YYYY') LAST_ANALYZED" >> ${REBUILDTABLESCRIPT}
echo ",round(d.bytes/1025/1024) SIZE_MB," >> ${REBUILDTABLESCRIPT}
echo "round((t.blocks * ${blksize}/1024/1024), 2) - round((t.num_rows * t.avg_row_len/1024/1024), 2) \"FRAGMENTED_SPACE_MB\"," >> ${REBUILDTABLESCRIPT}
echo "((round((t.blocks * ${blksize}/1024/1024), 2) - round((t.num_rows * t.avg_row_len/1024/1024), 2)) /" >> ${REBUILDTABLESCRIPT}
echo "round((t.blocks * ${blksize}/1024/1024), 2)) * 100 \"%RECLAIM\"" >> ${REBUILDTABLESCRIPT}
echo "from dba_tables t, dba_objects o, dba_segments d" >> ${REBUILDTABLESCRIPT}
echo "where t.owner= upper('${OWNER}')" >> ${REBUILDTABLESCRIPT}
echo "and t.table_name = upper('${OBJECT_NAME}')" >> ${REBUILDTABLESCRIPT}
echo "and o.owner=t.owner" >> ${REBUILDTABLESCRIPT}
echo "and o.object_name=t.table_name" >> ${REBUILDTABLESCRIPT}
echo "and o.owner=d.owner" >> ${REBUILDTABLESCRIPT}
echo "and t.table_name=d.SEGMENT_NAME;" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "PROMPT LOBS:" >> ${REBUILDTABLESCRIPT}
echo "PROMPT -----" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "col SEGMENT_NAME for a35" >> ${REBUILDTABLESCRIPT}
echo "col INDEX_NAME for a35" >> ${REBUILDTABLESCRIPT}
echo "col COMPRESSION for a8" >> ${REBUILDTABLESCRIPT}
echo "col SECUREFILE for a10" >> ${REBUILDTABLESCRIPT}
echo "col COLUMN_NAME for a35" >> ${REBUILDTABLESCRIPT}
echo "select SEGMENT_NAME,COLUMN_NAME,TABLESPACE_NAME,INDEX_NAME,SECUREFILE,COMPRESSION from dba_lobs" >> ${REBUILDTABLESCRIPT}
echo "where owner=upper('${OWNER}') and table_name = upper('${OBJECT_NAME}');" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "set feedback on" >> ${REBUILDTABLESCRIPT}
echo "PROMPT INDEXES BEFORE THE REBUILD:" >> ${REBUILDTABLESCRIPT}
echo "PROMPT ---------------------------" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "COLUMN OWNER FORMAT A25 heading \"Index Owner\"" >> ${REBUILDTABLESCRIPT}
echo "COLUMN INDEX_NAME FORMAT A30 heading \"Index Name\"" >> ${REBUILDTABLESCRIPT}
echo "COLUMN COLUMN_NAME FORMAT A25 heading \"On Column\"" >> ${REBUILDTABLESCRIPT}
echo "COLUMN COLUMN_POSITION FORMAT 9999 heading \"Pos\"" >> ${REBUILDTABLESCRIPT}
echo "COLUMN \"INDEX\" FORMAT A35" >> ${REBUILDTABLESCRIPT}
echo "COLUMN INDEX_TYPE FOR A16" >> ${REBUILDTABLESCRIPT}
echo "SELECT /*+RULE*/ IND.OWNER||'.'||IND.INDEX_NAME \"INDEX\"," >> ${REBUILDTABLESCRIPT}
echo " IND.INDEX_TYPE," >> ${REBUILDTABLESCRIPT}
echo " COL.COLUMN_NAME," >> ${REBUILDTABLESCRIPT}
echo " COL.COLUMN_POSITION," >> ${REBUILDTABLESCRIPT}
echo " IND.TABLESPACE_NAME," >> ${REBUILDTABLESCRIPT}
echo " IND.STATUS," >> ${REBUILDTABLESCRIPT}
echo " IND.UNIQUENESS," >> ${REBUILDTABLESCRIPT}
echo " IND.LOGGING," >> ${REBUILDTABLESCRIPT}
echo " IND.LAST_ANALYZED,round(d.bytes/1024/1024) SIZE_MB" >> ${REBUILDTABLESCRIPT}
echo "FROM SYS.DBA_INDEXES IND," >> ${REBUILDTABLESCRIPT}
echo " SYS.DBA_IND_COLUMNS COL," >> ${REBUILDTABLESCRIPT}
echo " DBA_SEGMENTS d" >> ${REBUILDTABLESCRIPT}
echo "WHERE IND.TABLE_NAME = upper('${OBJECT_NAME}')" >> ${REBUILDTABLESCRIPT}
echo "AND IND.TABLE_OWNER = upper('${OWNER}')" >> ${REBUILDTABLESCRIPT}
echo "AND IND.TABLE_NAME = COL.TABLE_NAME" >> ${REBUILDTABLESCRIPT}
echo "AND IND.OWNER = d.OWNER(+)" >> ${REBUILDTABLESCRIPT}
echo "AND IND.OWNER = COL.INDEX_OWNER" >> ${REBUILDTABLESCRIPT}
echo "AND IND.TABLE_OWNER = COL.TABLE_OWNER" >> ${REBUILDTABLESCRIPT}
echo "AND IND.INDEX_NAME = COL.INDEX_NAME" >> ${REBUILDTABLESCRIPT}
echo "AND IND.INDEX_NAME = d.SEGMENT_NAME(+);" >> ${REBUILDTABLESCRIPT}
echo "PROMPT" >> ${REBUILDTABLESCRIPT}
echo "PROMPT CONSTRAINTS BEFORE THE REBUILD:" >> ${REBUILDTABLESCRIPT}
echo "PROMPT -------------------------------" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "col CONSTRAINT_NAME for a30" >> ${REBUILDTABLESCRIPT}
echo "col R_CONSTRAINT_NAME for a30" >> ${REBUILDTABLESCRIPT}
echo "select CONSTRAINT_NAME,CONSTRAINT_TYPE,R_CONSTRAINT_NAME,STATUS,DEFERRED,INDEX_NAME from dba_constraints where">> ${REBUILDTABLESCRIPT}
echo "OWNER=upper('${OWNER}') and TABLE_NAME=upper('${OBJECT_NAME}') order by CONSTRAINT_TYPE;" >> ${REBUILDTABLESCRIPT}
echo "PROMPT" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "PROMPT [Setting the PARALLELISM FACTOR to ${PARALLEL_DEGREE}] ..." >> ${REBUILDTABLESCRIPT}
echo "alter session enable parallel dml;" >> ${REBUILDTABLESCRIPT}
echo "alter session enable parallel ddl;" >> ${REBUILDTABLESCRIPT}
echo "alter session enable parallel query;" >> ${REBUILDTABLESCRIPT}
echo "alter session force parallel dml parallel ${PARALLEL_DEGREE};" >> ${REBUILDTABLESCRIPT}
echo "alter session force parallel ddl parallel ${PARALLEL_DEGREE};" >> ${REBUILDTABLESCRIPT}
echo "alter session force parallel query parallel ${PARALLEL_DEGREE};" >> ${REBUILDTABLESCRIPT}
echo "PROMPT [Setting the SESSION DDL WAIT FOR LOCK to 5 minutes] ..." >> ${REBUILDTABLESCRIPT}
echo "alter session set ddl_lock_timeout=300;" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "PROMPT [Setting the SESSION RESUMABLE TIMEOUT to 6 Hours] ..." >> ${REBUILDTABLESCRIPT}
echo "ALTER SESSION ENABLE RESUMABLE TIMEOUT 21600 NAME 'REBUILD_OF_TABLE_${OBJECT_NAME}';" >> ${REBUILDTABLESCRIPT}
echo "PROMPT" >> ${REBUILDTABLESCRIPT}
echo "PROMPT [Start REBUILDING the table using: ALTER TABLE MOVE ${ORACLE12_ONLINE} command] ..." >> ${REBUILDTABLESCRIPT}
echo "PROMPT" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "PROMPT" >> ${REBUILDTABLESCRIPT}
echo "PROMPT TABLE REBUILD PROCEDURE WILL START WITHIN 5 Seconds ..." >> ${REBUILDTABLESCRIPT}
echo "PROMPT ---------------------------------------------------" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "SET FEEDBACK OFF" >> ${REBUILDTABLESCRIPT}
echo "exec dbms_lock.sleep(4);" >> ${REBUILDTABLESCRIPT}
echo "PROMPT [5]" >> ${REBUILDTABLESCRIPT}
echo "exec dbms_lock.sleep(1);" >> ${REBUILDTABLESCRIPT}
echo "PROMPT [4]" >> ${REBUILDTABLESCRIPT}
echo "exec dbms_lock.sleep(1);" >> ${REBUILDTABLESCRIPT}
echo "PROMPT [3]" >> ${REBUILDTABLESCRIPT}
echo "exec dbms_lock.sleep(1);" >> ${REBUILDTABLESCRIPT}
echo "PROMPT [2]" >> ${REBUILDTABLESCRIPT}
echo "exec dbms_lock.sleep(1);" >> ${REBUILDTABLESCRIPT}
echo "PROMPT [1]" >> ${REBUILDTABLESCRIPT}
echo "exec dbms_lock.sleep(1);" >> ${REBUILDTABLESCRIPT}
echo "--Exit the script if any failure encountered at any stage:" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "set serveroutput on" >> ${REBUILDTABLESCRIPT}
echo "WHENEVER SQLERROR EXIT SQL.SQLCODE;" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "PROMPT [START REBUILDING TABLE [${OWNER}.${OBJECT_NAME}]] ..." >> ${REBUILDTABLESCRIPT}
echo "PROMPT" >> ${REBUILDTABLESCRIPT}
echo "PROMPT [SETTING TABLE [${OWNER}.${OBJECT_NAME}] IN NOLOGGING MODE] ..." >> ${REBUILDTABLESCRIPT}
echo "ALTER TABLE ${OWNER}.${OBJECT_NAME} NOLOGGING;" >> ${REBUILDTABLESCRIPT}
echo "PROMPT" >> ${REBUILDTABLESCRIPT}
echo "PROMPT [REBUILDING TABLE [${OWNER}.${OBJECT_NAME}] [This may take quite long depending on the table size] ..." >> ${REBUILDTABLESCRIPT}
echo "SET FEEDBACK ON" >> ${REBUILDTABLESCRIPT}
echo "PROMPT" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "PROMPT Keep checking the progress using this statement:" >>${REBUILDTABLESCRIPT}
echo "PROMPT -----------------------------------------------" >>${REBUILDTABLESCRIPT}
echo "" >>${REBUILDTABLESCRIPT}
echo "PROMPT col OPERATION for a21" >>${REBUILDTABLESCRIPT}
echo "PROMPT col \"%DONE\" for 999.999" >>${REBUILDTABLESCRIPT}
echo "PROMPT col \"STARTED|MIN_ELAPSED|REMAIN\" for a26" >>${REBUILDTABLESCRIPT}
echo "PROMPT col MESSAGE for a90" >>${REBUILDTABLESCRIPT}
echo "PROMPT col \"USERNAME| SID,SERIAL#\" for a28" >>${REBUILDTABLESCRIPT}
echo "PROMPT select USERNAME||'| '||SID||','||SERIAL# \"USERNAME| SID,SERIAL#\",SQL_ID,round(SOFAR/TOTALWORK*100,2) \"%DONE\"" >>${REBUILDTABLESCRIPT}
echo "PROMPT ,to_char(START_TIME,'DD-Mon HH24:MI')||'| '||trunc(ELAPSED_SECONDS/60)||'|'||trunc(TIME_REMAINING/60) \"STARTED|MIN_ELAPSED|REMAIN\" ,MESSAGE" >>${REBUILDTABLESCRIPT}
echo "PROMPT from v\$session_longops" >>${REBUILDTABLESCRIPT}
echo "PROMPT where TARGET LIKE UPPER('%${OBJECT_NAME}%') and TOTALWORK <> '0' and SOFAR/TOTALWORK*100 <>'100'" >>${REBUILDTABLESCRIPT}
echo "PROMPT order by \"STARTED|MIN_ELAPSED|REMAIN\" desc, \"USERNAME| SID,SERIAL#\";;" >>${REBUILDTABLESCRIPT}
echo "" >>${REBUILDTABLESCRIPT}
echo "PROMPT" >>${REBUILDTABLESCRIPT}
echo "PROMPT EXECUTING: [ALTER TABLE ${OWNER}.${OBJECT_NAME} MOVE ${ORACLE12_ONLINE} ${TABLESPACE_NAME} PARALLEL ${PARALLEL_DEGREE} PCTFREE ${TABPCTFREE} ${COMPRESS};]">> ${REBUILDTABLESCRIPT}
echo "ALTER TABLE ${OWNER}.${OBJECT_NAME} MOVE ${ORACLE12_ONLINE} ${TABLESPACE_NAME} PARALLEL ${PARALLEL_DEGREE} PCTFREE ${TABPCTFREE} ${COMPRESS};">> ${REBUILDTABLESCRIPT}
echo "PROMPT COMPLETED: [ALTER TABLE ${OWNER}.${OBJECT_NAME} MOVE ${ORACLE12_ONLINE} ${TABLESPACE_NAME} PARALLEL ${PARALLEL_DEGREE} PCTFREE ${TABPCTFREE} ${COMPRESS};]" >>${REBUILDTABLESCRIPT}
echo "PROMPT" >>${REBUILDTABLESCRIPT}
echo "${DISABLELOGGING} PROMPT [SETTING TABLE [${OWNER}.${OBJECT_NAME}] IN LOGGING MODE] ..." >> ${REBUILDTABLESCRIPT}
echo "${DISABLELOGGING} ALTER TABLE ${OWNER}.${OBJECT_NAME} LOGGING ;" >> ${REBUILDTABLESCRIPT}
echo "${DISABLELOGGING} PROMPT" >>${REBUILDTABLESCRIPT}
echo "${DISABLEPARALLELISM} PROMPT [SETTING TABLE [${OWNER}.${OBJECT_NAME}] TO ITS ORIGINAL PARALLELISM DEGREE ${VALTABPARALLELDEGREE}] ...">> ${REBUILDTABLESCRIPT}
echo "${DISABLEPARALLELISM} ALTER TABLE ${OWNER}.${OBJECT_NAME} PARALLEL ${VALTABPARALLELDEGREE};" >> ${REBUILDTABLESCRIPT}
echo "" >>${REBUILDTABLESCRIPT}
echo "SET TERMOUT OFF PAGES 1000 LINESIZE 167 HEADING OFF FEEDBACK OFF VERIFY OFF ECHO OFF" >> ${REBUILDTABLESCRIPT}
echo "${ORACLE12_HASH} SPOOL ${REBUILDINDEXSCRIPT}" >> ${REBUILDTABLESCRIPT}
echo "${ORACLE12_HASH} select 'SPOOL ${REBUILDINDEXSPOOL}' from dual;" >> ${REBUILDTABLESCRIPT}
echo "${ORACLE12_HASH} select 'ALTER INDEX '||owner||'.\"'||index_name||'\" REBUILD ${ONLINE_REBUILD} ${TABLESPACE_NAME} PARALLEL ${PARALLEL_DEGREE} PCTFREE ${IDXPCTFREE} ${COMPRESS};'">> ${REBUILDTABLESCRIPT}
echo "${ORACLE12_HASH} from dba_indexes where TABLE_OWNER=upper('${OWNER}') and TABLE_NAME=upper('${OBJECT_NAME}');" >> ${REBUILDTABLESCRIPT}
echo "${ORACLE12_HASH} select 'ALTER INDEX '||owner||'.\"'||index_name||'\" PARALLEL ${VALTABPARALLELDEGREE};'from dba_indexes where TABLE_OWNER=upper('${OWNER}') and TABLE_NAME=upper('${OBJECT_NAME}');" >> ${REBUILDTABLESCRIPT}
echo "${ORACLE12_HASH} select 'spool off' from dual;" >> ${REBUILDTABLESCRIPT}
echo "${ORACLE12_HASH} SPOOL OFF" >>${REBUILDTABLESCRIPT}
echo "SET TERMOUT ON ECHO OFF FEEDBACK ON VERIFY OFF" >> ${REBUILDTABLESCRIPT}
echo "${ORACLE12_HASH} PROMPT" >> ${REBUILDTABLESCRIPT}
echo "${ORACLE12_HASH} PROMPT [REBUILDING INDEXES] ..." >> ${REBUILDTABLESCRIPT}
echo "${ORACLE12_HASH} @${REBUILDINDEXSCRIPT}" >> ${REBUILDTABLESCRIPT}
echo "${ORACLE12_HASH} PROMPT [INDEXES REBUILD COMPLETED.]" >> ${REBUILDTABLESCRIPT}
echo "${ORACLE12_HASH} PROMPT" >> ${REBUILDTABLESCRIPT}
echo "PROMPT [GATHERING STATISTICS ON TABLE [${OWNER}.${OBJECT_NAME}] AND ITS INDEXES] ..." >> ${REBUILDTABLESCRIPT}
echo "PROMPT " >> ${REBUILDTABLESCRIPT}
echo "PROMPT [UNLOCKING TABLE STATISTICS ...]" >> ${REBUILDTABLESCRIPT}
echo "exec dbms_stats.unlock_table_stats('${OWNER}', '${OBJECT_NAME}');" >> ${REBUILDTABLESCRIPT}
echo "PROMPT " >> ${REBUILDTABLESCRIPT}
echo "PROMPT [GATHERING TABLE STATISTICS ...]" >> ${REBUILDTABLESCRIPT}
echo "BEGIN" >> ${REBUILDTABLESCRIPT}
echo "DBMS_STATS.GATHER_TABLE_STATS (" >> ${REBUILDTABLESCRIPT}
echo "ownname => upper('${OWNER}')," >> ${REBUILDTABLESCRIPT}
echo "tabname => upper('${OBJECT_NAME}')," >> ${REBUILDTABLESCRIPT}
echo "cascade => TRUE," >> ${REBUILDTABLESCRIPT}
echo "METHOD_OPT => 'FOR ALL COLUMNS SIZE SKEWONLY'," >> ${REBUILDTABLESCRIPT}
echo "DEGREE => DBMS_STATS.AUTO_DEGREE," >> ${REBUILDTABLESCRIPT}
echo "estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);" >> ${REBUILDTABLESCRIPT}
echo "END;" >> ${REBUILDTABLESCRIPT}
echo "/" >> ${REBUILDTABLESCRIPT}
echo "PROMPT [GATHER STATISTICS COMPLETED.]" >> ${REBUILDTABLESCRIPT}
echo "${HASHNOTIFY} exec dbms_system.ksdwrt(3,'ORA-NOTIFICATION: REBUILD OF TABLE [${OWNER}.${OBJECT_NAME}] COMPLETED. LOGFILE: ${REBUILDTABLESPOOL}');">>${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "set feedback on" >> ${REBUILDTABLESCRIPT}
echo "PROMPT TABLE INFO AFTER THE REBUILD:" >> ${REBUILDTABLESCRIPT}
echo "PROMPT ****************************" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "set linesize 168 pages 1000 heading on" >> ${REBUILDTABLESCRIPT}
echo "col \"OWNER.TABLE\" for a35" >> ${REBUILDTABLESCRIPT}
echo "col tablespace_name for a20" >> ${REBUILDTABLESCRIPT}
echo "col \"READONLY\" for a8" >> ${REBUILDTABLESCRIPT}
echo "col \"%RECLAIM\" for 999" >> ${REBUILDTABLESCRIPT}
echo "col LAST_ANALYZED for a13" >> ${REBUILDTABLESCRIPT}
echo "col PCT_FREE for 99999999" >> ${REBUILDTABLESCRIPT}
echo "col COMPRESSION for a8" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "select /*+RULE*/ t.owner||'.'||t.table_name \"OWNER.TABLE\",t.TABLESPACE_NAME,d.extents,t.PCT_FREE,t.logging" >> ${REBUILDTABLESCRIPT}
echo ",t.COMPRESSION,t.READ_ONLY \"READONLY\",to_char(t.LAST_ANALYZED, 'DD-MON-YYYY') LAST_ANALYZED" >> ${REBUILDTABLESCRIPT}
echo ",round(d.bytes/1025/1024) SIZE_MB," >> ${REBUILDTABLESCRIPT}
echo "round((t.blocks * ${blksize}/1024/1024), 2) - round((t.num_rows * t.avg_row_len/1024/1024), 2) \"FRAGMENTED_SPACE_MB\"," >> ${REBUILDTABLESCRIPT}
echo "((round((t.blocks * ${blksize}/1024/1024), 2) - round((t.num_rows * t.avg_row_len/1024/1024), 2)) / round((t.blocks * ${blksize}/1024/1024), 2)) * 100 \"%RECLAIM\"" >> ${REBUILDTABLESCRIPT}
echo "from dba_tables t, dba_objects o, dba_segments d" >> ${REBUILDTABLESCRIPT}
echo "where t.owner= upper('${OWNER}')" >> ${REBUILDTABLESCRIPT}
echo "and t.table_name = upper('${OBJECT_NAME}')" >> ${REBUILDTABLESCRIPT}
echo "and o.owner=t.owner" >> ${REBUILDTABLESCRIPT}
echo "and o.object_name=t.table_name" >> ${REBUILDTABLESCRIPT}
echo "and o.owner=d.owner" >> ${REBUILDTABLESCRIPT}
echo "and t.table_name=d.SEGMENT_NAME;" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "PROMPT LOBS:" >> ${REBUILDTABLESCRIPT}
echo "PROMPT -----" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "col SEGMENT_NAME for a35" >> ${REBUILDTABLESCRIPT}
echo "col INDEX_NAME for a35" >> ${REBUILDTABLESCRIPT}
echo "col COMPRESSION for a8" >> ${REBUILDTABLESCRIPT}
echo "col SECUREFILE for a10" >> ${REBUILDTABLESCRIPT}
echo "col COLUMN_NAME for a35" >> ${REBUILDTABLESCRIPT}
echo "select SEGMENT_NAME,COLUMN_NAME,TABLESPACE_NAME,INDEX_NAME,SECUREFILE,COMPRESSION from dba_lobs" >> ${REBUILDTABLESCRIPT}
echo "where owner=upper('${OWNER}') and table_name = upper('${OBJECT_NAME}');" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "PROMPT INDEXES AFTER THE REBUILD:" >> ${REBUILDTABLESCRIPT}
echo "PROMPT --------------------------" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "set feedback on" >> ${REBUILDTABLESCRIPT}
echo "COLUMN OWNER FORMAT A25 heading \"Index Owner\"" >> ${REBUILDTABLESCRIPT}
echo "COLUMN INDEX_NAME FORMAT A30 heading \"Index Name\"" >> ${REBUILDTABLESCRIPT}
echo "COLUMN COLUMN_NAME FORMAT A25 heading \"On Column\"" >> ${REBUILDTABLESCRIPT}
echo "COLUMN COLUMN_POSITION FORMAT 999 heading Pos" >> ${REBUILDTABLESCRIPT}
echo "COLUMN INDEX FORMAT A35" >> ${REBUILDTABLESCRIPT}
echo "COLUMN INDEX_TYPE FORMAT A13" >> ${REBUILDTABLESCRIPT}
echo "SELECT /*+RULE*/ IND.OWNER||'.'||IND.INDEX_NAME \"INDEX\"," >> ${REBUILDTABLESCRIPT}
echo " IND.INDEX_TYPE," >> ${REBUILDTABLESCRIPT}
echo " IND.PCT_FREE," >> ${REBUILDTABLESCRIPT}
echo " COL.COLUMN_NAME," >> ${REBUILDTABLESCRIPT}
echo " COL.COLUMN_POSITION," >> ${REBUILDTABLESCRIPT}
echo " IND.TABLESPACE_NAME," >> ${REBUILDTABLESCRIPT}
echo " IND.STATUS," >> ${REBUILDTABLESCRIPT}
echo " IND.UNIQUENESS," >> ${REBUILDTABLESCRIPT}
echo " IND.LOGGING," >> ${REBUILDTABLESCRIPT}
echo " IND.LAST_ANALYZED,round(d.bytes/1024/1024) SIZE_MB" >> ${REBUILDTABLESCRIPT}
echo "FROM SYS.DBA_INDEXES IND," >> ${REBUILDTABLESCRIPT}
echo " SYS.DBA_IND_COLUMNS COL," >> ${REBUILDTABLESCRIPT}
echo " DBA_SEGMENTS d" >> ${REBUILDTABLESCRIPT}
echo "WHERE IND.TABLE_NAME = upper('${OBJECT_NAME}')" >> ${REBUILDTABLESCRIPT}
echo "AND IND.TABLE_OWNER = upper('${OWNER}')" >> ${REBUILDTABLESCRIPT}
echo "AND IND.TABLE_NAME = COL.TABLE_NAME" >> ${REBUILDTABLESCRIPT}
echo "AND IND.OWNER = d.OWNER(+)" >> ${REBUILDTABLESCRIPT}
echo "AND IND.OWNER = COL.INDEX_OWNER" >> ${REBUILDTABLESCRIPT}
echo "AND IND.TABLE_OWNER = COL.TABLE_OWNER" >> ${REBUILDTABLESCRIPT}
echo "AND IND.INDEX_NAME = COL.INDEX_NAME" >> ${REBUILDTABLESCRIPT}
echo "AND IND.INDEX_NAME = d.SEGMENT_NAME(+);" >> ${REBUILDTABLESCRIPT}
echo "PROMPT" >> ${REBUILDTABLESCRIPT}
echo "PROMPT CONSTRAINTS AFTER THE REBUILD:" >> ${REBUILDTABLESCRIPT}
echo "PROMPT ------------------------------" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "col CONSTRAINT_NAME for a30" >> ${REBUILDTABLESCRIPT}
echo "col R_CONSTRAINT_NAME for a30" >> ${REBUILDTABLESCRIPT}
echo "select CONSTRAINT_NAME,CONSTRAINT_TYPE,R_CONSTRAINT_NAME,STATUS,DEFERRED,INDEX_NAME from dba_constraints where">> ${REBUILDTABLESCRIPT}
echo "OWNER=upper('${OWNER}') and TABLE_NAME=upper('${OBJECT_NAME}') order by CONSTRAINT_TYPE;" >> ${REBUILDTABLESCRIPT}
echo "PROMPT" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "PROMPT [TABLE REBUILD OPERATION COMPLETED.]" >> ${REBUILDTABLESCRIPT}
echo "" >> ${REBUILDTABLESCRIPT}
echo "PROMPT" >> ${REBUILDTABLESCRIPT}
echo "PROMPT [SCRIPT COMPLETED.]" >> ${REBUILDTABLESCRIPT}
echo "PROMPT" >> ${REBUILDTABLESCRIPT}
echo "PROMPT SCRIPT: [${REBUILDTABLESCRIPT}]" >> ${REBUILDTABLESCRIPT}
echo "PROMPT LOGFILE: [${REBUILDTABLESPOOL}]" >> ${REBUILDTABLESCRIPT}
echo "PROMPT" >> ${REBUILDTABLESCRIPT}
echo "EXIT" >> ${REBUILDTABLESCRIPT}
echo "#!/bin/bash" > ${REBUILDTABLESCRIPTRUNNER}
echo "nohup ${ORACLE_HOME}/bin/sqlplus \"/ as sysdba\" @${REBUILDTABLESCRIPT} | tee ${REBUILDTABLESPOOL} 2>&1 &" >>${REBUILDTABLESCRIPTRUNNER}
chmod 740 ${REBUILDTABLESCRIPTRUNNER}
echo ""
echo -e "\033[33;5mFeel free to EXIT from this session as the REBUILD operation is running in the BACKGROUND.\033[0m"
echo ""
source ${REBUILDTABLESCRIPTRUNNER}
# #############
# END OF SCRIPT
# #############
# REPORT BUGS to: mahmmoudadel@hotmail.com
# DISCLAIMER: THIS SCRIPT IS DISTRIBUTED IN THE HOPE THAT IT WILL BE USEFUL, BUT WITHOUT ANY WARRANTY. IT IS PROVIDED "AS IS".
# DOWNLOAD THE LATEST VERSION OF DATABASE ADMINISTRATION BUNDLE FROM: http://dba-tips.blogspot.com/2014/02/oracle-database-administration-scripts.html
view raw rebuild_table.sh hosted with ❤ by GitHub
Posted by No comments:

Monday, September 7, 2020

ORA-13831: SQL profile or patch name specified is invalid [On 19c]

 Problem:

As of a sudden one critical application connecting to a 19c database crashed and kept throwing the following error in its logs:

ORA-13831: SQL profile or patch name specified is invalid


Analysis:

A quick search revealed that we're hitting Bug 29942554 

Because I had to get the application up and running quickly I've decided to go with the workaround (and luckily this bug has a workaround). But why I'm writing this post then? Because the workaround is not clear, so I thought of writing this article.


Solution:

1. Searching the Failing SQL Statement details shows that a new plan was created and accepted just few seconds before the application crash, and that would be the reason for triggering this bug:

SQL> SELECT SQL_HANDLE,PLAN_NAME,CREATED,LAST_MODIFIED,FIXED,ADAPTIVE,ENABLED FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE '%SELECT TYP_LF_BOOKED_HELD(FLT.FAR%' ORDER BY CREATED;



 2. Disabling that latest created SQL Plan for the problematic SQL will work around this bug and get the application back running:


SET SERVEROUTPUT ON
DECLARE
  l_plans_altered  PLS_INTEGER;
BEGIN
  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
    sql_handle      => 'SQL_6ef2b0406c073b2e',
    plan_name      => 'SQL_PLAN_6xwph81q0fftfdbdc6b11',
    attribute_name  => 'enabled',
    attribute_value  => 'NO');

  DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
END;
/

Conclusion:

SQL Statements having a Baseline may hit bug 29942554 if a new plan get accepted and added to the same baseline. As a workaround, disable the new accepted plan inside the Baseline.

Posted by No comments:

Friday, September 4, 2020

"ORA-14451: unsupported feature with temporary table" Error When Creating a PRIVATE TEMPORARY TABLE in 19c

Introduction:
Private Temporary Tables (PTT) is a new feature in Oracle 18c, they have the similar characteristics of global temp tables but instead of being created under the TEMPORARY tablespace, they get created in the session's PGA, which is a great feature for boosting the performance (if your PGA is big enough to host this kind of  PTT's).

Problem:

SQL> CREATE PRIVATE TEMPORARY TABLE ORA$PTT_users ON COMMIT PRESERVE DEFINITION  AS SELECT * FROM dba_users;
CREATE PRIVATE TEMPORARY TABLE ORA$PTT_users ON COMMIT PRESERVE DEFINITION  AS SELECT * FROM dba_users
*
ERROR at line 1:
ORA-14451: unsupported feature with temporary table

SQL> sho user
USER is "SYS"

Analysis:


Above misleading error will pop-up if you create a Private Temporary Table (PTT) while connecting as SYS, SYSDBA, SYSRAC or SYSBACKUP.


Solution:


Create the PRIVATE TEMPORARY TABLE (PTT) with any other user not connecting as SYSDBA, SYSRAC, SYSBACKUP,... etc.


Posted by No comments:

Setup AWR on Standby DB

 Starting with Oracle 12.2, AWR can be configured and run against Standby DB, before that release (since 11gR2) the only option available for having similar data was by generating an ASH report on the Standby DB, which was limited by how much data is still available in the standby instance memory.

Here are the briefed steps of how to set up AWR on the standby DB (Steps are the same for both RAC & standalone DB):

[On Primary]: Unlock user sys$umf

SQL> alter user sys$umf identified by "abc#123#ORCL" account unlock;


[On Primary]: Enable "_umf_remote_enabled" parameter:

SQL> alter system set "_umf_remote_enabled"=TRUE scope=BOTH;


[On Primary]: Create two DB links, from primary to standby and vice versa:

SQL> drop database link PR_TO_STBY;
SQL> drop database link STBY_TO_PR;

SQL> create database link PR_TO_STBY CONNECT TO sys$umf IDENTIFIED BY "abc123" using 'ORCL';
SQL> create database link STBY_TO_PR CONNECT TO sys$umf IDENTIFIED BY "abc123" using 'ORCLSTBY';
 

--Make sure the DB Links are working:

select * from dual@PR_TO_STBY;

D
-
X

select * from dual@STBY_TO_PR;

D
-
X
 


[On Primary] Add the primary DB to the new AWR Topology:

SQL> exec dbms_umf.unconfigure_node;
SQL> exec dbms_umf.configure_node ('prim');


[On Standby] Add the Standby DB to the new AWR Topology:

SQL> exec dbms_umf.UNCONFIGURE_NODE;
SQL> exec dbms_umf.configure_node ('stby', 'STBY_TO_PR');

[On Primary]: Create a new Topology:

SQL> exec DBMS_UMF.drop_topology   ('Topology_1');
SQL> exec DBMS_UMF.create_topology ('Topology_1');


[On Primary]: Register the standby database with the topology:


SQL> exec DBMS_UMF.register_node ('Topology_1', 'stby', 'PR_TO_STBY', 'STBY_TO_PR', 'FALSE', 'FALSE');

[On Primary]: Register the Standby DB as a remote DB: [May take from 2-75 seconds]


SQL> exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>'stby');

If you encounter "ORA-13519": Try to unregister and register the DB again:
SQL> exec DBMS_WORKLOAD_REPOSITORY.unregister_remote_database('stby','Topology_1',TRUE);
SQL> exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>'stby');

If encounter "ORA-20509: Service not registered": Run the following commands in sequence:
SQL> exec DBMS_UMF.unregister_node ('Topology_1', 'stby');
SQL> exec DBMS_UMF.register_node ('Topology_1', 'stby', 'PR_TO_STBY', 'STBY_TO_PR', 'FALSE', 'FALSE');
SQL> exec DBMS_WORKLOAD_REPOSITORY.register_remote_database(node_name=>'stby');

[On Primary]: Verify the above steps by running these queries:

SQL>     set line 170
    col topology_name format a15
    col node_name       format a15
    select * from dba_umf_topology;
    select TOPOLOGY_NAME, NODE_NAME, NODE_ID, NODE_TYPE from umf$_registration;
    select * from dba_umf_registration;


[On Primary]: Create a Snapshot on the Standby DB:

SQL> alter system archive log current;
SQL> exec dbms_workload_repository.create_remote_snapshot('stby');

Note: By default one snapshot will be created automatically every one hour on the Standby DB.


[On Primary or Standby]: To generate an AWR report for the standby DB:

SQL> @?/rdbms/admin/awrrpti
When asked for dbid, enter the DBID of the Standby DB (from the list).

 

Change the AWR SNAPSHOT Frequency on the Standby:

Unfortunately, the only way to change the AWR Snapshot frequency is to change the frequency on the Primary, then it will reflect on the Standby!!:

-- Changing the AWR Snapshot Frequency to 30 minutes on both Primary & Standby:

SQL> EXECUTE DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval => 30);

[Up to the date of writing this post; Oracle didn't provide a way to change the SNAPSHOT frequency on Standby without impacting the Snapshot frequency on the Primary]
 

Note: Whenever a SWITCHOVER happen between primary and standby you have to do a similar role switch for AWR settings as well using this command:

SQL> exec DBMS_UMF.SWITCH_DESTINATION(topology_name =>'Topology_1',force_switch=> 'TRUE');

In case the above command failed after the switchover and the snapshots are not getting automatically generated on the new primary DB, try to re-deploy all the above mentioned steps again to re-configuring AWR on the new standby.

References:
https://www.oracle.com/technetwork/database/availability/redo-apply-2745943.pdf

Posted by No comments:
Subscribe to: Posts (Atom)