[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
--//只要输入正则表达式，可以非常容易组合输出自己需要的结果。