[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
-- 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有顯示,有時候沒有值。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-2844757/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- [20211129]完善tpt killi.sql指令碼.txtSQL指令碼
- [20220510]完善tpt expandz.sql指令碼.txtSQL指令碼
- [20211130]完善tpt t.sql指令碼.txtSQL指令碼
- [20220323]完善tpt get_trace.sql指令碼.txtSQL指令碼
- [20220217]完善tpt gts.sql指令碼.txtSQL指令碼
- [20220823]完善tpt的ashtop.sql指令碼.txtSQL指令碼
- [20231025]完善tpt的trans.sql指令碼.txtSQL指令碼
- [20211126]完善tpt pr.sql指令碼.txtSQL指令碼
- [20230302]建立完善tpt o2.sql指令碼.txtSQL指令碼
- [20220422]完善tpt ash ash_index_helperx指令碼2.txtIndex指令碼
- [20220111]完善tpt ashash_index_helper指令碼.txtIndex指令碼
- [20220317]補充完善TPT 顯示欄位列的指令碼.txt指令碼
- [20220129]完善tpt ash ash_index_helperx指令碼.txtIndex指令碼
- [20220519]完善tpt dash_wait_chains2.sql指令碼.txtAISQL指令碼
- [20170628]完善ooerr指令碼.txt指令碼
- [20211129]更新dpcawr1.sql指令碼.txtPCASQL指令碼
- [20210506]完善tix指令碼.txt指令碼
- [20240313]使用tpt ashtop.sql指令碼的困惑.txtSQL指令碼
- [20210407]完善ti.sql指令碼.txtSQL指令碼
- [20210623]完善清除aud指令碼.txt指令碼
- [20211223]tpt ash ash_index_helperx指令碼.txtIndex指令碼
- [20201202]完善sosi指令碼.txt指令碼
- [20230510]測試使用tpt ddl指令碼是否產生日誌.txt指令碼
- [20211230]完善sql_id指令碼.txtSQL指令碼
- [20221010]完善descz.sql指令碼.txtSQL指令碼
- [20221101]完善descz.sql指令碼.txtSQL指令碼
- [20221101]完善gts.sql指令碼.txtSQL指令碼
- [20211122]完善descx.sql指令碼.txtSQL指令碼
- [20230414]完善seg2.sql指令碼.txtSQL指令碼
- [20231117]完善ashtt.sql指令碼.txtSQL指令碼
- [20230203]完善awr.sql指令碼.txtSQL指令碼
- [20231101]使用tpt seg2.sql指令碼問題.txtSQL指令碼
- [20221126]tpt pr.sql指令碼執行問題.txtSQL指令碼
- [20221012]完善spsw.sql指令碼.txtSQL指令碼
- [20221208]完善bind_cap.sql指令碼.txtSQL指令碼
- [20211202]完善d_buffer.sql指令碼.txtSQL指令碼
- [20220309]完善shp4.sql指令碼.txtSQL指令碼
- [20230210]建立完善swcnm.sql指令碼.txtSQL指令碼