"How to recover a schema dropped after EXPDP without doing the impdp?

Question

I was playing - for my upcoming project- how to perform expdp and impdp on play environment which is two levels away from the test environment . So i did expdp with scott/tiger and exported the scott schema. However, out of curiosity, I dropped Scott schema from the database (expecting to do impdp in the same database) then I got a lot of errors and lost the Scott schema from the database.

Is there I was I can recover Scott schema from the dumpfile?

This is what I did:

1. expdp scott/tiger schemas=scott directory=test_dir dumpfile=scott.dmp logfile=expdpscott.log

2. impdp scott/tiger schemas=scott directory=test_dir dumpfile=scott.dmp logfile=impdpscott.log . . . then I got this:

   Job "SCOTT"."SYS_IMPORT_SCHEMA_01" completed with 4 error(s) at 19:13:31"

   ... then I did:

3. drop user Scott cascade;

   and then

4. impdp scott/tiger schemas=scott directory=test_dir dumpfile=scott.dmp logfile=impdpscott.log

then I got this error:

UDI-01017: operation generated ORACLE error 1017 ORA-01017: invalid username/password; logon denied

... Now I realized that I did a stupid thing (which is good as a learner).

How can I get back or recover the dropped scott schema?

Answer 1

It would be nice if you posted which errors you got in step #2; maybe they were irrelevant.

Now, as you dropped Scott, connect as a privileged user (such as SYS) and create user Scott. Here's an example:

• check list of tablespaces in your database
• create user
• grant it some privileges

---

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USER_DATA
APEX
APEX_9695076087226093

7 rows selected.

SQL> create user littlefoot identified by lion
  2    default tablespace user_data
  3    temporary tablespace temp
  4    profile default
  5    quota  unlimited on user_data;

User created.

SQL>
SQL> grant create session       to littlefoot;

Grant succeeded.

SQL> grant create table         to littlefoot;

Grant succeeded.

SQL> grant create procedure     to littlefoot;

Grant succeeded.

SQL> grant create sequence      to littlefoot;

Grant succeeded.

SQL> grant create view          to littlefoot;

Grant succeeded.

SQL> grant create trigger       to littlefoot;

Grant succeeded.

SQL>

Once you do that, run IMPDP once again.