Re: ODBC failure from Access 97 to Oracle 8.1.7 database

Dino,

By any chance .. the SYS schema has not been analyzed ... has it?

Anurag

"Dino Hsu" <dino1.nospam_at_ms1.hinet.net> wrote in message news: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 - 10:40:48 CDT