Oracle 18c Datapump-Schema Level

Description:-

In this article we are going to see the Oracle 18c Datapump-Schema level backup
The OWNER parameter of exp has been replaced by the SCHEMAS parameter which is used to specify the schemas to be exported.
Export all the objects of HR’s schema

Syntax :

expdp system/Chennai#123@pdb1 directory=TEST_DIR SCHEMAS=hr dumpfile=hr.dmp logfile=hr.log

Let’s start the Demo:-

Pre-steps:-create a directory in the filesystem and creates a directory object in the database and grants privileges to the HR user.

[oracle@testdb ~]$ mkdir –p /u01/app/oracle/datapump
[oracle@testdb ~]$ sqlplus '/as sysdba'
SQL*Plus: Release 18.0.0.0.0 Production on Sat Jun 30 05:50:19 2018
Version 18.1.0.0.0
Copyright (c) 1982, 2017, Oracle. All rights reserved.
Connected to:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Version 18.1.0.0.0
SQL> alter session set container=pdb1;
Session altered.

SQL> create or replace directory test_dir as '/u01/app/oracle/datapump';
Directory created.

SQL> grant read,write on DIRECTORY test_dir to hr;
Grant succeeded.

Demo 1:-Export individual Schema From system Schema

[oracle@testdb ~]$ expdp system/Chennai#123@pdb1 directory=TEST_DIR SCHEMAS=hr dumpfile=hr.dmp logfile=hr.log

Export: Release 18.0.0.0.0 - Production on Sat Jun 30 07:08:01 2018
Version 18.1.0.0.0

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@pdb1 directory=TEST_DIR SCHEMAS=hr dumpfile=hr.dmp logfile=hr.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/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
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/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
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/datapump/hr.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Jun 30 07:09:10 2018 elapsed 0 00:01:07

Demo 2:-Export individual Schema From HR Schema

[oracle@testdb ~]$ expdp hr/hr@pdb1 directory=TEST_DIR SCHEMAS=hr dumpfile=hr2.dmp logfile=hr2.log

Export: Release 18.0.0.0.0 - Production on Sat Jun 30 07:17:39 2018
Version 18.1.0.0.0

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

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "HR"."SYS_EXPORT_SCHEMA_01": hr/********@pdb1 directory=TEST_DIR SCHEMAS=hr dumpfile=hr2.dmp logfile=hr2.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/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
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "HR"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HR.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/datapump/hr2.dmp
Job "HR"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Jun 30 07:18:44 2018 elapsed 0 00:01:02

Demo 3:-Export multiple Schema From system Schema

[oracle@testdb ~]$ expdp system/Chennai#123@pdb1 directory=TEST_DIR SCHEMAS=hr,hari dumpfile=hr_hari.dmp logfile=hr_hari.log

Export: Release 18.0.0.0.0 - Production on Sat Jun 30 07:44:42 2018
Version 18.1.0.0.0

Connected to: Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********@pdb1 directory=TEST_DIR SCHEMAS=hr,hari dumpfile=hr_hari.dmp logfile=hr_hari.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/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/TABLESPACE_QUOTA
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/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
. . exported "HARI"."SAM" 8.539 KB 512 rows
ORA-39173: Encrypted data has been stored unencrypted in dump file set.
Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/datapump/hr_hari.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Sat Jun 30 07:45:59 2018 elapsed 0 00:01:14

Catch Me On:- Hariprasath Rajaram

LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba

Datapump

Recent Posts

Get updated

Oracle 18c Datapump-Schema Level

You may also like

Previous Post

ORA-28014: cannot drop administrative users

View Post

Next Post

Oracle 18c Datapump-Tablespace Level

View Post

Recent Posts

Get updated