Mir Sayeed Hassan – Oracle Blog

Export the data by using the where clause from Pluggable database in Oracle 19C

Check the database status

SQL> select instance_name,version, open_mode from V$instance, v$database;

INSTANCE_NAME    VERSION        OPEN_MODE
---------------- --------------------------
prod1db         19.0.0.0.0      READ WRITE

Check the CDB & PDB Database associated to it.

SQL> show pdbs

CON_ID    CON_NAME     OPEN MODE    RESTRICTED
---------- -------------------------------------
2         PDB$SEED     READ ONLY      NO
3         PDB_TEST1    READ WRITE     NO

Connect to PDB Database & Create a Directory at OS & DB level

SQL> alter session set container=PDB_TEST1;
Session altered.

SQL> show pdbs

CON_ID    CON_NAME      OPEN MODE     RESTRICTED
---------- --------------------------------------
3         PDB_TEST1     READ WRITE      NO

Create Directory at OS LEVEL

[oracle@localhost ~]$ mkdir -p /u01/dump/

Create Directory at DB LEVEL

SQL> create or replace directory dump as '/u01/dump/';
Directory created.

SQL> grant read, write on directory dump to system;
Grant succeeded.

Prepare the PARFILE to perform the export at query level

[oracle@prod1db dump]$ cat SHOPPING_INVOICE_USED.par
DIRECTORY=dump
content=data_only
DUMPFILE=SHOPPING_INVOICE_USED_12052024.dmp
LOGFILE=SHOPPING_INVOICE_USED_12052024.log
TABLES=STORE.SHOPPING_INVOICE_USED
query=STORE.SHOPPING_INVOICE_USED:"where CREATION_DATE > to_date('2024/04/14','yyyy/mm/dd')"

[oracle@prod1db dump]$ expdp system@PDB_TEST1 parfile=SHOPPING_INVOICE_USED.par

Export: Release 19.0.0.0.0 - Production on Wed May 12 21:07:30 2024
Version 19.18.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Password:

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/********@STORE parfile=SHOPPING_INVOICE_USED.par
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . exported "STORE"."SHOPPING_INVOICE_USED" 341.4 MB 1799546 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/u01/dump/SHOPPING_INVOICE_USED_12052024.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at Wed May 12 21:08:24 2024 elapsed 0 00:00:53

=====Hence tested and verified in our test env======