Locking while Exporting
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Locking while Exporting

  1. #1
    Join Date
    Aug 2002
    Posts
    27

    Question 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

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    No, it will not be locked. EXP never perform any locks.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Aug 2002
    Posts
    27
    So how can I lock the table while exporting?
    Mohammed Omran, PhD

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Apr 2002
    Location
    Germany.Laudenbach
    Posts
    448
    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

  7. #7
    Join Date
    Nov 2000
    Posts
    224
    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.

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Yes, the data is consistent for a singe table (not sure about multiple-partition tables), but the consistent parameter gives consistency across all tables.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Aug 2002
    Posts
    27
    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?
    Mohammed Omran, PhD

  10. #10
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width