How to expdp PDB as SYSDBA without Password

Expdp PDB as SYSDBA

If we want to export data from a Pluggable Database (PDB) using expdp as sysdba by OS Authentication, we will get error ORA-39195 like this:

[oracle@test ~]$ expdp \"/ as sysdba\" tables=hr.employees
...
ORA-39001: invalid argument value
ORA-39195: At least one schema in the TABLE_FILTER does not exist.

This is because the utility thought we want to export data from the Container Database (CDB) which does not contain the data we want. A normal way to export data from a PDB is to provide a connection string for expdp.

[oracle@test ~]$ expdp \"sys/password@orclpdb1 as sysdba\" tables=hr.employees

Solution

To achieve our goal, we use a secret ingredient ORACLE_PDB_SID, an environment variable to let Oracle know what PDB we like to connect to without password. I have talked about its usages and restrictions of connecting a PDB directly by OS authentication in another post.

[oracle@test ~]$ export ORACLE_PDB_SID=ORCLPDB1
[oracle@test ~]$ expdp \"/ as sysdba\" tables=hr.employees

Export: Release 19.0.0.0.0 - Production on Wed Dec 16 02:37:37 2020
Version 19.9.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 "SYS"."SYS_EXPORT_TABLE_01":  "/******** AS SYSDBA" tables=hr.employees
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/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
. . exported "HR"."EMPLOYEES"                            17.08 KB     107 rows
Master table "SYS"."SYS_EXPORT_TABLE_02" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /u01/app/oracle/admin/ORA19C1/dpdump/AA736F65D66215CCE053992AA8C08959/expdat.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Wed Dec 16 02:38:02 2020 elapsed 0 00:00:24

On the other side, importing data into a PDB by impdp is the same way to do it.