Error code: ORA-39170
Description: Schema expression string does not correspond to any schemas.
Cause: A schema expression or schema list was supplied for a Data Pump job that did not identify any schemas in the source database.
Action: Correct the schema specifications and retry the job.
In most Places, users are created with “Identified by externally” in Create user command. To create a user to connect with the OS authentication in the Oracle database following parameter must be set.
REMOTE_OS_AUTHENT= ‘TRUE’
Set the following parameter is set in init[SID].ora file:
REMOTE_OS_AUTHENT= ‘TRUE’
Create user With following syntax:
create user ops$rps identified externally default tablespace users temporary tablespace temp profile default;
Take the export of the user using data pump:
$ expdp DIRECTORY=BACKUP_DIR123 DUMPFILE=schemas_exp230611.dmp logfile=schemas_exp230611.log SCHEMAS=OPS$RPS Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 23 June, 2011 13:08:01 Copyright (c) 2003, 2007, Oracle. All rights reserved. Username: / as sysdba Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options ORA-39001: invalid argument value ORA-39170: Schema expression 'OPS' does not correspond to any schemas.
we cannot take the export of the schemas with special characters. The right escape sequence must be used in order to avoid this. To take the export backup of the schemas with Special characters user the following format ‘\”
$ expdp DIRECTORY=BACKUP_DIR123 DUMPFILE=schemas_exp230611.dmp logfile=schemas_exp230611.log SCHEMAS='\"OPS$RPS\" ' Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 23 June, 2011 13:09:34 Copyright (c) 2003, 2007, Oracle. All rights reserved. Username: / as sysdba Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYS"."SYS_EXPORT_SCHEMA_01": /******** AS SYSDBA DIRECTORY=BACKUP_DIR123 DUMPFILE=schemas_exp230611.dmp logfile=schemas_exp230611.log SCHEMAS=\"OPS$RPS\" Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 504 KB 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 Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_SPEC Processing object type SCHEMA_EXPORT/PACKAGE/GRANT/OWNER_GRANT/OBJECT_GRANT Processing object type SCHEMA_EXPORT/PACKAGE/COMPILE_PACKAGE/PACKAGE_SPEC/ALTER_PACKAGE_SPEC Processing object type SCHEMA_EXPORT/PACKAGE/PACKAGE_BODY Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS . . exported "OPS$RPS"."DBT_R_SITEPARAM" 7.460 KB 1 rows . . exported "OPS$RPS"."DBT_R_DBCKCHAT" 0 KB 0 rows . . exported "OPS$RPS"."DBT_R_DBCKPLAN" 0 KB 0 rows . . exported "OPS$RPS"."DBT_R_MAP" 0 KB 0 rows . . exported "OPS$RPS"."DBT_R_MSTSUMROW" 0 KB 0 rows . . exported "OPS$RPS"."DBT_R_MSTSUMSTAT" 0 KB 0 rows . . exported "OPS$RPS"."DBT_R_MSTSUMTAB" 0 KB 0 rows . . exported "OPS$RPS"."DBT_R_RECORDS" 0 KB 0 rows . . exported "OPS$RPS"."DBT_R_SEQMAP" 0 KB 0 rows . . exported "OPS$RPS"."DBT_R_SEQUENCES" 0 KB 0 rows . . exported "OPS$RPS"."DBT_R_SLVSUMROW" 0 KB 0 rows . . exported "OPS$RPS"."DBT_R_SLVSUMTAB" 0 KB 0 rows . . exported "OPS$RPS"."DBT_R_TESTLOAD" 0 KB 0 rows . . exported "OPS$RPS"."DBT_R_TMP_REC" 0 KB 0 rows . . exported "OPS$RPS"."DBT_R_TMP_REC2" 0 KB 0 rows Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is: /install/schemas_exp230611.dmp Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 13:10:33