{"id":6753,"date":"2019-03-19T19:04:06","date_gmt":"2019-03-19T19:04:06","guid":{"rendered":"https:\/\/ittutorial.org\/?p=6753"},"modified":"2020-07-16T13:21:29","modified_gmt":"2020-07-16T13:21:29","slug":"oracle-data-pump-export-import-expdp-impdp-tutorial-with-examples-2","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/oracle-data-pump-export-import-expdp-impdp-tutorial-with-examples-2\/","title":{"rendered":"Oracle Data Pump &#8211; Export Import ( Expdp Impdp ) Tutorial with Examples-2"},"content":{"rendered":"<p>Hi,<\/p>\n<p>In this article I will continue to explain the data pump technology in Oracle databases.<\/p>\n<p><a href=\"http:\/\/salihdeveci.files.wordpress.com\/2013\/10\/data-pump.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-3387\" src=\"http:\/\/salihdeveci.files.wordpress.com\/2013\/10\/data-pump.jpg\" alt=\"data pump\" width=\"521\" height=\"493\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2013\/10\/data-pump.jpg 521w, https:\/\/ittutorial.org\/wp-content\/uploads\/2013\/10\/data-pump-300x284.jpg 300w\" sizes=\"auto, (max-width: 521px) 100vw, 521px\" \/><\/a><\/p>\n<p><!--more--><\/p>\n<p>&nbsp;<\/p>\n\n<p>Read the following first article before this article.<\/p>\n<p>&nbsp;<\/p>\n<blockquote class=\"wp-embedded-content\" data-secret=\"xqy6ifMzYv\"><p><a href=\"https:\/\/ittutorial.org\/oracle-data-pump-export-import-expdp-impdp-tutorial-with-examples-1\/\">Oracle Data Pump &#8211; Export Import ( Expdp Impdp ) Tutorial with Examples-1<\/a><\/p><\/blockquote>\n<p><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"&#8220;Oracle Data Pump &#8211; Export Import ( Expdp Impdp ) Tutorial with Examples-1&#8221; &#8212; IT Tutorial\" src=\"https:\/\/ittutorial.org\/oracle-data-pump-export-import-expdp-impdp-tutorial-with-examples-1\/embed\/#?secret=nOevFU7CmF#?secret=xqy6ifMzYv\" data-secret=\"xqy6ifMzYv\" width=\"600\" height=\"338\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe><\/p>\n<p>&nbsp;<\/p>\n<p>Schema and table based Export and import are done with the following command. For example, the HR schema used in the Production database or the person table under this scheme is needed for test or development purpose in the test database. We can move the related schema and tables with the following commands. First I&#8217;ll show the moving HR scheme like following.<\/p>\n<p>After set the database profile in the Linux operating system, the following command is executed to export of HR Schema.<\/p>\n<p>&nbsp;<\/p>\n<pre>[oracle@MehmetSalih ~]$  expdp \\\"\/ as sysdba\\\" SCHEMAS=HR DIRECTORY=DATAPUMP DUMPFILE=HR.dmp LOGFILE=HR.log<\/pre>\n<p>expdp command is run with sysdba.<\/p>\n<p>&nbsp;<\/p>\n\n<p><strong>SCHEMAS:\u00a0<\/strong>To be backed up schema or to be imported schema is specified with this parameter.<\/p>\n<p><strong>DIRECTORY:<\/strong>\u00a0The logical equivalent of the path to keep this physical dump file on the operating system is specified by the DIRECTORY parameter.\u00a0The file or files that are created during the export process will store to the path where this directory is located. Dumpfile is created under this directory path.<\/p>\n\n<p><strong>DUMPFILE:<\/strong>\u00a0The physical dump file created by the export or imported dump file is specified in the DUMPFILE parameter with its name like HR.dmp.<\/p>\n<p><strong>LOGFILE:<\/strong>\u00a0Logs produced during this process will be written to the log file specified by parameter LOGFILE<\/p>\n<p>&nbsp;<\/p>\n<p>We can transfer this export file to the test server with scp command as follows.<\/p>\n<p>&nbsp;<\/p>\n<pre>[oracle@MehmetSalih ~]$  scp \/backup\/HR.dmp oracle@192.168.63.63:\/Backup<\/pre>\n\n<p>&nbsp;<\/p>\n<p>This export file transfered to the test server is imported into the related test database as follows.<\/p>\n<pre>[oracle@Deveci ~]$  impdp \\\"\/ as sysdba\\\" SCHEMAS=HR DIRECTORY=DATAPUMP DUMPFILE=HR.dmp LOGFILE=HR.log table_exists_action=replace<\/pre>\n<p>&nbsp;<\/p>\n<p>When we examine the above impdp command, this process is done with sysdba as in expdp.\u00a0<span class=\"\" title=\"\">The other parameters ( DIRECTORY,DUMPFILE,SCHEMA,LOGFILE ) are the same as expdp above, so the table_exists_action parameter is different.<\/span><\/p>\n\n<p>&nbsp;<\/p>\n<p><span class=\"\" title=\"\"><strong>Table_exists_action<\/strong>: While Import process is running if any table exists in database what will Oracle do ? replace existing table or skip if it exists. this parameter can take following values.<\/span><\/p>\n<pre>TABLE_EXISTS_ACTION=[SKIP | APPEND | TRUNCATE | REPLACE]\r\n\r\n<\/pre>\n<p>SKIP is default value. If SKIP is used then table replacing is not done.<\/p>\n<p>APPEND\u00a0 loads rows from the export files and leaves target existing rows unchanged.<\/p>\n<p>TRUNCATE deletes existing rows in target table and then loads rows from the export.<\/p>\n<p>REPLACE drops the existing table in the target and then creates and loads it from the export.<\/p>\n<p>&nbsp;<\/p>\n\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>Next articles&#8217; link are as follows.<\/p>\n<blockquote class=\"wp-embedded-content\" data-secret=\"M1CTWh6duZ\"><p><a href=\"https:\/\/ittutorial.org\/oracle-data-pump-export-import-expdp-impdp-tutorial-with-examples-3\/\">Oracle Data Pump &#8211; Export Import ( Expdp Impdp ) Tutorial with Examples-3<\/a><\/p><\/blockquote>\n<p><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"&#8220;Oracle Data Pump &#8211; Export Import ( Expdp Impdp ) Tutorial with Examples-3&#8221; &#8212; IT Tutorial\" src=\"https:\/\/ittutorial.org\/oracle-data-pump-export-import-expdp-impdp-tutorial-with-examples-3\/embed\/#?secret=p1mzZgKaYd#?secret=M1CTWh6duZ\" data-secret=\"M1CTWh6duZ\" width=\"600\" height=\"338\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe><\/p>\n<blockquote class=\"wp-embedded-content\" data-secret=\"d4VK6b6xSa\"><p><a href=\"https:\/\/ittutorial.org\/oracle-data-pump-export-import-expdp-impdp-tutorial-with-examples-4\/\">Oracle Data Pump &#8211; Export Import ( Expdp Impdp ) Tutorial with Examples-4<\/a><\/p><\/blockquote>\n<p><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"&#8220;Oracle Data Pump &#8211; Export Import ( Expdp Impdp ) Tutorial with Examples-4&#8221; &#8212; IT Tutorial\" src=\"https:\/\/ittutorial.org\/oracle-data-pump-export-import-expdp-impdp-tutorial-with-examples-4\/embed\/#?secret=pHTOH0YtQP#?secret=d4VK6b6xSa\" data-secret=\"d4VK6b6xSa\" width=\"600\" height=\"338\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe><\/p>\n<p>&nbsp;<\/p>\n<h5><span style=\"color: #ff0000;\">Do you want to learn Oracle Database for Beginners, then read the following articles.<\/span><\/h5>\n<p><a href=\"https:\/\/ittutorial.org\/oracle-database-19c-tutorials-for-beginners\/\">https:\/\/ittutorial.org\/oracle-database-19c-tutorials-for-beginners\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Hi, In this article I will continue to explain the data pump technology in Oracle databases.<\/p>\n","protected":false},"author":1,"featured_media":3388,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[5044,1994],"tags":[446,447,2661,2659,2660,2662,481,508,609,2665,2663,1031,1120,1131,2455,1204,2656,1262,2654,2657,1276,1327,1345,2664,2653,2655,1478,1515,2658],"class_list":["post-6753","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-datapump","category-oracle","tag-data-pump","tag-data-pump-export-import","tag-datapump-export","tag-datapump-export-import","tag-datapump-impdp-expdp","tag-datapump-import","tag-dba_datapump_jobs","tag-deveci","tag-export-import","tag-how-to-backup-schema-oracle","tag-how-to-backup-table-oracle","tag-mehmet-salih-deveci","tag-oracle","tag-oracle-12c","tag-oracle-18c","tag-oracle-data-pump","tag-oracle-expdp","tag-oracle-export-import","tag-oracle-export-table","tag-oracle-impdp","tag-oracle-import-table","tag-oracle-rman","tag-oracle-support","tag-oracle-table-backup","tag-oracle-table-export","tag-oracle-table-import","tag-rman","tag-salih-deveci","tag-what-is-datapump"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/ittutorial.org\/wp-content\/uploads\/2013\/10\/data-pump-2.gif","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6753","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=6753"}],"version-history":[{"count":7,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6753\/revisions"}],"predecessor-version":[{"id":16921,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6753\/revisions\/16921"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/3388"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=6753"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=6753"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=6753"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}