Skip to Main Content
  • Questions

  • How to ensure the consistency of data when impdp and expdp

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, ezio.

Asked: April 05, 2016 - 3:38 am UTC

Last updated: April 05, 2016 - 12:02 pm UTC

Version: 9i 10g

Viewed 1000+ times

You Asked

Hi:
I want to reorg my table, so I use imp or impdp to import data,But I afraid there will be some other session insert or delete data when I do this.

Is there methods to avoid this?

what I know are:
1) startup restrict
2) close the listener
3) lock the user that I import or exp


Will those methods can achieve my goals or any other method.

Thanks a lot .

and Connor said...

Rather than use expdp/impdp, if you just want to reorg a table, check out DBMS_REDEFINITION

http://www.oracle.com/technetwork/issue-archive/2008/08-jul/o48asktom-089856.html


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

can not use others

ezio, April 05, 2016 - 5:03 am UTC

Thanks for answer, I have two databases, one is 9i so I can not use that, one is 10g, but it use MSSM not ASSM.
Connor McDonald
April 05, 2016 - 6:22 am UTC

http://docs.oracle.com/cd/A91202_01/901_doc/appdev.901/a89852/d_redefi.htm#1001053

dbms_redefinition has been there since 9.0

a little more

ezio, April 05, 2016 - 8:12 am UTC

thanks ,but there is no more space to create a interim table.
Connor McDonald
April 05, 2016 - 12:02 pm UTC

So in that case, after you expdp, you are going to have to drop that table anyway (because you dont have enough space for both the existing and new table).

In which case, your issue of people needing access to the table has surely gone away, because you're going to take access away for the entire length of the impdp anyway.

Sounds to me like you would be better off:

1) deciding if you need to reorg anyway
2) getting some *temporary* tspace

Otherwise you're up for a big outage and big risk....because once you drop that table, if your impdp doesnt work...welll...thats not fun
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Data Pump

All of the database utilities including Data Pump are explained in the Utilities guide.