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

[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