Current Oracle Spatial Blog Articles
This is not a spatial article but an article on how to limit the list of objects returned to an ODBC client (eg Access) on initial connection.
One of the problems users face when connecting ODBC clients to an Oracle database is that the list of objects (tables, views etc) returned includes all the schemas in the database even if they are Oracle’s own (eg MDSYS, CTXSYS, DBSNMP, DMSYS, EXFSYS, OLAPSYS, SYSMAN, XDB etc etc). Here is an example where I have used the Microsoft ODBC driver for Oracle to make a database connection using Open Office Base:
Note that objects have been extracted from a large range of schemas (I have collapsed the schema folders holding the tables to make it easier to display in an image).
Plainly the display of objects from Oracle owned schemas or schemas for which the connecting user has not been granted permissions (via GRANTs) to any of its objects is confusing.
But there is a way around this.
Oracle’s Metalink describes the issue in article 124117.1. In this article it is suggested that schema level views be created for ALL_OBJECTS and ALL_SYNONYMS to override the views with the same name owned by SYS as these are the two views that are queried by ODBC drivers once a connection is established.
The suggested views are very simplistic so I modified these for my own use and publish them here.
When I now make a connection to Oracle using either the Microsoft of Oracle ODBC drivers I now get the desired reduced list as can be seen in the image below.
Ahh, that’s much nicer.
Now I am not suggesting that my views are right for anyone, all I am doing is pointing a way forward for those people who have experienced this problem and would like to be able to correct it. The approach Oracle suggests is powerful and flexible: you are free to modify or “tune” the view however you wish.
I hope this is of help to someone.