Sunday, 19 January 2025

Using EXPDP/IMPDP for an Object or Schema in Oracle Database

With data pump, it is possible to export a single table or entire schema, and not just full database, which makes it pretty convenient for such cases. Data Pump gives you better control to manage logical structure of your database and hence in this article, we are going to see how we can export/import a single table or entire schema if needed.

I am going to show you a DEMO in Oracle Database 19c, for full 'HR' schema export/import & for 'EMPLOYEES' table which is also part of 'HR' schema. Also, due to limited resources, I am going to export from my pluggable database PDB2 and import back to it after dropping exported objects. If your use case is different, you can easily adapt & modify the commands as per your need.

Let's begin...


EXPORT:

1) Connect to your PDB database or non-container, if you are practicing on that.

[oracle@oravm19c expdp]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Sun Jan 19 08:39:46 2025
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> alter session set container=pdb2;

Session altered.

2) Create DBA_DIRECTORY to hold DATA PUMP objects. I am already having directory so I'll DROP and RECREATE it. Also, giving READ, WRITE privilege to 'HR' user on this DIRECTORY.

SQL> drop directory DB_DUMP;

Directory dropped.

SQL> create directory DB_DUMP as '/u01/app/expdp';

Directory created.

SQL> grant read, write on directory DB_DUMP to HR;

Grant succeeded.

3) Exporting 'EMPLOYEES' table. You can also specify multiple table names like 'tables=EMPLOYEES,DEPARTMENTS'.

[oracle@oravm19c expdp]$ expdp HR/hrpwd@oravm19c:1521/pdb2.practice.com directory=DB_DUMP tables=EMPLOYEES dumpfile=EMP_TAB.dmp logfile=EMP_TAB.log

Export: Release 19.0.0.0.0 - Production on Sun Jan 19 08:52:45 2025
Version 19.3.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "HR"."SYS_EXPORT_TABLE_01":  HR/********@oravm19c:1521/pdb2.practice.com directory=DB_DUMP tables=EMPLOYEES dumpfile=EMP_TAB.dmp logfile=EMP_TAB.log 
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
. . exported "HR"."EMPLOYEES"                            17.08 KB     107 rows
Master table "HR"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_TABLE_01 is:
  /u01/app/expdp/EMP_TAB.dmp
Job "HR"."SYS_EXPORT_TABLE_01" successfully completed at Sun Jan 19 08:53:04 2025 elapsed 0 00:00:17

4) Exporting entire 'HR' schema. You can also specify multiple schema names like 'schemas=HR,SH,SCOTT'.

[oracle@oravm19c expdp]$ expdp HR/hrpwd@oravm19c:1521/pdb2.practice.com directory=DB_DUMP schemas=HR dumpfile=HR_SCHEMA.dmp logfile=HR_SCHEMA.log

Export: Release 19.0.0.0.0 - Production on Sun Jan 19 08:54:49 2025
Version 19.3.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "HR"."SYS_EXPORT_SCHEMA_01":  HR/********@oravm19c:1521/pdb2.practice.com directory=DB_DUMP schemas=HR dumpfile=HR_SCHEMA.dmp logfile=HR_SCHEMA.log 
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
. . exported "HR"."EMPLOYEES"                            17.08 KB     107 rows
. . exported "HR"."LOCATIONS"                            8.437 KB      23 rows
. . exported "HR"."JOB_HISTORY"                          7.195 KB      10 rows
. . exported "HR"."JOBS"                                 7.109 KB      19 rows
. . exported "HR"."DEPARTMENTS"                          7.125 KB      27 rows
. . exported "HR"."COUNTRIES"                            6.367 KB      25 rows
. . exported "HR"."REGIONS"                              5.546 KB       4 rows
Master table "HR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_SCHEMA_01 is:
  /u01/app/expdp/HR_SCHEMA.dmp
Job "HR"."SYS_EXPORT_SCHEMA_01" successfully completed at Sun Jan 19 08:55:42 2025 elapsed 0 00:00:51

IMPORT:


5) Before import, I'll drop the HR schema, recreate the user again and grant necessary privileges to be able to import the schema objects again.

SQL> alter session set container=pdb2;

Session altered.

SQL> drop user HR cascade;

User dropped.

SQL> create user HR identified by hrpwd default tablespace USERS quota unlimited on USERS;

User created.

SQL> grant create session, create table, DATAPUMP_IMP_FULL_DATABASE to HR;

Grant succeeded.

SQL> grant read, write on directory DB_DUMP to HR;

Grant succeeded.

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

6) Importing 'HR' Schema back to PDB2.

[oracle@oravm19c expdp]$ impdp HR/hrpwd@oravm19c:1521/pdb2.practice.com directory=DB_DUMP schemas=HR dumpfile=HR_SCHEMA.dmp logfile=HR_import.log

Import: Release 19.0.0.0.0 - Production on Sun Jan 19 09:33:56 2025
Version 19.3.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "HR"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "HR"."SYS_IMPORT_SCHEMA_01":  HR/********@oravm19c:1521/pdb2.practice.com directory=DB_DUMP schemas=HR dumpfile=HR_SCHEMA.dmp logfile=HR_import.log 
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."EMPLOYEES"                            17.08 KB     107 rows
. . imported "HR"."LOCATIONS"                            8.437 KB      23 rows
. . imported "HR"."JOB_HISTORY"                          7.195 KB      10 rows
. . imported "HR"."JOBS"                                 7.109 KB      19 rows
. . imported "HR"."DEPARTMENTS"                          7.125 KB      27 rows
. . imported "HR"."COUNTRIES"                            6.367 KB      25 rows
. . imported "HR"."REGIONS"                              5.546 KB       4 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
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/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job "HR"."SYS_IMPORT_SCHEMA_01" successfully completed at Sun Jan 19 09:34:17 2025 elapsed 0 00:00:19

7) Similarly I can import the table from either this HR Schema dump or that separate dump. To maintain content sequence, I'll use the individual dump but will drop EMPLOYEES table from HR schema first.

SQL> drop table EMPLOYEES cascade constraints;

Table dropped.

[oracle@oravm19c expdp]$ impdp HR/hrpwd@oravm19c:1521/pdb2.practice.com directory=DB_DUMP tables=EMPLOYEES dumpfile=EMP_TAB.dmp logfile=EMP_import.log

Import: Release 19.0.0.0.0 - Production on Sun Jan 19 09:39:54 2025
Version 19.3.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "HR"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "HR"."SYS_IMPORT_TABLE_01":  HR/********@oravm19c:1521/pdb2.practice.com directory=DB_DUMP tables=EMPLOYEES dumpfile=EMP_TAB.dmp logfile=EMP_import.log 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "HR"."EMPLOYEES"                            17.08 KB     107 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "HR"."SYS_IMPORT_TABLE_01" successfully completed at Sun Jan 19 09:40:12 2025 elapsed 0 00:00:16

I hope this helps !!

No comments:

Post a Comment