Filtering Schema Browser Schemas

It's not unusual for a typical database to have lots of schemas. In fact, many of today's database applications routinely create a schema per application user. These user schemas often possess no database objects, but instead access the central application tables by using grants and synonyms. When you're working within TOAD's Schema Browser, these user schemas are really of limited value. Thus you would like to see them filtered out of the drop-down list in the Schema Browser for the schema to focus upon (because they have no objects and will never really display anything). As with everywhere else in TOAD, there is an option to control this and it's simple to do.

Figure 2.10 shows the TOAD Schema Browser drop-down list for all the available schemas within the database, of which 16 are shown. But some of these schemas contain no objects, so there is really little or no value in being able to select them (i.e. their schema browser tabs would show no objects under them if displayed).

Figure 2.10. TOAD Schema Browser, showing all available schemas.

To eliminate such schemas from the drop-down list, you simply check the Only Show Users That Own Objects option under the group User/Schema Lists on Page 2 of the Schema Browser category of the TOAD Options screen as shown in Figure 2.11. You can access the TOAD Options screen from the main menu at View, Options.

Figure 2.11. TOAD options for Schema Browser filtering.

Now those empty schemas will be eliminated as shown in Figure 2.12 (the choices for BERT and DBSNMP are no longer displayed). This not only keeps the drop-down list for available schemas within the database short, but also offers for selection only those schemas that would have something to display within the Schema Browser. Note that the option for Only Show Users That Own Objects Excluding Synonyms in Figure 2.11 might be the best possible choice. Because it's not uncommon for database applications that create a schema per user to access tables via private and public synonyms, these users would display very little in the Schema Browser.

Figure 2.12. Displaying only schemas with objects.

Of course, just filtering by users who own objects may not be selective enough. What if your Oracle database contains many subject areas or applications? You might need to work on just a select few, and the others may be of little or no value to you. TOAD permits you to customize the list of users available by using the Configure User Lists screen as shown in Figure 2.13. You can access this screen from the main menu at View, Oracle Users List. You simply check and uncheck those schemas of interest and then click the OK button.

Figure 2.13. TOAD Configure User Lists screen.

Now the Schema Browser's Schema drop-down list will display only those selected schemas as shown in Figure 2.14. This is probably the most effective way to filter the Schema Browser since most database application developers tend to work on objects owned by just a few select schemas at a time. Just remember that you've set these filters or you may mistakenly think that TOAD is not propely showing you everything within your database.

Figure 2.14. Displaying only selected schemas.