[20221010]完善descz.sql指令碼.txt
[20221010]完善descz.sql指令碼.txt
--//前一陣子執行descz遇到的問題,資料型別NVARCHAR2的不顯示Low_value,High_value.
--//例子如下:
> @ descz lis.COM_GRIDSETTING "column_name in ('CLIENT_ID','CLASS_NAME','GRID_NAME')"
eXtended describe of lis.COM_GRIDSETTING
DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
INPUT OWNER.TABLE_NAME <filters>
SAMPLE : @ TAB_LH TABLE_NAME "column_id between 3 and 5"
IF NOT INPUT <filters> ,USE "1=1" .
Owner Table_Name SAMPLE_SIZE LAST_ANALYZED Col# Column Name Null? Type NUM_DISTINCT Density NUM_NULLS HISTOGRAM NUM_BUCKETS Low_value High_value
----- --------------- ----------- ------------------- ---- ----------- ---------- -------------- ------------ ------------ --------- --------- ----------- --------- ----------
LIS COM_GRIDSETTING 58684 2022-10-09 14:10:26 2 GRID_NAME NOT NULL NVARCHAR2(800) 17 .00000852021 0 FREQUENCY 17
58684 2022-10-09 14:10:26 3 CLASS_NAME NOT NULL NVARCHAR2(800) 48 .00000852021 0 FREQUENCY 48
5461 2022-10-09 14:10:26 7 CLIENT_ID NOT NULL NUMBER(10,0) 486 .00186200000 0 HYBRID 254 1 25113
--//查詢NVARCHAR2型別時,沒有顯示Low_value,High_value,修改完善一下.
--//沒有包括timestamp型別,順便加入看看.注意data_type中timestamp是以timestamp(N)格式出現.
$ cat descz.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.
COL desc_column_id HEAD "Col#" FOR A4
COL desc_column_name HEAD "Column Name" FOR A20
COL desc_data_type HEAD "Type" FOR A20 WORD_WRAP
COL desc_nullable HEAD "Null?" FOR A10
COL desc_density HEAD "Density" FOR 9.99999999999
COL desc_owner HEAD Owner FOR A10
COL desc_table_name HEAD Table_Name FOR A20
COL trans_low HEAD "Low_value" FOR A40
COL trans_high HEAD "High_value" FOR A40
prompt eXtended describe of &1
PROMPT
PROMPT DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
PROMPT INPUT OWNER.TABLE_NAME <filters>
PROMPT SAMPLE : @ TAB_LH TABLE_NAME "column_id between 3 and 5"
PROMPT IF NOT INPUT <filters> ,USE "1=1" .
PROMPT
break on desc_owner on desc_table_name skip 1
SELECT
owner desc_owner,
table_name desc_table_name,
sample_size ,
last_analyzed,
CASE WHEN hidden_column = 'YES' THEN 'H' ELSE ' ' END||
LPAD(column_id,3) desc_column_id,
column_name desc_column_name,
CASE WHEN nullable = 'N' THEN 'NOT NULL' ELSE NULL END AS desc_nullable,
data_type||CASE
-- WHEN data_type = 'NUMBER' THEN '('||data_precision||CASE WHEN data_scale = 0 THEN NULL ELSE ','||data_scale END||')'
WHEN data_type = 'NUMBER' THEN '('||data_precision||','||data_scale||')'
ELSE '('||data_length||')'
END AS desc_data_type,
-- data_default,
num_distinct,
density desc_density,
num_nulls,
CASE WHEN histogram = 'NONE' THEN null ELSE histogram END histogram,
num_buckets,
CASE
WHEN data_type IN ('CHAR', 'VARCHAR2')
THEN
UTL_RAW.cast_to_varchar2 (low_value)
WHEN data_type IN ('NVARCHAR2')
THEN
to_char(UTL_RAW.cast_to_nvarchar2 (low_value))
WHEN data_type = 'NUMBER'
THEN
TO_CHAR (UTL_RAW.cast_to_number (low_value))
WHEN data_type in ( 'DATE') or data_type like 'TIMESTAMP%'
THEN
RTRIM (
LTRIM (
TO_CHAR (
100
* ( TO_NUMBER (SUBSTR (low_value, 1, 2), 'XX')
- 100)
+ (TO_NUMBER (SUBSTR (low_value, 3, 2), 'XX') - 100),
'0000'))
|| '-'
|| LTRIM (
TO_CHAR (TO_NUMBER (SUBSTR (low_value, 5, 2), 'XX'),
'00'))
|| '-'
|| LTRIM (
TO_CHAR (TO_NUMBER (SUBSTR (low_value, 7, 2), 'XX'),
'00'))
|| ' '
|| LTRIM (
TO_CHAR (
TO_NUMBER (SUBSTR (low_value, 9, 2), 'XX') - 1,
'00'))
|| ':'
|| LTRIM (
TO_CHAR (
TO_NUMBER (SUBSTR (low_value, 11, 2), 'XX') - 1,
'00'))
|| ':'
|| LTRIM (
TO_CHAR (
TO_NUMBER (SUBSTR (low_value, 13, 2), 'XX') - 1,
'00')))
END
trans_low,
CASE
WHEN data_type IN ('CHAR', 'VARCHAR2')
THEN
UTL_RAW.cast_to_varchar2 (high_value)
WHEN data_type IN ('NVARCHAR2')
THEN
to_char(UTL_RAW.cast_to_nvarchar2 (high_value))
WHEN data_type = 'NUMBER'
THEN
TO_CHAR (UTL_RAW.cast_to_number (high_value))
WHEN data_type in ( 'DATE') or data_type like 'TIMESTAMP%'
THEN
RTRIM (
LTRIM (
TO_CHAR (
100
* ( TO_NUMBER (SUBSTR (high_value, 1, 2), 'XX')
- 100)
+ (TO_NUMBER (SUBSTR (high_value, 3, 2), 'XX') - 100),
'0000'))
|| '-'
|| LTRIM (
TO_CHAR (TO_NUMBER (SUBSTR (high_value, 5, 2), 'XX'),
'00'))
|| '-'
|| LTRIM (
TO_CHAR (TO_NUMBER (SUBSTR (high_value, 7, 2), 'XX'),
'00'))
|| ' '
|| LTRIM (
TO_CHAR (
TO_NUMBER (SUBSTR (high_value, 9, 2), 'XX') - 1,
'00'))
|| ':'
|| LTRIM (
TO_CHAR (
TO_NUMBER (SUBSTR (high_value, 11, 2), 'XX') - 1,
'00'))
|| ':'
|| LTRIM (
TO_CHAR (
TO_NUMBER (SUBSTR (high_value, 13, 2), 'XX') - 1,
'00')))
END
trans_high
--,'--' desc_succeeded
FROM
dba_tab_cols
WHERE
upper(table_name) LIKE
upper(CASE
WHEN INSTR('&1','.') > 0 THEN
SUBSTR('&1',INSTR('&1','.')+1)
ELSE
'&1'
END
)
AND owner LIKE
CASE WHEN INSTR('&1','.') > 0 THEN
UPPER(SUBSTR('&1',1,INSTR('&1','.')-1))
ELSE
user
END
--AND column_name = decode('&&2','',column_name,upper('&&2'))
AND ( &&2 )
ORDER BY
owner,
table_name,
column_id
/
--//注:必須使用to_char轉換一下,因為UTL_RAW.cast_to_nvarchar2返回型別是nvarchar2型別,前後不一致.
SYS@192.168.100.235:1521/orcl> @ descz lis.COM_GRIDSETTING "column_name in ('CLIENT_ID','CLASS_NAME','GRID_NAME')"
eXtended describe of lis.COM_GRIDSETTING
DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
INPUT OWNER.TABLE_NAME <filters>
SAMPLE : @ TAB_LH TABLE_NAME "column_id between 3 and 5"
IF NOT INPUT <filters> ,USE "1=1" .
Owner Table_Name SAMPLE_SIZE LAST_ANALYZED Col# Column Name Null? Type NUM_DISTINCT Density NUM_NULLS HISTOGRAM NUM_BUCKETS Low_value High_value
---------- -------------------- ----------- ------------------- ---- -------------------- ---------- -------------------- ------------ -------------- ---------- --------------- ----------- -------------- --------------
LIS COM_GRIDSETTING 58684 2022-10-09 14:10:26 2 GRID_NAME NOT NULL NVARCHAR2(800) 17 .00000852021 0 FREQUENCY 17 BisOutGridView layoutControl2
58684 2022-10-09 14:10:26 3 CLASS_NAME NOT NULL NVARCHAR2(800) 48 .00000852021 0 FREQUENCY 48 AuditFrm TestingFrm
5461 2022-10-09 14:10:26 7 CLIENT_ID NOT NULL NUMBER(10,0) 486 .00186200000 0 HYBRID 254 1 25113
--//OK,現在nvarchar2型別的Low_value,High_value有顯示了.
--//測試timestamp型別:
SCOTT@test01p> create table empx as select * from emp;
Table created.
SCOTT@test01p> alter table empx modify(hiredate timestamp(6));
Table altered.
SCOTT@test01p> update empx set hiredate=systimestamp-50*365 where empno=7369;
1 row updated.
SCOTT@test01p> update empx set hiredate=systimestamp where empno=7934;
1 row updated.
SCOTT@test01p> commit;
Commit complete.
SCOTT@test01p> @ tpt/gts empx
Gather Table Statistics for table empx...
PL/SQL procedure successfully completed.
SCOTT@test01p> select min(hiredate),max(hiredate) from empx;
MIN(HIREDATE) MAX(HIREDATE)
-------------------------- --------------------------
1972-10-22 20:39:27.000000 2022-10-10 20:35:42.536000
SCOTT@test01p> @ tpt/gts empx
Gather Table Statistics for table empx...
PL/SQL procedure successfully completed.
SCOTT@test01p> @ descz empx column_name='HIREDATE'
SCOTT@test01p> @ prxx
==============================
DESC_OWNER : SCOTT
DESC_TABLE_NAME : EMPX
SAMPLE_SIZE : 14
LAST_ANALYZED : 2022-10-10 20:40:34
DESC_COLUMN_ID : 5
DESC_COLUMN_NAME : HIREDATE
DESC_NULLABLE :
DESC_DATA_TYPE : TIMESTAMP(6)(11)
NUM_DISTINCT : 13
DESC_DENSITY : .0769230769230769
NUM_NULLS : 0
HISTOGRAM :
NUM_BUCKETS : 1
TRANS_LOW : 1972-10-22 20:39:27
TRANS_HIGH : 2022-10-10 20:35:42
PL/SQL procedure successfully completed.
--//丟失秒後面的數值.
SELECT table_name,low_value,high_value
FROM dba_tab_cols
WHERE owner = 'SCOTT' AND table_name like 'EMP%' and column_name ='HIREDATE';
TABLE_NAME LOW_VALUE HIGH_VALUE
---------- -------------- ------------------------------
EMP 77B40C11010101 77BB0517010101
EMPX 77AC0A1615281C 787A0A0A15242B1FF2B600
SCOTT@test01p> @ tpt/calc x1FF2B600 + 0
DEC HEX
---------------- --------
536000000.000000 1FF2B600
--//可以看出後面的536000000正好與select min(hiredate),max(hiredate) from empx;輸出的最大值秒後面的值對上.
--//再次修改指令碼如下:
/* Formatted on 2022/10/10 21:04:36 (QP5 v5.227.12220.39754) */
-- Copyright 2018 Tanel Poder. All rights reserved. More info at
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.
COL desc_column_id HEAD "Col#" FOR A4
COL desc_column_name HEAD "Column Name" FOR A20
COL desc_data_type HEAD "Type" FOR A20 WORD_WRAP
COL desc_nullable HEAD "Null?" FOR A10
COL desc_density HEAD "Density" FOR 9.99999999999
COL desc_owner HEAD Owner FOR A10
COL desc_table_name HEAD Table_Name FOR A20
COL trans_low HEAD "Low_value" FOR A40
COL trans_high HEAD "High_value" FOR A40
PROMPT eXtended describe of &1
PROMPT
PROMPT DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
PROMPT INPUT OWNER.TABLE_NAME <filters>
PROMPT SAMPLE : @ TAB_LH TABLE_NAME "column_id between 3 and 5"
PROMPT IF NOT INPUT <filters> ,USE "1=1" .
PROMPT
BREAK ON desc_owner ON desc_table_name SKIP 1
SELECT owner desc_owner
,table_name desc_table_name
,sample_size
,last_analyzed
, CASE WHEN hidden_column = 'YES' THEN 'H' ELSE ' ' END
|| LPAD (column_id, 3)
desc_column_id
,column_name desc_column_name
,CASE WHEN nullable = 'N' THEN 'NOT NULL' ELSE NULL END
AS desc_nullable
, data_type
|| CASE
-- WHEN data_type = 'NUMBER' THEN '('||data_precision||CASE WHEN data_scale = 0 THEN NULL ELSE ','||data_scale END||')'
WHEN data_type = 'NUMBER'
THEN
'(' || data_precision || ',' || data_scale || ')'
ELSE
'(' || data_length || ')'
END
AS desc_data_type
, -- data_default,
num_distinct
,density desc_density
,num_nulls
,CASE WHEN histogram = 'NONE' THEN NULL ELSE histogram END histogram
,num_buckets
,CASE
WHEN data_type IN ('CHAR', 'VARCHAR2')
THEN
UTL_RAW.cast_to_varchar2 (low_value)
WHEN data_type IN ('NVARCHAR2')
THEN
TO_CHAR (UTL_RAW.cast_to_nvarchar2 (low_value))
WHEN data_type = 'NUMBER'
THEN
TO_CHAR (UTL_RAW.cast_to_number (low_value))
WHEN data_type IN ('DATE')
THEN
RTRIM
(
LTRIM
(
TO_CHAR
(
100
* ( TO_NUMBER (SUBSTR (low_value, 1, 2), 'XX')
- 100)
+ (TO_NUMBER (SUBSTR (low_value, 3, 2), 'XX') - 100)
,'0000'
)
)
|| '-'
|| LTRIM
(
TO_CHAR
(
TO_NUMBER (SUBSTR (low_value, 5, 2), 'XX')
,'00'
)
)
|| '-'
|| LTRIM
(
TO_CHAR
(
TO_NUMBER (SUBSTR (low_value, 7, 2), 'XX')
,'00'
)
)
|| ' '
|| LTRIM
(
TO_CHAR
(
TO_NUMBER (SUBSTR (low_value, 9, 2), 'XX') - 1
,'00'
)
)
|| ':'
|| LTRIM
(
TO_CHAR
(
TO_NUMBER (SUBSTR (low_value, 11, 2), 'XX') - 1
,'00'
)
)
|| ':'
|| LTRIM
(
TO_CHAR
(
TO_NUMBER (SUBSTR (low_value, 13, 2), 'XX') - 1
,'00'
)
)
)
WHEN data_type LIKE 'TIMESTAMP%'
THEN
RTRIM
(
LTRIM
(
TO_CHAR
(
100
* ( TO_NUMBER (SUBSTR (low_value, 1, 2), 'XX')
- 100)
+ (TO_NUMBER (SUBSTR (low_value, 3, 2), 'XX') - 100)
,'0000'
)
)
|| '-'
|| LTRIM
(
TO_CHAR
(
TO_NUMBER (SUBSTR (low_value, 5, 2), 'XX')
,'00'
)
)
|| '-'
|| LTRIM
(
TO_CHAR
(
TO_NUMBER (SUBSTR (low_value, 7, 2), 'XX')
,'00'
)
)
|| ' '
|| LTRIM
(
TO_CHAR
(
TO_NUMBER (SUBSTR (low_value, 9, 2), 'XX') - 1
,'00'
)
)
|| ':'
|| LTRIM
(
TO_CHAR
(
TO_NUMBER (SUBSTR (low_value, 11, 2), 'XX') - 1
,'00'
)
)
|| ':'
|| LTRIM
(
TO_CHAR
(
TO_NUMBER (SUBSTR (low_value, 13, 2), 'XX') - 1
,'00'
)
)
|| '.'
|| LTRIM
(
TO_CHAR
(
TO_NUMBER (SUBSTR (low_value, 15), 'XXXXXXXXX')
,'000000000'
)
)
)
END
trans_low
,CASE
WHEN data_type IN ('CHAR', 'VARCHAR2')
THEN
UTL_RAW.cast_to_varchar2 (high_value)
WHEN data_type IN ('NVARCHAR2')
THEN
TO_CHAR (UTL_RAW.cast_to_nvarchar2 (high_value))
WHEN data_type = 'NUMBER'
THEN
TO_CHAR (UTL_RAW.cast_to_number (high_value))
WHEN data_type IN ('DATE')
THEN
RTRIM
(
LTRIM
(
TO_CHAR
(
100
* ( TO_NUMBER (SUBSTR (high_value, 1, 2), 'XX')
- 100)
+ (TO_NUMBER (SUBSTR (high_value, 3, 2), 'XX') - 100)
,'0000'
)
)
|| '-'
|| LTRIM
(
TO_CHAR
(
TO_NUMBER (SUBSTR (high_value, 5, 2), 'XX')
,'00'
)
)
|| '-'
|| LTRIM
(
TO_CHAR
(
TO_NUMBER (SUBSTR (high_value, 7, 2), 'XX')
,'00'
)
)
|| ' '
|| LTRIM
(
TO_CHAR
(
TO_NUMBER (SUBSTR (high_value, 9, 2), 'XX') - 1
,'00'
)
)
|| ':'
|| LTRIM
(
TO_CHAR
(
TO_NUMBER (SUBSTR (high_value, 11, 2), 'XX') - 1
,'00'
)
)
|| ':'
|| LTRIM
(
TO_CHAR
(
TO_NUMBER (SUBSTR (high_value, 13, 2), 'XX') - 1
,'00'
)
)
)
WHEN data_type LIKE 'TIMESTAMP%'
THEN
RTRIM
(
LTRIM
(
TO_CHAR
(
100
* ( TO_NUMBER (SUBSTR (high_value, 1, 2), 'XX')
- 100)
+ (TO_NUMBER (SUBSTR (high_value, 3, 2), 'XX') - 100)
,'0000'
)
)
|| '-'
|| LTRIM
(
TO_CHAR
(
TO_NUMBER (SUBSTR (high_value, 5, 2), 'XX')
,'00'
)
)
|| '-'
|| LTRIM
(
TO_CHAR
(
TO_NUMBER (SUBSTR (high_value, 7, 2), 'XX')
,'00'
)
)
|| ' '
|| LTRIM
(
TO_CHAR
(
TO_NUMBER (SUBSTR (high_value, 9, 2), 'XX') - 1
,'00'
)
)
|| ':'
|| LTRIM
(
TO_CHAR
(
TO_NUMBER (SUBSTR (high_value, 11, 2), 'XX') - 1
,'00'
)
)
|| ':'
|| LTRIM
(
TO_CHAR
(
TO_NUMBER (SUBSTR (high_value, 13, 2), 'XX') - 1
,'00'
)
)
|| '.'
|| LTRIM
(
TO_CHAR
(
TO_NUMBER (SUBSTR (high_value, 15), 'XXXXXXXXX')
,'000000000'
)
)
)
END
trans_high
--,'--' desc_succeeded
FROM dba_tab_cols
WHERE UPPER (table_name) LIKE
UPPER
(
CASE
WHEN INSTR ('&1', '.') > 0
THEN
SUBSTR ('&1', INSTR ('&1', '.') + 1)
ELSE
'&1'
END
)
AND owner LIKE
CASE
WHEN INSTR ('&1', '.') > 0
THEN
UPPER (SUBSTR ('&1', 1, INSTR ('&1', '.') - 1))
ELSE
USER
END
--AND column_name = decode('&&2','',column_name,upper('&&2'))
AND (&&2)
ORDER BY owner, table_name, column_id
/
SCOTT@test01p> @ descz empx column_name='HIREDATE'
SCOTT@test01p> @ prxx
==============================
DESC_OWNER : SCOTT
DESC_TABLE_NAME : EMPX
SAMPLE_SIZE : 14
LAST_ANALYZED : 2022-10-10 20:40:34
DESC_COLUMN_ID : 5
DESC_COLUMN_NAME : HIREDATE
DESC_NULLABLE :
DESC_DATA_TYPE : TIMESTAMP(6)(11)
NUM_DISTINCT : 13
DESC_DENSITY : .0769230769230769
NUM_NULLS : 0
HISTOGRAM :
NUM_BUCKETS : 1
TRANS_LOW : 1972-10-22 20:39:27.
TRANS_HIGH : 2022-10-10 20:35:42.536000000
PL/SQL procedure successfully completed.
SCOTT@test01p> @ desczz emp column_name='HIREDATE'
SCOTT@test01p> @ prxx
==============================
DESC_OWNER : SCOTT
DESC_TABLE_NAME : EMP
SAMPLE_SIZE : 14
LAST_ANALYZED : 2018-10-07 19:34:52
DESC_COLUMN_ID : 5
DESC_COLUMN_NAME : HIREDATE
DESC_NULLABLE :
DESC_DATA_TYPE : DATE(7)
NUM_DISTINCT : 13
DESC_DENSITY : .0769230769230769
NUM_NULLS : 0
HISTOGRAM :
NUM_BUCKETS : 1
TRANS_LOW : 1980-12-17 00:00:00
TRANS_HIGH : 1987-05-23 00:00:00
PL/SQL procedure successfully completed.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2917876/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20221101]完善descz.sql指令碼.txtSQL指令碼
- [20210506]完善tix指令碼.txt指令碼
- [20201202]完善sosi指令碼.txt指令碼
- [20210623]完善清除aud指令碼.txt指令碼
- [20231117]完善ashtt.sql指令碼.txtSQL指令碼
- [20211230]完善sql_id指令碼.txtSQL指令碼
- [20211122]完善descx.sql指令碼.txtSQL指令碼
- [20221012]完善spsw.sql指令碼.txtSQL指令碼
- [20221101]完善gts.sql指令碼.txtSQL指令碼
- [20230203]完善awr.sql指令碼.txtSQL指令碼
- [20230123]完善curheapz.sql指令碼.txtSQL指令碼
- [20210407]完善ti.sql指令碼.txtSQL指令碼
- [20210125]完善hide.sql指令碼.txtIDESQL指令碼
- [20191111]完善bind_cap.sql指令碼.txtSQL指令碼
- [20220217]完善tpt gts.sql指令碼.txtSQL指令碼
- [20220510]完善tpt expandz.sql指令碼.txtSQL指令碼
- [20211202]完善d_buffer.sql指令碼.txtSQL指令碼
- [20211129]完善tpt tablist.sql指令碼.txtSQL指令碼
- [20211130]完善tpt t.sql指令碼.txtSQL指令碼
- [20211129]完善tpt killi.sql指令碼.txtSQL指令碼
- [20211126]完善tpt pr.sql指令碼.txtSQL指令碼
- [20230414]完善seg2.sql指令碼.txtSQL指令碼
- [20230210]建立完善swcnm.sql指令碼.txtSQL指令碼
- [20221208]完善bind_cap.sql指令碼.txtSQL指令碼
- [20220311]完善ash_wait_chains指令碼.txtAI指令碼
- [20220309]完善shp4.sql指令碼.txtSQL指令碼
- [20231025]完善tpt的trans.sql指令碼.txtSQL指令碼
- [20241114]建立完善ext_kglob.sh指令碼.txt指令碼
- [20220111]完善tpt ashash_index_helper指令碼.txtIndex指令碼
- [20220323]完善tpt get_trace.sql指令碼.txtSQL指令碼
- [20220823]完善tpt的ashtop.sql指令碼.txtSQL指令碼
- [20221208]完善bind_cap_awr.sql指令碼.txtSQL指令碼
- [20190416]完善shared latch測試指令碼2.txt指令碼
- [20220129]完善tpt ash ash_index_helperx指令碼.txtIndex指令碼
- [20211111]補充完善ash_wait_chains指令碼.txtAI指令碼
- [20230302]建立完善tpt o2.sql指令碼.txtSQL指令碼
- [20230203]建立完善sp1x.sql指令碼.txtSQL指令碼
- [20220519]完善tpt dash_wait_chains2.sql指令碼.txtAISQL指令碼