[20221216]建立修改表统计信息modtab.sql脚本.txt

--//最近经常要修改表统计信息,需要一个修改表统计信息的脚本,建立如下:

$ cat modtab.sql
set verify off
set termout 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,
 nvl(upper(CASE WHEN INSTR('&1','.') > 0 THEN
            UPPER(SUBSTR('&1',1,INSTR('&1','.')-1))
        ELSE
            user
        END),user) v_owner
from dual;

set termout on

prompt
prompt input argument list : owner.table_name table_stat_attribute value
prompt
prompt table_stat_attribute = numrows numblks avgrlen
prompt
prompt @desc_proc sys dbms_stats set_table_stats
prompt
prompt exec dbms_stats.set_table_stats('&v_owner','&v_table',&2=>&3,NO_INVALIDATE=>false,force=>true);;
prompt
pause press enter to continue ...
exec dbms_stats.set_table_stats('&v_owner','&v_table',&2=>&3,NO_INVALIDATE=>false,force=>true);
prompt

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> @ tpt/tab2 scott.empx
Show tables matching condition "scott.empx" (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                EMPX                           TAB            14             4         0      0     41 2022-10-10 20:40:34          1           DISABLED

SCOTT@test01p> @ modtab scott.empx numblks 200
input argument list : owner.table_name table_stat_attribute value
table_stat_attribute = numrows numblks avgrlen
@desc_proc sys dbms_stats set_table_stats
exec dbms_stats.set_table_stats('SCOTT','EMPX',numblks=>200,NO_INVALIDATE=>false,force=>true);
press enter to continue ...

SCOTT@test01p> @ tpt/tab2 scott.empx
Show tables matching condition "scott.empx" (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                EMPX                           TAB            14           200         0      0     41 2023-01-17 22:01:42          1           DISABLED
--//修改成功!!