Mostly Making oracle easy, mostly:
Search for:
In SQL Developer

Space Report from SQL Developer..in SQLcl

2 Mins Read

I was using the Instance Viewer today and ran into the drill down for Storage.

Instance Viewer, whiz-bang!

Let’s take a closer look at that report.

Some of this isn’t going to work in a CLI…

I like this report enough, that I want to be able to run it in SQLcl. SQLcl is a command-line interface, so I’ll have to make a few changes.

The links won’t work, boo. Those are easy to fix up – just remove the SQLDev:Link code. The Used (proportion) column is a Gauge, so that probably won’t work either…

Side bar:

Adding Commands/Reports to SQLcl

I’m going to use the ALIAS feature to create a ‘SPACE’ command.

I’m going to need some SQL first, so I clear the Statements log panel, and run the report again to get the raw SQL.

The Statements panel is only available for Oracle THIN JDBC connections.

Now that I have the base SQL, the main thing is to handle the Gauge. So I go with a Nested Case, and I’ll use some Pipe (|) characters to denote the green, orange, and red areas.

In SQLcl, it’s easy to make this report a new command.

You can use binds too if you want…

No worries, the code is below if you want to skip a few steps.

But now I can run it:

Tip: use a fixed width font when typing and whitespace matters!

Let’s blow that up a bit…

Not sophisticated, but it’ll work.

My Question/Challenge To You

Are you using SQLcl?

If so, have you used the Alias feature?

If your answers are anything but ‘yes’ and ‘yes’ – why not? And if so, share what you’ve built, we’d love to see it!

The Code

SELECT /*+ all_rows use_concat */ ddf.file_name AS "File Name",
       ddf.tablespace_name AS "Tablespace",
       ddf.online_status AS "Status",
       to_char(nvl(ddf.bytes / 1024 / 1024,0),'99999990.000') AS "Size (MB)",
       to_char(decode(nvl(u.bytes / 1024 / 1024,0),0,nvl( (ddf.bytes - nvl(s.bytes,0) ) / 1024 / 1024,0),nvl(u.bytes / 1024 / 1024,0) ),'99999999.999') AS
"Used (MB)",
       CASE
        WHEN ddf.online_status = 'OFFLINE' THEN 'OFFLINE'
        WHEN ddf.online_status = 'RECOVER' THEN 'RECOVER'
        ELSE
            CASE
                WHEN TRIM(to_char(decode( (nvl(u.bytes,0) / ddf.bytes * 100),0,nvl( (ddf.bytes - nvl(s.bytes,0) ) / ddf.bytes * 100,0), (nvl(u.bytes,0) / ddf.bytes
* 100) ),'990') ) BETWEEN 0    AND 14 THEN '|*     | | |'
                WHEN TRIM(to_char(decode( (nvl(u.bytes,0) / ddf.bytes * 100),0,nvl( (ddf.bytes - nvl(s.bytes,0) ) / ddf.bytes * 100,0), (nvl(u.bytes,0) / ddf.bytes
* 100) ),'990') ) BETWEEN 15    AND 24 THEN '|**    | | |'
                WHEN TRIM(to_char(decode( (nvl(u.bytes,0) / ddf.bytes * 100),0,nvl( (ddf.bytes - nvl(s.bytes,0) ) / ddf.bytes * 100,0), (nvl(u.bytes,0) / ddf.bytes
* 100) ),'990') ) BETWEEN 25    AND 34 THEN '|***   | | |'
                WHEN TRIM(to_char(decode( (nvl(u.bytes,0) / ddf.bytes * 100),0,nvl( (ddf.bytes - nvl(s.bytes,0) ) / ddf.bytes * 100,0), (nvl(u.bytes,0) / ddf.bytes
* 100) ),'990') ) BETWEEN 35    AND 44 THEN '|****  | | |'
                WHEN TRIM(to_char(decode( (nvl(u.bytes,0) / ddf.bytes * 100),0,nvl( (ddf.bytes - nvl(s.bytes,0) ) / ddf.bytes * 100,0), (nvl(u.bytes,0) / ddf.bytes
* 100) ),'990') ) BETWEEN 45    AND 54 THEN '|***** | | |'
                WHEN TRIM(to_char(decode( (nvl(u.bytes,0) / ddf.bytes * 100),0,nvl( (ddf.bytes - nvl(s.bytes,0) ) / ddf.bytes * 100,0), (nvl(u.bytes,0) / ddf.bytes
* 100) ),'990') ) BETWEEN 55    AND 64 THEN '|******| | |'
                WHEN TRIM(to_char(decode( (nvl(u.bytes,0) / ddf.bytes * 100),0,nvl( (ddf.bytes - nvl(s.bytes,0) ) / ddf.bytes * 100,0), (nvl(u.bytes,0) / ddf.bytes
* 100) ),'990') ) BETWEEN 65    AND 74 THEN '|******* | |'
                WHEN TRIM(to_char(decode( (nvl(u.bytes,0) / ddf.bytes * 100),0,nvl( (ddf.bytes - nvl(s.bytes,0) ) / ddf.bytes * 100,0), (nvl(u.bytes,0) / ddf.bytes
* 100) ),'990') ) BETWEEN 75    AND 84 THEN '|********| |'
                WHEN TRIM(to_char(decode( (nvl(u.bytes,0) / ddf.bytes * 100),0,nvl( (ddf.bytes - nvl(s.bytes,0) ) / ddf.bytes * 100,0), (nvl(u.bytes,0) / ddf.bytes
* 100) ),'990') ) BETWEEN 85    AND 94 THEN '|********* |'
                WHEN TRIM(to_char(decode( (nvl(u.bytes,0) / ddf.bytes * 100),0,nvl( (ddf.bytes - nvl(s.bytes,0) ) / ddf.bytes * 100,0), (nvl(u.bytes,0) / ddf.bytes
* 100) ),'990') ) BETWEEN 95    AND 100 THEN '|**********|'
                ELSE '?'
            END
    END
