0

Many people in my office use Oracle tables by linking them in Access (either 2010 or 2016) databases.

They always complain about the fact that, when Access is about to present them the list of tables they have access to in a specific Oracle instance for selection, they have to wait 10 to 15 minutes for the selection window to pop-up.

The reason seems to be the huge number of accessible tables, in turn related to the huge number of accessible schemas, on that particular instance, so that the resulting list contains tens of thousands of table names.

There was a similar problem with some DB2 databases, but was solved by adding a 'SCHEMALIST' option in the ODBC configuration (db2cli.ini) for those databases, to limit the number of schemas visible to the connection.

I had a look at Oracle ODBC documentation, but couldn't find a similar option, so I'm stuck.

Does somebody have a trick to share about how to filter schemas/tables visible to an ODBC connection to Oracle or otherwise speed-up linking those tables?

1
  • This probably could be solved by granting privileges only to the tables/schemas to which people need access. It's good for security as well as performance.
    – mustaccio
    Commented Apr 9, 2020 at 12:56

1 Answer 1

0

What if you create a special Oracle user that Access would use to connect? That user would only have the ability to access the tables that are relevant, plus it would have a synonym for every table it had access. Then when you connect from Access to Oracle, Access would only populate the table list with the tables that you want to see. I'm going to guess that you have at least tens of thousands of tables in your schema if not hundreds of thousands of tables. Are you running Peoplesoft?

You might want to find other tools to work with besides Access. There must be other BI tools that you can work with. Possibly even a few open source tools.

2
  • I definitely recommend "look at other tools". Don't forget to check out the tools that are already included with your licensed Oracle RDBMS. (eg APEX, ORDS) Commented Apr 9, 2020 at 13:24
  • Unfortunately, users need to log into the DB using their own accounts for audit purposes. Also changing the tool is out of question since that would involve a signifcant training effort. I was looking for a completely transparent solution, but sometimes this is asking too much. Seems like they have to live with that
    – aatoma
    Commented Apr 16, 2020 at 11:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.