iam getting ORA-01031: insufficient privileges. ODBC

Posted on 2009-02-17
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
iam getting ORA-01031: insufficient privileges.
it was running fine before we upgrade to 11g
Question by:it-rex
    LVL 10

    Expert Comment

    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?
    LVL 3

    Expert Comment

    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?
    LVL 34

    Expert Comment

    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)?
    LVL 11

    Author Comment

    we are sure that this user has all the rights to do these inserts.
    any more thoughts..
    LVL 34

    Accepted Solution

    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?
    LVL 11

    Author Comment

    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!!!
    LVL 34

    Expert Comment

    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?
    LVL 11

    Author Closing Comment

    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
    How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
    This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
    This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now