Datapump Tablespace Level in Oracle 19c : In this article we will see Datapump Tablespace Level in Oracle 19c and how we can take one or multiple tablespaces export level backup and import individual or multiple tablespaces in Oracle database.
This article guides you through the process of using the Export and Import utilities in Oracle Data Pump, or using the original Export and Import utilities in Oracle Database, to move table data from one database to another.
Oracle Data Pump technology enables very high-speed movement of data and metadata from one database to another.
Data Pump is useful for :
Moving data from one schema to another
Move data from one version of Oracle to another
Moving data from one OS to another
Creating logical backups
Schema Exports/Imports Syntax :-
expdp system/password@PROD directory=EXP_DIR tablespaces=users dumpfile=users.dmp logfile=users.log
and
impdp system/password@PROD directory=EXP_DIR tablespaces=users dumpfile=users.dmp logfile=users.logPrerequisite for Export/Import :
Atfirst select a existing user account or create a user account and create a directory object it can access. The directory object is only a pointer to a physical directory, creating it does not actually create the physical directory on the file system of the database server.
Here we use an existing User ‘SCOTT’.
[oracle@vm-1 ~]$ mkdir -p /u01/backup/export/
[oracle@vm-1 ~]$
[oracle@vm-1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Dec 14 21:45:24 2020
Version 19.8.0.0.0
Copyright (c) 1982, 2020, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0
SQL>
SQL> CREATE OR REPLACE DIRECTORY exp_dir AS '/u01/backup/export/';
Directory created.
SQL> GRANT READ, WRITE ON DIRECTORY exp_dir TO scott;
Grant succeeded.
SQL>EXPORT :
Individual Schemas From system Schema :
[oracle@vm-1 ~]$ expdp system/system@PROD directory=EXP_DIR tablespaces=users dumpfile=users.dmp logfile=users.log
Export: Release 19.0.0.0.0 - Production on Tue Dec 15 00:12:07 2020
Version 19.8.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 "SYSTEM"."SYS_EXPORT_TABLESPACE_01": system/********@PROD directory=EXP_DIR tablespaces=users dumpfile=users.dmp logfile=users.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/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."EMP" 8.773 KB 14 rows
. . exported "SCOTT"."DEPT" 6.023 KB 4 rows
. . exported "SCOTT"."SALGRADE" 5.953 KB 5 rows
. . exported "PRASANTA"."EMPLOYEES" 6.140 KB 10 rows
. . exported "PRASANTA"."TUTORIALS_TBL" 0 KB 0 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLESPACE_01 is:
/u01/backup/export/users.dmp
Job "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully completed at Tue Dec 15 00:13:43 2020 elapsed 0 00:01:33
[oracle@vm-1 ~]$
Multiple tablespaces From system Schema :
[oracle@vm-1 ~]$ expdp system/system@PROD directory=EXP_DIR tablespaces=users,TBS1 dumpfile=users_TBS1.dmp logfile=users_TBS1.log
Export: Release 19.0.0.0.0 - Production on Tue Dec 15 00:22:55 2020
Version 19.8.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 "SYSTEM"."SYS_EXPORT_TABLESPACE_01": system/********@PROD directory=EXP_DIR tablespaces=users,TBS1 dumpfile=users_TBS1.dmp logfile=users_TBS1.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/IDENTITY_COLUMN
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
. . exported "SCOTT"."EMP" 8.773 KB 14 rows
. . exported "SCOTT"."DEPT" 6.023 KB 4 rows
. . exported "SCOTT"."SALGRADE" 5.953 KB 5 rows
. . exported "PRASANTA"."EMPLOYEES" 6.140 KB 10 rows
. . exported "PRASANTA"."T1" 405.6 KB 10000 rows
. . exported "PRASANTA"."TUTORIALS_TBL" 0 KB 0 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLESPACE_01 is:
/u01/backup/export/users_TBS1.dmp
Job "SYSTEM"."SYS_EXPORT_TABLESPACE_01" successfully completed at Tue Dec 15 00:23:40 2020 elapsed 0 00:00:43
[oracle@vm-1 ~]$
IMPORT :
Individual tablespaces From system Schema :
[oracle@vm-1 ~]$ impdp system/system@PROD directory=EXP_DIR tablespaces=TBS1 dumpfile=users_TBS1.dmp logfile=TBS1.log
Import: Release 19.0.0.0.0 - Production on Tue Dec 15 00:52:37 2020
Version 19.8.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 "SYSTEM"."SYS_IMPORT_TABLESPACE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLESPACE_01": system/********@PROD directory=EXP_DIR tablespaces=TBS1 dumpfile=users_TBS1.dmp logfile=TBS1.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "PRASANTA"."T1" 405.6 KB 10000 rows
Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_TABLESPACE_01" successfully completed at Tue Dec 15 00:52:49 2020 elapsed 0 00:00:11
[oracle@vm-1 ~]$
Multiple tablespaces From system Schema :
[oracle@vm-1 ~]$ impdp system/system@PROD directory=EXP_DIR tablespaces=BIGTBS_01,TBS1 dumpfile=users_TBS1.dmp logfile=BIGTBS_01_TBS1.log
Import: Release 19.0.0.0.0 - Production on Tue Dec 15 00:30:33 2020
Version 19.8.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 "SYSTEM"."SYS_IMPORT_TABLESPACE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLESPACE_01": system/********@PROD directory=EXP_DIR tablespaces=BIGTBS_01,TBS1 dumpfile=users_TBS1.dmp logfile=BIGTBS_01_TBS1.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "PRASANTA"."T1" 405.6 KB 10000 rows
Processing object type TABLE_EXPORT/TABLE/IDENTITY_COLUMN
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYSTEM"."SYS_IMPORT_TABLESPACE_01" successfully completed at Tue Dec 15 00:31:33 2020 elapsed 0 00:00:58
[oracle@vm-1 ~]$
I hope my written content will helped you. Your suggestions/feedback are most welcome orakldba.com.
Keep learning… Have a great day!!!
Please comment here for your any query related to above content. You could write to mail me through : orakldba@gmail.com.