• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2659
  • Last Modified:

iam getting ORA-01031: insufficient privileges. ODBC

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
iam getting ORA-01031: insufficient privileges.
it was running fine before we upgrade to 11g
1 Solution
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 AdministratorCommented:
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)?
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

it-rexAuthor Commented:
we are sure that this user has all the rights to do these inserts.
any more thoughts..
Mark GeerlingsDatabase AdministratorCommented:
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?
it-rexAuthor 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 AdministratorCommented:
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"  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?
it-rexAuthor 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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now