ORA-39170: Schema expression ‘OPS’ does not correspond to any schemas


In most of the Places users are created with “ Identified by externally” in Create user command.To create a user to connect with the OS authentication in 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


 [oracle@mumsa001212|INTBPO]/apps/oracle > exp230611.dmp logfile=schemas_exp230611.log SCHEMAS=OPS$RPS 
Export: Release 10.2.0.4.0 - 64bit Production on Thursday, 23 June, 2013 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,

To take the export backup of the schemas with Special characters user the following format ’\” <username> \ “‘


[oracle@mumsa001212|INTBPO] 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, 2013 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: