Exploring Your Database

Database Explorer

Table of Content

Once you sign onto a database, you will be shown the Database Explorer window. This shows you all the objects in your database.

What you see in this window will depend on the type of database you are connecting to, and how AQT has been customized . . . the following description is generic, to give a general guide.

The Database Explorer has three panes:

the Object tree/schemas on the left shows the different types of objects in your database (Tables, Views, Indexes, Triggers etc). When you click on an object, you will be shown all the schemas within that object.
If the system-table query feature is not been enabled, or has not been configured for your database, you will only see Tables, Views and System tables in this object list.
For some databases (SQL Server, Sybase, Informix, MySQL, HP SQL/MX, Netezza), the Database listbox will appear above the object tree. This box lists all databases within the current server – you can select one of these to switch to that database.
the Object list in the middle grid lists all the objects (eg. tables) for a selected object-type.
Above the Object Display is the Admin toolbar; this provides the ability to manage the object selected in the object display. Full use of the Admin Component is limited to the Extended Edition of AQT.
the Object Details in the right grid shows the properties of the object that has been selected in the object display.

Notes:

the objects displayed by the Database Explorer window is different for every database type. This will depend on the way AQT has been configured for the database.
you can set a number of options for the appearance of the Object List and Object Details displays. These are given in the View menu option.
you can set the fonts used in the grids with Options > Database Explorer > Appearance.
you can adjust the pane borders between the grids and resize the screen. These settings are retained from one AQT session to the next.
the grid columns will automatically adjust their width to the size of the data. The section How to use Grids in AQT explains how grid columns are sized.

Using the Object Tree

The object tree shows the different types of objects in your database (Tables, View etc), each represented by a particular icon.

When you click in the object tree on an object icon (such as Table), the high-level grouping of the object is populated. This high-level grouping is called the Owner, Creator or Schema (depending on the terminology of your database). You could think of them as being folders used to group and organize the objects of that object-type (however, unlike files, the objects can’t be moved from one folder to another).
When you click on a Schema in the object tree, the object list will show all the objects (eg. tables) within that Schema.
Some object-types do not have a schema associated with them. Tablespaces and Users are examples of these. When you click on those object icons in the Database Explorer, the object list is populated. The same applies to some “simple” databases (such as Access and MySQL), for which tables do not have a schema.
The object tree can be hidden, to maximize valuable screen real-estate for the two grids. Clicking on the Objects icon in the Toolbar will hide or redisplay the object tree.
the Refresh icon in the toolbar is used to reload the information in the Object Tree from the database. You might do this if, for instance, you have created a new Schema.

If the object tree shows you only “Tables and Views” and “System Tables” then either you are using a database which doesn’t have system tables (eg. Access) or you have not enabled the system-table query feature. This setting is controlled by Options > General > Table Information > Get Table Information from.

Showing Objects, Favorites or Queries

The left pane of the Database Explorer window can show either Objects (as per the above screen-shot), Favorites or Queries. Click on the toolbar buttons Objects / Favorites / Queries to select what is shown in this pane.

If you select neither, the left pane will not be shown. This can be useful for increasing the screen real-estate.

Using the Object List

The Object List shows all the objects within the schema that you selected in the object tree.

Clicking on an object in the Object List will display the details of that object in the Object Details display.

