This discussion is archived
7 Replies Latest reply: Nov 2, 2012 3:43 PM by phaeus RSS

expdp or impdp as 'sys'

732974 Newbie
Currently Being Moderated
This question probably been asked before, but I'm not able to find it, so here goes (again):

I've read in Oracle docs that it is usually adviseable to export (exp or expdp), or import (imp or impdp) using the SYSTEM account rather than the SYS account (mainly for FULL exports and imports).

I guess my question is if this is really true, and if so, why?

Is it because sys and system data will not be exported or imported, or something related to that?

Also, in our scripts, while using the SYSTEM account, I have to pass a password to run expdp (or impdp), such as
 expdp system/$SYSTEM . . .
but this method allows someone who queries the system processes to see the password (bad).

but as sys, I can simply use
expdp  \'/ as sysdba\' . . .
But I recall that exporting or importing as SYS was not recommended by Oracle.

Can anyone help clear this up?
  • 1. Re: expdp or impdp as 'sys'
    gottikere Explorer
    Currently Being Moderated
    Not sure from where you got the information like ORACLE RECOMMENDS NOT TO TAKE EXPORT AS sys user.

    I am using exp and expdp to take the export using sys user only .......

    It much depends on the requirment.....

    Thanks and Regards,
    Satish.G.S
    http://gssdba.wordpress.com
  • 2. Re: expdp or impdp as 'sys'
    723212 Newbie
    Currently Being Moderated
    Please read this article:
    http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#i1012504
    In fact, oracle does recommend running IMPDP using SYSTEM and not SYS user.
    --
    Cheers
  • 3. Re: expdp or impdp as 'sys'
    Srini Chavali-Oracle Oracle ACE Director
    Currently Being Moderated
    As the NOTE indicates, the behavior for SYS user is specialized/different and it should not be used for invoking expdp/impdp, unless advised by Support

    http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#i1012781
    http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#i1012504

    HTH
    Srini
  • 4. Re: expdp or impdp as 'sys'
    Lubiez Jean-Valentin Guru
    Currently Being Moderated
    Hello,

    but this method allows someone who queries the system processes to see the password (bad).
    You may also create a specific Oracle User with DATAPUMP_EXP_FULL_DATABASE Role. It should be enough to Export the Database in FULL mode:

    http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm#i1007504

    By that way, you won't have to use the SYSTEM User (and the SYS User shouldn't be used).

    Else, it's also possible to specify the User / Password in a Parameter file by using the parameter USERID. For instance, you begin (at the first line) the Parameter File with the line below:
    userid=<username>/<password>
    ...
    Then, you call expdp with the PARFILE parameter. The Password won't appear in the process.

    This link will give you more detail:

    http://shonythomas.blogspot.fr/2011/07/how-to-hide-oracle-passwords-sqlldr.html


    Hope this help.
    Best regards,
    Jean-Valentin

    Edited by: Lubiez Jean-Valentin on Apr 18, 2012 11:52 PM
  • 5. Re: expdp or impdp as 'sys'
    Dean Gagne Expert
    Currently Being Moderated
    You can also invoke expdp/impdp with no user name and have it prompt you. I think this method will not show the password on the running jobs.

    Dean
  • 6. Re: expdp or impdp as 'sys'
    972267 Newbie
    Currently Being Moderated
    I have to export a database which uses VPDB (Virtual Private Database). I tried to use a specific user (oraexp with EXPDP_FULL_DATABASE privilege) to script expdp but it gives a warning in exporting VPDB policy. If I use sys the problem does not occur. If I use sys I have another problem that is the syntax of bash script (Linux Platform). This is due to the need of specify role "as SYSDBA" or "as SYSOPER" at login. It works interactively but I have not succeded in scripting a bash variable to be interpreted the right way. Even if I surround "sys/pasword as sysdba" with double quotes to get it as sisngle string, when executing "as sysdba" is interpreted as a "bad parameter" of expdp.
    Another problem is that if an EXPDP job fails it leaves a table in the schema of the user that it is executing the JOB. Leaving tables in the SYS schema could not be dangerous, but is surely not a best practise....

    Anyone can help me?

    thanks in advance,
    Fabrizio De Massis
  • 7. Re: expdp or impdp as 'sys'
    phaeus Pro
    Currently Being Moderated
    Hello,
    if you use VPDB you may grant exempt access policy to this user, so that it can export the protected data.

    regards
    Peter

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points