[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有显示，有时候没有值。