I am an EBS Apps DBA. When I talk with other DBA.s, it is clear that every one of us EBS DBAs has their own set of cool queries helping us complete our day-to-day tasks. I have such a toolkit too. Sometimes I borrow a query from another DBA’s blog, sometimes I find something useful in a MOS Doc or a book. My toolkit is ‘organized’ in a couple of text documents with short remarks on usage and an origin of a query. This tookit of mine has worked for me for many years until I discovered Blitz Report. Now I use Blitz Report exclusively for all of my DBA reports It provides me with new possibilities of staying organized, especially when I am working in a team.

First of all Blitz Report comes with a set of seeded DBA reports which is constantly updated. And you can create your own reports. It’s much more convenient to maintain your reports in Blitz Report which allows you to provide a description and offers version control. Let me provide an example.

Suppose you manage a team of DBAs. Every DBA uses their own query to check tablespaces size. Some of the queries are obsolete and don’t consider possible datafiles autoextension. So you decided to write a query relying on a dba_tablespace_usage_metrics table and ask your teammates to use this report as an ‘official’ one. So you start with a really simple query:

select * from dba_tablespace_usage_metrics order by used_percent desc

This is the first version of your report, and below is the example of the report output

Creating a DBA Blitz report
Blitz Report DBA report output example

Later you and you team members decide that it’s better to change display units to something more readable than a number of blocks for the “Used Space” and “Tablespace Size” columns. So you modify the report in the following way to display the space metrics in megabytes:

select
tablespace_name "Tablespace Name",
(used_space*(select value from v$parameter where name = 'db_block_size'))/(1024*1024) "Used Space Mb",
(tablespace_size*(select value from v$parameter where name = 'db_block_size'))/(1024*1024) "Tablespace Size Mb",
used_space/tablespace_size*100 "Used Percent"
from
dba_tablespace_usage_metrics
order by used_percent desc

This is now the second version of the report

Creating a DBA Blitz report
Output of a DBA Blitz Report example

Someone on the team then asks to add a “Free Space” column. No problem, it can be easily done:

select
tablespace_name "Tablespace Name",
((tablespace_size-used_space)*(select value from v$parameter where name = 'db_block_size'))/(1024*1024) "Free Space Mb",
(used_space*(select value from v$parameter where name = 'db_block_size'))/(1024*1024) "Used Space Mb",
(tablespace_size*(select value from v$parameter where name = 'db_block_size'))/(1024*1024) "Tablespace Size Mb",
used_space/tablespace_size*100 "Used Percent"
from
dba_tablespace_usage_metrics
order by used_percent desc

This is now the third version of the report

When using Blitz Report, not only does the software automatically track all of these changes with a fresh version number, but you can provide comments for each version to keep track of the changes.

Controlling Blitz Report version history

With Blitz Report you have a centralized repository for all your reports. If a report is changed all your team members will have an immediate access to a new version which eliminates the situation when someone can use an obsolete local version of a report. The Blitz Report version control system allows you to track who changed a report and why it was changed. You can restore a previous version of a report if necessary.

It’s not easy for a DBA to give up a habit of using sqlplus to get data from the backend. But give Blitz Report a try – and you will love it.