Re: ODBC failure from Access 97 to Oracle 8.1.7 database

For your information, I have unexpectedly solved the problem by downloading the latest Oracle ODBC Driver 8.1.7.7.0 from technet.oracle.com.

On Sun, 25 Aug 2002 11:41:04 +0200, "Anton Buijs" <aammbuijs_at_xs4all.nl> wrote:

>The objects accessed in views like ALL_TABLES are OBJ$, USER$ etc. They are
>owned by SYS and there are no privs granted on these objects, not even to
>SYSTEM or a DBA role.
>
>You can get the execution plan only when you run this script as SYS.
>Believe me, you don't want to see it. The plan returned 154 lines when I
>tested your script (V8.1.5 Personal, Win98).
>And then: how would you change it? Add, alter or drop indexes on the
>$-tables? Not supported by Oracle.
>You may be more lucky to search on Metalink. Maybe you find a bugfix.
>
>Dino Hsu <dino1.nospam_at_ms1.hinet.net> schreef in berichtnieuws
>uh3hmu0dqjsoq9l44vmlsm9ur303flnqdh_at_4ax.com...
>| Dear all,
>|
>| I encounter a problem when connecting to an Oracle database in Access
>| 97 using ODBC interface, the settings are:
>| Oralce client: 8.1.7 on Windows 98 SE
>| Oracle server: 8.1.7 on Windows NT SP3
>| ODBC driver: ORACLE ODBC Driver 8.01.07, Oracle Corp., SQORD32.DLL,
>| 2002/05/11
>|
>| Symptoms:
>| 1.List of tables shows up, wherefrom I can select tables, but in a
>| minute, it prompts "ODBC failure", and then ORA-01031 user requested
>| cancel of current operation, for each table selected.
>| 2.If I de-select "Enable Query timeout" in ODBC Driver Setup, it takes
>| 10 min. to perform the operation with no errors. However, I have to
>| wait the amount of time when I try to browse the liked table.
>| 3.This problem doesn't occur when I connect the same client to other
>| databases. On the other hand, it persists when I connect from other
>| client to the same database.
>| 4.In session management screen in OEM, I can see the session is
>| actually doing a SELECT query which takes a long time, so I write a
>| script odbs_test.sql to trace its performance, but it comes out with:
>| ORA-01039: insufficient privileges on underlying objects of the view
>|
>| * odbs_test.sql starts here *
>| set time on
>| set autotrace traceonly
>| spool odbc_test.log
>| SELECT
>| '',
>| T.owner,
>| T.table_name,
>| TO_NUMBER(NULL),
>| '',
>| '',
>| 0,
>| TO_NUMBER(NULL),
>| '',
>| '',
>| num_rows,
>| blocks,
>| NULL
>| FROM ALL_TABLES T
>| WHERE TABLE_NAME = 'TB_PRODUCTS' UNION
>| SELECT
>| '',
>| a.table_owner,
>| a.table_name,
>| decode(a.uniqueness, 'UNIQUE', 0, 1),
>| a.owner,
>| a.index_name,
>| 3,
>| b.column_position,
>| b.column_name,
>| 'A',
>| a.distinct_keys,
>| a.leaf_blocks,
>| NULL
>| FROM ALL_INDEXES a, ALL_IND_COLUMNS b
>| WHERE
>| a.owner = b.index_owner AND
>| a.index_name = b.index_name AND
>| a.table_owner='DM_ADMIN' AND
>| a.table_name='TB_PRODUCTS' UNION
>| SELECT
>| '',
>| c.table_owner,
>| c.table_name,
>| decode(a.uniqueness, 'UNIQUE', 0, 1),
>| a.owner,
>| a.index_name,
>| 3,
>| b.column_position,
>| b.column_name,
>| 'A',
>| a.distinct_keys,
>| a.leaf_blocks,
>| NULL
>| FROM ALL_INDEXES a, ALL_IND_COLUMNS b, ALL_SYNONYMS c
>| WHERE
>| a.owner = b.index_owner AND
>| a.index_name = b.index_name AND
>| b.table_owner = c.table_owner AND
>| b.table_name = c.table_name AND
>| (c.owner='DM_ADMIN' OR c.table_owner='DM_ADMIN') AND
>| c.synonym_name='TB_PRODUCTS'
>| ORDER BY 4, 5, 6, 8;
>| spool off
>| set autotrace off
>| set time off
>| * odbs_test.sql ends here *
>|
>|
>| * odbs_test.log starts here *
>| Execution Plan
>| ----------------------------------------------------------
>| ERROR:
>| ORA-01039: insufficient privileges on underlying objects of the view
>|
>|
>| SP2-0612: Error generating AUTOTRACE EXPLAIN report
>|
>| Statistics
>| ----------------------------------------------------------
>| 0 recursive calls
>| 52 db block gets
>| 747 consistent gets
>| 0 physical reads
>| 0 redo size
>| 1037 bytes sent via SQL*Net to client
>| 425 bytes received via SQL*Net from client
>| 2 SQL*Net roundtrips to/from client
>| 1 sorts (memory)
>| 0 sorts (disk)
>| 1 rows processed
>|
>| * odbs_test.log ends here *
>|
>| Anyone any ideas or advice? Thanks in advance.
>|
>| Regards,
>| Dino
>|
Received on Sun Aug 25 2002 - 19:21:26 CDT