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
thatjeffsmith
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.

4 Comments

  1. Avatar
    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

      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.

Reply To Konstantin Cancel Reply

RSS
Follow by Email
LinkedIn
Share