Hello all, i have a question with regards to sql formatting....i run the below sql statements but when i get the output below....the "mbytes" col messes up the formatting for that whole column...any suggestion as to how to keep that allign ?
set lines 400
SET PAGESIZE 9999
SET VERIFY OFF
SET COLSEP '|'
BREAK ON segment_type SKIP 2
--COMPUTE SUM OF Mbytes ON segment_type
COLUMN segment_type FORMAT A20 HEADING 'Segment Type'
COLUMN owner FORMAT A15 HEADING 'Owner'
COLUMN segment_name FORMAT A30 HEADING 'Segment Name'
COLUMN partition_name FORMAT A30 HEADING 'Partition Name'
COLUMN tablespace_name FORMAT A20 HEADING 'Tablespace Name'
--COLUMN Mbytes FORMAT 9,999,999,999,999 HEADING 'Mbytes'
COLUMN extents FORMAT 999,999,999 HEADING 'Extents'
SELECT
a.segment_type segment_type
, a.owner owner
, a.segment_name segment_name
, a.partition_name partition_name
, a.tablespace_name tablespace_name
, round(a.bytes/1024/1024) Mbytes
, a.extents extents
FROM
(select
b.segment_type
, b.owner
, b.segment_name
, b.partition_name
, b.tablespace_name
, b.bytes
, b.extents
from
dba_segments b
order by
b.bytes desc
) a
WHERE
rownum < 101
ORDER BY
segment_type, bytes desc, owner, segment_name
/
SQL> @dba_top_segments.sql
Segment Type |Owner |Segment Name |Partition Name |Tablespace Name | MBYTES| Extents
--------------------|---------------|------------------------------|------------------------------|--------------------|----------|------------
CLUSTER |EMAIL |PCLUSTER | |KREG_DATA | 1152| 201
INDEX |PERFSTAT |STATS$SQLTEXT_PK | |TOOLS | 4272| 4,264
|PERFSTAT |STATS$SQL_SUMMARY_PK | |TOOLS | 2419| 690
|PERFSTAT |STATS$SQL_PLAN_USAGE_PK | |TOOLS | 2178| 921
Edited by: user8363520 on Aug 4, 2011 12:10 PM
Edited by: user8363520 on Aug 4, 2011 12:11 PM