[20230414]完善seg2.sql脚本.txt

--//原始脚本seg2.sql会显示分区名,大多数情况下应用表都是没有分区的表,显示它有点多余,完善该脚本.
--//缺省情况下不显示该列.

$ cat seg2.sql

-- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.

col seg_owner head OWNER for a20
col seg_segment_name head SEGMENT_NAME for a30
col seg_segment_type head SEGMENT_TYPE for a20
col v_con new_value v_con

set term off
select decode('&&2',NULL,'noprint','1','print','p','print','print','print','noprint') v_con from dual;
set term on

col seg_partition_name head SEG_PART_NAME for a30 &v_con

select
    round(bytes/1048576) seg_MB,
    owner seg_owner,
    segment_name seg_segment_name,
    partition_name seg_partition_name,
    segment_type seg_segment_type,
    tablespace_name seg_tablespace_name,
  blocks,
    header_file hdrfil,
    HEADER_BLOCK hdrblk
from
    dba_segments
where
    upper(segment_name) LIKE
                upper(CASE
                    WHEN INSTR('&1','.') > 0 THEN
                        SUBSTR('&1',INSTR('&1','.')+1)
                    ELSE
                        '&1'
                    END
                     )
AND    owner LIKE
        CASE WHEN INSTR('&1','.') > 0 THEN
            UPPER(SUBSTR('&1',1,INSTR('&1','.')-1))
        ELSE
            user
        END
/

--//测试如下:
SCOTT@book> @ seg2 dept ''
SEG_MB OWNER SEGMENT_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK
------ ----- ------------ ------------ ------------------- ------ ------ ------
     0 SCOTT DEPT         TABLE        USERS                    8      4    130

SCOTT@book> @ seg2 dept p
SEG_MB OWNER SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK
------ ----- ------------ ------------- ------------ ------------------- ------ ------ ------
     0 SCOTT DEPT                       TABLE        USERS                    8      4    130

SYS@192.168.100.141:1521/dyhis> @ seg2 audsys.AUD$UNIFIED print
SEG_MB OWNER  SEGMENT_NAME SEG_PART_NAME SEGMENT_TYPE    SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK
------ ------ ------------ ------------- --------------- ------------------- ------ ------ ------
   175 AUDSYS AUD$UNIFIED  SYS_P20923    TABLE PARTITION SYSAUX               22400      3 145690
    80 AUDSYS AUD$UNIFIED  SYS_P21663    TABLE PARTITION USERS                10240      2  48657
   144 AUDSYS AUD$UNIFIED  SYS_P19622    TABLE PARTITION SYSAUX               18432      3 656234
    88 AUDSYS AUD$UNIFIED  SYS_P20283    TABLE PARTITION SYSAUX               11264      3 550906

SYS@192.168.100.141:1521/dyhis> @ seg2 audsys.AUD$UNIFIED ''
SEG_MB OWNER  SEGMENT_NAME SEGMENT_TYPE    SEG_TABLESPACE_NAME BLOCKS HDRFIL HDRBLK
------ ------ ------------ --------------- ------------------- ------ ------ ------
   175 AUDSYS AUD$UNIFIED  TABLE PARTITION SYSAUX               22400      3 145690
    80 AUDSYS AUD$UNIFIED  TABLE PARTITION USERS                10240      2  48657
   144 AUDSYS AUD$UNIFIED  TABLE PARTITION SYSAUX               18432      3 656234
    88 AUDSYS AUD$UNIFIED  TABLE PARTITION SYSAUX               11264      3 550906