Dynamically Clean up the target Oracle DB

1. This job will call a main script. This script will first call a pre-defiend SQL file by replacing the proper DB USERNAME.

   SPOOL /home/tmp/drop.sql select 'drop table '||owner ||'.'||table_name||' cascade constraint;' as LBL_SCRIPT from all_tables where owner = upper('#_dbuser_#'); select 'drop sequence '||sequence_owner||'.'||sequence_name||';' as LBL_SCRIPT from all_sequences where sequence_owner = upper('#_dbuser_#'); select 'drop PROCEDURE '||object_name||';' as LBL_SCRIPT from ALL_OBJECTS where OWNER = upper('#_dbuser_#') and OBJECT_TYPE IN ('PROCEDURE'); select 'drop FUNCTION '||object_name||';' as LBL_SCRIPT from ALL_OBJECTS where OWNER = upper('#_dbuser_#') and OBJECT_TYPE IN ('FUNCTION'); SPOOL OFF exit

2. The script will then try to select out the table,sequence,procedure and function from the target DB and paste it into a sql file by executing the SQL file in previous step.

   ######################################### #  generate the drop script for existing db ######################################### function generate_drop_script {     echo "generate the drop script"     sqlplus $dbuser/$dbpassword@ORCL @$gropFile     echo "remove unwanted lines int the drop script"     sed -i -e 's/[0-9]*.*row.*//ig' $finalDropFile     sed -i -e 's/LBL_SCRIPT//g' $finalDropFile     echo "append exit command to the drop script"     echo "exit;" >> $finalDropFile }

3. The script will remove those unwanted characters/words from the generated sql file in previous step.
4. Execute the generated sql file to clean up the DB.