[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