1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

11g impdp grant issues with schemas

Discussion in 'SQL PL/SQL' started by jrmtl, Nov 21, 2013.

  1. jrmtl

    jrmtl Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Hello all,

    Pardon my ignorance on this task... but I am trying to expdp and then import several users from a 11.1. to an 11.2 DB.
    After importing completes on the destination , I try to login with an importeed users and it says `user does not have the create session privilege'

    What did parameter did I miss on the exp or imp ?

    thx
    JR
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,348
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    None. The user was created without CREATE SESSION privilege on the source database. It's a simple issue of connecting as SYS and and granting that privilege:

    grant create session to <username here>;


    This will fix the issue.
     
  3. jrmtl

    jrmtl Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Thx for responding !

    I noticed that the grant was granted within a role that does existon the destination DB. How can export the role or should I just create the roles manually before the import?
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,348
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    If you're doing schema exports you don't get roles; you would need to perform a full database export to get those. You could use dbms_metadata.get_ddl() to extract the commands to create a role or roles; conversely you can simply create the role and grant the privileges yourself. You should also have seen an error on grants in the log (or impdp output if you didn't create a logfile) showing that role, possibly among others, is missing.
     
    jrmtl likes this.
  5. jrmtl

    jrmtl Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Great that's what i thought and I have to manually create the roles on the destination as I'm not doing a full import.
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,348
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You will also need to grant the 'missing' roles to the affected user.
     
  7. jrmtl

    jrmtl Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    I noted that it's done during the schema import ... if the roles don't exist i get errors therefore if I create beforehand it grants it automatically
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,348
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    True -- I based my last response on the previous import attempt that didn't succeed at the grants. If you're re-running the import than impdp does that for you.