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

[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 --//修改成功!!