For more than 20 years my profession is the Oracle database administration. Starting in the early 90th on DEC VMS with a VT-100 terminal I’ve worked with many different tools. SQL*Plus is of course the most important one because it’s available from the beginning and replaced sqldba and later on srvmgrl. But in 1999 I had the pleasure working with a tool which was intended to help Oracle PL/SQL developers: Tool for Oracle Application Development (T.O.A.D).
Even for DBAs Toad already helped because of the simple Editor, the schema browser and of course the data grids which were much more readable than the SQL*Plus output. And that hasn’t changed until today. Toad still has a simple Editor with a very comfortable data grid. But with the extended functionality of the Oracle database Toad became more and more a Swiss knife for DBAs. I’m not only able to easily find objects but can manage my favorite Multitenant database as well.
Toad Database Browser
Long ago this tool was introduced and – to be honest – I didn’t use it at all. But it has one genius feature: One view on many databases: You can simply select databases (regardless if they are Pluggable databases or single databases, on one or several servers) and get an overview on the parameters, sessions or datafile I/O. May be some of you have found a similar view with another tool – for me it’s unique in the market!
One can love or hate Schema Browser depending on the layout. Some friends do prefer the tree view because they claim that the tabbed view is too chaotic. But with some view changes to the layout the tabbed view can become exciting:
- Configure the schema browser so it only shows the tabs you really need:
- add some additional columns to the left hand side so it gives you an overview on the size and free space of your tablespaces or let you order your tables by the number of rows.
- my personal favorite: look for objects regardless of the schema. The following example shows the schema browser with additional columns (e.g. schema_name, number of rows, last analyzed) sorted by table_names. With the filter column it’s possible to show or search for tables regardless of the schema.
When I first saw “compare Databases” I was wondering about the functionality. Why should I want to compare entire databases? Most of the time they do have different schemas and the tablespace layout is different. But then I realized that I can easily compare the server parameters (init.ora) or check for database options being installed or not. Very helpful if you have a bunch of development databases like one of my customers and one group is complaining “this database is slower than the other” or “this feature doesn’t work while it worked before”. Easy to get an overview and makes my life easier.
Comparing schemas is very common and often used. But Toad Schema compare has one additional feature. You can store the schema layout while comparing it and that allows you to do a historical comparison of schemas. So how did that schema looked one month ago, what happened last week.
This example shows the comparison between three schemas: The target schema is “DEMO1” compared to DEMO. But you can easily change the target schema to DEMO3 and compare it with DEMO. Another very useful feature is the Log Switch frequency map. It will give you a quick overview of the logswitches for a certain time. It helps if you encountered a problem with space e.g. your data tablespace runs out of space. If you ask if there was anything special happen to the application the normal answer is: “NO”. But with the log switch frequency map you can immediately see that there is a massive amount of log switches within a small time range. Ask the question again: “did anything happen to the application on March 26th between 11 and 12 a.m.”? Now there is a better chance to get the right answer…
These are only few examples of the rich functionality with Toad esp. with Toad DBA module. I would appreciate comments on this blog and like to hear about your favorite features in Toad.
If you are from Germany you should visit the German Oracle Users Conference (DOAG) from November 18th to 20th in Nuremberg. And if you want to get a deeper look into Toad features and functions you can join my seminar about “Toad for the DBA” on November 21st right after the Conference in Nuremberg.