[20251124]建立完善tpt的tab2.sql脚本.txt

[20251124]建立完善tpt的tab2.sql脚本.txt --//在tab2.sql脚本的基础上修改支持正则表达式，这样查询时更加方便灵活。 $ cat tpt/tab2z.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. column  tab_owner       heading OWNER           format a20 column  tab_table_name  heading TABLE_NAME      format a30 column  tab_type        heading TYPE            format a4 column  tab_num_rows    heading NUM_ROWS        format 99999999999 column  tab_blocks heading BLOCKS               format 999999999999 column  tab_empty_blocks heading EMPTY          format 99999999 column  tab_avg_space   heading AVGSPC          format 99999 column  tab_avg_row_len heading ROWLEN          format 99999 set term off column v_owner new_value v_owner column v_table new_value v_table select  upper(CASE WHEN INSTR('&1','.') > 0 THEN               SUBSTR('&1',INSTR('&1','.')+1)         ELSE             '&1'         END        )  v_table,  CASE WHEN INSTR('&1','.') > 0 THEN             UPPER(SUBSTR('&1',1,INSTR('&1','.')-1))         ELSE             user         END v_owner from dual; select  decode('&v_table',null,'*',replace('&v_table','%','*')) v_table ,replace('&v_owner','%','*') v_owner from dual; set term on prompt Show tables matching condition "&1" (if schema is not specified then current user's tables only are shown)... select         owner                           tab_owner,         table_name                      tab_table_name,         case                 when cluster_name is not null then 'CLU'                 when partitioned = 'NO'  and iot_name is not null then 'IOT'                 when partitioned = 'YES' and iot_name is not null then 'PIOT'                 when partitioned = 'NO' and iot_name is null then 'TAB'                 when partitioned = 'YES' and iot_name is null then 'PTAB'                 when temporary = 'Y' then 'TEMP'                 else 'OTHR'         end                             tab_type,         num_rows                        tab_num_rows,         blocks                          tab_blocks,         empty_blocks                    tab_empty_blocks,         avg_space                       tab_avg_space, --      chain_cnt                       tab_chain_cnt,         avg_row_len                     tab_avg_row_len, --      avg_space_freelist_blocks       tab_avg_space_freelist_blocks, --      num_freelist_blocks             tab_num_freelist_blocks, --      sample_size                     tab_sample_size,         last_analyzed                   tab_last_analyzed,     degree,     compression --  , compress_for  -- 11.2 from         dba_tables where --      upper(table_name) LIKE --                              upper(CASE --                                      WHEN INSTR('&1','.') > 0 THEN --                                          SUBSTR('&1',INSTR('&1','.')+1) --                                      ELSE --                                          '&1' --                                      END --                                   ) ESCAPE '\' --AND   owner LIKE --              CASE WHEN INSTR('&1','.') > 0 THEN --                      UPPER(SUBSTR('&1',1,INSTR('&1','.')-1)) --              ELSE --                      user --              END ESCAPE '\' regexp_like (table_name ,'&v_table') and regexp_like (owner ,'&v_owner') / --//许多情况可能还是习惯性输入%,为了更加灵活可以使用%代替*. --//测试： SCOTT@book01p> @ tab2z sys|scott.^ts\$|t[1|2] Show tables matching condition "sys|scott.^ts\$|t[1|2]" (if schema is not specified then current user's tables only are shown)... OWNER TABLE_NAME TYPE     NUM_ROWS        BLOCKS     EMPTY AVGSPC ROWLEN TAB_LAST_ANALYZED   DEGREE     COMPRESS ----- ---------- ---- ------------ ------------- --------- ------ ------ ------------------- ---------- -------- SYS   TS$        CLU             6             7         0      0     90 2024-08-11 08:50:50          1 DISABLED SCOTT T1         TAB             4             4         0      0     20 2025-11-18 08:54:33          1 DISABLED SCOTT T2         TAB             4             5         0      0      9 2025-11-24 09:02:14          1 DISABLED --//显示ts$,里面的$要转义一下。 SCOTT@book01p> @ tab2z scott. Show tables matching condition "scott." (if schema is not specified then current user's tables only are shown)... OWNER TABLE_NAME TYPE     NUM_ROWS        BLOCKS     EMPTY AVGSPC ROWLEN TAB_LAST_ANALYZED   DEGREE     COMPRESS ----- ---------- ---- ------------ ------------- --------- ------ ------ ------------------- ---------- -------- SCOTT BONUS      TAB             0             0         0      0      0 2024-08-18 09:02:54          1 DISABLED SCOTT DEPT       TAB             4             5         0      0     20 2025-11-23 16:04:28          1 DISABLED SCOTT EMP        TAB            14             5         0      0     38 2025-08-13 16:55:21          1 DISABLED SCOTT JOB_TIMES  TAB             0             5         0      0      0 2025-10-30 10:27:54          1 DISABLED SCOTT SALGRADE   TAB             5             5         0      0     10 2024-08-18 09:02:55          1 DISABLED SCOTT T1         TAB             4             4         0      0     20 2025-11-18 08:54:33          1 DISABLED SCOTT T2         TAB             4             5         0      0      9 2025-11-24 09:02:14          1 DISABLED SCOTT T3         TAB             4             4         0      0     12 2025-11-24 08:53:01          1 DISABLED 8 rows selected. SCOTT@book01p> @ tab2z t% Show tables matching condition "t%" (if schema is not specified then current user's tables only are shown)... OWNER TABLE_NAME TYPE     NUM_ROWS        BLOCKS     EMPTY AVGSPC ROWLEN TAB_LAST_ANALYZED   DEGREE     COMPRESS ----- ---------- ---- ------------ ------------- --------- ------ ------ ------------------- ---------- -------- SCOTT BONUS      TAB             0             0         0      0      0 2024-08-18 09:02:54          1 DISABLED SCOTT DEPT       TAB             4             5         0      0     20 2025-11-23 16:04:28          1 DISABLED SCOTT EMP        TAB            14             5         0      0     38 2025-08-13 16:55:21          1 DISABLED SCOTT JOB_TIMES  TAB             0             5         0      0      0 2025-10-30 10:27:54          1 DISABLED SCOTT SALGRADE   TAB             5             5         0      0     10 2024-08-18 09:02:55          1 DISABLED SCOTT T1         TAB             4             4         0      0     20 2025-11-18 08:54:33          1 DISABLED SCOTT T2         TAB             4             5         0      0      9 2025-11-24 09:02:14          1 DISABLED SCOTT T3         TAB             4             4         0      0     12 2025-11-24 08:53:01          1 DISABLED 8 rows selected. SCOTT@book01p> define v_table DEFINE V_TABLE         = "T*" (CHAR) --//查询T*,这里*表示0次或者多次出现，这样输出全部。 --//查询含有T的表，直接输入T就可以了。 --//也可以输入t+，+表示1或者多次出现。 SCOTT@book01p> rename  t3 to tt; Table renamed. SCOTT@book01p> @ tab2z t+ Show tables matching condition "t+" (if schema is not specified then current user's tables only are shown)... OWNER TABLE_NAME TYPE     NUM_ROWS        BLOCKS     EMPTY AVGSPC ROWLEN TAB_LAST_ANALYZED   DEGREE     COMPRESS ----- ---------- ---- ------------ ------------- --------- ------ ------ ------------------- ---------- -------- SCOTT DEPT       TAB             4             5         0      0     20 2025-11-23 16:04:28          1 DISABLED SCOTT JOB_TIMES  TAB             0             5         0      0      0 2025-10-30 10:27:54          1 DISABLED SCOTT T1         TAB             4             4         0      0     20 2025-11-18 08:54:33          1 DISABLED SCOTT T2         TAB             4             5         0      0      9 2025-11-24 09:02:14          1 DISABLED SCOTT TT         TAB             4             4         0      0     12 2025-11-24 08:53:01          1 DISABLED SCOTT@book01p> @ tab2z ^t+ Show tables matching condition "^t+" (if schema is not specified then current user's tables only are shown)... OWNER TABLE_NAME TYPE     NUM_ROWS        BLOCKS     EMPTY AVGSPC ROWLEN TAB_LAST_ANALYZED   DEGREE     COMPRESS ----- ---------- ---- ------------ ------------- --------- ------ ------ ------------------- ---------- -------- SCOTT T1         TAB             4             4         0      0     20 2025-11-18 08:54:33          1 DISABLED SCOTT T2         TAB             4             5         0      0      9 2025-11-24 09:02:14          1 DISABLED SCOTT TT         TAB             4             4         0      0     12 2025-11-24 08:53:01          1 DISABLED SCOTT@book01p> @ tab2z t{2} Show tables matching condition "t{2}" (if schema is not specified then current user's tables only are shown)... OWNER TABLE_NAME TYPE     NUM_ROWS        BLOCKS     EMPTY AVGSPC ROWLEN TAB_LAST_ANALYZED   DEGREE     COMPRESS ----- ---------- ---- ------------ ------------- --------- ------ ------ ------------------- ---------- -------- SCOTT TT         TAB             4             4         0      0     12 2025-11-24 08:53:01          1 DISABLED SCOTT@book01p> @ tab2z dept|emp|^t[1|2] Show tables matching condition "dept|emp|^t[1|2]" (if schema is not specified then current user's tables only are shown)... OWNER TABLE_NAME TYPE     NUM_ROWS        BLOCKS     EMPTY AVGSPC ROWLEN TAB_LAST_ANALYZED   DEGREE     COMPRESS ----- ---------- ---- ------------ ------------- --------- ------ ------ ------------------- ---------- -------- SCOTT DEPT       TAB             4             5         0      0     20 2025-11-23 16:04:28          1 DISABLED SCOTT EMP        TAB            14             5         0      0     38 2025-08-13 16:55:21          1 DISABLED SCOTT T1         TAB             4             4         0      0     20 2025-11-18 08:54:33          1 DISABLED SCOTT T2         TAB             4             5         0      0      9 2025-11-24 09:02:14          1 DISABLED --//只要输入正则表达式，可以非常容易组合输出自己需要的结果。