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@orclpdb 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=ORCLPDB
[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/ORCLCDB/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

As you can see, the database release we run in the above example is 19.9.

19c Problem

For release 19c, we saw that the console still prompts for password in the command due to an issue about OS authentication (Bug 28707931) in 19c database server.

Although we can just press Enter to ignore it, the command line cannot be used in shell scripts for daily routines.

To overcome it, you need to patch your database 19c to 19.7 or higher in order to run it without password prompting.

If you don't or can't patch it, you may use the basic way to do it.

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

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