Oracle import script for data export and import only

Just a little script to import from an export dump file.
Note that the below script is based on a simple export dump from a list of generated table names. A dump file and a control file, named .dmp and .ctl, will be generated. .ctl will be the list of the generated table names for use in exp

Feel free to copy if you need to use the script or drop me a comment and I try to get back to you if you need help.

Execution command : file.ksh


#!/bin/sh
#

option='f'
export ORACLE_HOME=/software/oracle/product/9.2.0.8
export PATH=${ORACLE_HOME}/bin:${PATH}

while [ $option = 'f' ]
do

option='p'
#prompt and check password
echo 'Enter DBO password'
read passwd

#try to connect to DB
#sqlplus operation silenced by nohup and diverting output to /dev/null
#file test.txt will only be generated if connection is possible
nohup sqlplus -s DBO/$passwd@${ORACLE_SID} <<> /dev/null
spool test.txt
select * from dual ;
spool off
EOF

#checking for text.txt, prompt for correct password again if not present.
if [[ ! -f test.txt ]]
then
echo 'Incorrect password. Please enter password again.'
option='f'
fi
done

rm -f test.txt

#Check dump and control file
#Simple file ops to check for file. If files do not exist, stop operation.
if [[ ! -f $1.dmp ]]
then
echo 'Missing dump file ' $1.dmp
exit ;
fi

if [[ ! -f $1.ctl ]]
then
echo 'Missing control file ' $1.ctl
exit ;
fi

#disable constraints
#doing a sqlplus connection and selecting only relational constraints from the table that are enabled only.
#Choosing enabled only to reduce time in disabling disabled constraints already.
echo 'Disabling Constraints'
nohup sqlplus DBO/$passwd@${ORACLE_SID} <<> /dev/null
spool alter.sql
set pages 1000
SELECT 'alter table '||table_name||' disable novalidate constraint
'||constraint_name||' cascade;'
FROM user_constraints where CONSTRAINT_TYPE='R' and STATUS='ENABLED' ;
spool off
start alter.sql
EOF

rm alter.sql

#delete data
# using the original generated list of table names.
# the format of the list is
# table ={
# , table1
# , table2
# }
#script to extract table names only and to add delete statements
echo 'Generating delete data'
grep , #1.ctl | cut -c 3- > list.txt
echo "Prompt Generate list" > remove.sql
while read table
do
echo "delete from " $table " ;" >> remove.sql
echo "commit ;" >> remove.sql
done < list.txt

echo "Deleting Data"
nohup sqlplus DBO/$passwd@${ORACLE_SID} <<> /dev/null
start remove.sql
EOF

rm remove.sql
rm list.txt

#import data
echo 'Importing starts'
imp DBO/$passwd@${ORACLE_SID} FULL=Y CONSTRAINTS=N INDEXES=N GRANTS=N STATISTICS=NONE IGNORE=Y FILE=$1.dmp LOG=a.log

echo 'Enabling Constraints'
#enable constraints
# same as disabling, only enable disabled relational constraints
nohup sqlplus DBO/$passwd@${ORACLE_SID} <<> /dev/null
spool alter.sql
set pages 1000
SELECT 'alter table '||table_name||' enable novalidate constraint
'||constraint_name||' ;'
FROM user_constraints where CONSTRAINT_TYPE='R' and STATUS='DISABLED' ;
spool off
start alter.sql
EOF

rm alter.sql

echo 'End of importing'