CODEWITHSUNDEEP
oracle-databaseoracle11g
Aazim
Aazim

Reputation: 770

Oracle 11g. Unable to import dump files, even though schema is created

I have created a user in Oracle 11gR2, using the following script

create user          cata
identified by        cata
default   tablespace tbs
temporary tablespace temp;

grant DBA to cata;

After trying to import a dump file using the command 

impdp system/password@ORCL11 schemas=cata dumpfile=cata.dmp logfile=log.txt

i'm getting the following error

ORA-39002: invalid operation ORA-39165: Schema ATGDB_CATA was not found.

Surprisingly, when i try to export a dump from the same schema, i'm able to do that. So, if the schema was not created properly then i should not be able to export the dump file as well, right ?

I have also checked in dba_users & the schema is created. Is there anything else that i can do which could resolve this problem

Upvotes: 4

Views: 26399

Answers (3)

JAYAPRAKASH
JAYAPRAKASH

Reputation: 1

ORA-39002: invalid operation ORA-39070: Unable to open the log file. ORA-29283: invalid file operation ORA-06512: at "SYS.UTL_FILE", line 536 ORA-29283: invalid file operation

SOLUTION: create or replace directory test_ dir as 'FOLDER_NAME' ;

that 'FOLDER_NAME' must has that dump file

step : 1 create folder SAMPLE under orcle_installed_path/sql/SAMPLE put that dump file into that SAMPLE folder.

go to bin and execute ./sqlplus and login SQL>create or replace directory test_ dir as 'SAMPLE' ;

SQL> SQL> GRANT READ, WRITE on directory test_dir to 'USER';

SQL> GRANT IMP_FULL_DATABASE to 'USER'; exit then impdb to import that dump

Upvotes: 0

user2492995
user2492995

Reputation: 1

Grant the roles of read and write on the Directory in which you created to the New User: EX: 

GRANT READ, WRITE ON DIRECTORY dir_name TO NEW_USER:

Also grant the following role to the new user:

GRANT IMP_FULL_DATABASE TO NEW_USER;

Thanks! NC

Upvotes: 0

GWu
GWu

Reputation: 2787

Out of the error message I guess that the original schema name was "atgdb_cata".

As you are now trying to import into a schema named "cata" you need to specify the parameter remap_schema

So for your case: impdp system/password@ORCL11 schemas=atgdb_cata dumpfile=cata.dmp logfile=log.txt remap_schema=atgdb_cata:cata

Upvotes: 11

Related Questions