ORA-39170: Schema expression ‘ORAKLDBA’ does not correspond to any schemas : We are getting this error when we export/import in oracle.
Suppose you need to take export backup of a schema or import dump of a schema which name contains string like, “ORAKLDBA.DB” or “ORAKLDBA$DB“. Some of the Places users are created with “Identified by externally” in Create user command.
[oracle@orakldba ~]$ expdp \"/ as sysdba\" directory=EXP_DIR dumpfile=Exp_bkp.dmp logfile=Exp_bkp.log schemas="ORAKLDBA.DB"
Export: Release 19.0.0.0.0 - Production on Fri Jun 11 18:44:03 2021
Version 19.3.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
ORA-39001: invalid argument value
ORA-39170: Schema expression 'ORAKLDBA' does not correspond to any schemas.
[oracle@orakldba ~]$
Follow below steps to take export backup of a schema or import dump of a schema which name contains string or resolve ORA-39170 error.
- Create a parfile.
- Run export or import command using parfile.
EXPORT :
Create a parfile :
You need to create a parfile to take export Backup.
[oracle@orakldba ~]$ vi exp_bkp.par
[oracle@orakldba ~]$
[oracle@orakldba ~]$ cat exp_bkp.par
directory=EXP_DIR dumpfile=EXP_bkp.dmp logfile=EXP_bkp.log SCHEMAS=\"ORAKLDBA.DB\"
[oracle@orakldba ~]$
Take export backup using parfile :
[oracle@orakldba ~]$ expdp \"/ as sysdba\" parfile=exp_bkp.par
Export: Release 19.0.0.0.0 - Production on Fri Jun 11 19:05:45 2021
Version 19.3.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 "SYS"."SYS_EXPORT_SCHEMA_01": "/******** AS SYSDBA" parfile=exp_bkp.par
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/u02/backup/EXP_bkp.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at Fri Jun 11 19:07:26 2021 elapsed 0 00:01:28
[oracle@orakldba ~]$
IMPORT :
Create a parfile:
You need to create a parfile to import Dump.
[oracle@orakldba ~]$ vi imp_bkp.par
[oracle@orakldba ~]$
[oracle@orakldba ~]$ cat imp_bkp.par
directory=EXP_DIR dumpfile=EXP_bkp.dmp logfile=Import.log SCHEMAS=\"ORAKLDBA.DB\"
[oracle@orakldba ~]$
Import dump using parfile :
[oracle@orakldba ~]$ impdp \"/ as sysdba\" parfile=imp_bkp.par
Import: Release 19.0.0.0.0 - Production on Fri Jun 11 19:40:49 2021
Version 19.3.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
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01": "/******** AS SYSDBA" parfile=imp_bkp.par
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"ORAKLDBA.DB" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Job "SYS"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Fri Jun 11 19:41:19 2021 elapsed 0 00:00:17
[oracle@orakldba ~]$
I hope my written content will helped you. Your suggestions/feedback are most welcome from orakldba.com.
Keep learning… Have a great day!!!
Please comment here for your any query related to above content. You can write to mail me through : orakldba@gmail.com.