Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

sqlplus formatting....

user8363520Aug 4 2011 — edited Aug 5 2011
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
This post has been answered by 2889 on Aug 4 2011
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 2 2011
Added on Aug 4 2011
4 comments
383 views