Creating a USER using the imp command [duplicate]

Posted on

Question :

I was told I can use exp to export a certain USER, then DROP the user and with the imp command I can recreate that USER with all of its tables.

  1. Is it true?
  2. How do I do it?

I already have an ADMIN user with just about all privileges (there are 204 of them if it matters).

I tried various options of exporting the USER with the ADMIN account or with the USER’s account, but no matter what I do the imp command fails if I try to run it when the USER has been dropped.

Example command:

-- From CLI:
exp USERID=MYUSER/PASS@INSTANCE FILE=mydump.dmp FULL=y
-- From SQLPLUS with admin user
DROP USER MYUSER CASCADE;
-- From CLI:
imp ADMIN/PASS@INSTANCE FILE=mydump.dmp FULL=y
-- The above command will put the contents of MYUSER into the ADMIN account ...
imp ADMIN/PASS@INSTANCE FILE=mydump.dmp FROMUSER=MYUSER TOUSER=MYUSER 
-- The above command fails with  the below message:

. importing MYUSER's objects into MYUSER
IMP-00003: ORACLE error 1435 encountered
ORA-01435: user does not exist
Import terminated successfully with warnings.

Answer :

To answer my own question – the answer is NO – the imp command cannot create the user for me.

Leave a Reply

Your email address will not be published. Required fields are marked *