查詢表資訊

brj880719發表於2017-10-26

收集表資訊指令碼

大神寫的指令碼分享給大家，用來收集表資訊的

  1. -- | PURPOSE : Prompt the user for a schema and and table name then query all |
  2. -- | metadata about the table. |
  3. -- | NOTE : As with any code, ensure to test this script in a development |
  4. -- | environment before attempting to run it in production. |
  5. -- +----------------------------------------------------------------------------+


  8. SET PAGESIZE 9999
  9. SET VERIFY OFF
  10. SET FEEDBACK OFF
  11. SET LONG 9000

  13. -- +----------------------------------------------------------------------------+
  14. -- | PROMPT USER FOR SCHEMA AND TABLE |
  15. -- +----------------------------------------------------------------------------+

  17. ACCEPT sch prompt 'Enter Schema (i.e. SCOTT) : '
  18. ACCEPT tab prompt 'Enter Table (i.e. EMP) : '


  21. PROMPT
  22. PROMPT +----------------------------------------------------------------------------+
  23. PROMPT | TABLE INFORMATION |
  24. PROMPT +----------------------------------------------------------------------------+

  26. COLUMN owner FORMAT A15 HEADING "Owner"
  27. COLUMN table_name FORMAT A30 HEADING "Table Name"
  28. COLUMN tablespace_name FORMAT A28 HEADING "Tablespace"
  29. COLUMN last_analyzed FORMAT A20 HEADING "Last Analyzed"
  30. COLUMN num_rows FORMAT 999,999,999 HEADING "# of Rows"

  32. SELECT
  33.     owner
  34.   , table_name
  35.   , tablespace_name
  36.   , TO_CHAR(last_analyzed, 'DD-MON-YYYY HH24:MI:SS') last_analyzed
  37.   , num_rows
  38. FROM
  39.     dba_tables
  40. WHERE
  41.       owner = UPPER('&sch')
  42.   AND table_name = UPPER('&tab')
  43. /

  45. PROMPT
  46. PROMPT +----------------------------------------------------------------------------+
  47. PROMPT | OBJECT INFORMATION |
  48. PROMPT +----------------------------------------------------------------------------+

  50. COLUMN object_id HEADING "Object ID"
  51. COLUMN data_object_id HEADING "Data Object ID"
  52. COLUMN created FORMAT A20 HEADING "Created"
  53. COLUMN last_ddl_time FORMAT A20 HEADING "Last DDL"
  54. COLUMN status HEADING "Status"

  56. SELECT
  57.     object_id
  58.   , data_object_id
  59.   , TO_CHAR(created, 'DD-MON-YYYY HH24:MI:SS') created
  60.   , TO_CHAR(last_ddl_time, 'DD-MON-YYYY HH24:MI:SS') last_ddl_time
  61.   , status
  62. FROM
  63.     dba_objects
  64. WHERE
  65.       owner = UPPER('&sch')
  66.   AND object_name = UPPER('&tab')
  67.   AND object_type = 'TABLE'
  68. /

  70. PROMPT
  71. PROMPT +----------------------------------------------------------------------------+
  72. PROMPT | SEGMENT INFORMATION |
  73. PROMPT +----------------------------------------------------------------------------+

  75. COLUMN segment_type HEADING "Segment Type"
  76. COLUMN bytes FORMAT 9,999,999,999,999 HEADING "Bytes"
  77. COLUMN extents FORMAT 999,999,999 HEADING "Extents"
  78. COLUMN initial_extent FORMAT 999,999,999,999 HEADING "Initial|Extent"
  79. COLUMN next_extent FORMAT 999,999,999,999 HEADING "Next|Extent"
  80. COLUMN min_extents FORMAT 999 HEADING "Min|Extents"
  81. COLUMN max_extents FORMAT 9,999,999,999 HEADING "Max|Extents"
  82. COLUMN pct_increase FORMAT 999.00 HEADING "Pct|Increase"
  83. COLUMN freelists HEADING "Free|Lists"
  84. COLUMN freelist_groups HEADING "Free|List Groups"

  86. SELECT
  87.     segment_type segment_type
  88.   , bytes/1024/1024 Mbytes
  89.   , extents extents
  90.   , initial_extent initial_extent
  91.   , next_extent next_extent
  92.   , min_extents min_extents
  93.   , max_extents max_extents
  94.   , pct_increase pct_increase
  95.   , freelists freelists
  96.   , freelist_groups freelist_groups
  97. FROM
  98.     dba_segments
  99. WHERE
  100.       owner = UPPER('&sch')
  101.   AND segment_name = UPPER('&tab')
  102. /


  105. PROMPT
  106. PROMPT +----------------------------------------------------------------------------+
  107. PROMPT | COLUMNS |
  108. PROMPT +----------------------------------------------------------------------------+

  110. COLUMN column_name FORMAT A20 HEADING "Column Name"
  111. COLUMN data_type FORMAT A25 HEADING "Data Type"
  112. COLUMN nullable FORMAT A13 HEADing "Null?"

  114. SELECT
  115.     column_name
  116.   , DECODE(nullable, 'Y', ' ', 'NOT NULL') nullable
  117.   , DECODE(data_type
  118.                , 'RAW', data_type || '(' || data_length || ')'
  119.                , 'CHAR', data_type || '(' || data_length || ')'
  120.                , 'VARCHAR', data_type || '(' || data_length || ')'
  121.                , 'VARCHAR2', data_type || '(' || data_length || ')'
  122.                , 'NUMBER', NVL2( data_precision
  123.                                  , DECODE( data_scale
  124.                                             , 0
  125.                                             , data_type || '(' || data_precision || ')'
  126.                                             , data_type || '(' || data_precision || ',' || data_scale || ')'
  127.                                    )
  128.                                  , data_type)
  129.                , data_type
  130.     ) data_type
  131. FROM
  132.     dba_tab_columns
  133. WHERE
  134.       owner = UPPER('&sch')
  135.   AND table_name = UPPER('&tab')
  136. ORDER BY
  137.     column_id
  138. /


  141. PROMPT
  142. PROMPT +----------------------------------------------------------------------------+
  143. PROMPT | CONSTRAINTS |
  144. PROMPT | UNCOMMENT THIS SECTION FROM THE SCRIPT TO GET THE INFO ABOUT CONST |
  145. PROMPT +----------------------------------------------------------------------------+
  146. PROMPT

  148. COLUMN constraint_name FORMAT A18 HEADING "Constraint Name"
  149. COLUMN constraint_type FORMAT A11 HEADING "Constraint|Type"
  150. COLUMN search_condition FORMAT A15 HEADING "Search Condition"
  151. COLUMN r_constraint_name FORMAT A20 HEADING "R / Constraint Name"
  152. COLUMN delete_rule FORMAT A11 HEADING "Delete Rule"
  153. COLUMN status HEADING "Status"

  155. BREAK ON constraint_name ON constraint_type

  157. SELECT
  158.     a.constraint_name
  159.   , DECODE(a.constraint_type
  160.              , 'P', 'Primary Key'
  161.              , 'C', 'Check'
  162.              , 'R', 'Referential'
  163.              , 'V', 'View Check'
  164.              , 'U', 'Unique'
  165.              , a.constraint_type
  166.     ) constraint_type
  167.   , b.column_name
  168.   , a.search_condition
  169.   , NVL2(a.r_owner, a.r_owner || '.' || a.r_constraint_name, null) r_constraint_name
  170.   , a.delete_rule
  171.   , a.status
  172. FROM
  173.     dba_constraints a
  174.   , dba_cons_columns b
  175. WHERE
  176.       a.owner = UPPER('&sch')
  177.   AND a.table_name = UPPER('&tab')
  178.   AND a.constraint_name = b.constraint_name
  179.   AND b.owner = UPPER('&sch')
  180.   AND b.table_name = UPPER('&tab')
  181. ORDER BY
  182.     a.constraint_name
  183.   , b.position
  184. /


  187. SET PAGESIZE 9999
  188. SET VERIFY OFF
  189. SET FEEDBACK ON

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24851054/viewspace-2146407/，如需轉載，請註明出處，否則將追究法律責任。

相關文章