Oracle 10g database exp and imp question

[Faroch Hordil]

Hi, I am reading document B10825-01.pdf Oracle Database Utilities, chapter
20 "Original Export and Import". I am an aspiring DBA but not a DBA.

My understanding from B10825-01.pdf is that to export all the tables, stored
procedures etc for SCOTT, I would use this:

exp system/password@db10gtest rows=y owner=scott

I have tried this it creates a file EXPDAT.DMP

I then do this:

SQLPLUS system/password@db10gtest
DROP USER SCOTT CASCADE;

and SCOTT is deleted.


To test the import, I now want to put SCOTT and all his data, stored
procedures etc back as they were before I deleted them. I am attempting to
do this with "imp".

I have typed this

imp system/password@db10gtest full=y


and I get a few errors including this

ORA-01435: user does not exist

When I create user SCOTT using CREATE USER SCOTT IDENTIFIED BY PASSWORD and
then do the imp again everything is imported. But I don't want to create him
beforehand, I want the user to be created if it does not exist during the
import. According to B10825-01.pdf (page 20-19), user definitions are only
imported in "FULL DATABASE MODE". This is why I have added "full=y" in the
imp line above. But it still doesn't import the user if it don't exist in
the first place.

Please help me with how to create user if it doesn't exist during an imp. In
this example I know the user is SCOTT. In a real system I might not know the
user name and there maybe more than one. I am sorry if this is too simple. I
am using Oracle 10g on Windows.

Thank you
Fred

[FredBear]

"Faroch Hordil" <fred1231@anon.com> a écrit dans le message de
news:1111315622.20007.0@demeter.uk.clara.net...
|
|
| Hi, I am reading document B10825-01.pdf Oracle Database Utilities, chapter
| 20 "Original Export and Import". I am an aspiring DBA but not a DBA.
|
| My understanding from B10825-01.pdf is that to export all the tables, stored
| procedures etc for SCOTT, I would use this:
|
| exp system/password@db10gtest rows=y owner=scott
|
| I have tried this it creates a file EXPDAT.DMP
|
| I then do this:
|
| SQLPLUS system/password@db10gtest
| DROP USER SCOTT CASCADE;
|
| and SCOTT is deleted.
|
|
| To test the import, I now want to put SCOTT and all his data, stored
| procedures etc back as they were before I deleted them. I am attempting to
| do this with "imp".
|
| I have typed this
|
| imp system/password@db10gtest full=y
|
|
| and I get a few errors including this
|
| ORA-01435: user does not exist
|
| When I create user SCOTT using CREATE USER SCOTT IDENTIFIED BY PASSWORD and
| then do the imp again everything is imported. But I don't want to create him
| beforehand, I want the user to be created if it does not exist during the
| import. According to B10825-01.pdf (page 20-19), user definitions are only
| imported in "FULL DATABASE MODE". This is why I have added "full=y" in the
| imp line above. But it still doesn't import the user if it don't exist in
| the first place.
|
| Please help me with how to create user if it doesn't exist during an imp. In
| this example I know the user is SCOTT. In a real system I might not know the
| user name and there maybe more than one. I am sorry if this is too simple. I
| am using Oracle 10g on Windows.
|
| Thank you
| Fred
|

If you're using 10g, use data pump (expdp/impdp) instead of old imp and exp.
Then you'll can do what you want.

http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10825/toc.htm
http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10825.pdf

Regards
Michel Cadot

[Rauf Sarwar]

Faroch Hordil wrote: Hi, I am reading document B10825-01.pdf Oracle Database Utilities,
chapter 20 "Original Export and Import". I am an aspiring DBA but not a DBA. My understanding from B10825-01.pdf is that to export all the tables,
stored procedures etc for SCOTT, I would use this: exp system/password@db10gtest rows=y owner=scott I have tried this it creates a file EXPDAT.DMP I then do this: SQLPLUS system/password@db10gtest DROP USER SCOTT CASCADE; and SCOTT is deleted. To test the import, I now want to put SCOTT and all his data, stored procedures etc back as they were before I deleted them. I am
attempting to do this with "imp". I have typed this imp system/password@db10gtest full=y and I get a few errors including this ORA-01435: user does not exist When I create user SCOTT using CREATE USER SCOTT IDENTIFIED BY
PASSWORD and then do the imp again everything is imported. But I don't want to
create him beforehand, I want the user to be created if it does not exist during
the import. According to B10825-01.pdf (page 20-19), user definitions are
only imported in "FULL DATABASE MODE". This is why I have added "full=y"
in the imp line above. But it still doesn't import the user if it don't
exist in the first place.
<snip>

Using old style exp/imp in 10g,

Unless you do export in FULL mode, how do you expect to do import in
FULL mode... like what you are trying to do? It may be confusing a bit
but that is precisely what the docco means... user definitions are only
imported if both export and import are done in FULL mode. I am afraid
you would have to create the user in the target database if you want to
only do schema level export/import.

Regards
/Rauf