Re: ODBC failure from Access 97 to Oracle 8.1.7 database

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 - 04:41:04 CDT