Notes on the Database Explorer for Oracle

The Database Explorer displays information on your database by running queries against the Oracle dictionary views. AQT has a choice of two sets of dictionary views:

• the ALL dictionary views (such as ALL_OBJECTS). These views only display the objects to which you have access. They are normally available to all users.
• the DBA dictionary views (such as DBA_OBJECTS). These views display all objects, irrespective of whether you have access to them. They are normally available only to privileged users.

The problem which AQT faces is that the ALL views can be very slow on systems with a large number of objects, particularly for users who have access to a lot of objects. The alternative is to use the DBA views, however many users will not have access to them.

How AQT deals with this is determined by Options > Database Explorer > For Oracle use DBA dictionary views?

• when this is No, AQT will use the ALL views
• when this is Yes, AQT will use the DBA views
• when this is Auto (the default) AQT will run a query to determine whether you have access to the DBA views. If so, AQT will use the DBA views, if not, AQT will use the ALL views

In most cases the Auto option would be the best option for most users. It also allows AQT to use the appropriate set of tables depending on the database and userid you are using.

You can see which set of tables AQT is using with Help > Database Details > Oracle Use DBA Views?

Use Caching

Many of the displays in the Database Explorer are a lot faster if you use caching.

See Options > Database Explorer > Disk Cache. It is recommended that both Use Disk Cache for Schemas and Use Disk Cache for Objects is selected.

When this option is selected, once the information is retrieved it is saved in a cache file on disk. When you subsequently display this information, the data is read from the cache file rather than the database. If necessary you can refresh the cache with F6 (for the list of schemas) or F5 (for the list of objects).

Using Queries on the Base System Tables (sys.obj$ etc)

Queries that use the Base System Tables are given in oracle.cfg file. They have been commented out so, by default, are not used.

Except for one query (Synonyms on Table) these queries only affect:

• the schema-lists (as shown in the Object Tree on the left)
• the object-lists (in the middle grid), which are displayed when you click on a Schema.

To implement these queries:

• ensure that you have access to the following system tables: sys.user$, sys.obj$, sys.tab$, sys.ts$
• edit oracle.cfg and de-comment the faster queries (eg. remove the ' from the start of the line). These are located near the bottom of the file.
• restart AQT

Note that these are "simpler" queries and may give slightly different results to the standard queries:

• you will see all objects irrespective of whether you have access to them
• there are a few other unusual cases in which the objects shown are slightly different to the standard queries.

If there are any problems with these queries, you can revert back to the standard queries by commenting out the fast queries.

Queries Still Slow?

If the queries that populate the schema-lists (in the Object Tree) are still very slow, you can speed them as follows.

• Change queries 1,2,3,4,5,6,9,18 to:

  Select USERNAME from SYS.ALL_USERS ORDER BY 1

• This will show, in the schema-list, all the Oracle user-ids. This is a fast, but not very good, way of displaying the schemas. It will show user-ids/schemas even if there are no objects for them (so when you click on a schema name you may not see any objects in the middle grid).
• Some people want to hard-code a list of schemas that people are to see. This can be done by changing queries 1,2,3,4,5,6,9,18 to (for instance):

  Select 'SCHEMA1' from DUAL union select 'SCHEMA2' from DUAL union select 'SCHEMA3' from DUAL