{"id":6854,"date":"2017-07-06T07:40:13","date_gmt":"2017-07-05T23:40:13","guid":{"rendered":"http:\/\/rmohan.com\/?p=6854"},"modified":"2017-07-06T07:40:40","modified_gmt":"2017-07-05T23:40:40","slug":"oracle-data-pump-expdp-impdp","status":"publish","type":"post","link":"https:\/\/mohan.sg\/?p=6854","title":{"rendered":"Oracle data pump expdp \/ impdp"},"content":{"rendered":"

Oracle<\/a>\u00a011g later new features make the default conditions in the allocation of table space when the empty table is ignored to reduce the table space resource consumption, so that the use of Oracle’s exp export user data will be ignored when the empty table, it will cause data Not complete, of course, in the use of exp export data is not no way, this has been mentioned before,<\/p>\n

Use this method is to solve the use of exp export user data will not ignore the empty table, but there is a more efficient way is to use expdp \/ impdp – Oracle data pump, to import and export Oracle data, compared to exp \/ Imp expdp \/ impdp is more efficient data import and export tools used, of course, here expdp \/ impdp and exp \/ imp difference between the simple to say.<\/p>\n

And exp different, the use of expdp export data first use DBA users to enter Oracle to perform a specified backup path and then authorized to read and write before they can operate, the specific steps are as follows:<\/p>\n

\n
\n
\n
SQL>\u00a0<\/code>create<\/code>\u00a0or<\/code>\u00a0replace<\/code>\u00a0directory\u00a0dpdata1\u00a0<\/code>as<\/code>\u00a0'\/data\/backup\/oracle_backup'<\/code>;<\/code><\/div>\n
\u00a0<\/code><\/div>\n
Directory\u00a0created.<\/code><\/div>\n
\u00a0<\/code><\/div>\n
SQL>\u00a0<\/code>grant<\/code>\u00a0read<\/code>,write\u00a0<\/code>on<\/code>\u00a0directory\u00a0dpdata1\u00a0<\/code>to<\/code>\u00a0u01;<\/code><\/div>\n
\u00a0<\/code><\/div>\n
Grant<\/code>\u00a0succeeded.<\/code><\/div>\n
\u00a0<\/code><\/div>\n
SQL>\u00a0<\/code>grant<\/code>\u00a0read<\/code>,write\u00a0<\/code>on<\/code>\u00a0directory\u00a0dpdata1\u00a0<\/code>to<\/code>\u00a0u02;<\/code><\/div>\n
\u00a0<\/code><\/div>\n
Grant<\/code>\u00a0succeeded.<\/code><\/div>\n
\u00a0<\/code><\/div>\n
SQL>\u00a0<\/code>select<\/code>\u00a0*\u00a0<\/code>from<\/code>\u00a0dba_directories;<\/code><\/div>\n<\/div>\n<\/div>\n<\/div>\n

Here dpdatal designated to backup the data to export the path, and then authorized to export the directory to the user, it should be noted that this directory must be Oracle users to ensure that the file permissions to write, after the completion of the specified backup directory can Expdp start with the export of data, the use of expdp export data will import.log log file generation, because here is a number of user export, I will specify the log file under the file name<\/p>\n

\n
\n
\n
[oracle@localhost\u00a0~]$\u00a0expdp\u00a0u01<\/code>\/passwword_u01<\/code>\u00a0\u00a0directory=dpdata1\u00a0 dumpfile=u01.dmp\u00a0logfile=u01.log<\/code><\/div>\n
expdp\u00a0u02<\/code>\/password_u02<\/code>\u00a0\u00a0directory=dpdata1\u00a0 dumpfile=u02.dmp\u00a0logfile=u02.log<\/code><\/div>\n<\/div>\n<\/div>\n<\/div>\n

After the export will be in the specified backup directory to generate backup files and log.<\/p>\n

In the import time to use impdp import, the specific use of the method with the qu almost, but need to specify the backup directory, the operation is as follows:<\/p>\n

\n
\n
\n
[oracle@localhost\u00a0oracle_backup]$\u00a0impdp\u00a0u01<\/code>\/passwword_u01<\/code>\u00a0directory=dpdata1\u00a0dumpfile=u02.dmp\u00a0FULL=y<\/code><\/div>\n
[oracle@localhost\u00a0oracle_backup]$\u00a0impdp\u00a0u02<\/code>\/passwword_u02<\/code>\u00a0directory=dpdata1\u00a0dumpfile=u02.dmp\u00a0FULL=y<\/code><\/div>\n<\/div>\n<\/div>\n<\/div>\n

So that the import is completed, but here is the need to note that the use of impdp import data, if Oracle did not create the appropriate user, after the import will automatically create the user, so when you need to pay attention to the import The data is wrong, the simple Oracle data pump expdp \/ impdp is roughly the same, there are other in accordance with the table name, query conditions, the whole library import and export operations are roughly the same, just need to modify a few parameters, Do too much explanation<\/p>\n

—————————- gorgeous split line —————– ———–<\/p>\n","protected":false},"excerpt":{"rendered":"

Oracle 11g later new features make the default conditions in the allocation of table space when the empty table is ignored to reduce the table space resource consumption, so that the use of Oracle’s exp export user data will be ignored when the empty table, it will cause data Not complete, of course, in the […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[34],"tags":[],"_links":{"self":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/6854"}],"collection":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=6854"}],"version-history":[{"count":2,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/6854\/revisions"}],"predecessor-version":[{"id":6856,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/6854\/revisions\/6856"}],"wp:attachment":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6854"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6854"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6854"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}