Forum Stats

  • 3,824,835 Users
  • 2,260,429 Discussions
  • 7,896,325 Comments

Discussions

Bring EXP/IMP back to be up-to-date tools rather than legacy

User_PT223
User_PT223 Member Posts: 9 Blue Ribbon
edited Sep 19, 2019 9:35AM in Database Ideas - Ideas

There're cases when datapump can't be used, most obvious example - when you don't have access to filesystem of the machine where source / target databases are running.

Good old IMP/EXP could do the job if they had supported all the modern column types and didn't require such a powerful grants.

It's a shame for 21st century Oracle that such an easy task as copying data of a table from one database to another is becoming such a pain in the arse.

William RobertsonSven W.Andreas HuberKiran SwamiRainer StenzelRCutshawNiels Hecker
10 votes

Active · Last Updated

Comments

  • Mike Kutz
    Mike Kutz Member Posts: 6,195 Silver Crown
  • User_PT223
    User_PT223 Member Posts: 9 Blue Ribbon

    Why do you need to create a file?

    Just IMPDP over a DB Link.

    https://oracle-base.com/articles/10g/oracle-data-pump-10g#NetworkExportsImports

    For many different reasons.

    Sometimes you just don't have a direct connection between two databases, sometimes you just need to have a local copy (a sort of backup) of certain table(s) so you can keep a history.

    Exporting something to a file is a great feature that exp/imp used to deliver, but in order to use them you need to obtain extra grants and privileges that obviously are something unnecessary extra to what is actually needed.

    William Robertson
  • BPeaslandDBA
    BPeaslandDBA Member Posts: 4,615 Blue Diamond
    It's a shame for 21st century Oracle that such an easy task as copying data of a table from one database to another is becoming such a pain in the arse.

    I'd agree with that. I find Data Pump to be a pain when compared with the simplicity of the old exp/imp utilities. However, Data Pump does have advantages. Rather than bring back the old utilities, I'd like to see Oracle expand on DP's functionality to include the ability to dump to a file local to where expdp was run. It would still dump to a DIRECTORY object like it does now, but also have additional capability.

    User_PT223
  • User51642 Yong Huang
    User51642 Yong Huang Member Posts: 164 Bronze Badge
    It's a shame for 21st century Oracle that such an easy task as copying data of a table from one database to another is becoming such a pain in the arse.

    I'd agree with that. I find Data Pump to be a pain when compared with the simplicity of the old exp/imp utilities. However, Data Pump does have advantages. Rather than bring back the old utilities, I'd like to see Oracle expand on DP's functionality to include the ability to dump to a file local to where expdp was run. It would still dump to a DIRECTORY object like it does now, but also have additional capability.

    Very well said! To add a little code into expdp or impdp that handles file copying to or from the local client through Oracle Net (SQL*Net) should be quite easy but is not done. Using network_link is possible only if you have access to another database, and the data type cannot be long or long raw (restriction recently lifted?). Another good feature the traditional exp/imp has is using a pipe. I think it's more difficult to enhance data pump to allow working with pipes.

    User_PT223
  • William Robertson
    William Robertson Member Posts: 9,567 Bronze Crown

    For many different reasons.

    Sometimes you just don't have a direct connection between two databases, sometimes you just need to have a local copy (a sort of backup) of certain table(s) so you can keep a history.

    Exporting something to a file is a great feature that exp/imp used to deliver, but in order to use them you need to obtain extra grants and privileges that obviously are something unnecessary extra to what is actually needed.

    Exactly - a file on a database server you don't have access to is not much use, especially if the server is actually a VM that will refreshed, wiping out your file.

    User_PT223