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.
- Is it true?
- 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.