Excluding Users from full Import

[Peer Jones]

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

[efrijters]

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

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

[robertbalmer]

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

[efrijters]

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

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)

[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

[julian]

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.

[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...

[jmodic]

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....