ODBC failure from Access 97 to Oracle 8.1.7 database

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 - 03:25:42 CDT