Export & Import Oracle 10g Tables Using Shell Scripts

#!/usr/bin/bash

Export Table:-

export ORACLE_SID=SNM

export ORACLE_HOME=/opt/app/oracle/product/10.2.0

#su – oracle -c /opt/app/oracle/product/10.2.0/bin/sqlplus ‘/ as sysdba’

echo ‘Enter UserName for DBConnection’

read userName

echo ‘Enter Password for DBConnection’

read password

echo ‘Enter the Export file Name’

read filename

su – oracle -c “

export ORACLE_SID=SNM

#create spfile from pfile

sqlplus / as sysdba <<- EOF

 CREATE OR REPLACE DIRECTORY test_dir AS ‘/tmp’;

 GRANT READ, WRITE ON DIRECTORY test_dir TO $userName;

 disconnect

exit

EOF

“

su – oracle -c “

export ORACLE_SID=SNM

export ORACLE_HOME=/opt/app/oracle/product/10.2.0

/opt/app/oracle/product/10.2.0/bin/expdp $userName/$password tables=table1,table2 directory=test_dir dumpfile=$filename.dmp logfile=expdpbackup.log

“

Import Table:-

#!/usr/bin/bash

export ORACLE_SID=SNM

export ORACLE_HOME=/opt/app/oracle/product/10.2.0

#su – oracle -c /opt/app/oracle/product/10.2.0/bin/sqlplus ‘/ as sysdba’

echo ‘Enter UserName for DBConnection’

read userName

echo ‘Enter Password for DBConnection’

read password

echo ‘Enter the Import file Name(File which has been exported earlier)’

read filename

fslash=’/’

snm=’@snm’

#/opt/app/oracle/product/10.2.0/bin/sqlplus omc_omc111/omc_omc111@snm <

#echo ${userName}${fslash}${password}${snm}

/opt/app/oracle/product/10.2.0/bin/sqlplus ${userName}${fslash}${password}${snm} <

spool /alcatel/databasedump.txt

drop table table1;

drop table table2;

commit ;

spool off;

disconnect

exit

EOF

su – oracle -c “

export ORACLE_SID=SNM

export ORACLE_HOME=/opt/app/oracle/product/10.2.0

/opt/app/oracle/product/10.2.0/bin/impdp $userName/$password tables=table1,table2 directory=test_dir dumpfile=$filename.dmp logfile=expdpbackup.log

disconnect

exit

“