ITPub博客

首页 > 数据库 > Oracle > [20211122]完善descx.sql脚本.txt

[20211122]完善descx.sql脚本.txt

原创 Oracle 作者:lfree 时间:2021-11-23 09:19:19 1 删除 编辑

[20211122]完善descx.sql脚本.txt

--// 按照tpt descx.sql脚本修改一下,增加一些功能,我主要不想使用display_raw函数,而且还有许多系统使用11g,无法在sql语句中定义函数。
--// 另外就是我们生产系统表字段太多了,有必要加入一些过滤,显示特定字段。

$ 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 A30
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,
        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 = 'NUMBER'
       THEN
          TO_CHAR (UTL_RAW.cast_to_number (low_value))
       WHEN data_type = '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')))
    END
           trans_low,
    CASE
       WHEN data_type IN ('CHAR', 'VARCHAR2')
       THEN
          UTL_RAW.cast_to_varchar2 (high_value)
       WHEN data_type = 'NUMBER'
       THEN
          TO_CHAR (UTL_RAW.cast_to_number (high_value))
       WHEN data_type = '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')))
    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
/

1.环境:

SCOTT@book> @ ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> create index i_emp_hiredate on emp(trunc(hiredate));
Index created.

2.测试:
SCOTT@book> @ descz scott.emp 1=1
eXtended describe of scott.emp

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           Col# Column Name                    Null?      Type                 NUM_DISTINCT        Density  NUM_NULLS HISTOGRAM       NUM_BUCKETS Low_value                                High_value
---------- -------------------- ---- ------------------------------ ---------- -------------------- ------------ -------------- ---------- --------------- ----------- ---------------------------------------- ----------------------------------------
SCOTT      EMP                     1 EMPNO                          NOT NULL   NUMBER(4,0)                    14   .07142857143          0                           1 7369                                     7934
                                   2 ENAME                                     VARCHAR2(10)                   14   .07142857143          0                           1 ADAMS                                    WARD
                                   3 JOB                                       VARCHAR2(9)                     5   .20000000000          0                           1 ANALYST                                  SALESMAN
                                   4 MGR                                       NUMBER(4,0)                     6   .16666666667          1                           1 7566                                     7902
                                   5 HIREDATE                                  DATE(7)                        13   .07692307692          0                           1 1980-12-17 00:00:00                      1987-05-23 00:00:00
                                   6 SAL                                       NUMBER(7,2)                    12   .08333333333          0                           1 800                                      5000
                                   7 COMM                                      NUMBER(7,2)                     4   .25000000000         10                           1 0                                        1400
                                   8 DEPTNO                                    NUMBER(2,0)                     3   .33333333333          0                           1 10                                       30
                                H    SYS_NC00009$                              DATE(7)                                                                                 -- ::                                    -- ::

9 rows selected.

--//分析表。

SCOTT@book> @ descz emp "column_name='SAL' or hidden_column='YES'"
eXtended describe of emp

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           Col# Column Name                    Null?      Type                 NUM_DISTINCT        Density  NUM_NULLS HISTOGRAM       NUM_BUCKETS Low_value                                High_value
---------- -------------------- ---- ------------------------------ ---------- -------------------- ------------ -------------- ---------- --------------- ----------- ---------------------------------------- ----------------------------------------
SCOTT      EMP                     6 SAL                                       NUMBER(7,2)                    12   .08333333333          0                           1 800                                      5000
                                H    SYS_NC00009$                              DATE(7)                        13   .07692307692          0                           1 1980-12-17 00:00:00                      1987-05-23 00:00:00

SCOTT@book> @ descz emp "column_id between 3 and 5"
eXtended describe of emp

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           Col# Column Name                    Null?      Type                 NUM_DISTINCT        Density  NUM_NULLS HISTOGRAM       NUM_BUCKETS Low_value                                High_value
---------- -------------------- ---- ------------------------------ ---------- -------------------- ------------ -------------- ---------- --------------- ----------- ---------------------------------------- ----------------------------------------
SCOTT      EMP                     3 JOB                                       VARCHAR2(9)                     5   .20000000000          0                           1 ANALYST                                  SALESMAN
                                   4 MGR                                       NUMBER(4,0)                     6   .16666666667          1                           1 7566                                     7902
                                   5 HIREDATE                                  DATE(7)                        13   .07692307692          0                           1 1980-12-17 00:00:00                      1987-05-23 00:00:00

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2843556/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    3081
  • 访问量
    6806368