A number of properties of an object can generally be displayed. For instance, for tables you can display the columns in the table, the access list for the table, the views based on that table, the contents of the table, plus much more. Right-click an object in the object list or click the list-box at the top of the object details to see what properties may be displayed. Selecting one of these options will display that property in the object details display.
The options you see when you right-click an object in the object list will depend on the system queries that have been set up for the database. These vary depending on the database type, as each database stores different information in the system tables. If you wish, you can change these queries or set up new ones. The Advanced Customization section explains how. Warning: changes to the system queries can have significant effects on the way AQT works, and should only be made with extreme caution, and only if you are an expert in your database type.
There may be more than one option for what information is displayed in the object list. When this is the case, a listbox displaying the options will appear at the top of the grid.
Some queries may not work if either
- you do not have permission to read the system table, or
- you are using an old version of the database that is not compatible with the AQT queries.
If you want to move quickly through all the objects in the object list you can use the keyboard's up and down arrows. This is a lot quicker than clicking on the individual objects.
If you type a letter while the focus is on the grid, the display will go to the first entry that begins with that letter.
If you have a large number of items in the object list, you can click on the Find icon to specify a Filter to restrict the objects displayed.
In the Evaluation version of Advanced Query Tool, a maximum of 1000 objects will be shown in the object list.
AQT caches the information in the Object List - this is discussed in more detail later.
You can Refresh the contents of the list by clicking on the Refresh button (above the grid on the right), hitting F5 or clicking on View > Refresh > Middle Grid

Using the Object Details

you can Print the contents of the Object Details display with File > Print Right Grid
you can Copy the contents of the Object Details grid by selecting a range of cells then hitting Ctrl+C
sometimes the Object Details display is text-box rather than a grid (such as display View or Procedure text). In this case, you can right-click the text and select various text functions such as Print, Copy, Copy to File etc
sometimes what is displayed in the Details Display are themselves database objects (indexes, tables etc). This can easily be seen, as the icons will be displayed with the objects. In this case you can right-click an object to get a list of actions you can do with this object. This will include displaying the details of the object, displaying table contents etc.

If the Administration Component is active for your database, you will see the Administration Toolbar at the top of the Object List in the middle grid. Once you click on an object, this toolbar will display a number of icons. You may or may not see all these icons, depending on how the Administration Component has been configured for your database.

The Database Explorer has three panes:

the Object tree/schemas on the left shows the different types of objects in your database (Tables, Views, Indexes, Triggers etc). When you click on an object, you will be shown all the schemas within that object.
If the system-table query feature is not been enabled, or has not been configured for your database, you will only see Tables, Views and System tables in this object list.
For some databases (SQL Server, Sybase, Informix, MySQL, HP SQL/MX, Netezza), the Database listbox will appear above the object tree. This box lists all databases within the current server – you can select one of these to switch to that database.
the Object list in the middle grid lists all the objects (eg. tables) for a selected object-type.
Above the Object Display is the Admin toolbar; this provides the ability to manage the object selected in the object display. Full use of the Admin Component is limited to the Extended Edition of AQT.
the Object Details in the right grid shows the properties of the object that has been selected in the object display.

Notes:

the objects displayed by the Database Explorer window is different for every database type. This will depend on the way AQT has been configured for the database.
you can set a number of options for the appearance of the Object List and Object Details displays. These are given in the View menu option.
you can set the fonts used in the grids with Options > Database Explorer > Appearance.
you can adjust the pane borders between the grids and resize the screen. These settings are retained from one AQT session to the next.
the grid columns will automatically adjust their width to the size of the data. The section How to use Grids in AQT explains how grid columns are sized.

Using the Object Tree

The object tree shows the different types of objects in your database (Tables, View etc), each represented by a particular icon.

When you click in the object tree on an object icon (such as Table), the high-level grouping of the object is populated. This high-level grouping is called the Owner, Creator or Schema (depending on the terminology of your database). You could think of them as being folders used to group and organize the objects of that object-type (however, unlike files, the objects can’t be moved from one folder to another).
When you click on a Schema in the object tree, the object list will show all the objects (eg. tables) within that Schema.
Some object-types do not have a schema associated with them. Tablespaces and Users are examples of these. When you click on those object icons in the Database Explorer, the object list is populated. The same applies to some “simple” databases (such as Access and MySQL), for which tables do not have a schema.
The object tree can be hidden, to maximize valuable screen real-estate for the two grids. Clicking on the Objects icon in the Toolbar will hide or redisplay the object tree.
the Refresh icon in the toolbar is used to reload the information in the Object Tree from the database. You might do this if, for instance, you have created a new Schema.

