We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

iam getting ORA-01031: insufficient privileges. ODBC

it-rex
it-rex asked
on
Medium Priority
3,206 Views
Last Modified: 2012-05-06
I am attempting to export a Microsoft Access 2003 Service Pack 3 table into Oracle.
I am using ODBC with the Oracle Version 11 driver. (the driver number is 11.01.00.06)
iam getting ORA-01031: insufficient privileges.
it was running fine before we upgrade to 11g
Comment
Watch Question

CERTIFIED EXPERT

Commented:
You would have provided an username while creating the DSN. Does that user have appropriate rights? Are you able to perform INSERT operations when you log in to SQL Plus using that user?
You could try tracing on that error by setting the following in the pfile/spfile on the Oracle server and restarting.  You'll get a trace file when the error occurs.  It is wise to save the spfile to a pfile before making any changes so that if there's a syntax error, you can start using the pfile.

alter system set event="1031 trace name errorstack level 10" scope=spfile;

To reset all events:
alter system reset event scope=spfile;
and restart.

Of course, this kind of reminds me of the old joke about the patient telling his doctor that "it hurts when I do THIS".  So the doctor says "don't do THAT".  In other words, why not use the 10g client?
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
That Oracle error: "ORA-01031: insufficient privileges" indicates that Oracle thinks the Oracle user logon you use does not have permission to do whatever action you are trying to do via ODBC.

We don't know if that is: create a table; add records to a table, update existing records in a table, etc.  Which Oracle username does your ODBC connection use? Which Oracle username (schema) owns the table you are trying to use?

Was the Oracle database that you connect to also upgraded to Oracle11, or is this just an Oracle client upgrade, but the database is still Oracle 10 (or earlier)?

Author

Commented:
we are sure that this user has all the rights to do these inserts.
any more thoughts..
Database Administrator
CERTIFIED EXPERT
Commented:
And Oracle is sure the user does *NOT* have the rights.  Sorry, Oracle wins this argument.

Which Oracle user do you use for the ODBC connection?  Which Oracle user (schema) owns the table you are trying to insert records into?

What got upgraded to Oracle11g, just the client or was there also a database upgrade?

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

Commented:
the client is still 10g only the database is upgraded to 11g.
i have compared the user rights he used to use with 10g DB and he stll has the same riaghts exactly!!!
Mark GeerlingsDatabase Administrator
CERTIFIED EXPERT

Commented:
Now you say "the client is still 10g"  but earlier you told us: "I am using ODBC with the Oracle Version 11 driver. (the driver number is 11.01.00.06)"  That certainly looks like an 11g client to me!.  Anyway, I don't think the client version is the problem.  I think the problem is either in the database (since you did say now "the database is upgraded to 11g") or in the Oracle username that your ODBC connection uses.

Please tell us:

1. Which Oracle username do you use for the ODBC connection?
2. Which Oracle user owns the table?
3. Does your Access program do Oracle inserts only, or does it also try to do something else?
4. Do you have any other Access programs that interact with Oracle to do something else (like just read Oracle data) and if yes, do they still work?

Author

Commented:
thanks for your help it is an issue between 10g client and 11g DB the user had to be granted "select any transaction"
after i have done that it is just working fine.
thanks all for your help
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.