Creating, Altering, and Managing Database Objects

Probably the most common task any DBA does is to create, alter, and otherwise manage database objects. For example, the typical DBA might perform daily routine tasks such as:

• Add a new data file to a tablespace

• Modify a data file to increase its size

• Create a new rollback segment

• Create a new database role

• Grant a role or privilege to a role

• Create a new database user

• Grant a role or privilege to a user

• Create a table and some indexes on it

• Add or drop columns from a table

• Add a new constraint to a table

• Add a new index to a table

• Create a new view definition

In fact, the complete list of routine DBA tasks is almost endless, such that the preceding list merely attempts to show the nature of such tasks. Basically the DBA performs nearly all the commands found in the Oracle SQL Reference manual. That means the DBA is issuing ALTER, ANALYZE, COMMENT, CREATE, DROP, GRANT, RENAME, and REVOKE commands for all database object types and in all their various syntactical formats. That's one heck of a lot of syntax to have to remember!

Fortunately for the DBA, TOAD nicely encapsulates and abstracts nearly 100% of these commands by using its Schema Browser, shown in Figure 5.31. You can launch the Schema Browser either by clicking its icon on the main toolbar (second icon from the top left by default) or from the main menu at Database, Schema Browser. Although Figure 5.31 shows the Users tab, the basic principles of user interaction and screen behavior are virtually the same across all tabs. The right-hand side of the window will show detailed information for the selected object, whereas the left-hand side of the window will show the objects and toolbar icons to work on the selected objects. Moreover, those options are also available by using the context menu. Plus, users can multi-select objects with either method. Note that the first three toolbar icons shown on all the tabs are Copy Script to Clipboard (that is, reverse-engineer the DDL), Create New Object, and Alter Existing Object.

Figure 5.31. TOAD Schema Browser for routine DBA tasks.

Because the typical DBA needs to create, alter, and manage objects on a never-ending basis, wouldn't it be useful if TOAD could automatically open this screen when it launched? Well, it can, if you use the TOAD Options category for Startup as shown in Figure 5.32. You just need to check the box for Browser Within Startup Windows Per Connection. Now TOAD will automatically launch the Schema Browser at startup. It is also recommended that you check the box for Check for Access to DBA Views.

Figure 5.32. TOAD Startup Options for Schema Browser.