Thursday, 6 March 2025

Using WHERE in Datapump


How to export a table based on WHERE condition.

Create a parameter file exp.par with below contents 
userid='hrm/123'
directory=exptest
dumpfile=attendance.DMP
tables=attendance
query="WHERE employee_no LIKE '12345' "
logfile=attendance.log

Invoke expdp calling the parameter file
expdp parfile=exp.par

Export: Release 19.0.0.0.0 - Production on Thu Mar 6 12:51:09 2025
Version 19.25.0.0.0
Copyright (c) 1982, 2024, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "HRM"."SYS_EXPORT_TABLE_01":  hrm/******** parfile=exp.par
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/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/TRIGGER
. . exported "HRM"."attendance"                        145.4 KB     491 rows
Master table "HRM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for HRM.SYS_EXPORT_TABLE_01 is:
  E:\BACKUPS_FOR_RESTORE\attendance.DMP
Job "HRM"."SYS_EXPORT_TABLE_01" successfully completed at Thu Mar 6 12:51:27 2025 elapsed 0 00:00:17

No comments:

Post a Comment