expdp / impdp

create directory mydir as '/home/oracle/dpdump';
grant read,write on directory mydir to asanga;


expdp asanga/asa dumpfile=mydir:test.dmp schemas=asanga flashback_scn=14073963 logfile=mydir:expdp.log


expdp asanga/asa directory=mydir dumpfile=test.dmp schemas=asanga flashback_scn=14073963 logfile=expdp.log


impdp asanga/asa dumpfile=mydir:test.dmp logfile=mydir:impdp.log full=y
impdp asanga/asa directory=mydir dumpfile=test.dmp logfile=impdp.log full=y

to remap to different schema and generate new OID for type objects.
impdp directory=mydir dumpfile=remap.dmp logfile=rmap.log full=y remap_schema=sbx:sbxway transform=oid:n:type


FLASHBACK_TIME


This Export parameter specifies a timestamp of the form "YYYY-MM-DD HH24:MI:SS". Export finds the SCN that most closely matches the specified timestamp.

flashback_time='"2008-02-25 15:30:00"'

to get the date from the system on linux
flashback_time=\"$(date +%F' '%T)\"

Default value: none


Can specify the FLASHBACK_TIME parameter on the command line.


flashback_time=\"TO_TIMESTAMP\(\'19-06-2004 13:24:26\', \'DD-MM-YYYY HH24:MI:SS\'\)\"

You can also specifiy the FLASHBACK_TIME parameter in a parameter file.
When using the TO_TIMESTAMP function in a parameter file in Oracle10g,
use double quotes.
E.g.:
FLASHBACK_TIME="TO_TIMESTAMP('19-06-2004 13:24:26', 'DD-MM-YYYY HH24:MI:SS')"

Exclude Objects

expdp hr/hr EXCLUDE=INDEX:\"LIKE \'EMP%\'\" DUMPFILE=dpump_dir1:exp.dmp

Importing only a subset of rows
Using the query option it is possible to specify a select statment to a table or all the tables in schema. If the table is not specified then by default it will be applied to all the tables in schema.

create table x (a number);

Table created.

SQL> begin
  2  for i in 1 .. 100
  3  loop
  4  insert into x values(i);
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> commit;

SQL>  create table y as select * from x;

Table created.

SQL> delete from y where mod(a,3)=0;

33 rows deleted.

SQL> commit;

Two tables X and Y has 100 and 67 rows now. Export the two tables

Starting "ASANGA"."SYS_EXPORT_SCHEMA_01":  asanga/******** directory=dumpdir dumpfile=test.dmp logfile=test.log schemas=asanga
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
. . exported "ASANGA"."X"                                5.585 KB     100 rows
. . exported "ASANGA"."Y"                                5.359 KB      67 rows
Master table "ASANGA"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************

Drop the tables from schema before import

SQL> drop table x;

Table dropped.

SQL> drop table y;

Table dropped.

Import only the rows where value of column A is between 10 and 20 on table X and only rows where value is even.

impdp asanga/asa directory=dumpdir dumpfile=test.dmp logfile=imp.log query=X:'"where a between 10 and 20"',
Y:'"where mod(a,2)=0"'
...
...
Master table "ASANGA"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "ASANGA"."SYS_IMPORT_FULL_01":  asanga/******** directory=dumpdir dumpfile=test.dmp logfile=imp.log query=X:"where a between 10 and 20",
Y:"where mod(a,2)=0"
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"ASANGA" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "ASANGA"."X"                                5.585 KB      11 out of 100 rows
. . imported "ASANGA"."Y"                                5.359 KB      34 out of 67 rows
Job "ASANGA"."SYS_IMPORT_FULL_01" completed with 1 error(s) at 13:33:47