Showing Objects, Favorites or Queries

If you select neither, the left pane will not be shown. This can be useful for increasing the screen real-estate.

Using the Object List

The Object List shows all the objects within the schema that you selected in the object tree.

Clicking on an object in the Object List will display the details of that object in the Object Details display.

A number of properties of an object can generally be displayed. For instance, for tables you can display the columns in the table, the access list for the table, the views based on that table, the contents of the table, plus much more. Right-click an object in the object list or click the list-box at the top of the object details to see what properties may be displayed. Selecting one of these options will display that property in the object details display.
The options you see when you right-click an object in the object list will depend on the system queries that have been set up for the database. These vary depending on the database type, as each database stores different information in the system tables. If you wish, you can change these queries or set up new ones. The Advanced Customization section explains how. Warning: changes to the system queries can have significant effects on the way AQT works, and should only be made with extreme caution, and only if you are an expert in your database type.
There may be more than one option for what information is displayed in the object list. When this is the case, a listbox displaying the options will appear at the top of the grid.
Some queries may not work if either
- you do not have permission to read the system table, or
- you are using an old version of the database that is not compatible with the AQT queries.
If you want to move quickly through all the objects in the object list you can use the keyboard's up and down arrows. This is a lot quicker than clicking on the individual objects.
If you type a letter while the focus is on the grid, the display will go to the first entry that begins with that letter.
If you have a large number of items in the object list, you can click on the Find icon to specify a Filter to restrict the objects displayed.
In the Evaluation version of Advanced Query Tool, a maximum of 1000 objects will be shown in the object list.
AQT caches the information in the Object List - this is discussed in more detail later.
You can Refresh the contents of the list by clicking on the Refresh button (above the grid on the right), hitting F5 or clicking on View > Refresh > Middle Grid

Using the Object Details

you can Print the contents of the Object Details display with File > Print Right Grid
you can Copy the contents of the Object Details grid by selecting a range of cells then hitting Ctrl+C
sometimes the Object Details display is text-box rather than a grid (such as display View or Procedure text). In this case, you can right-click the text and select various text functions such as Print, Copy, Copy to File etc
sometimes what is displayed in the Details Display are themselves database objects (indexes, tables etc). This can easily be seen, as the icons will be displayed with the objects. In this case you can right-click an object to get a list of actions you can do with this object. This will include displaying the details of the object, displaying table contents etc.

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

Customizing the appearance

The appearance of the Database Explorer window can be customized in a number of ways.

Font

The font used in the Database Explorer window can be set in Options > Database Explorer > Appearance.

Style

You can select the style used by the grids with Options > Database Explorer > Appearance. The styles starting with DB are designed for use with the Database Explorer grids. The following are the in-built styles:

DB1	Plain
DB2	Two color
DB3	Gridlines
DB4	Two color plus gridlines

Hiding / Showing the Object Tree

The Object Tree can be hidden or shown by clicking on View > Objects, or the Objects button in the Toolbar.

The admin toolbar can be shown / hidden with View > Show Admin Toolbar. If you do not use the administration systen, you can permanently hide if with Options > User Rights > Hide the Admin Component.

You can dock the admin toolbar at the top/bottom/left/right of the Object List. This is specified with View > Admin Toolbar location.

Background and Header Colors

The background color is specified in Options > General > Backround Color. This applies to all AQT windows, not just the Database Explorer.

The color of the header is specified in Options > Database Explorer > Appearance.

You can customize both of these colors to be different for difference databases. This can be useful to have a visual cue about the database you are using - for instance you can use a red color for a critical database. This is discussed in more detail in Options > Database Profiles.

Object Details Location

