DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Excluding Users from full Import

  1. #1
    Join Date
    Nov 2001
    Posts
    118

    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

  2. #2
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    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
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  3. #3
    Join Date
    May 2003
    Location
    Pretoria, Rep of South Africa
    Posts
    191

    effective ways

    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
    Able was I ere I saw Elba

  4. #4
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    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)
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  5. #5
    Join Date
    Nov 2001
    Posts
    118
    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

  6. #6
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    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.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  7. #7
    Join Date
    Oct 2002
    Location
    Breda, The Netherlands
    Posts
    317
    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...
    An expert is one who knows more and more about less and less until he knows absolutely everything about nothing.

  8. #8
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width