[20221010]完善descz.sql指令碼.txt

lfree發表於2022-10-12

[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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章