On 3 Dec 2005 18:48:06 +0100, "Vincenzoni Attilio"
<aaa.avincenzoni_at_hotmail.com> wrote:
>I'm an user, not IS guy, and i'm not sure whether i'm on topic. I have an
>Access / Oracle problem, where Access people couldn't help.
>
>Until recently, several branches of our department either had their own
>8.1.7 Oracle server, or they had separated instances (SIDs) on a bigger
>host. The oracle database structure, table names etc, is the same in every
>branch.
>
>In my workstation (W2000, Access 97) i had multiple ODBC names, each
>referring to the proper server / SID of every branch. Within MS Access 97, I
>linked via ODBC tables from every server, and by pushing a command button, I
>could run a series of queries interrogating the same table of every
>branch, one after another, and could consolidate their data into a local
>table for subsequent analysis.
>
>Recently, the IS people set up a single department host running Oracle 9,
>where every branch has their own tables under separated schemas. They all
>refer to the same server and database name, then according to the id/pw with
>which they log on the server, they see their own tables.
>
>From Access, i again can see every branch's tables, by linking the server
>and giving the proper id/pw when promped for. Hower, i am no longer able of
>running a series of queries, for after i have taken the data from the first
>table, when i try to query the second table under another schema, ODBC
>reports an Oracle error that "the table name does not exist". However, if i
>exit Access, reenter, and manually run the query, the table is seen and the
>query completes successfully.
>
>In other words, i cannot access tables under different schemas from a
>single session of Access / ODBC. I must exit and reenter Access in order to
>shift to another schema and query their tables, which is most unpractical.
>
>Why does this happen and how can I run my queries under a single Access
>session?
>
>Thank you for any insight you could provide.
>
>VA
>
>
>
>
>
>
>
>
Apparently the data is in schema_1, schema_2, etc and you log in as
the schema owner.
As schema_2 never granted select privilege to schema_1, and schema_1
also doesn't have a synonym on schema_2, you get 'ora-942 table or
view does not exist'
Obviously, the best solution is merging all data in *one single
schema* adding an extra column to all individual tables, denoting the
original schema. Whoever want to see the original data simply adds
and schema = '1' to their query.
The second best solution (better : workaround) is setting up a
reporting user (which doesn't own any tables), and all the individual
schema's grant the select privilege on all affected tables to that
reporting_user.
You will then be able to run your queries by simply prefixing the
schemaname to all table names, ie schema_1.emp, schema_2.emp etc.
If you would like to use synonyms you are going to have one set of
synonyms per schema. This may be a nightmare.
--
Sybrand Bakker, Senior Oracle DBA
Received on Sat Dec 03 2005 - 12:05:49 CST
