23
Jan
07

expdp and impdp are great

Before oracle 10g, there was exp and imp tools. They are good export and import tools but I had only one problem with them.

In Thailand, people tend to put all tables in the same schema even if it’s a different application. That is simply because they don’t know any better.

For better administration and security, I want my tables splitted per application in different schemas with limited access to other schemas if required. Additionally to that, I would like to have those tables in a separated tablespace (and datafile).

exp/imp tool allows to move tables from one schema to another schema and the option is logically called fromuser/touser.

The problem is that if I wanted to move the tables to another tabelspace it was a little more tricky. It involves generating the script of the tables using the exp tool. Modify them manually by changing the targetted tablespace and then run them in sqlplus to generate the tables in the new schema and new tablespace.

With expdp and impdp, I can first select the tables I want to move (that will be the same as before using tables option).

The greatness comes with impdp where I can mention the following options: REMAP_SCHEMA (a bit like the fromuser/touser of imp) and REMAP_TABLESPACE. That is really cool.

I had a bit of trouble getting expdp and impdp to work. The thing is that before using them , the DBA must create a directory as follow:

C:\ sqlplus / as sysdba

SQL>CREATE DIRECTORY DATA_PUMP_DIR AS ‘A VALID DIRECTORY’;

SQL>COMMIT;

In my opinion, the commit is not required because create directory is a ddl command. But I was struggling and I saw an example doing like this. I’m not too sure what worked because I had to drop the directory first (just like we drop a table but we mention directory instead) and recreate it. So it might be it or might not. If anyone got any idea, I’m happy to know and the comments would do marvel for this : )

lLet’s get back to work.

It is now possible to call expdp

C:\ expdp system\acomplexepassword@tnsname directory=data_pump_dir dumpfile=Mydump%U.dmp tables=my,big,list,of,tables

And here you go. Your data are exported.

Then use the impdp command as follow:

C:\ impdp system\acomplexepassword@tnsname directory=data_pump_dir dumpfile=mydump01.dmp remap_schema=source_schema:dest_schema remap_tablespace=source_tablespace:dest_tablespace

And this is it.

In 2 command lines, what would have been otherwise a complexe situation becomes very easy.

note:

1) If you don’t have the data_pump_dir directory specified the you’ll get error messages

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name DATA_PUMP_DIR is invalid

2) The dumpfile name in impdp is called dump01.dmp because I specified the variable %U in the name at export time. It’s an increment. It means you can keep the same name, if the file already exists in the directory then dump02.dmp will be created etc…

3) A valid directory in the example above is a directory that exists on your server (such as D:\dumps)

Advertisements

