5 Replies Latest reply: Feb 18, 2013 8:51 AM by Richard Harrison . RSS

    sys and system for export and import

    902332
      Hi all,

      is there any difference taking the export or import, usiing sys user and system user.

      some schema export and imports has to do with system user only.

      what difference makes it.

      Thank you!
        • 2. Re: sys and system for export and import
          Fran
          sys user is not recommended to take import/export by oracle, unless oracle support told you.
          • 3. Re: sys and system for export and import
            Osama_Mustafa
            "I am not a fan of exporting as the SYS user and I, personally, do not use SYS, but it can be done, and there are very special circumstances where you may need to perform an export as SYS. As well, you will find that some DBAs do, in fact, export their databases as the SYS user. It's difficult to say what's happened in this case without reviewing an import log file. It is possible that not all objects were imported for some reason (space issues, etc.) or grants/privileges are missing. My first check would be to make sure that all objects were imported, so I would compare object counts between the source and the target. Second, if all objects were imported successfully, I would check that all grants and privileges came across in the import. The root cause may, in fact, be importing as SYS, but some basic checks should be performed first."


            http://searchoracle.techtarget.com/answer/Using-SYS-for-export
            • 4. Re: sys and system for export and import
              Aman....
              899329 wrote:
              Hi all,

              is there any difference taking the export or import, usiing sys user and system user.

              some schema export and imports has to do with system user only.

              what difference makes it.
              What's the difference between logging in as Sys and System and a normal user, let's hear that from you first! And that would answer this question too.

              Aman....
              • 5. Re: sys and system for export and import
                Richard Harrison .
                Hi,
                Generally never use sys - you can't get a read consistent export for one thing as the sys user. As long as the user you run expdp/impdp has exp_full_database/imp_full_database they should be able to unload/load anything.

                SYS owned objects cannot be exported/imported anyway. tab$ for instance is a table in sys but rows in this are added/removed by recursive sql when running create/drop table.

                Regards,
                Harry