Re: New SqlPlus type utility available for download

"Scott Mattes" <ScottMattes_at_yahoo.com> wrote in message news:<wMq99.18781$WJ3.3404840_at_news1.news.adelphia.net>...
> I prefer having the results in a grid form, then I don't have to worry about
> sizing the lines/fields and all those extraneous column headers.

If I'm going to execute one of my hundreds of Sq*Plus scripts, I would prefer to have the output identical to Sql*Plus output if you know what I mean. If I run this in QSqlPlus:

set echo off
set feedback off

prompt
prompt SGA Memory Map (overall)
prompt

column dummy      noprint
column area       format a20 heading 'Main SGA Areas'
column name       format a20
column pool       format a20
column bytes      format 999,999,999,999

break on report
compute sum of sum(bytes) on report

SELECT 1 dummy, 'DB Buffer Cache' area, name, sum(bytes) FROM v$sgastat
WHERE pool is null and

      name = 'db_block_buffers'
group by name
union all
SELECT 2, 'Shared Pool', pool, sum(bytes) FROM v$sgastat
WHERE pool = 'shared pool'
group by pool
union all
SELECT 3, 'Large Pool', pool, sum(bytes) FROM v$sgastat
WHERE pool = 'large pool'
group by pool
union all
SELECT 4, 'Java Pool', pool, sum(bytes) FROM v$sgastat
WHERE pool = 'java pool'
group by pool
union all
SELECT 5, 'Redo Log Buffer', name, sum(bytes) FROM v$sgastat
WHERE pool is null and

      name = 'log_buffer'
group by name
union all
SELECT 6, 'Fixed SGA', name, sum(bytes)
FROM v$sgastat
WHERE pool is null and

      name = 'fixed_sga'
group by name
ORDER BY 4 desc;

column area format a20 heading 'Shared Pool Areas'

prompt
prompt SGA Memory Map (shared pool)
prompt                      

whenever sqlerror exit

SELECT 'Shared Pool' area, name, sum(bytes) FROM v$sgastat
WHERE pool = 'shared pool' and

      name in ('library cache','dictionary cache','free memory','sql area')
group by name
union all
SELECT 'Shared Pool' area, 'miscellaneous', sum(bytes) FROM v$sgastat
WHERE pool = 'shared pool' and

      name not in ('library cache','dictionary cache','free memory','sql area')
group by pool
order by 3 desc;

Then the output looks like this, which is what I would expect from a Sql*Plus replacement:

SGA Memory Map (overall)

Main SGA Areas       NAME                      SUM(BYTES)
-------------------- -------------------- ---------------
Shared Pool          shared pool               46,137,344
DB Buffer Cache      db_block_buffers          33,554,432
Java Pool            java pool                 33,554,432
Redo Log Buffer      log_buffer                   524,288
Fixed SGA            fixed_sga                    282,576
                                          ---------------
                                              114,053,072
SGA Memory Map (shared pool)
Shared Pool Areas    NAME                      SUM(BYTES)
-------------------- -------------------- ---------------
Shared Pool          free memory               31,483,592
Shared Pool          miscellaneous             11,080,240
Shared Pool          library cache              2,264,252
Shared Pool          sql area                     753,620
Shared Pool          dictionary cache             555,640
                                          ---------------
                                               46,137,344