Shell Script to export database tables from oracle


After a long time gap I am back again. This time I was assigned a very interesting task to automate backup and restore functionality on a oracle installed on a linux machine(though I am not a geek using linux as well as oracle :P).

I started to dig around the google for this task and found many webpages which have helped(I really appriciate all the people who gave me the knowledge on this..) to acheive the task successfully.

So here I go:

1> Create a folder where you want your backup folder to be placed. I have created a folder called DBIMPEXP at /home/oracle/

$ cd /home/oracle/DBIMPEXP

2> vi

3> Type all the below into this file.

ORACLE_HOME=/opt/oracle/products/11.1.0/database;export ORACLE_HOME

echo $PATH
SUBJECT="Database export operation performed"

echo "Database or tables have been exported successfully!" >$MSG
echo "This is a system generated message so do not reply to this email!" >>$MSG

NOW=$(date +"%d-%m-%Y-%T")
echo $NOW

exp DBusername/DBPassword@yourSID file=/home/oracle/DBIMPEXP/"Backup_$NOW.dmp" log=/home/oracle/DBIMPEXP/"explog_$NOW.log" tables={SC_LEARNING,SC_NEWS}

/bin/mail -s "$SUBJECT" "$EMAIL" < $MSG

4> To save and close hit Esc and ctrl+zz

5> execute the shell script by sh

6> Now inside the /home/oracle/DBIMPEXP folder you should get the dmp file and the log file.

7> Thats it!!! Be happy always :)