Locking while Exporting

Hi All,
I just want to check the following:
Will a table (e.g., A) be locked (no rows will be inserted) while I am exporting the table A to a file?

Thanks in advance
Best Regards
Omran

No, it will not be locked. EXP never perform any locks.

So how can I lock the table while exporting?

If what you want is for the export not to see any changes committed after the export started, you need the CONSISTENT=Y option set. I believe this does not matter for a single table or table-partition export, it will be consistent anyway

Quote:

---

Originally posted by omran
So how can I lock the table while exporting?

---

I can't imagine why in the world would someone want to lock the table while exporting it, but if you realy need to do it, you simply logon to the database with SQL*Plus (or whatever your favorite tool to interact with Oracle database is) and lock the table using SQL command. Then perform your export in another session and once the export is done, you unlock the table from your first session.

Once again, it would be intesresting to know why would you like to prevent changes to the table while exporting it.

Quote:

---

Originally posted by jmodic

Once again, it would be intesresting to know why would you like to prevent changes to the table while exporting it.

---

HI,
i believe he want a consistent logical backup of his table, so the CONSISTENT -Parameter should be the answer.
ORca
:cool:

Don't you think that Export provides read consistent image of the data. It will give you the data image when the export was started. If you try to do export of big table during busy period you may Snapshot too old error.

Yes, the data is consistent for a singe table (not sure about multiple-partition tables), but the consistent parameter gives consistency across all tables.

Thank you all for your help...
Actually my problem is the following:
Table A will be populated when any row is inserted into table B.
Then each hour table A will be exported and truncated.
I am afraid that in the period from the export to the truncation that some reocrds will be inserted in A and I will loss them. Any help?

Use a count flag which checks for the number of records in the table A just before and after export.
If counts dont match then put the new records in temp table and trancute A.

Copy the data from temp table back to working table for next hours export.

This is one of the solutions.

Regards
Abhay.

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.