Toad® for Oracle: Historical and future space usage

Did you know that Toad® for Oracle can allow you to track Tablespace Usage historically and even forecast how much space you’ll need going forward? Knowing this information is critical for planning purposes, identifying anomalies and much more.

View current space usage and add more

Most of us know that Toad for Oracle allows us to view current space usage and administer all our tablespaces. You are probably using this today. You simply access your tablespaces tab within either the Schema Browser or Database Browser.

This is a very useful feature allowing you to see how much space is currently being used. If you need to add more space,  you can simply double-click on any tablespace and an Alter Tablespace dialog will open.

Now comes the fun part!!

There is another Tablespaces window you can open by simply selecting Database>Administer>Tablespaces from the pull-down menu.

Alternatively, you can type “Tablespaces” in our Jump Search Bar located on the upper right corner of your Toad for Oracle desktop. If you're not sure where your Jump Search is, Ctrl-J will place your cursor directly there.

By using either method, you’ll open the Tablespaces Window where you’ll see the Space (default tab), Files, Objects, Space History and IO History tabs.

View and alter tablespaces and Datafiles

The Space and File tabs not only allow you to view the space usage of all your tablespaces and Datafiles respectively but, you can also alter them too by double-clicking.

Forecast our tablespace usage

Now let’s see how we can view our historical space usage and forecast our tablespace usage.

Before you can see information in the Space and IO History tabs, you’ll need to first set it up. If you haven’t created the Toad Space Manager tables, you can create them by clicking on the Create/Recreate Toad Space Manager Tables icon with the Space History tab. It is highlighted in Yellow.

Edit the time and frequency

Once done, you can edit the time and frequency of when the DBMS_JOB runs as well as the history retention.

Once you’ve started collecting metrics, you can tailor your view based upon which tablespaces you want to see with “All” being a choice. For full transparency, you can even see the actual SQL statement being run to collect these metrics (Show SQL). Knowing where this information is stored allows you access to the data for exporting, customizing reports or whatever you can imagine.

Zoom in on the graph

You can even zoom in on the graph using your mouse. Simply drag a rectangle from left to right on the area you to zoom in on to view a specific time range.

Forecast your tablespace usage

In order to forecast your tablespace usage, you can click on the Forecast button.

On this screen, you can specify how many days in the future you want to project space usage for. There is an option to print out this graph.

It is as simple as that!

If you are licensed for using the AWR Browser, there is even a button to explore specific object growth.

This is just one of the many simple, yet powerful features in Toad for Oracle that I hope you take can take advantage of.

Note: This feature requires the DB Admin Module and DBA privileges.

Other useful resources:

Product page: Toad for Oracle general information.

Blog:Coffee with Toad® [Toad World® blog monthly recap]

Technical brief: Top Five Reasons to Choose Toad Over SQL Developer

Case study: Opening doors and creating opportunities with data insights

Questions?

Have questions, comments, or an idea for a new feature? Interact with experts on theToad for Oracle forum on Toad World®.

Share on social media

If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post. Thanks!