39 Responses to “expdp and impdp are great”


  1. 1 Siddhartha
    March 3, 2007 at 7:47 am

    Hi,

    You should grant read and write permission on directory object.

  2. April 4, 2007 at 7:14 am

    Hi.

    You are right. That would be for a *nix system though (How I wish my company use it).

    But on windows it’s pretty much open…

  3. April 4, 2007 at 7:14 am

    oh! And thanks a lot for the comment : )

  4. 6 Niraj
    June 26, 2007 at 1:39 pm

    Hi All,

    It is good , but i like to generate my .dmp file in destination server.

    Example.

    From svr01 i m taking export dump (expdp) of svr02(sid->BEA)

    How can ii do that.
    Please assist.

    Thanks
    -Niraj

  5. October 25, 2007 at 6:04 am

    If you are on windows, you can map a network destination to a drive letter and then use it as your destination.

    N ico

  6. 8 Alzebdeh
    March 14, 2008 at 6:16 am

    Hello,
    What if I got the dump from the DBA and wanted to apply the impdp on my personal PC and I do not know the name of the directory and I have only the dump. Then what to do and how to import the file to my local database?

    Alzebdeh

  7. 9 Victor
    June 25, 2008 at 6:25 pm

    @Alzebdeh

    The directory parameter for impdp is the directory where your dump file is stored, not where it originated.

  8. 10 jojo
    January 13, 2009 at 8:23 pm

    Hello Nico,

    What if you wish to export the entire database and not just tables?

    Jojo

  9. January 14, 2009 at 3:22 am

    Hi.

    set oracle_sid=[oraclesid]

    expdp help=y

    if not 10g then exp help = y

    either way there’s an option full=y

    expdp userid=nicolas/goodpassword directory=oracle_configured_directory file=test.dmp full=y

    not sure to remember exactly the parameter name but you should be able to square that up with expdp help=y.

    Of course it works the other way around: imp help=y and impdp help=y

    impdp is a new tool coming with 10g. Use it preferably.

    regards,

    Nico

  10. 12 jojo
    January 14, 2009 at 5:52 am

    Hello Nico,

    Thank you for the information. I looked up the syntax and below is what I found.

    “expdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log

    Please permit me to ask these questions – i am a newbie in these tasks,

    1) does it mean that the data now resides in DB10G.dmp (w.r.t to example above).

    2) if yes, does it also mean that the exported database (DB10G.dmp) could be imported into Oracle Express Edition (for example) with the code below – and the exact copy restored with doing additional work (e.g. copying control files ect)?

    impdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=impdpDB10G.log

    3) I guess the original database remains intact – i.e. upon using “expdp”

    I would appreciate usual prompt response.

    Regards
    Emmanuel

  11. January 15, 2009 at 12:27 am

    Hi again,

    provided you’ve created TEST_DIR, yes it should ūüôā

    1) Yes it means that all schemas are now in DB10G.dmp however, the impdp won’t create a new DB and table spaces for you

    2) related to 1), before you are able to import, you will have to create the tablespace if they don’t exist yet. The easiest way to know what’s missing is to run the command and look for errors.

    3) The database is untouched when working with expdp. And if you did use impdp on your production database, it wouldn’t transfer the data if the table already exists unless you specified so.

    note: Don’t specify usual prompt response >< I’m not working for you. I do it because it pleases me. The best you could get from it is a non response…

    Nico

  12. 14 jojo
    January 18, 2009 at 1:14 pm

    Thank you for the answer and caution. The latter has been taken in good faith.

  13. 15 jojo
    January 18, 2009 at 3:38 pm

    Hello Nico,

    Please, any help on the error messages below?

    ————————————————————————————
    ;;;
    Export: Release 10.2.0.1.0 – Production on Sunday, 18 January, 2009 15:46:53

    Copyright (c) 2003, 2005, Oracle. All rights reserved.
    ;;;
    Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production
    With the Partitioning, OLAP and Data Mining options
    Starting “JOJO”.”SYS_EXPORT_FULL_01″: jojo/********@masterit full=y include=grant include=index directory=item_dir dumpfile=jojo.dmp content=all
    Estimate in progress using BLOCKS method…
    Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
    Total estimation using BLOCKS method: 0 KB
    Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
    Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
    Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/GRANT/OWNER_GRANT/OBJECT_GRANT
    ORA-39127: unexpected error from call to export_string := SYS.DBMS_RMGR_GROUP_EXPORT.grant_exp(4845,1,…)
    ORA-06502: PL/SQL: numeric or value error: NULL index table key value
    ORA-06512: at “SYS.DBMS_RMGR_GROUP_EXPORT”, line 130
    ORA-06512: at line 1
    ORA-06512: at “SYS.DBMS_METADATA”, line 5111
    ORA-39127: unexpected error from call to export_string := SYS.DBMS_RMGR_GROUP_EXPORT.grant_exp(4844,1,…)
    ORA-06502: PL/SQL: numeric or value error: NULL index table key value
    ORA-06512: at “SYS.DBMS_RMGR_GROUP_EXPORT”, line 130
    ORA-06512: at line 1
    ORA-06512: at “SYS.DBMS_METADATA”, line 5111
    ORA-39127: unexpected error from call to export_string := SYS.DBMS_RMGR_GROUP_EXPORT.grant_exp(4843,1,…)
    ORA-06502: PL/SQL: numeric or value error: NULL index table key value
    ORA-06512: at “SYS.DBMS_RMGR_GROUP_EXPORT”, line 130
    ORA-06512: at line 1
    ORA-06512: at “SYS.DBMS_METADATA”, line 5111
    Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/GRANT/PROCOBJ_GRANT
    ORA-39127: unexpected error from call to export_string := SYS.DBMS_RMGR_GROUP_EXPORT.grant_exp(4845,1,…)
    ORA-06502: PL/SQL: numeric or value error: NULL index table key value
    ORA-06512: at “SYS.DBMS_RMGR_GROUP_EXPORT”, line 130
    ORA-06512: at line 1
    ORA-06512: at “SYS.DBMS_METADATA”, line 5111
    ORA-39127: unexpected error from call to export_string := SYS.DBMS_RMGR_GROUP_EXPORT.grant_exp(4844,1,…)
    ORA-06502: PL/SQL: numeric or value error: NULL index table key value
    ORA-06512: at “SYS.DBMS_RMGR_GROUP_EXPORT”, line 130
    ORA-06512: at line 1
    ORA-06512: at “SYS.DBMS_METADATA”, line 5111
    ORA-39127: unexpected error from call to export_string := SYS.DBMS_RMGR_GROUP_EXPORT.grant_exp(4843,1,…)
    ORA-06502: PL/SQL: numeric or value error: NULL index table key value
    ORA-06512: at “SYS.DBMS_RMGR_GROUP_EXPORT”, line 130
    ORA-06512: at line 1
    ORA-06512: at “SYS.DBMS_METADATA”, line 5111
    Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
    Processing object type DATABASE_EXPORT/SCHEMA/TABLE/GRANT/CROSS_SCHEMA/OBJECT_GRANT
    Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
    Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
    Processing object type DATABASE_EXPORT/SCHEMA/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT
    Processing object type DATABASE_EXPORT/SCHEMA/FUNCTION/GRANT/OWNER_GRANT/OBJECT_GRANT
    Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/GRANT/OWNER_GRANT/OBJECT_GRANT
    Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/OWNER_GRANT/OBJECT_GRANT
    Processing object type DATABASE_EXPORT/SCHEMA/VIEW/GRANT/CROSS_SCHEMA/OBJECT_GRANT
    Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
    Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
    ORA-39127: unexpected error from call to export_string := SYS.DBMS_RMGR_GROUP_EXPORT.grant_exp(4845,1,…)
    ORA-06502: PL/SQL: numeric or value error: NULL index table key value
    ORA-06512: at “SYS.DBMS_RMGR_GROUP_EXPORT”, line 130
    ORA-06512: at line 1
    ORA-06512: at “SYS.DBMS_METADATA”, line 5111
    ORA-39127: unexpected error from call to export_string := SYS.DBMS_RMGR_GROUP_EXPORT.grant_exp(4844,1,…)
    ORA-06502: PL/SQL: numeric or value error: NULL index table key value
    ORA-06512: at “SYS.DBMS_RMGR_GROUP_EXPORT”, line 130
    ORA-06512: at line 1
    ORA-06512: at “SYS.DBMS_METADATA”, line 5111
    ORA-39127: unexpected error from call to export_string := SYS.DBMS_RMGR_GROUP_EXPORT.grant_exp(4843,1,…)
    ORA-06502: PL/SQL: numeric or value error: NULL index table key value
    ORA-06512: at “SYS.DBMS_RMGR_GROUP_EXPORT”, line 130
    ORA-06512: at line 1
    ORA-06512: at “SYS.DBMS_METADATA”, line 5111
    Master table “JOJO”.”SYS_EXPORT_FULL_01″ successfully loaded/unloaded
    ******************************************************************************
    Dump file set for JOJO.SYS_EXPORT_FULL_01 is:
    C:\EXP_ITEM\JOJO.DMP
    Job “JOJO”.”SYS_EXPORT_FULL_01″ completed with 9 error(s) at 15:47:25
    ———————————————————————————-

  14. 16 Ebrahim
    November 7, 2009 at 6:40 am

    I have exported out a full production schema in a downtime window.
    Now I realize that I donot have enough space in the test database, so I want to exclude out some unimportant tables while doing impdp. (I can not take another expdp for a week).

    How to exclude out the tables during impdp that exist in the expdp dmp files?

    thanks for you help!

  15. November 19, 2009 at 2:43 am

    Hello,

    if you got the data in your dump file then you can extract whatever you want from it.

    You might not be able to do an exclude list but you can definitely do an include list using the TABLES= my,list,of,tables

    If your DB is well organized you can then pick just the schema you need.

    Nico

  16. 18 Srini
    May 6, 2010 at 5:38 pm

    Hi All,

    I get following error while using impdb utility –

    SQL>Grant read, write on directory data_pump_dir to openpages;

    C:\oracle\admin\OP\dpdump>Impdp system/@OP dumpfile=openpages_raintst_0305.dmp logfile=openpages.log directory=DATA_PUMP_DIR schemas=OPENPAGES

    ORA-39001: invalid argument value
    ORA-39000: bad dump file specification
    ORA-31619: invalid dump file C:\oracle\admin\OP\dpdump\openpages_raintst_0305.dmp

    I’m using Oracle 10.2.0.4. The export dump was created on Production server and running the above import on my UAT server.

    Any comments would be appreciated!

    Thanks
    Srini

    • 19 kk
      July 7, 2010 at 4:54 am

      hi all,

      i have been facing a similar issue like srini is facing above.
      i am newbie and just installed oracle 11g server edition.
      i nedd to perform the EXPDP on a table , just to see how it works , but inspite of trying all the available links and options on the net , nothing seems to work good.
      I dont know what to do ? and how to go about ?

      all i have is an installed 11g with sql plus on windows 7 !

      also it would be great if somebody assist me understanding of various things like :

      1.where is my oracle home , and how do i find that out ?

      2. where do i find TNSNAMES.ora file ?

      thanks

  17. 20 ravi
    December 20, 2010 at 5:58 pm

    hi,

    Please give me solution to my problem.my client has exported schema from linux mechine to my windows database(11g).when i tried to immport the the schema i got an error called ORA-39000 bad dump file.: invalid operation
    ORA-39070: Unable to open the log file.
    ORA-39087: directory name DATA_PUMP_DIR is invalid

    We tried several times to import it.but failed to do import it.the same error bad dump file is repeating.my client is not interested to export again.the size of schema is 500mb.

    Kindly let us know if any one can give solution.and post to your solution to my personal id instance_ravi@yahoo.com.i am not member in this group that’s i am requesting you to send solution to my personal id.

    Ravindra

  18. 21 Sherif Mattar
    March 9, 2011 at 3:14 pm

    hi Ravi,,
    did you try to switch the transfer type,, i think you can use binary ftp.

    Sherif

  19. 22 Roche
    June 27, 2011 at 4:08 am

    Hi,
    When i export a table from one schema it is showing successfully exported.
    But when import to different schema it is giving a error message.

    Please Help.

    Thank you

    EXPORT LOG:
    ==========
    ;;;
    Export: Release 11.2.0.1.0 – Production on Mon Jun 27 11:23:44 2011

    Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
    ;;;
    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Starting “VACAPP”.”SYS_EXPORT_TABLE_01″: vacapp/******** TABLES=J_GST DIRECTORY=dbpump11g DUMPFILE=exp11g.dmp LOGFILE=exp11glog.log
    Estimate in progress using BLOCKS method…
    Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
    Total estimation using BLOCKS method: 64 KB
    Processing object type TABLE_EXPORT/TABLE/TABLE
    Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
    . . exported “VACAPP”.”J_GST” 6.281 KB 2 rows
    Master table “VACAPP”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
    ******************************************************************************
    Dump file set for VACAPP.SYS_EXPORT_TABLE_01 is:
    C:\ORACLERESTORE\EXP11G.DMP
    Job “VACAPP”.”SYS_EXPORT_TABLE_01″ successfully completed at 11:24:05

    IMPORT LOG:
    ===========
    ;;;
    Import: Release 11.2.0.1.0 – Production on Mon Jun 27 12:00:34 2011

    Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
    ;;;
    Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    Master table “VACOPS”.”SYS_IMPORT_TABLE_01″ successfully loaded/unloaded
    ORA-39166: Object VACOPS.J_GST was not found.
    Job “VACOPS”.”SYS_IMPORT_TABLE_01″ successfully completed at 12:00:41
    Job “VACOPS”.”SYS_IMPORT_TABLE_01″ successfully completed at 12:00:41

  20. 23 sharad
    September 7, 2011 at 2:38 pm

    Hi ,

    Can u plz tell me what is significance of the ‘ ! ‘ when put before impdp command?
    SQL> !impdp system/bmcAdm1n@orcl directory=DBDUMP_dir dumpfile=AR7604_IOKO_Final.dmp LOGFILE=arioko.log !impdp system/bmcAdm1n@orcl dumpfile=AR7604_IOKO_Final.d
    mp LOGFILE=arioko.log C:\app\ADministrator\admin\orcl\dpdump
    this is whole command
    and basic command generalised one is
    impdp hr/hr DUMPFILE=dpump_dir1:expfull.dmp FULL=y LOGFILE=dpump_dir2:full_imp.log
    Can u plz tell me what is significance of the ‘ ! ‘ when put before impdp command?

    Regard,
    Sharad.

  21. February 23, 2013 at 2:40 pm

    I go to see day-to-day a few sites and sites to read articles,
    however this weblog provides feature based posts.

  22. February 23, 2013 at 2:54 pm

    Howdy! Do you know if they make any plugins to help with SEO?
    I’m trying to get my blog to rank for some targeted keywords but I’m not seeing very good gains.
    If you know of any please share. Cheers!

  23. March 17, 2013 at 4:20 pm

    VitaliKor features L-arginine, ginseng, ginkgo, w in addition to vitamin antioxidants.

    The constituents involving VitaliKor come together to provide
    your day-to-day importance of 14 vitamins and minerals.

  24. March 21, 2013 at 11:11 pm

    Hey there I am so thrilled I found your site, I really found you by error, while I was looking on Aol for something else, Anyways I am
    here now and would just like to say cheers for a
    incredible post and a all round interesting blog
    (I also love the theme/design), I don’t have time to go through it all at the minute but I have book-marked it and also added your RSS feeds, so when I have time I will be back to read more, Please do keep up the fantastic work.

  25. April 8, 2013 at 8:33 pm

    I am really enjoying the theme/design of your site. Do you ever run into any
    web browser compatibility issues? A number of my blog readers have complained about my blog not working correctly in Explorer but looks great in Opera.
    Do you have any ideas to help fix this problem?

  26. May 15, 2013 at 11:51 pm

    Excellent weblog right here! Also your site lots up fast!
    What host are you the use of? Can I get your affiliate hyperlink for your
    host? I desire my website loaded up as fast as yours lol

  27. June 16, 2013 at 3:38 am

    Hey! Do you know if they make any plugins to help with Search Engine
    Optimization? I’m trying to get my blog to rank for some targeted keywords but I’m not seeing very good success.

    If you know of any please share. Kudos!

  28. June 28, 2013 at 2:03 am

    Hey there! I know this is kinda off topic however I’d figured I’d ask.
    Would you be interested in exchanging links or
    maybe guest writing a blog article or vice-versa?
    My website discusses a lot of the same subjects as yours
    and I believe we could greatly benefit from each other.

    If you might be interested feel free to shoot me an
    email. I look forward to hearing from you!
    Excellent blog by the way!

  29. July 6, 2013 at 11:16 am

    Good information. Lucky me I discovered your site by accident (stumbleupon).
    I have book marked it for later!

  30. July 20, 2013 at 11:08 am

    Attractive section of content. I just stumbled upon your site and
    in accession capital to assert that I get actually enjoyed account your blog posts.

    Any way I’ll be subscribing to your feeds and even I achievement you access consistently rapidly.

  31. August 17, 2013 at 6:57 pm

    Do you have a spam problem on this site; I also am a blogger,
    and I was wondering your situation; many of us have developed some
    nice practices and we are looking to trade strategies with other
    folks, why not shoot me an email if interested.

  32. March 15, 2014 at 11:18 am

    I have been surfing online more than 2 hours today,
    yet I never found any interesting article like yours. It is pretty worth
    enough for me. In my view, if all webmasters and bloggers made good content as
    you did, the internet will be a lot more useful than ever before.

  33. June 23, 2014 at 6:57 pm

    Very nice post. I just stumbled upon your blog and wanted
    to say that I’ve truly enjoyed surfing around your blog posts.
    After all I will be subscribing to your rss feed and I hope you write
    again very soon!
    france suisse pari sportif

  34. August 26, 2014 at 10:59 pm

    It’s perfect time to make some plans for the future and it’s time
    to be happy. I’ve read this post and if I could I desire to suggest you few interesting things or
    advice. Perhaps you can write next articles referring to this article.

    I want to read even more things about it!

  35. 38 espionner un compte snapchat
    February 11, 2015 at 8:06 am

    If a person has been hacked, it is suggested that he or she delete the Snapchat account.
    Keep your brain from becoming lunch in Plants verses Zombies.
    They give people relief from daily stress and make the mood cheerful.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: