How to List All Export and Import Commands in Oracle

 

#List export import keywords and their description C:\Windows\System32>expdp help=y C:\Windows\System32>impdp help=y C:\Windows\System32>exp help=y C:\Windows\System32>imp help=y Example: C:\Windows\System32>expdp help=y Export: Release 19.0.0.0.0 - Production on Thu Mar 7 18:46:00 2024 Version 19.16.0.0.0 Copyright (c) 1982, 2022, Oracle and/or its affiliates.  All rights reserved. The Data Pump export utility provides a mechanism for transferring data objects between Oracle databases. The utility is invoked with the following command:    Example: expdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp You can control how Export runs by entering the 'expdp' command followed by various parameters. To specify parameters, you use keywords:    Format:  expdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)    Example: expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=scott                or TABLES=(T1:P1,T1:P2), if T1 is partitioned table USERID must be the first parameter on the command line. ------------------------------------------------------------------------------ The available keywords and their descriptions follow. Default values are listed within square brackets. ABORT_STEP Stop the job after it is initialized or at the indicated object. Valid values are -1 or N where N is zero or greater. N corresponds to the object's process order number in the master table. ACCESS_METHOD Instructs Export to use a particular method to unload data. Valid keyword values are: [AUTOMATIC], DIRECT_PATH and EXTERNAL_TABLE. ATTACH Attach to an existing job. For example, ATTACH=job_name. CLUSTER Utilize cluster resources and distribute workers across the Oracle RAC [YES]. COMPRESSION Reduce the size of a dump file. Valid keyword values are: ALL, DATA_ONLY, [METADATA_ONLY] and NONE. COMPRESSION_ALGORITHM Specify the compression algorithm that should be used. Valid keyword values are: [BASIC], LOW, MEDIUM and HIGH. CONTENT Specifies data to unload. Valid keyword values are: [ALL], DATA_ONLY and METADATA_ONLY. DATA_OPTIONS Data layer option flags. Valid keyword values are: GROUP_PARTITION_TABLE_DATA, VERIFY_STREAM_FORMAT and XML_CLOBS. DIRECTORY Directory object to be used for dump and log files. DUMPFILE Specify list of destination dump file names [expdat.dmp]. For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp. ENCRYPTION Encrypt part or all of a dump file. Valid keyword values are: ALL, DATA_ONLY, ENCRYPTED_COLUMNS_ONLY, METADATA_ONLY and NONE. ENCRYPTION_ALGORITHM Specify how encryption should be done. Valid keyword values are: [AES128], AES192 and AES256. ENCRYPTION_MODE Method of generating encryption key. Valid keyword values are: DUAL, PASSWORD and [TRANSPARENT]. ENCRYPTION_PASSWORD Password key for creating encrypted data within a dump file. ENCRYPTION_PWD_PROMPT Specifies whether to prompt for the encryption password [NO]. Terminal echo will be suppressed while standard input is read. ESTIMATE Calculate job estimates. Valid keyword values are: [BLOCKS] and STATISTICS. ESTIMATE_ONLY Calculate job estimates without performing the export [NO]. EXCLUDE Exclude specific object types. For example, EXCLUDE=SCHEMA:"='HR'". FILESIZE Specify the size of each dump file in units of bytes. FLASHBACK_SCN SCN used to reset session snapshot. FLASHBACK_TIME Time used to find the closest corresponding SCN value. FULL Export entire database [NO]. HELP Display Help messages [NO]. INCLUDE Include specific object types. For example, INCLUDE=TABLE_DATA. JOB_NAME Name of export job to create. KEEP_MASTER Retain the master table after an export job that completes successfully [NO]. LOGFILE Specify log file name [export.log]. LOGTIME Specifies that messages displayed during export operations be timestamped. Valid keyword values are: ALL, [NONE], LOGFILE and STATUS. METRICS Report additional job information to the export log file [NO]. NETWORK_LINK Name of remote database link to the source system. NOLOGFILE Do not write log file [NO]. PARALLEL Change the number of active workers for current job. PARFILE Specify parameter file name. QUERY Predicate clause used to export a subset of a table. For example, QUERY=employees:"WHERE department_id > 10". REMAP_DATA Specify a data conversion function. For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO. REUSE_DUMPFILES Overwrite destination dump file if it exists [NO]. SAMPLE Percentage of data to be exported. SCHEMAS List of schemas to export [login schema]. SERVICE_NAME Name of an active Service and associated resource group to constrain Oracle RAC resources. SOURCE_EDITION Edition to be used for extracting metadata. STATUS Frequency (secs) job status is to be monitored where the default [0] will show new status when available. TABLES Identifies a list of tables to export. For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995. TABLESPACES Identifies a list of tablespaces to export. TRANSPORTABLE Specify whether transportable method can be used. Valid keyword values are: ALWAYS and [NEVER]. TRANSPORT_FULL_CHECK Verify storage segments of all tables [NO]. TRANSPORT_TABLESPACES List of tablespaces from which metadata will be unloaded. VERSION Version of objects to export. Valid keyword values are: [COMPATIBLE], LATEST or any valid database version. VIEWS_AS_TABLES Identifies one or more views to be exported as tables. For example, VIEWS_AS_TABLES=HR.EMP_DETAILS_VIEW. ------------------------------------------------------------------------------ The following commands are valid while in interactive mode. Note: abbreviations are allowed. ADD_FILE Add dumpfile to dumpfile set. CONTINUE_CLIENT Return to logging mode. Job will be restarted if idle. EXIT_CLIENT Quit client session and leave job running. FILESIZE Default filesize (bytes) for subsequent ADD_FILE commands. HELP Summarize interactive commands. KILL_JOB Detach and delete job. PARALLEL Change the number of active workers for current job. REUSE_DUMPFILES Overwrite destination dump file if it exists [NO]. START_JOB Start or resume current job. Valid keyword values are: SKIP_CURRENT. STATUS Frequency (secs) job status is to be monitored where the default [0] will show new status when available. STOP_JOB Orderly shutdown of job execution and exits the client. Valid keyword values are: IMMEDIATE. STOP_WORKER Stops a hung or stuck worker. TRACE Set trace/debug flags for the current job. C:\Windows\System32>impdp help=y Import: Release 19.0.0.0.0 - Production on Thu Mar 7 18:47:13 2024 Version 19.16.0.0.0 Copyright (c) 1982, 2022, Oracle and/or its affiliates.  All rights reserved. The Data Pump Import utility provides a mechanism for transferring data objects between Oracle databases. The utility is invoked with the following command:      Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp You can control how Import runs by entering the 'impdp' command followed by various parameters. To specify parameters, you use keywords:      Format:  impdp KEYWORD=value or KEYWORD=(value1,value2,...,valueN)      Example: impdp scott/tiger DIRECTORY=dmpdir DUMPFILE=scott.dmp USERID must be the first parameter on the command line. ------------------------------------------------------------------------------ The available keywords and their descriptions follow. Default values are listed within square brackets. ABORT_STEP Stop the job after it is initialized or at the indicated object. Valid values are -1 or N where N is zero or greater. N corresponds to the object's process order number in the master table. ACCESS_METHOD Instructs Import to use a particular method to load data. Valid keyword values are: [AUTOMATIC], CONVENTIONAL, DIRECT_PATH, EXTERNAL_TABLE, and INSERT_AS_SELECT. ATTACH Attach to an existing job. For example, ATTACH=job_name. CLUSTER Utilize cluster resources and distribute workers across the Oracle RAC [YES]. CONTENT Specifies data to load. Valid keywords are: [ALL], DATA_ONLY and METADATA_ONLY. DATA_OPTIONS Data layer option flags. Valid keywords are: DISABLE_APPEND_HINT, ENABLE_NETWORK_COMPRESSION, REJECT_ROWS_WITH_REPL_CHAR, SKIP_CONSTRAINT_ERRORS, CONTINUE_LOAD_ON_FORMAT_ERROR, TRUST_EXISTING_TABLE_PARTITIONS and VALIDATE_TABLE_DATA. DIRECTORY Directory object to be used for dump, log and SQL files. DUMPFILE List of dump files to import from [expdat.dmp]. For example, DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp. ENCRYPTION_PASSWORD Password key for accessing encrypted data within a dump file. Not valid for network import jobs. ENCRYPTION_PWD_PROMPT Specifies whether to prompt for the encryption password [NO]. Terminal echo is suppressed while standard input is read. ESTIMATE Calculate network job estimates. Valid keywords are: [BLOCKS] and STATISTICS. EXCLUDE Exclude specific object types. For example, EXCLUDE=SCHEMA:"='HR'". FLASHBACK_SCN SCN used to reset session snapshot. FLASHBACK_TIME Time used to find the closest corresponding SCN value. FULL Import everything from source [YES]. HELP Display help messages [NO]. INCLUDE Include specific object types. For example, INCLUDE=TABLE_DATA. JOB_NAME Name of import job to create. KEEP_MASTER Retain the master table after an import job that completes successfully [NO]. LOGFILE Log file name [import.log]. LOGTIME Specifies that messages displayed during import operations be timestamped. Valid keyword values are: ALL, [NONE], LOGFILE and STATUS. MASTER_ONLY Import just the master table and then stop the job [NO]. METRICS Report additional job information to the import log file [NO]. NETWORK_LINK Name of remote database link to the source system. NOLOGFILE Do not write log file [NO]. PARALLEL Change the number of active workers for current job. PARFILE Specify parameter file. PARTITION_OPTIONS Specify how partitions should be transformed. Valid keywords are: DEPARTITION, MERGE and [NONE]. QUERY Predicate clause used to import a subset of a table. For example, QUERY=employees:"WHERE department_id > 10". REMAP_DATA Specify a data conversion function. For example, REMAP_DATA=EMP.EMPNO:REMAPPKG.EMPNO. REMAP_DATAFILE Redefine data file references in all DDL statements. REMAP_SCHEMA Objects from one schema are loaded into another schema. REMAP_TABLE Table names are remapped to another table. For example, REMAP_TABLE=HR.EMPLOYEES:EMPS. REMAP_TABLESPACE Tablespace objects are remapped to another tablespace. REUSE_DATAFILES Tablespace will be initialized if it already exists [NO]. SCHEMAS List of schemas to import. SERVICE_NAME Name of an active service and associated resource group to constrain Oracle RAC resources. SKIP_UNUSABLE_INDEXES Skip indexes that were set to the Index Unusable state. SOURCE_EDITION Edition to be used for extracting metadata. SQLFILE Write all the SQL DDL to a specified file. STATUS Frequency (secs) job status is to be monitored where the default [0] will show new status when available. STREAMS_CONFIGURATION Enable the loading of Streams metadata [YES]. TABLE_EXISTS_ACTION Action to take if imported object already exists. Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE. TABLES Identifies a list of tables to import. For example, TABLES=HR.EMPLOYEES,SH.SALES:SALES_1995. TABLESPACES Identifies a list of tablespaces to import. TARGET_EDITION Edition to be used for loading metadata. TRANSFORM Metadata transform to apply to applicable objects. Valid keywords are: DISABLE_ARCHIVE_LOGGING, INMEMORY, INMEMORY_CLAUSE, LOB_STORAGE, OID, PCTSPACE, SEGMENT_ATTRIBUTES, SEGMENT_CREATION, STORAGE, and TABLE_COMPRESSION_CLAUSE. TRANSPORTABLE Options for choosing transportable data movement. Valid keywords are: ALWAYS and [NEVER]. Only valid in NETWORK_LINK mode import operations. TRANSPORT_DATAFILES List of data files to be imported by transportable mode. TRANSPORT_FULL_CHECK Verify storage segments of all tables [NO]. Only valid in NETWORK_LINK mode import operations. TRANSPORT_TABLESPACES List of tablespaces from which metadata is loaded. Only valid in NETWORK_LINK mode import operations. VERSION Version of objects to import. Valid keywords are: [COMPATIBLE], LATEST, or any valid database version. Only valid for NETWORK_LINK and SQLFILE. VIEWS_AS_TABLES Identifies one or more views to be imported as tables. For example, VIEWS_AS_TABLES=HR.EMP_DETAILS_VIEW. Note that in network import mode, a table name is appended to the view name. ------------------------------------------------------------------------------ The following commands are valid while in interactive mode. Note: abbreviations are allowed. CONTINUE_CLIENT Return to logging mode. Job will be restarted if idle. EXIT_CLIENT Quit client session and leave job running. HELP Summarize interactive commands. KILL_JOB Detach and delete job. PARALLEL Change the number of active workers for current job. START_JOB Start or resume current job. Valid keywords are: SKIP_CURRENT. STATUS Frequency (secs) job status is to be monitored where the default [0] will show new status when available. STOP_JOB Orderly shutdown of job execution and exits the client. Valid keywords are: IMMEDIATE. STOP_WORKER Stops a hung or stuck worker. TRACE Set trace/debug flags for the current job. C:\Windows\System32>exp help=y Export: Release 19.0.0.0.0 - Production on Thu Mar 7 18:48:00 2024 Version 19.16.0.0.0 Copyright (c) 1982, 2022, Oracle and/or its affiliates.  All rights reserved. You can let Export prompt you for parameters by entering the EXP command followed by your username/password:      Example: EXP SCOTT/TIGER Or, you can control how Export runs by entering the EXP command followed by various arguments. To specify parameters, you use keywords:      Format:  EXP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)      Example: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)                or TABLES=(T1:P1,T1:P2), if T1 is partitioned table USERID must be the first parameter on the command line. Keyword    Description (Default)      Keyword      Description (Default) -------------------------------------------------------------------------- USERID     username/password          FULL         export entire file (N) BUFFER     size of data buffer        OWNER        list of owner usernames FILE       output files (EXPDAT.DMP)  TABLES       list of table names COMPRESS   import into one extent (Y) RECORDLENGTH length of IO record GRANTS     export grants (Y)          INCTYPE      incremental export type INDEXES    export indexes (Y)         RECORD       track incr. export (Y) DIRECT     direct path (N)            TRIGGERS     export triggers (Y) LOG        log file of screen output  STATISTICS   analyze objects (ESTIMATE) ROWS       export data rows (Y)       PARFILE      parameter filename CONSISTENT cross-table consistency(N) CONSTRAINTS  export constraints (Y) OBJECT_CONSISTENT    transaction set to read only during object export (N) FEEDBACK             display progress every x rows (0) FILESIZE             maximum size of each dump file FLASHBACK_SCN        SCN used to set session snapshot back to FLASHBACK_TIME       time used to get the SCN closest to the specified time QUERY                select clause used to export a subset of a table RESUMABLE            suspend when a space related error is encountered(N) RESUMABLE_NAME       text string used to identify resumable statement RESUMABLE_TIMEOUT    wait time for RESUMABLE TTS_FULL_CHECK       perform full or partial dependency check for TTS TABLESPACES          list of tablespaces to export TRANSPORT_TABLESPACE export transportable tablespace metadata (N) TEMPLATE             template name which invokes iAS mode export Export terminated successfully without warnings. C:\Windows\System32>imp help=y Import: Release 19.0.0.0.0 - Production on Thu Mar 7 18:48:48 2024 Version 19.16.0.0.0 Copyright (c) 1982, 2022, Oracle and/or its affiliates.  All rights reserved. You can let Import prompt you for parameters by entering the IMP command followed by your username/password:      Example: IMP SCOTT/TIGER Or, you can control how Import runs by entering the IMP command followed by various arguments. To specify parameters, you use keywords:      Format:  IMP KEYWORD=value or KEYWORD=(value1,value2,...,valueN)      Example: IMP SCOTT/TIGER IGNORE=Y TABLES=(EMP,DEPT) FULL=N                or TABLES=(T1:P1,T1:P2), if T1 is partitioned table USERID must be the first parameter on the command line. Keyword  Description (Default)       Keyword      Description (Default) -------------------------------------------------------------------------- USERID   username/password           FULL         import entire file (N) BUFFER   size of data buffer         FROMUSER     list of owner usernames FILE     input files (EXPDAT.DMP)    TOUSER       list of usernames SHOW     just list file contents (N) TABLES       list of table names IGNORE   ignore create errors (N)    RECORDLENGTH length of IO record GRANTS   import grants (Y)           INCTYPE      incremental import type INDEXES  import indexes (Y)          COMMIT       commit array insert (N) ROWS     import data rows (Y)        PARFILE      parameter filename LOG      log file of screen output   CONSTRAINTS  import constraints (Y) DESTROY                overwrite tablespace data file (N) INDEXFILE              write table/index info to specified file SKIP_UNUSABLE_INDEXES  skip maintenance of unusable indexes (N) FEEDBACK               display progress every x rows(0) TOID_NOVALIDATE        skip validation of specified type ids FILESIZE               maximum size of each dump file STATISTICS             import precomputed statistics (always) RESUMABLE              suspend when a space related error is encountered(N) RESUMABLE_NAME         text string used to identify resumable statement RESUMABLE_TIMEOUT      wait time for RESUMABLE COMPILE                compile procedures, packages, and functions (Y) STREAMS_CONFIGURATION  import streams general metadata (Y) STREAMS_INSTANTIATION  import streams instantiation metadata (N) DATA_ONLY              import only data (N) The following keywords only apply to transportable tablespaces TRANSPORT_TABLESPACE import transportable tablespace metadata (N) TABLESPACES tablespaces to be transported into database DATAFILES datafiles to be transported into database TTS_OWNERS users that own data in the transportable tablespace set Import terminated successfully without warnings.