-
a) A simple and straightforward solution:
Have a cron job (or whatever OS scheduler you are using) that runs SQL*Plus script every hour. in that script you'll first lock the table, then callout the exp utility to export the table, and finaly you truncate it. So your SQL script will be something like:
Code:
REM Lock the table
LOCK TABLE my_table;
REM Now that a table is locked export it
HOST exp UN/PW file=blahblah.dmp tables=my_table
REM Once export is complete, you can truncate the table
REM and unlock it implicitely
TRUNCATE TABLE my_table;
EXIT
The downside of this aproach is that it is clumsy and that any DML's to your table are prevented while the table is being exported, which might be much too serious limitation for a real life system.
b) More elegant solution:
Modify your table to be a partitioned table, consisting of two partitions. Define the two partitions so that on every odd hour the records will be automaticaly inserted into partition 1 and on every even hour records will go into partition 2. Now each hour you have 60 minutes to perform your export and truncate of the "quiet" partition, knowing that during that time aall new records will be inserted in the other partition. Of course this correctly handles only new inserts, but not the updates and deletes.
The upside of this aproach is that new raecords can still be inserted into your table while you are performing your export and truncate the partition.
-
To overcome all the performance issues....i gotta a idea....
U jest need 2 extra temp_table and creation of 1 function/trigger.
u say that when Table B is insterted/updated record changes happen in table A right.....Write i more trigger which will insert/update record in Temp_Table as well
Before u start export Drop/Disabe the trigger which populates temp_table...
After Export Recreate/enable the same trigger....
Compare the data of temp_table and table A....if its consistent, then Truncate table A....
Else put the new/modified record in another temp_table1.
Now truncate Table A
use the temp_table1's data back into table A after truncate....
Now truncate both temp tables.
Hope this is better idea.
Abhay.
