[20211129]完善tpt tablist.sql脚本.txt

[20211129]完善tpt tablist.sql脚本.txt --//1.我加入几个字段 c.histogram  , c.sample_size。 --//2.最后的order by，加入了column_id字段，这样基本表定义顺序输出。 ORDER BY     h.owner   , h.table_name --  , h.column_name     , c.column_id   , h.endpoint_number / --//3.查询使用表dba_tab_cols代替dba_tab_columns，这样可以查询隐含列的信息。 --  dba_tab_columns     c     dba_tab_cols        c --//4.加入了PARTITION BY.         -- h.endpoint_number - lag(endpoint_number, 1) over (         h.endpoint_number - lag(endpoint_number, 1,0) over ( PARTITION BY h.owner , h.table_name , h.column_name $ cat tabhist.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. -- CREATE OR REPLACE FUNCTION HEXSTR ( p_number in number ) return varchar2 -- as --         l_str varchar2(4000) := to_char(p_number,'fm'||rpad('x',50,'x')); --         l_return varchar2(4000); -- begin --         while ( l_str is not null ) --         loop --                 l_return := l_return || chr(to_number(substr(l_str,1,2),'xx')); --                 l_str := substr( l_str, 3 ); --         end loop; --         return l_return; -- end; -- / -- -- GRANT EXECUTE ON HEXSTR TO PUBLIC; -- CREATE OR REPLACE PUBLIC SYNONYM HEXSTR FOR HEXSTR; col tabhist_ep_actual_value head ENDPOINT_ACTUAL_VALUE for a40 col tabhist_ep_value        head ENDPOINT_VALUE for a30 just right col tabhist_ep_value2       for a80 col tabhist_col_name        head COLUMN_NAME for a30 col tabhist_data_type       head DATA_TYPE for a20 word_wrap break on tabhist_col_name on tabhist_data_type skip 1 select     h.column_name                  tabhist_col_name   , c.data_type                    tabhist_data_type   , c.histogram   , c.sample_size   , h.endpoint_number   , CASE         WHEN c.data_type = 'NUMBER' THEN LPAD(TO_CHAR(h.endpoint_value), 30, ' ')         WHEN c.data_type IN ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2') THEN              --to_char(to_number((substr(trim(to_char(h.endpoint_value,lpad('x',63,'x'))),1,12)),'XXXXXXXXXXXXXXXX'))              to_char(to_number((substr(trim(to_char(h.endpoint_value,lpad('x',63,'x'))),1,12)),'XXXXXXXXXXXXXXXX'),'XXXXXXXXXXXXXXXXXXXXXXXXXXXXX')              --hexstr(to_number((substr(trim(to_char(h.endpoint_value,lpad('x',63,'x'))),1,12)),'XXXXXXXXXXXXXXXX'))              --hexstr(substr(to_char(h.endpoint_value,'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),1,12))         ELSE              trim(substr(trim(to_char(h.endpoint_value,lpad('x',63,'x'))),1,12))     END tabhist_ep_value   , CASE WHEN c.histogram = 'FREQUENCY' THEN         h.endpoint_number - lag(endpoint_number, 1,0) over ( PARTITION BY h.owner , h.table_name , h.column_name                                                           order by                                                             h.owner                                                           , h.table_name                                                           , h.column_name                                                           , h.endpoint_number         ) ELSE h.endpoint_number --NULL     END frequency    ,CASE WHEN c.histogram = 'HEIGHT BALANCED' THEN         CASE WHEN c.data_type = 'NUMBER' THEN               h.endpoint_value - lag(endpoint_value, 1,0) over ( PARTITION BY h.owner , h.table_name , h.column_name                                                           order by                                                             h.owner                                                           , h.table_name                                                           , h.column_name                                                           , h.endpoint_number         )         ELSE null END     ELSE null END height_bal --  , hexstr(h.endpoint_value)              tabhist_ep_value --  , to_char(h.endpoint_value,'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')              tabhist_ep_value2   , h.endpoint_actual_value        tabhist_ep_actual_value from --  dba_tab_columns     c     dba_tab_cols        c   , dba_tab_histograms  h where     c.owner         = h.owner and c.table_name    = h.table_name and c.column_name   = h.column_name and upper(h.table_name) LIKE                 upper(CASE                     WHEN INSTR('&1','.') > 0 THEN                         SUBSTR('&1',INSTR('&1','.')+1)                     ELSE                         '&1'                     END                      ) AND h.owner LIKE         CASE WHEN INSTR('&1','.') > 0 THEN             UPPER(SUBSTR('&1',1,INSTR('&1','.')-1))         ELSE             user         END AND UPPER(h.column_name) LIKE UPPER('&2') ORDER BY     h.owner   , h.table_name --  , h.column_name     , c.column_id   , h.endpoint_number / --//我的测试： SCOTT@book> @ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production create table t as select * from all_objects; SCOTT@book> execute sys.dbms_stats.gather_table_stats ( OwnName => user,TabName => 't',Estimate_Percent => null,Method_Opt => 'FOR ALL COLUMNS SIZE 254 ',Cascade => True ,No_Invalidate => false) PL/SQL procedure successfully completed. SCOTT@book> @ tabhist t owner COLUMN_NAME                    DATA_TYPE            HISTOGRAM       SAMPLE_SIZE ENDPOINT_NUMBER                 ENDPOINT_VALUE  FREQUENCY HEIGHT_BAL ENDPOINT_ACTUAL_VALUE ------------------------------ -------------------- --------------- ----------- --------------- ------------------------------ ---------- ---------- ---------------------------------------- OWNER                          VARCHAR2             FREQUENCY             84808            2251                   415045585F30       2251                                                     FREQUENCY             84808            2256                   415050514F53          5                                                     FREQUENCY             84808            2264                   4248FFFFFFFF          8                                                     FREQUENCY             84808            2649                   435458535953        385                                                     FREQUENCY             84808            2706                   4442534E4D50         57                                                     FREQUENCY             84808            3014                   455846535953        308                                                     FREQUENCY             84808            3025                   464C4F57535F         11                                                     FREQUENCY             84808            3059                   4851FFFFFFFF         34                                                     FREQUENCY             84808            3107                   495800000000         48                                                     FREQUENCY             84808            4688                   4D44535952FF       1581                                                     FREQUENCY             84808            4800                   4F44FFFFFFFF        112                                                     FREQUENCY             84808            5517                   4F4C41505359        717                                                     FREQUENCY             84808            5525                   4F5241434C45          8                                                     FREQUENCY             84808            5764                   4F5244444154        239                                                     FREQUENCY             84808            5774                   4F5244504C55         10                                                     FREQUENCY             84808            8287                   4F5244535953       2513                                                     FREQUENCY             84808            8295                   4F55544C4E00          8                                                     FREQUENCY             84808            8297                   4F5742535952          2                                                     FREQUENCY             84808            8309                   4F5742535953         12                                                     FREQUENCY             84808            8319                   504CFFFFFFFF         10                                                     FREQUENCY             84808           42320                   5055424C4942      34001                                                     FREQUENCY             84808           42379                   53434F545400         59                                                     FREQUENCY             84808           42679                   534800000000        300                                                     FREQUENCY             84808           42687                   53495F494E46          8                                                     FREQUENCY             84808           80046                   535952FFFFFF      37359                                                     FREQUENCY             84808           83438                   5359534D414E       3392                                                     FREQUENCY             84808           84006                   53595354454D        568                                                     FREQUENCY             84808           84319                   574D53595300        313                                                     FREQUENCY             84808           84808                   584441FFFFFF        489 SCOTT@book> @ grp owner t count owner in table t... OWNER                            COUNT(*) ------------------------------ ---------- APEX_030200                          2251 APPQOSSYS                               5 BI                                      8 CTXSYS                                385 DBSNMP                                 57 EXFSYS                                308 FLOWS_FILES                            11 HR                                     34 IX                                     48 MDSYS                                1581 OE                                    112 OLAPSYS                               717 ORACLE_OCM                              8 ORDDATA                               239 ORDPLUGINS                             10 ORDSYS                               2513 OUTLN                                   8 OWBSYS                                  2 OWBSYS_AUDIT                           12 PM                                     10 PUBLIC                              34001 SCOTT                                  59 SH                                    300 SI_INFORMTN_SCHEMA                      8 SYS                                 37359 SYSMAN                               3392 SYSTEM                                568 WMSYS                                 313 XDB                                   489 29 rows selected. --//我有点不理解的是直方图有时候ENDPOINT_ACTUAL_VALUE有显示，有时候没有值。