[20240426]建立完善tpt seg2.sql脚本.txt

[20240426]建立完善tpt seg2.sql脚本.txt --//我修改tpt 的seg2脚本,一般使用seg2.sql脚本查询大部分对象没有partition_name,我设计缺省取消输出显示. --//但是有一个小问题,我经常忘记输入参数2.这样因为在执行时会出现"奇怪现象",要回车才显示输出. --//做一些小改动就可以避免这个问题.修改如下: --//下划线内容为增加的内容,这样就可以规避这个问题,类似的问题可以如下方法解决.比如参数1等等... --//另外tpt还提供1个清除参数设置的脚本undefparm.sql. $ cat undefparm.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. undefine 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 $　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 column 2 new_value 2 ~~~~~~~~~~~~~~~~~~~ select '' "2" from dual where 1=2; ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 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 / --//简单测试看看: 1.环境: SCOTT@test01p> @ver1 PORT_STRING                    VERSION        BANNER                                                                               CON_ID ------------------------------ -------------- -------------------------------------------------------------------------------- ---------- IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0 2.测试: SCOTT@test01p> define 2 SP2-0135: symbol 2 is UNDEFINED SCOTT@test01p> @ seg2 scott.dept     SEG_MB OWNER                SEGMENT_NAME                   SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK ---------- -------------------- ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------          0 SCOTT                DEPT                           TABLE                USERS                                   8         11        130 SCOTT@test01p> @ seg2 scott.dept 1     SEG_MB OWNER                SEGMENT_NAME                   SEG_PART_NAME                  SEGMENT_TYPE         SEG_TABLESPACE_NAME                BLOCKS     HDRFIL     HDRBLK ---------- -------------------- ------------------------------ ------------------------------ -------------------- ------------------------------ ---------- ---------- ----------          0 SCOTT                DEPT                                                          TABLE                USERS                                   8         11        130 --//多了一个SEG_PART_NAME字段的输出. SCOTT@test01p> define 2 DEFINE 2               = "1" (CHAR) SCOTT@test01p> @undefparm SCOTT@test01p> define 2 SP2-0135: symbol 2 is UNDEFINED