Solve ORA-39170: Schema expression 'OPS' does not correspond to any schemas

advertisements

_____________________________________________________________________________________________________________________

Error Description:
Data pump expdp failed with following error message.
$ expdp directory=exp_dir schemas=ops$jobs dumpfile=exp.dmp logfile=exp.log
Export: Release 11.2.0.3.0 - Production on Thu Sep 4 07:30:24 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
ORA-39001: invalid argument value
ORA-39170: Schema expression 'OPS' does not correspond to any schemas.
 
Solution 1:
If you are using the command line mode for export then you should use \ with any kind of special character. Here in this case the username contains $ as the special character.

Syntax: expdp directory=exp_dir schemas=ops\$jobs dumpfile=exp.dmp logfile=exp.log

Solution 2:
Use a parameter file (parfile) to specify all the parameters and call the parameter file with expdp.

Example.

SQL> create user ops$jobs identified by welcome;
User created.

SQL> grant connect , resource to ops$jobs;
Grant succeeded.

SQL> conn ops$jobs/welcome
Connected.

SQL> conn ops$jobs/welcome
Connected.
SQL>
SQL> CREATE TABLE EMP
           (EMPNO NUMBER(4) NOT NULL,
            ENAME VARCHAR2(10),
            JOB VARCHAR2(9),
            MGR NUMBER(4),
            HIREDATE DATE,
            SAL NUMBER(7, 2),
            COMM NUMBER(7, 2),
            DEPTNO NUMBER(2));
Table created.
 
INSERT INTO EMP VALUES
        (7369, 'SMITH',  'CLERK',     7902,
        TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20);
1 row created.
INSERT INTO EMP VALUES
        (7499, 'ALLEN',  'SALESMAN',  7698,
        TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30);
1 row created.
SQL> commit;
Commit complete.
SQL>
SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
EMP                            TABLE

$ expdp directory=exp_dir schemas=ops$jobs dumpfile=exp.dmp logfile=exp.log
Export: Release 11.2.0.3.0 - Production on Thu Sep 4 07:30:24 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Username: / as sysdba

Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production

ORA-39001: invalid argument value
ORA-39170: Schema expression 'OPS' does not correspond to any schemas.
 
$ expdp directory=exp_dir schemas=ops\$jobs dumpfile=exp.dmp logfile=exp.log
Export: Release 11.2.0.3.0 - Production on Thu Sep 4 07:33:07 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA directory=exp_dir Schemas=ops$jobs dumpfile=exp.dmp logfile=exp.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 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/TABLE/TABLE
. . exported "OPS$JOBS"."EMP"                            8.539 KB      13 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /u07/exp_dir/exp.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 07:34:52
 
Solution 2 example:
 
$ cat exp.par
directory=exp_dir
schemas=ops$jobs
dumpfile=exp.dmp
logfile=exp.log

$expdp parfile=exp.par
Export: Release 11.2.0.3.0 - Production on Thu Sep 4 07:36:56 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Username: / as sysdba
Connected to: Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  /******** AS SYSDBA parfile=exp.par
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 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/TABLE/TABLE
. . exported "OPS$JOBS"."EMP"                            8.539 KB      13 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
  /u07/exp_dir/exp.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 07:37:21

_____________________________________________________________________________________________________________________

Website Stats