Using TOAD's Schema Browser

Whether you're a DBA or a developer, TOAD's Schema Browser is a powerful and functional interface for exploring all your database objects. Not only does the Schema Browser enable you to quickly and easily navigate the complex structures within the database, but it also enables you to both manage and control all those structures (where your granted Oracle privileges permit). This screen is so extremely useful that when you're not coding or debugging, you'll most likely be exploring your database with the TOAD Schema Browser. It is accessible from the main toolbar and from the main menu at Database, Schema Browser.

Although many of today's Windows tools utilize tree views for their explorer GUI design, TOAD has pioneered and adopted a tabbed GUI design. The problem with tree views is that you end up scrolling too much when lots of information must be displayed. For example, opening a tree-view node for a user's tables might well display dozens of nodes and cause the tree view to scroll numerous other items of interest out of the main viewing area. By contrast, with the tabbed approach, less scrolling is generally required. Returning to the prior example, choosing a user's tables from the Tables tab does not cause your other main object categories (that is, tabs) to scroll off anywhere. Although it may initially take some getting used to, TOAD's tabbed GUI design is infinitely more productive in terms of wasted scrolling efforts. And time is money.

Finally, you might think you can skip this chapter because the Schema Browser is just another explorer and people use those every day. But the TOAD Schema Browser has so many features and capabilities you might never find that skipping this one chapter could make your overall TOAD usage much less productive. In fact, during the TOAD User Groups when "Tips and Tricks" are discussed, this one area alone seems to generate the most comments like "I did not know TOAD could do that." So please read on to learn how to fully utilize TOAD's Schema Browser and all its advanced capabilities.
Making Schema Browser Your Startup Screen
Many people find the Schema Browser so useful that they want TOAD to bring it up as their initial screen when TOAD either launches or creates a new database connection. The concept is that often you must navigate to something of interest and then perform work on it. Thus using the Schema Browser to locate and then operate on that object is often the most productive method for working within TOAD. To accomplish this, you simply check the box for Browser under the group Startup Windows per Connection under the StartUp category in the TOAD Options screen, as shown in Figure 2.1. The TOAD Options screen is accessible from the main menu at View, Options. Now TOAD will open a Schema Browser when you first launch it and for each new database connection.

Enabling or Disabling Schema Browser Tabs
Most people do not work with every conceivable Oracle object type, but rather a select subset that fits their needs and job description. For example, a PL/SQL developer may not care to see tabs for tablespaces, rollback segments, profiles, policies, and other more DBA-relevant information. So you can simply right-click to open a context menu on any tab or a tab's toolbar and select which tabs to enable or disable for display on the Schema Browser Then you will have a Schema Browser whose displayed tabs fit your needs. This might be quite useful for people who want the tabs to span lines, but cannot accept having all the tabs being displayed and thus losing so much real estate for displaying each tab's contextual information.

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.

Filtering Schema Browser Objects
It's not unusual for a typical database schema to have lots of objects. For example, Enterprise Resource Planning (ERP) applications such as SAP, Peoplesoft, Siebel, and Oracle Apps can have thousands of tables. As such, they often follow some naming convention to ease the object name overload. So the general ledger table names may be prefixed with "GL_", the accounts receivable with "AR_", the accounts payable with "AP_", and so on. But as the DBA, you or the developer might not be interested in always seeing all possible choices, but instead merely a user-defined subset based on the naming standard. TOAD provides filters for most of the Schema Browser tabs for accomplishing exactly this task.


Using Favorites as a Custom Schema Browser
Often in TOAD, you need to work with the same group or groups of objects. For example, you might only need to routinely work with the production tables for CUSTOMER and EMPLOYEE, their indexes and views. So you would like to have a customized Schema Browser for just those objects. TOAD offers the Favorites tab so that you can easily and quickly work in this manner. It can be a huge productivity enhancer. But far too often people don't seem to know about this feature's existence, and thus are working harder than they really need to. You merely need to set it up, define your favorites, and then begin using it as your customized schema browser.


Setting Some Advanced Schema Browser Options
TOAD's Schema Browser is a highly complex bit of code with numerous capabilities and nearly endless options. Although some of these options have already been examined within this chapter, there are still many more. In fact, there are so many options (and growing) that the TOAD Options category for the Schema Browser now has two complete pages. Unfortunately the options on each page do not seem to be based on any logical grouping, but rather just an overflow of what could fit on one page. So you'll need to visit both pages to be certain you've chosen all of the right option settings.