{"id":6,"date":"2008-03-24T10:59:21","date_gmt":"2008-03-24T14:59:21","guid":{"rendered":"http:\/\/www.dev-notes.com\/blog\/2008\/03\/24\/using-oracle-data-pump\/"},"modified":"2008-03-24T10:59:21","modified_gmt":"2008-03-24T14:59:21","slug":"using-oracle-data-pump","status":"publish","type":"post","link":"https:\/\/www.dev-notes.com\/blog\/2008\/03\/24\/using-oracle-data-pump\/","title":{"rendered":"Using Oracle Data Pump"},"content":{"rendered":"

To use Data Pump, a directory must be set up. In the SQL*Plus code example below, we are creating a directory dedicated for Data Pump usage.<\/p>\n

create or replace directory datapumpdir as ‘d:datadump’;<\/p>\n

With the Data Pump directory set, we can now run the following command line to export data. There are two examples below; the first is a full database export (ie. all schemas), and the second exports only one schema.<\/p>\n

\nexpdp username\/password@tnsname directory=datapumpdir dumpfile=full%U.dmp filesize=2G logfile=full.log full=y<\/p>\n

expdp username\/password@tnsname directory=datadumpdir dumpfile=schema%U.dmp filesize=2G logfile=schema.log schemas=scott<\/p>\n

Note the %U variable in the dump file name. Because we set the maximum file size at 2gb, we introduced a potential error point: what happens if our dump file exceeds 2gb? Because we set a %U variable at the end of the file name, it means our first dump file will be tagged with a number “01”, eg. full01.dmp. If full01.dmp exceeds 2gb in size, the file full02.dmp will be created automatically. full03.dmp will be created if full02.dmp reaches 2gb in size, and so on.<\/p>\n

To import, we need to first make sure the receiving database has the directory set, and then we can run the command:<\/p>\n

impdp username\/password@tnsname schemas=scott directory=datapumpdir dumpfile=full%U.dmp logfile=full.log<\/p>\n

Below is the help file for the expdp command, for our reference.<\/p>\n

\nExport: Release 10.1.0.4.0 - Production on Monday, 24 March, 2008 11:22\n\nCopyright (c) 2003, Oracle.  All rights reserved.\n\n\nThe Data Pump export utility provides a mechanism for transferring data objects\nbetween Oracle databases. The utility is invoked with the following command:\n\n   Example: expdp scott\/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp\n\nYou can control how Export runs by entering the 'expdp' command followed\nby various parameters. To specify parameters, you use keywords:\n\n   Format:  expdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)\n   Example: expdp scott\/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott\n               or TABLES=(T1:P1,T1:P2), if T1 is partitioned table\n\nUSERID must be the first parameter on the command line.\n\nKeyword               Description (Default)\n------------------------------------------------------------------------------\nATTACH                Attach to existing job, e.g. ATTACH [=job name].\nCONTENT               Specifies data to unload where the valid keywords are:\n                      (ALL), DATA_ONLY, and METADATA_ONLY.\nDIRECTORY             Directory object to be used for dumpfiles and logfiles.\nDUMPFILE              List of destination dump files (expdat.dmp),\n                      e.g. DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.\nESTIMATE              Calculate job estimates where the valid keywords are:\n                      (BLOCKS) and STATISTICS.\nESTIMATE_ONLY         Calculate job estimates without performing the export.\nEXCLUDE               Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.\nFILESIZE              Specify the size of each dumpfile in units of bytes.\nFLASHBACK_SCN         SCN used to set session snapshot back to.\nFLASHBACK_TIME        Time used to get the SCN closest to the specified time.\nFULL                  Export entire database (N).\nHELP                  Display Help messages (N).\nINCLUDE               Include specific object types, e.g. INCLUDE=TABLE_DATA.\nJOB_NAME              Name of export job to create.\nLOGFILE               Log file name (export.log).\nNETWORK_LINK          Name of remote database link to the source system.\nNOLOGFILE             Do not write logfile (N).\nPARALLEL              Change the number of active workers for current job.\nPARFILE               Specify parameter file.\nQUERY                 Predicate clause used to export a subset of a table.\nSCHEMAS               List of schemas to export (login schema).\nSTATUS                Frequency (secs) job status is to be monitored where\n                      the default (0) will show new status when available.\nTABLES                Identifies a list of tables to export - one schema only.\nTABLESPACES           Identifies a list of tablespaces to export.\nTRANSPORT_FULL_CHECK  Verify storage segments of all tables (N).\nTRANSPORT_TABLESPACES List of tablespaces from which metadata will be unloaded.\nVERSION               Version of objects to export where valid keywords are:\n                      (COMPATIBLE), LATEST, or any valid database version.\n\nThe following commands are valid while in interactive mode.\nNote: abbreviations are allowed\n\nCommand               Description\n------------------------------------------------------------------------------\nADD_FILE              Add dumpfile to dumpfile set.\n                      ADD_FILE=dumpfile-name\nCONTINUE_CLIENT       Return to logging mode. Job will be re-started if idle.\nEXIT_CLIENT           Quit client session and leave job running.\nHELP                  Summarize interactive commands.\nKILL_JOB              Detach and delete job.\nPARALLEL              Change the number of active workers for current job.\n                      PARALLEL=.\nSTART_JOB             Start\/resume current job.\nSTATUS                Frequency (secs) job status is to be monitored where\n                      the default (0) will show new status when available.\n                      STATUS=[interval]\nSTOP_JOB              Orderly shutdown of job execution and exits the client.\n                      STOP_JOB=IMMEDIATE performs an immediate shutdown of the\n                      Data Pump job.\n<\/pre>\n
Below is the help text for impdp command.<\/p>\n
\nImport: Release 10.1.0.4.0 - Production on Monday, 24 March, 2008 11:26\n\nCopyright (c) 2003, Oracle.  All rights reserved.\n\n\nThe Data Pump Import utility provides a mechanism for transferring data objects\nbetween Oracle databases. The utility is invoked with the following command:\n\n     Example: impdp scott\/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp\n\nYou can control how Import runs by entering the 'impdp' command followed\nby various parameters. To specify parameters, you use keywords:\n\n     Format:  impdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)\n     Example: impdp scott\/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp\n\nUSERID must be the first parameter on the command line.\n\nKeyword               Description (Default)\n------------------------------------------------------------------------------\nATTACH                Attach to existing job, e.g. ATTACH [=job name].\nCONTENT               Specifies data to load where the valid keywords are:\n                      (ALL), DATA_ONLY, and METADATA_ONLY.\nDIRECTORY             Directory object to be used for dump, log, and sql files.\nDUMPFILE              List of dumpfiles to import from (expdat.dmp),\n                      e.g. DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.\nESTIMATE              Calculate job estimates where the valid keywords are:\n                      (BLOCKS) and STATISTICS.\nEXCLUDE               Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.\nFLASHBACK_SCN         SCN used to set session snapshot back to.\nFLASHBACK_TIME        Time used to get the SCN closest to the specified time.\nFULL                  Import everything from source (Y).\nHELP                  Display help messages (N).\nINCLUDE               Include specific object types, e.g. INCLUDE=TABLE_DATA.\nJOB_NAME              Name of import job to create.\nLOGFILE               Log file name (import.log).\nNETWORK_LINK          Name of remote database link to the source system.\nNOLOGFILE             Do not write logfile.\nPARALLEL              Change the number of active workers for current job.\nPARFILE               Specify parameter file.\nQUERY                 Predicate clause used to import a subset of a table.\nREMAP_DATAFILE        Redefine datafile references in all DDL statements.\nREMAP_SCHEMA          Objects from one schema are loaded into another schema.\nREMAP_TABLESPACE      Tablespace object are remapped to another tablespace.\nREUSE_DATAFILES       Tablespace will be initialized if it already exists (N).\nSCHEMAS               List of schemas to import.\nSKIP_UNUSABLE_INDEXES Skip indexes that were set to the Index Unusable state.\nSQLFILE               Write all the SQL DDL to a specified file.\nSTATUS                Frequency (secs) job status is to be monitored where\n                      the default (0) will show new status when available.\nSTREAMS_CONFIGURATION Enable the loading of Streams metadata\nTABLE_EXISTS_ACTION   Action to take if imported object already exists.\n                      Valid keywords: (SKIP), APPEND, REPLACE and TRUNCATE.\nTABLES                Identifies a list of tables to import.\nTABLESPACES           Identifies a list of tablespaces to import.\nTRANSFORM             Metadata transform to apply (Y\/N) to specific objects.\n                      Valid transform keywords: SEGMENT_ATTRIBUTES and STORAGE.\n                      ex. TRANSFORM=SEGMENT_ATTRIBUTES:N:TABLE.\nTRANSPORT_DATAFILES   List of datafiles to be imported by transportable mode.\nTRANSPORT_FULL_CHECK  Verify storage segments of all tables (N).\nTRANSPORT_TABLESPACES List of tablespaces from which metadata will be loaded.\n                      Only valid in NETWORK_LINK mode import operations.\nVERSION               Version of objects to export where valid keywords are:\n                      (COMPATIBLE), LATEST, or any valid database version.\n                      Only valid for NETWORK_LINK and SQLFILE.\n\nThe following commands are valid while in interactive mode.\nNote: abbreviations are allowed\n\nCommand               Description (Default)\n------------------------------------------------------------------------------\nCONTINUE_CLIENT       Return to logging mode. Job will be re-started if idle.\nEXIT_CLIENT           Quit client session and leave job running.\nHELP                  Summarize interactive commands.\nKILL_JOB              Detach and delete job.\nPARALLEL              Change the number of active workers for current job.\n                      PARALLEL=.\nSTART_JOB             Start\/resume current job.\n                      START_JOB=SKIP_CURRENT will start the job after skipping\n                      any action which was in progress when job was stopped.\nSTATUS                Frequency (secs) job status is to be monitored where\n                      the default (0) will show new status when available.\n                      STATUS=[interval]\nSTOP_JOB              Orderly shutdown of job execution and exits the client.\n                      STOP_JOB=IMMEDIATE performs an immediate shutdown of the\n                      Data Pump job.\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
Data Pump is a new (since 11g) export\/import utility with efficient server-side processing.  This note introduces the very basics on its usage.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[20],"tags":[],"class_list":["post-6","post","type-post","status-publish","format-standard","hentry","category-oracle"],"_links":{"self":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts\/6","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/comments?post=6"}],"version-history":[{"count":0,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/posts\/6\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/media?parent=6"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/categories?post=6"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dev-notes.com\/blog\/wp-json\/wp\/v2\/tags?post=6"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}