expdp help =y
[oracle@sample ~]$ expdp help=y
Export: Release 19.0.0.0.0 - Production on Thu Aug 5 21:33:52 2021
Version 19.3.0.0.0
Copyright (c) 1982, 2019, 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.
Full Database Export
[oracle@oracle backup]$ expdp system/password directory=dumpdir dumpfile=full.dmp
logfile=full.log full=y Master table "INDIA"."SYS_EXPORT_FULL_01" successfully loaded/unloaded ****************************************************************************** Dump file set for INDIA.SYS_EXPORT_FULL_01 is: /home/oracle/backup/full.dmp Job "INDIA"."SYS_EXPORT_FULL_01" successfully completed at Fri Aug 6 00:19:43 2021
elapsed 0 00:02:41
Include and Exclude
Include
expdp testdb/testdb schemas=apple include=TABLE:"IN ('test', 'emp')"
directory=dumpdir dumpfile=include.dmp logfile=include.log
Exclude
expdp testdb/testdb schemas=apple exclude=TABLE:"= 'employee'"
directory=dumpdir dumpfile=exclude.dmp logfile=exclude.log
Content {Meta_data_only / data_only}
Meta_data only
expdp system/password schemas=apple directory=dump_dir dumpfile=apple_meta.dmp
logfile=apple_meta.log content=METADATA_ONLY
Data_only
expdp system/password schemas=apple directory=dump_dir dumpfile=apple_data.dmp
logfile=apple_data.log content=DATA_ONLY
Query
expdp system/password full=Y directory=dump_dir dumpfile=full.dmp logfile=full.log
query='apple.EMP:"WHERE no=1",orange.DEPT:"WHERE no=2"'
Flashback_Scn
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
3795360
expdp system/password directory=dumpdir dumpfile=flash.dmp logfile=flash.log
flashback_scn=3795360
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/home/oracle/backup/flash.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Aug 6 00:40:13 2021
elapsed 0 00:00:31
Flashback_Time
expdp directory=dumpdir
dumpfile=flash_time.dmp
logfile=flash_time.log'
tables=india.tamil
flashback_time=\"to_timestamp(\'08-06-2021 00:48:00\', \'DD-MM-YYYY HH24:MI:SS\')\"
PARFILE
[oratest@oracle ~]$ cat > schema_par directory=dump dumpfile=schema_par.dmp logfile=schema.log schemas=india tables=apple
[oratest@oracle ~]$ expdp system/oracle PARFILE=schema_par
TABLES
SQL> create or replace directory dump_dir as '/home/oracle/backup'; Directory created.
[oracle@oracle ~]$ expdp directory=dump_dir dumpfile=india.dmp logfile=india.log
tables=india.apple Export: Release 19.0.0.0.0 - Production on Wed Aug 4 00:03:28 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Username: test Password:
Tablespace
exp system/password directory=dump_dir dumpfile=tablespace.dmp logfile=tablespace.log
tablespaces=system,india
Attach
The ATTACH parameter is used to attach the interrupted job and
status of data pump operation.
[oracle@oracle ~]$ expdp system/oracle attach=SYS_EXPORT_SCHEMA_01 Export: Release 19.0.0.0.0 - Production on Mon Aug 6 22:19:02 2021 Version 19.3.0.0.0 Copyright (c) 1982, 2021, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Job: SYS_EXPORT_SCHEMA_01 Owner: SYSTEM
Directory
Directory Parameter is used for Dump file and Log file.
Directory Parameter is used for Dump file and Log file. Sql > create or replace directory dumpdir as ‘/backup/export’; Directory created. Sql > grant read,write on directory dumpdir to apple; Grant succeeded. Sql > exit [oracle@oracle ~]$ expdp directory=dumpdir dumpfile=full.dmp logfile=full.log
tables=emp
Transport_Tablespaces
Use the TRANSPORT_TABLESPACES parameter to specify a list of tablespace names for
which object metadata will be exported from the source database into the
target database. Keep INDIA Tablespace in Read only mode.
SQL> alter tablespace INDIA read only; Tablespace altered.
SQL> select name from v$datafile; NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/ORCL/system01.dbf /u01/app/oracle/oradata/ORCL/sysaux01.dbf /u01/app/oracle/oradata/ORCL/undotbs01.dbf /u01/app/oracle/oradata/ORCL/india01.dbf /u01/app/oracle/oradata/ORCL/users01.dbf
[oracle@oracle]$ scp /u01/app/oracle/oradata/proddb/india01.dbf
[email protected]/u01/app/oracle/oradata/TEST/datafile
[oracle@oracle export]$ expdp DUMPFILE=india.dmp LOGFILE=india.logDIRECTORY=dumpdir
TRANSPORT_TABLESPACES=india Export: Release 19.0.0.0.0 - Production on Thu Aug 6 23:07:59 2018 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Username: system Password: ****************************************************************************** Datafiles required for transportable tablespace INDIA: /u01/app/oracle/oradata/proddb/india01.dbf Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at Thu
Aug 6 23:08:48 2021
Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8
