Re: New SqlPlus type utility available for download

From: Scott Mattes <ScottMattes_at_yahoo.com>
Date: Mon, 26 Aug 2002 13:07:40 GMT
Message-ID: <wKpa9.25589$WJ3.4256149_at_news1.news.adelphia.net>


After I stripped out the report formatting and the sqlerror stuff it ran just fine.

But, I admit that what I like is not what others like -- to each their own..

"Jennifer" <sweetiwpie183_at_yahoo.com> wrote in message news:f29fd891.0208240913.6ce86d7a_at_posting.google.com...
> "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
> column sum(bytes) format 999,999,999,999 justify right
>
> 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
Received on Mon Aug 26 2002 - 15:07:40 CEST

Original text of this message