Table Level Export & Import in Oracle 19c using Data pump.

DESCRIPTION:

In this Blog, we will learn how to use the Oracle Data Pump Export and Import utility in Oracle 19c database

A table-mode import is specified using the TABLES parameter. In table mode, only the specified set of tables, partitions, and their dependent objects are loaded. The source can be a full, schema, table space, or table-mode export dump file set or another database.

Create a table with an encrypted column and insert rows:

SQL> select * from table01;

ID NAME

---------- ------------------------------

 1 aaa

 2 bbb

 3 ccc

 4 ddd

 5 eee

Before start the Export Process, we need to create a Directory which is used to store the dump file.Data Pump is a server-based technology, so it typically deals with directory objects pointing to physical directories on the database server.

Dumpfile: The name of the exported dump file.

Logfile: The name of the directory object that is used to store the log file of the export operation, i.e. the name of the log file

Export the dump file of the table:

Directory Name:MY_Dir

Table Name: Table01

[oracle@oracle19c ~]$ expdp directory=My_Dir dumpfile=table01.dmp logfile=table01.log tables='table01';

Export: Release 19.0.0.0.0 - Production on Mon Jan 25 08:39:53 2021

Version 19.3.0.0.0

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

Username: data

Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Starting "DATA"."SYS_EXPORT_TABLE_01":  data/******** directory=My_Dir dumpfile=table01.dmp logfile=table01.log tables=table01

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 "DATA"."TABLE01"                            5.515 KB       5 rows

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

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

Dump file set for DATA.SYS_EXPORT_TABLE_01 is:

  /u01/app/oracle/oradata/table01.dmp

Job "DATA"."SYS_EXPORT_TABLE_01" successfully completed at Mon Jan 25 08:40:05 2021 elapsed 0 00:00:08

[oracle@oracle19c ~]$ !sq

sqlplus / as sysdba;

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 25 08:40:09 2021

Version 19.3.0.0.0

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

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

 SQL> conn data/pump

Connected.

Drop the table: 

SQL> drop table table01;

 Table dropped.

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

[oracle@oracle19c ~]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 25 08:41:28 2021

Version 19.3.0.0.0

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

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> conn data/pump

Connected.

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

The Data Pump Import utility is started using the impdp command

The following example is a command to import Tables using Dump file.

Dumpfile:table01.dmp

[oracle@oracle19c ~]$ impdp directory=My_Dir dumpfile=table01.dmp logfile=table01.log;

Import: Release 19.0.0.0.0 - Production on Mon Jan 25 08:42:02 2021

Version 19.3.0.0.0

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

Username: data

Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Master table "DATA"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "DATA"."SYS_IMPORT_FULL_01":  data/******** directory=My_Dir dumpfile=table01.dmp logfile=table01.log

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "DATA"."TABLE01"                            5.515 KB       5 rows

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER

Job "DATA"."SYS_IMPORT_FULL_01" successfully completed at Mon Jan 25 08:42:21 2021 elapsed 0 00:00:14

[oracle@oracle19c ~]$ !sq

sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Jan 25 08:42:27 2021

Version 19.3.0.0.0

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

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> conn data/pump

Connected.

Finally, verify the contents of the Imported table:

SQL> select * from table01;

ID NAME

---------- ------------------------------

 1                   aaa

 2                   bbb

 3                   ccc

 4                  ddd

 5                  eee

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