AS "Used (Proportion)",
       to_char(decode( (nvl(u.bytes,0) / ddf.bytes * 100),0,nvl( (ddf.bytes - nvl(s.bytes,0) ) / ddf.bytes * 100,0), (nvl(u.bytes,0) / ddf.bytes * 100) ),'990.99'
) AS "Used (%)",
       ddf.autoextensible AS "Auto Extend"
  FROM sys.dba_data_files ddf,
       ( SELECT file_id,
                SUM(bytes) bytes
           FROM sys.dba_free_space
          GROUP BY file_id
) s,
       ( SELECT file_id,
                SUM(bytes) bytes
           FROM sys.dba_undo_extents
          WHERE STATUS <> 'EXPIRED'
          GROUP BY file_id
) u
 WHERE (
    ddf.file_id = s.file_id (+)
       AND ddf.file_id = u.file_id (+)
)
UNION
SELECT v.name AS "File Name",
       dtf.tablespace_name AS "Tablespace",
       dtf.status AS "Status",
       to_char(nvl(dtf.bytes / 1024 / 1024,0),'99999990.000') AS "Size (MB)",
       to_char(nvl(t.bytes_used / 1024 / 1024,0),'99999990.000') AS "Used (MB)",
       CASE
        WHEN dtf.status = 'OFFLINE' THEN 'OFFLINE'
        ELSE
            CASE
                WHEN TRIM(to_char(nvl(t.bytes_used / dtf.bytes * 100,0),'990.99') ) BETWEEN 0   AND  14 THEN '|*     | | |'
                WHEN TRIM(to_char(nvl(t.bytes_used / dtf.bytes * 100,0),'990.99') ) BETWEEN 15  AND  24 THEN '|**    | | |'
                WHEN TRIM(to_char(nvl(t.bytes_used / dtf.bytes * 100,0),'990.99') ) BETWEEN 25  AND  34 THEN '|***   | | |'
                WHEN TRIM(to_char(nvl(t.bytes_used / dtf.bytes * 100,0),'990.99') ) BETWEEN 35  AND  44 THEN '|****  | | |'
                WHEN TRIM(to_char(nvl(t.bytes_used / dtf.bytes * 100,0),'990.99') ) BETWEEN 45  AND  54 THEN '|***** | | |'
                WHEN TRIM(to_char(nvl(t.bytes_used / dtf.bytes * 100,0),'990.99') ) BETWEEN 55  AND  64 THEN '|******| | |'
                WHEN TRIM(to_char(nvl(t.bytes_used / dtf.bytes * 100,0),'990.99') ) BETWEEN 65  AND  74 THEN '|******* | |'
                WHEN TRIM(to_char(nvl(t.bytes_used / dtf.bytes * 100,0),'990.99') ) BETWEEN 75  AND  84 THEN '|********| |'
                WHEN TRIM(to_char(nvl(t.bytes_used / dtf.bytes * 100,0),'990.99') ) BETWEEN 85  AND  94 THEN '|********* |'
                WHEN TRIM(to_char(nvl(t.bytes_used / dtf.bytes * 100,0),'990.99') ) BETWEEN 95  AND 100 THEN '|**********|'
                ELSE '?'
            END
    END
AS "Used (Proportion)",
       to_char(nvl(t.bytes_used / dtf.bytes * 100,0),'990') AS "Used (%)",
       dtf.autoextensible AS "Auto Extend"
  FROM sys.dba_temp_files dtf,
       sys.v_$tempfile v,
       v$temp_extent_pool t
 WHERE (
    dtf.file_name = v.name
        OR dtf.file_id = v.file#
)
   AND dtf.file_id = t.file_id (+)
 ORDER BY 1
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Related Posts

4 Comments

  2. Konstantin Reply

    Hi
    I have noticed slight shift in proportion buckets. The first one is 15 percent, the last on is 5 and the rest are 10. Why is that?

    • thatjeffsmith Post Author

      With autoextend, it’s almost a moot issue, but in my head, i didn’t really care until it hit 95% vs 91%.

      It’s your SQL at this point, so you can trash it, change it, or whatever.

      This post was an attempt to share with readers about 4-5 concepts in SQLDev and SQLcl in hopes they would use their imagination to do even cooler things with it in their every day work lives.

Write A Comment