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.
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.
[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
[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:
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
[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
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
No comments:
Post a Comment