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.
Thanks. This is just what I needed.
My pleasure!
wow, it worked.
but in previous versions i used to do “‘/ as sysdba'” (outer is ” inner is ‘ ) looks this one is not working anymore ?
It’s still working, but when we come to a multitenant database, there’re some differentiations.