Export backup is one of the great feature introduced by Oracle, we can take backup of whole database,single schema, single table or single row of the table by this utility.
Below is simple example for taking export backup of single schema/user:
expdp username@db_name DIRECTORY=directory_name DUMPFILE=dumpfile_name.dmp logfile=logfile_name.log schemas=username
We can use two different directories for dumpfile and logfile to store as shown below:
dumpfile= datapump_dir:expdp_fi_mgr_1010_tables%U.dmp dumpfile= datapump_dir1:expdp_fi_mgr_1010_tables.log
For taking single table backup use tables parameter :
If you wanted to export table from specific date then use below parameter:
query=table1:"where column_name > '01-JAN-2015'",table2:"where coulumn_name >= '01-JAN-2016'";
There are many parameter of expdp for taking backup. It is very important to take into consideration of expdp parameter because whatever you will export you have import into other schema or database according to user or environment requirement.
Say If your export is running and you need to see what all things your command is doing you can do it by following way
First find out the job name of your expdp command :
SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs; OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ---------- -------------------- ---------- ---------- ------------ SYSTEM EXP_BACKUP EXPORT FULL EXECUTING
From above EXP_BACKUP is Job_name, you can assign this name in expdp commamd or if you don’t then this name will be assigned by Oracle itself.
Now user attach parameter to see the export backup stats:
[Test@test1 ~]$ expdp attach=EXP_BACKUP Username: / as sysdba Export: Release 18.104.22.168.0 - Production on Tue 06 June, 2016 13:32:33 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Password: Connected to: Oracle Database 11g Enterprise Edition Release 22.214.171.124.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Job: EXP_BACKUP Owner: SYSTEM Operation: EXPORT Creator Privs: TRUE GUID: Code_here Start Time: Tuesday, 06 June, 2016 13:32:33 Mode: FULL Instance: Instance_name Max Parallelism: 1 EXPORT Job Parameters: Parameter Name Parameter Value: CLIENT_COMMAND expdp command will display here State: EXECUTING Bytes Processed: 0 Current Parallelism: 1 Job Error Count: 0 Dump File: dump_file.dmp path will display here bytes written: 10,446 Worker 1 Status: Process Name: DW00 State: EXECUTING Object Schema: Username Object Type: object_name Completed Objects: 11 Total Objects: 11 Worker Parallelism: 1
Above will give you all details of expdp command what object it is curently exporting , how many bytes completed ,objects completed, etc.. Now it is showing only 1 Worker, if you use more parallel then that many number of Worker will increase and what they are doing will too get displayed.