7 Replies Latest reply: Nov 2, 2012 5:43 PM by phaeus RSS

    expdp or impdp as 'sys'

    732974
      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
          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'
            Heddy.Moz
            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
              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
                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
                  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
                    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
                      Hello,
                      if you use VPDB you may grant exempt access policy to this user, so that it can export the protected data.

                      regards
                      Peter