Query(Row) Level Export data pump and Import data pump

Description:-

Allows you to specify a query clause that is used to filter the data that gets export and import.When the query is to be applied to a specific table, a colon must separate the table name from the query clause. More than one table-specific query can be specified, but only one query can be specified per table.Now let’ see how to particular table row export and import using data pump.

Step 1:Check table rows and export the particular table row.

SQL> show user

USER is “REMO”

SQL> select * from bb;

SQL> insert into bb values(22);

1 row created.

SQL> insert into bb values(23);

1 row created.

SQL> insert into bb values(24);

1 row created.

SQL> insert into bb values(25);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from bb;

ID

———-

22

23

24

25

SQL> host

[oracle@trichydoyen ~]$ expdp directory=dir dumpfile=remo_bb_row.dmp log=remo_bb_row.log tables=’REMO.BB’ query=\”where id=25\”

Export: Release 12.2.0.1.0 - Production on Sat Jan 11 14:30:53 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Username: remo/remo

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Legacy Mode Active due to the following parameters:

Legacy Mode Parameter: "log=remo_bb_row.log" Location: Command Line, Replaced with: "logfile=remo_bb_row.log"

Legacy Mode has set reuse_dumpfiles=true parameter.

Starting "REMO"."SYS_EXPORT_TABLE_01":  remo/******** directory=dir dumpfile=remo_bb_row.dmp logfile=remo_bb_row.log tables=REMO.BB query="where id=25" reuse_dumpfiles=true

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Processing object type TABLE_EXPORT/TABLE/TABLE

. . exported "REMO"."BB"                                 5.062 KB       1 rows

Master table "REMO"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for REMO.SYS_EXPORT_TABLE_01 is:

  /u01/exportfile/remo_bb_row.dmp

Job "REMO"."SYS_EXPORT_TABLE_01" successfully completed at Sat Jan 11 14:31:51 2020 elapsed 0 00:00:47

Step 2: Drop the export table row

[oracle@trichydoyen ~]$ !sq

sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jan 11 14:32:00 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> conn remo/remo

Connected.

SQL> select * from bb;

ID

———-

22

23

24

25

SQL> delete from bb where id=25;

1 row deleted.

SQL> select * from bb;

ID

———-

22

23

24

SQL> commit;

Commit complete.

SQL> select * from bb;

ID

———-

22

23

24

Step 3:Now import the mentioned table row and check the table row.

SQL> host

[oracle@trichydoyen ~]$ impdp directory=dir dumpfile=remo_bb_row.dmp  fromuser=’REMO’ touser=’REMO’ tables=’BB’ table_exists_action=APPEND

Import: Release 12.2.0.1.0 - Production on Sat Jan 11 14:42:46 2020

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

Username: remo/remo

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

Legacy Mode Active due to the following parameters:

Legacy Mode Parameter: "fromuser=REMO" Location: Command Line, Replaced with: "remap_schema"

Master table "REMO"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded

Starting "REMO"."SYS_IMPORT_TABLE_01":  remo/******** directory=dir dumpfile=remo_bb_row.dmp remap_schema=REMO:REMO tables=BB table_exists_action=APPEND

Processing object type TABLE_EXPORT/TABLE/TABLE

Table "REMO"."BB" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "REMO"."BB"                                 5.062 KB       1 rows

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Job "REMO"."SYS_IMPORT_TABLE_01" successfully completed at Sat Jan 11 14:43:00 2020 elapsed 0 00:00:07

[oracle@trichydoyen ~]$ !sq

sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Sat Jan 11 14:43:56 2020

Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 – 64bit Production

SQL> conn remo/remo

Connected.

SQL> select * from bb;

ID

———-

22

23

24

25