The Database Explorer runs a number of queries on the Oracle system views. If you have a large database these queries can be quite slow. This is because the standard Oracle system views (all_tables etc) are complicated multi-table joins, which are slow on Oracle systems with a large number of objects.
This is particularly true with the Functions, Procedures and Packages display, which uses all_objects (which is very slow).
There are a number of solutions to this problem:
Queries that use the Base System Tables are given in the Oracle config file 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:
To implement these queries:
Note that these are "simpler" queries and may give slightly different results 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.
If the queries that populate the schema-lists (in the Object Tree) are still very slow, you can speed them as follows.
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).
Select 'SCHEMA1' from DUAL union select 'SCHEMA2' from DUAL union select 'SCHEMA3' from DUAL