You can specify that the Object Details display is to be located below the Object List. This can be useful if you wish to see the full width of the Object List or Object Details displays. This can be set by clicking on the up/down icon at the top of the Object Details, or View > Show Object Details below Object List.

Using Filters in the Schema and Object Lists

The Database Explorer window has two filters:

Schema Filter. This is located above the Object Tree. This only appears when you are displaying an object which has schemas. It filters the schemas of the objects currently being displayed.
Object Filter. This is located above the Object List. This filters the objects in the object list.

Information about how to specify filters can be seen at Filters.

Schema Filter

You can use the Schema Filter to reduce the number schemas shown in the schema-list for an object.

Multiple Filters

For the Schema Filter, you can specify multiple filters, separated by a comma. For instance:

CAT,.TEST

this will show all schemas containing CAT plus all those starting with TEST.

Remembering Filters

You can specify that AQT remembers your schema filters from one AQT session to the next. This is done with Options > Database Explorer > Remember schema filters between sessions

Performance of the Schema Filter

The schema filter operates quite differently from the Object filter, and other filters in AQT. With these, items are shown in a grid, and the grid rows are made visible / not visible according to the filter.

Schemas as shown in a Tree, and with this, items cannot made not-visible. As a result, the schema-list will be rebuilt every time the filter changes. This can be a time-consuming operation if you have a large number of schemas. A better option is to double-click the filter box then specify the filter on the filter dialog.

Secondly, filtering will run a lot faster if you are using Caching of the schemas. If caching is not being used, AQT will need to fetch the schemas from the database whenever the filter is changed. This is also a time-consuming operation.

As a final point, AQT will retrieve all schemas from the database and apply the filter to determine which of these is displayed. If you wish AQT to only retrieve a limited number of schemas from the database then this can be done by amending the queries in the config file for the database.

Using Caching in the Database Explorer

The Database Explorer has two options for caching the data in the displays.

the History Cache. This caches the displays to memory
the Disk Cache. This caches the displays to disk.

Caching will save a lot of time in AQT if you are moving around the Database Explorer. It saves AQT from continually re-fetching information on objects from the database. This can give a significant performance improvement if you are dealing with large databases, plus reduces the load on the network plus database.

The Disk Cache was introduced in AQT v11 and supercedes the History Cache. However the History Cache has been retained as some users may prefer it over the Disk Cache.

The pros and cons of these two caches are as follows:

both will cache both the list-of-schemas (which appears in the Object Tree) plus the list-of-objects (which appear in Object List)
the History Cache holds the data in memory so will result in more memory being used, particularly if you have a large number of objects in your database.
the Disk Cache holds data to disk, so the data is retained between AQT sessions. This will allow a faster startup of AQT if it takes a long time for AQT to fetch the list of your database objects.
by contrast, the History Cache will not retain any data between sessions. Some users prefer this as it allows a "clean" startup of AQT every time.

The options for specifying caching are given in Options > Database Explorer.

History Cache

History Caching is enabled by default.

When AQT is displaying information from the cache, you will see the following message in the status-bar:

x objects loaded from History buffer. Hit F5 if you need to refresh the data

Hitting F5 (Refresh) will re-fetch the data from the database. You might need to do this if you have created or dropped some objects.

The History icon (above the Object Tree) gives you quick access to your recently displayed objects.

Disk Cache

To use this form of caching, go to Options > Database Explorer. Click on Use Disk Cace for Schemas and/or Use Disk Cache for Objects.

When either of these options are used, the History Cache will be disabled.

When AQT has fetched the data from the disk cache, you will get the following messages in the bar at the bottom of the displays.

(for the Object Tree) xx schemas displayed from cache. Hit F6 to refresh
(for the Object List) xx objects displayed from cache. Hit F5 to refresh

You may wish to refresh the displays if more schemas or objects have been created. You can do this with F5/F6 or hitting the Refresh buttons above the displays.

Noe: when F6 is hit to refresh the schemas, all the schemas in the Object Tree are refreshed.