Oracle 18c Datapump-Query Parameter
Description:-
- In this article we are going to see the Oracle 18c Datapump-Query level backup
Syntax :
From Scott user
expdp scott/tiger tables=emp directory=TEST_DIR query=\’where deptno=20\’ dumpfile=query.dmp logfile=query.log
From sys user
expdp tables=scott.emp query=\’where deptno=20\’ directory=TEST_DIR dumpfile=query1.dmp logfile=query1.lo
query=test:”where DATE > ’01-DEC-2013′”
query= scott.EMP:”WHERE LOAD_DATE = TRUNC (SYSDATE -190)”
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 using query parameter From normal Schema
[oracle@testdb ~]$ expdp hari/hari@pdb1 tables=emp directory=TEST_DIR query=\'where DEPARTMENT_ID=80\' dumpfile=query.dmp logfile=query.log Export: Release 18.0.0.0.0 - Production on Tue Jul 10 20:11:27 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 "HARI"."SYS_EXPORT_TABLE_01": hari/********@pdb1 tables=emp directory=TEST_DIR query='where DEPARTMENT_ID=80' dumpfile=query.dmp logfile=query.log 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 "HARI"."EMP" 12.08 KB 34 rows Master table "HARI"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for HARI.SYS_EXPORT_TABLE_01 is: /u01/app/oracle/datapump/query.dmp Job "HARI"."SYS_EXPORT_TABLE_01" successfully completed at Tue Jul 10 20:11:57 2018 elapsed 0 00:00:28
Demo 2:-Export using query parameter From normal Schema
[oracle@testdb ~]$ expdp hari/hari@pdb1 tables=emp directory=TEST_DIR query="'where salary > 5000'" dumpfile=query1.dmp logfile=query.log
Export: Release 18.0.0.0.0 - Production on Tue Jul 10 20:28:15 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 "HARI"."SYS_EXPORT_TABLE_01": hari/********@pdb1 tables=emp directory=TEST_DIR query='where salary > 5000' dumpfile=query1.dmp logfile=query.log
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 "HARI"."EMP" 13.71 KB 58 rows
Master table "HARI"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HARI.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/datapump/query1.dmp
Job "HARI"."SYS_EXPORT_TABLE_01" successfully completed at Tue Jul 10 20:28:26 2018 elapsed 0 00:00:11
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
