7 Replies Latest reply on May 3, 2010 2:08 PM by spur230

    rights needed for impdp and expdp

    spur230
      Hello friends,

      I am using oralce 10g 10.2.0.4 in sun os. I want to use data pump with schema_remap A:B option and posibility network_link.

      What are all the grants and priviledges for database user I need to ask for in schema A and B?

      What kind of OS user could perform impdp /expdp? Lastly, will I need any special right if I want to use OEM?

      Thank you for any comment and suggestion.
        • 1. Re: rights needed for impdp and expdp
          Andy Klock
          Here is a good starting point:

          http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_overview.htm

          You need EXP_FULL_DATABASE to export from other schemas and IMP_FULL_DATABASE to import into other schemas (this includes SCHEMA_REMAP).

          Datapump is authenticated by the user running it, so whether you are running it from a client workstation or OEM or the server (ok, not the server, you'll need to be part of the DBA group for that) datapump will use your Oracle user's permissions.

          Datapump creates it's files on the database server so if you want these files in another directory you will have to create and Oracle Directory.
          • 2. Re: rights needed for impdp and expdp
            spur230
            Thank you Andy. It really helped.
            • 3. Re: rights needed for impdp and expdp
              spur230
              Datapump is authenticated by the user running it, so whether you are running it from a client workstation or OEM or the server (ok, not the server, you'll need to be part of the DBA group for that) datapump will use your Oracle user's permissions.
              Does the user need to be part of DBA group to run data pump utility from server?
              • 4. Re: rights needed for impdp and expdp
                Dean Gagne-Oracle
                It all depends on what you want that user to be able to export/import. If all that use is exporting/importing is their own objects, then they don't need any raised privs. If you want that user to be able to export objects owned by other schemas, then they would need the exp_full_database and in order to import, they would need imp_full_database.

                Dean
                1 person found this helpful
                • 5. Re: rights needed for impdp and expdp
                  spur230
                  Thank you dean. My confusion is when I telnet to the unix machine have oracle to you impdp does that user need to be in oracle dba group.
                  • 6. Re: rights needed for impdp and expdp
                    Dean Gagne-Oracle
                    I'm not sure what you are asking. Are you asking about the unix-user or the oracle-schema?

                    If you are asking about the oracle-schema, I'm not sure what you mean about the dba-group. There is a dba privilege that oracle has defined that you can give to users, but this would give the user full privileges on the database. You do not have to give the oracle_schema that dba privilege unless you want that user to be able to export/import objects from other oracle-schemas.

                    If you set up the unix system to allow only unix-users that belong to the dba-group to be able to run expdp or impdp, then I would think that you would have to add that group to the unix-users that you want to be able to run expdp/impdp.

                    Or maybe you are asking a different question?

                    Sorry about the confusion.

                    Dean
                    1 person found this helpful
                    • 7. Re: rights needed for impdp and expdp
                      spur230
                      Thank you Dean I was asking if the unix user need to be part of dba group.