6 Replies Latest reply: May 24, 2013 3:13 AM by DK2010 RSS

    EXPDP/IMPDP EXCLUDING ROLES

    N88
      Hi All,


      I can't call myself new to this forums since I have more than 10 post here but I can call myself new to Oracle databases so please bear with me.


      I have a scenario where I need to datapump fron source to target but the issue here is I should not export any connect,resource,dba roles associated with the users.

      Can anyone help me what needs to be excluded during the initial export to avoid them being imported on the target.




      Thanks in advance for your help,

      -N
        • 1. Re: EXPDP/IMPDP EXCLUDING ROLES
          Reid Ricks-Oracle
          Syntax and Description

          EXCLUDE=object_type[:name_clause] [, ...]

          The object_type specifies the type of object to be excluded. To see a list of valid values for object_type, query the following views: DATABASE_EXPORT_OBJECTS for full mode, SCHEMA_EXPORT_OBJECTS for schema mode, and TABLE_EXPORT_OBJECTS for table and tablespace mode. The values listed in the OBJECT_PATH column are the valid object types.

          All object types for the given mode of export will be included in the export except those specified in an EXCLUDE statement. If an object is excluded, then all of its dependent objects are also excluded. For example, excluding a table will also exclude all indexes and triggers on the table.
          • 2. Re: EXPDP/IMPDP EXCLUDING ROLES
            N88
            Hi 705159 ,


            Thank you for your reply.

            I m familiar with the EXCLUDE option. I need everything(tables,objects,synonyms and all that is there) except the roles and that too only certain roles.

            Initially I figured out that I would do content=data_only and then apply all the metadata But found out that it is leads to same point.

            I have to manually go revoke all the CONNECT,RESOURCE and DBA roles for certain users.

            EXCLUDE option comes with role_grants, default_roles and grants.

            I am sure role_grants and grants would not solve my problem. I haven't tried default roles.

            So, it would be helpful if some had faced similar situation can guide me.



            Thanks,
            -N
            • 3. Re: EXPDP/IMPDP EXCLUDING ROLES
              N88
              Hi user705159,




              Please ignore my previous message. I have been looking into the exclude syntax from when you posted a reply.

              And I have been trying something like this with the exclude option.

              EXCLUDE=ROLE:" LIKE 'USERNAME.%ROLE_NAME%'" and I couldn't get to exclude the role assigned to a user

              Any help is highly impreciated.


              Thanks
              -N
              • 4. Re: EXPDP/IMPDP EXCLUDING ROLES
                DK2010
                Hi,

                You can use the command like, EXCLUDE=ROLE_GRANT will exclude the role like DBA and RESOURCE
                expdp conn/pass  directory=DATA_DD_DIR dumpfile=scott_role.dmp logfile=scott_role.log SCHEMAS=SCOTT EXCLUDE=ROLE_GRANT
                HTH
                • 5. Re: EXPDP/IMPDP EXCLUDING ROLES
                  N88
                  Hi DK2010,


                  I have used what you have specified below and it excludes all the grants for a particular role.

                  Then, I have to move data_only and then apply changed DDL's which I am not sure if that is right or not.

                  Can anyone correct me on the above statement.



                  Thanks,
                  -N
                  • 6. Re: EXPDP/IMPDP EXCLUDING ROLES
                    DK2010
                    Hi,

                    As per your First Port you wanted to exclude _ connect,resource,dba roles_ on the basis of that i have given you the command.

                    You can also user the parameter EXCLUDE=SYSTEM_GRANT if you want to exclude some other system grant from the user, i am not sure about your requirement

                    HTH