Sunday, November 8, 2009

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


0 Comments:

Post a Comment

The Link Exchange - Your ultimate resource for link exchange!

About This Blog

  © Blogger template Webnolia by Ourblogtemplates.com 2009

Back to TOP