11g impdp grant issues with schemas

All posts relating to Oracle database administration.

Moderator: Tim...

11g impdp grant issues with schemas

Postby jrmtl » Thu Nov 21, 2013 6:26 pm

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 i mis on the exp or imp ?

thx
JR
jrmtl
Senior Member
 
Posts: 195
Joined: Tue Feb 22, 2005 7:08 pm
Location: Montreal

Re: 11g impdp grant issues with schemas

Postby Tim... » Fri Nov 22, 2013 12:17 am

Hi.

Please post the commands you used to do the export and import. Remember to alter any sensitive information.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17931
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: 11g impdp grant issues with schemas

Postby jrmtl » Fri Nov 22, 2013 3:08 pm

I think it`s because the grant is given within the role which was not exported and hence imported.
I would have to give the grant to each user unless I bring over the role via the export command .

This option doesn't seem to work INCLUDE=ROLES so I guess I would have to manually create the roles associated with the users on the destination
jrmtl
Senior Member
 
Posts: 195
Joined: Tue Feb 22, 2005 7:08 pm
Location: Montreal

Re: 11g impdp grant issues with schemas

Postby Tim... » Fri Nov 22, 2013 5:32 pm

Hi.

You can only include things that are in the scope of export, so for example, roles are in the scope of a full database export, but not in the scope of a table or user level export.

You can always create the roles using DBMS_METADATA.

http://www.oracle-base.com/dba/script.p ... le_ddl.sql

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17931
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK

Re: 11g impdp grant issues with schemas

Postby jrmtl » Fri Nov 22, 2013 5:58 pm

Great thx !
jrmtl
Senior Member
 
Posts: 195
Joined: Tue Feb 22, 2005 7:08 pm
Location: Montreal

Re: 11g impdp grant issues with schemas

Postby Tim... » Fri Nov 22, 2013 6:17 pm

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog
Tim...
Site Admin
 
Posts: 17931
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK


Return to Oracle Database Administration

Who is online

Users browsing this forum: No registered users and 5 guests