25 lines
944 B
SQL
25 lines
944 B
SQL
set lines 10000
|
|
set pages 10000
|
|
set feedback off
|
|
set echo off
|
|
set term off
|
|
spool vg.sql
|
|
select 'select * from ( ' from dual
|
|
union all
|
|
SELECT 'SELECT ''' || owner || '.' || name || ''' as queue '
|
|
|| chr(13)|| ', ''' || q.queue_table || ''' as "QueueTable"'
|
|
|| chr(13)|| ', ' || '(select ROUND(sum(bytes)/power(1024,2)) from dba_segments s where s.owner = ''' || owner || ''' and s.segment_name = ''' || q.queue_table || ''') as "Size(MB)"'
|
|
|| chr(13)|| ', ' || '(select count(1) from ' || owner || '.' || queue_table || ') as row_count'
|
|
|| chr(13)|| ', ' || retention || ' retention from dual union all'
|
|
FROM dba_queues q where q.owner like 'TRAF%' and q.queue_type ='NORMAL_QUEUE'
|
|
union all
|
|
select ' select ''abc'' queue, '''' queuetable ,0 "Size(MB)" , 1 rowcount,1 retention from dual' from dual
|
|
union all
|
|
select ') order by "Size(MB)" desc ; ' from dual
|
|
;
|
|
|
|
spool off
|
|
@html @./vg.sql
|
|
|
|
|