Excluding Users from full Import

Hi Everyone,

I'm looking for an effective way of importing a full dump file that contains several hundred users.

At the same time I don't want SYS and SYSTEM to be overwritten, and including a string of the remaining about 350 users in a parameterfile (FROMUSER TOUSER etc.) seems to be very inefficient.

Does anyone know if there is an import parameter I can use that excludes SYS and SYSTEM when actually performing a full import?

Thanks
Peer

I don't thnik that's possible...
How about creating a script that imports your data per user?

Code:

---

set trimspool on
spool c:\import.bat
SELECT "imp user/passw@db full=n ignore=y file=full_db.dmp etc
fromuser="||username||" touser="||username from all_users
where username not in ('SYS','SYSTEM')
spool off
(run import.bat)

---

(maybe slow, but it does what you want...)

HTH,
Erik

Shouldnt you look at a better exp way....
sql>exp 'sys/sys@db as sysdba' file=all_your_users.dmp owner=(user1,user2,etc..)
then do an easy import :p

You can do the same trick for export, but then you have to manually edit the spooled file...

Code:

---

set trimspool on
spool c:\export.bat
prompt "EXP user/passw@db direct=y full=n file=x all_other_options owner="

SELECT username||',' from all_users
where username not in ('SYS','SYSTEM')
spool off
(edit and run export.bat)

---

Thanks for the help, but as I feared there doesn't seem to be a simple way using some parameter switch when calling exp.
Much appreciated though.

Bye
Peer

Quote:

---

Originally posted by Peer Jones
Thanks for the help, but as I feared there doesn't seem to be a simple way using some parameter switch when calling exp.
Much appreciated though.

Bye
Peer

---

You should have not taken a full export but used the OWNER parameter to exclude the users that you don't need.

I tested my theory, but there's a limit on the length of the exp-statement. I had issued 418 users and the result was a "The input line is too long"-error...

Quote:

---

Originally posted by efrijters
I tested my theory, but there's a limit on the length of the exp-statement. I had issued 418 users and the result was a "The input line is too long"-error...

---

Store that owner=long-list-of-users part in a parfile and use PARFILE= in your command line....