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

[20221216]建立修改表字段统计信息modcol.sql脚本.txt --//最近经常要修改表字段统计信息,需要一个修改表字段的统计信息的脚本,建立如下: $ cat modcol.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 column_name column_stat_attribute value prompt prompt column_stat_attribute = density distcnt nullcnt avgclen prompt prompt @desc_proc sys dbms_stats set_column_stats prompt prompt exec dbms_stats.SET_COLUMN_STATS('&v_owner','&v_table','&2',&3=>&4,NO_INVALIDATE=>false,force=>true);; prompt pause press enter to continue ... exec dbms_stats.SET_COLUMN_STATS('&v_owner','&v_table','&2',&3=>&4,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> @ tab_lh scott empx  '' DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. INPUT   OWNER TABLE_NAME COLUMN SAMPLE  : @ TAB_LH TABLE_NAME [COLUMN_NAME] IF NOT INPUT COLUMN_NAME ,USE "" . COLUMN_NAME DATA_TYPE     DATA_LENGTH N NUM_DISTINCT    DENSITY SAMPLE_SIZE Low_value  High_value  NUM_NULLS NUM_BUCKETS LAST_ANALYZED       HISTOGRAM       DATA_DEFAULT ----------- ------------- ----------- - ------------ ---------- ----------- ---------- ----------- --------- ----------- ------------------- --------------- -------------------- EMPNO       NUMBER                 22 Y           14 .071428571          14 7369       7934                0           1 2022-10-10 20:40:34 NONE ENAME       VARCHAR2               10 Y           14 .071428571          14 ADAMS      WARD                0           1 2022-10-10 20:40:34 NONE JOB         VARCHAR2                9 Y            5         .2          14 ANALYST    SALESMAN            0           1 2022-10-10 20:40:34 NONE MGR         NUMBER                 22 Y            6 .166666667          13 7566       7902                1           1 2022-10-10 20:40:34 NONE HIREDATE    TIMESTAMP(6)           11 Y           13 .076923077          14                                0           1 2022-10-10 20:40:34 NONE SAL         NUMBER                 22 Y           12 .083333333          14 800        5000                0           1 2022-10-10 20:40:34 NONE COMM        NUMBER                 22 Y            4        .25           4 0          1400               10           1 2022-10-10 20:40:34 NONE DEPTNO      NUMBER                 22 Y            3 .333333333          14 10         30                  0           1 2022-10-10 20:40:34 NONE 8 rows selected. SCOTT@test01p> @ modcol scott.empx comm nullcnt 9 input argument list : owner.table_name column_name column_stat_attribute value column_stat_attribute = density distcnt nullcnt avgclen @desc_proc sys dbms_stats set_column_stats exec dbms_stats.SET_COLUMN_STATS('SCOTT','EMPX','comm',nullcnt=>9,NO_INVALIDATE=>false,force=>true); press enter to continue ... PL/SQL procedure successfully completed. SCOTT@test01p> @ modcol scott.empx comm density 0.251 input argument list : owner.table_name column_name column_stat_attribute value column_stat_attribute = density distcnt nullcnt avgclen @desc_proc sys dbms_stats set_column_stats exec dbms_stats.SET_COLUMN_STATS('SCOTT','EMPX','comm',density=>0.251,NO_INVALIDATE=>false,force=>true); press enter to continue ... PL/SQL procedure successfully completed. SCOTT@test01p> @ tab_lh scott empx  comm DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION. INPUT   OWNER TABLE_NAME COLUMN SAMPLE  : @ TAB_LH TABLE_NAME [COLUMN_NAME] IF NOT INPUT COLUMN_NAME ,USE "" . COLUMN_NAME DATA_TYPE DATA_LENGTH N NUM_DISTINCT    DENSITY SAMPLE_SIZE Low_value High_value NUM_NULLS NUM_BUCKETS LAST_ANALYZED       HISTOGRAM       DATA_DEFAULT ----------- --------- ----------- - ------------ ---------- ----------- --------- -------------------- ----------- ------------------- --------------- -------------------- COMM        NUMBER             22 Y            4       .251           4 0         1400               9           1 2023-01-17 21:56:12 NONE --//ok!!