Customized Data Backup in Oracle

To take a data backup, there are many options available such as using RMAN, taking a dump using export and export pump utilities. But sometimes you need to backup specific data from specific tables at certain intervals. Here is an example of how to do this. I have done this on XE. This post also gives an idea of using XE in commercial environments by managing the data size restrictions of oracle XE. In this example, we backup data which is more than 45 days old from a particular table.
First we create a directory object that refers to the location where the dump files will be stored. Also, grant appropriate access rights to the directory object.


Next, we define the procedure which does the data backup.


now that we have the procedure ready, we will create and schedule a job that will call this procedure at an interval defined by us.
create the job as shown in the following code.

This job will run everyday at 2 am. It will backup data which is 45 days old from the table MY_TABLE01. You can access the DMP files at the location mentioned in the directory object. Just make sure you have read/write access to the directory on the linux filesystem.
If you want to backup data from multiple tables, then either repeat the backup code in the procedure for another table using a new job handle, if there is no common relation between the data or else use the same handle code to list additional tables provided the same filter query can be applied to all the tables.