Scripts:报告数据库中表信息汇总dba_table_info.sql

-- +----------------------------------------------------------------------------+
-- |                          Jeffrey M. Hunter                                 |
-- |                      jhunter@idevelopment.info                             |
-- |                         www.idevelopment.info                              |
-- |----------------------------------------------------------------------------|
-- |      Copyright (c) 1998-2012 Jeffrey M. Hunter. All rights reserved.       |
-- |----------------------------------------------------------------------------|
-- | DATABASE : Oracle                                                          |
-- | FILE     : dba_table_info.sql                                              |
-- | CLASS    : Database Administration                                         |
-- | PURPOSE  : Prompt the user for a schema and and table name then query all  |
-- |            metadata about the table.                                       |
-- | NOTE     : As with any code, ensure to test this script in a development   |
-- |            environment before attempting to run it in production.          |
-- +----------------------------------------------------------------------------+


SET TERMOUT OFF;
COLUMN current_instance NEW_VALUE current_instance NOPRINT;
SELECT rpad(instance_name, 17) current_instance FROM v$instance;
SET TERMOUT ON;


PROMPT 
PROMPT +------------------------------------------------------------------------+
PROMPT | Report   : Table Information                                           |
PROMPT | Instance : &current_instance                                           |
PROMPT +------------------------------------------------------------------------+


PROMPT 
ACCEPT schema     CHAR PROMPT 'Enter table owner : '
ACCEPT table_name CHAR PROMPT 'Enter table name  : '


SET ECHO        OFF
SET FEEDBACK    6
SET HEADING     ON
SET LINESIZE    180
SET LONG        9000
SET PAGESIZE    50000
SET TERMOUT     ON
SET TIMING      OFF
SET TRIMOUT     ON
SET TRIMSPOOL   ON
SET VERIFY      OFF


CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES


PROMPT 
PROMPT +------------------------------------------------------------------------+
PROMPT | TABLE INFORMATION                                                      |
PROMPT +------------------------------------------------------------------------+


COLUMN owner               FORMAT a20                   HEADING "Owner"
COLUMN table_name          FORMAT a30                   HEADING "Table Name"
COLUMN tablespace_name     FORMAT a30                   HEADING "Tablespace"
COLUMN last_analyzed       FORMAT a23                   HEADING "Last Analyzed"
COLUMN num_rows            FORMAT 9,999,999,999,999     HEADING "# of Rows"


SELECT
    owner
  , table_name
  , tablespace_name
  , TO_CHAR(last_analyzed, 'DD-MON-YYYY HH24:MI:SS') last_analyzed
  , num_rows
FROM
    dba_tables
WHERE
      owner      = UPPER('&schema')
  AND table_name = UPPER('&table_name')
/


PROMPT 
PROMPT +------------------------------------------------------------------------+
PROMPT | OBJECT INFORMATION                                                     |
PROMPT +------------------------------------------------------------------------+


COLUMN object_id                                     HEADING "Object ID"
COLUMN data_object_id                                HEADING "Data Object ID"
COLUMN created             FORMAT A23                HEADING "Created"
COLUMN last_ddl_time       FORMAT A23                HEADING "Last DDL"
COLUMN status                                        HEADING "Status"


SELECT
    object_id
  , data_object_id
  , TO_CHAR(created, 'DD-MON-YYYY HH24:MI:SS')        created
  , TO_CHAR(last_ddl_time, 'DD-MON-YYYY HH24:MI:SS')  last_ddl_time
  , status
FROM
    dba_objects
WHERE
      owner       = UPPER('&schema')
  AND object_name = UPPER('&table_name')
  AND object_type = 'TABLE'
/


PROMPT 
PROMPT +------------------------------------------------------------------------+
PROMPT | SEGMENT INFORMATION                                                    |
PROMPT +------------------------------------------------------------------------+


COLUMN segment_name        FORMAT a30                HEADING "Segment Name"
COLUMN partition_name      FORMAT a30                HEADING "Partition Name"
COLUMN segment_type        FORMAT a16                HEADING "Segment Type"
COLUMN tablespace_name     FORMAT a30                HEADING "Tablespace"
COLUMN num_rows            FORMAT 9,999,999,999,999  HEADING "Num Rows"
COLUMN bytes               FORMAT 9,999,999,999,999  HEADING "Bytes"
COLUMN last_analyzed       FORMAT a23                HEADING "Last Analyzed"


SELECT 
    seg.segment_name      segment_name
  , null                  partition_name
  , seg.segment_type      segment_type
  , seg.tablespace_name   tablespace_name
  , tab.num_rows          num_rows
  , seg.bytes             bytes
  , TO_CHAR(tab.last_analyzed, 'DD-MON-YYYY HH24:MI:SS') last_analyzed
from
    dba_segments seg
  , dba_tables tab
WHERE
      seg.owner = UPPER('&schema')
  AND seg.segment_name = UPPER('&table_name')
  AND seg.segment_name = tab.table_name
  AND seg.owner = tab.owner
  AND seg.segment_type = 'TABLE'
UNION ALL
SELECT 
    seg.segment_name      segment_name
  , seg.partition_name    partition_name
  , seg.segment_type      segment_type
  , seg.tablespace_name   tablespace_name
  , part.num_rows         num_rows
  , seg.bytes             bytes
  , TO_CHAR(part.last_analyzed, 'DD-MON-YYYY HH24:MI:SS') last_analyzed
FROM
    dba_segments seg
  , dba_tab_partitions part
WHERE
      part.table_owner = UPPER('&schema')
  AND part.table_name = UPPER('&table_name')
  AND part.partition_name = seg.partition_name
  AND seg.segment_type = 'TABLE PARTITION'
ORDER BY
    segment_name
  , partition_name
/




PROMPT 
PROMPT +------------------------------------------------------------------------+
PROMPT | COLUMNS                                                                |
PROMPT +------------------------------------------------------------------------+


COLUMN column_name         FORMAT a30                HEADING "Column Name"
COLUMN data_type           FORMAT a25                HEADING "Data Type"
COLUMN nullable            FORMAT a13                HEADing "Null?"


SELECT
    column_name
  , DECODE(nullable, 'Y', ' ', 'NOT NULL') nullable
  , DECODE(data_type
               , 'RAW',      data_type || '(' ||  data_length || ')'
               , 'CHAR',     data_type || '(' ||  data_length || ')'
               , 'VARCHAR',  data_type || '(' ||  data_length || ')'
               , 'VARCHAR2', data_type || '(' ||  data_length || ')'
               , 'NUMBER', NVL2(   data_precision
                                 , DECODE(    data_scale
                                            , 0
                                            , data_type || '(' || data_precision || ')'
                                            , data_type || '(' || data_precision || ',' || data_scale || ')'
                                   )
                                 , data_type)
               , data_type
    ) data_type
FROM
    dba_tab_columns
WHERE
      owner      = UPPER('&schema')
  AND table_name = UPPER('&table_name')
ORDER BY
    column_id
/




PROMPT 
PROMPT +------------------------------------------------------------------------+
PROMPT | INDEXES                                                                |
PROMPT +------------------------------------------------------------------------+


COLUMN index_name          FORMAT a40                HEADING "Index Name"
COLUMN column_name         FORMAT a30                HEADING "Column Name"
COLUMN column_length                                 HEADING "Column Length"


BREAK ON index_name SKIP 1


SELECT 
    index_owner || '.' || index_name  index_name
  , column_name
  , column_length
FROM
    dba_ind_columns
WHERE
      table_owner  = UPPER('&schema')
  AND table_name   = UPPER('&table_name')
ORDER BY
    index_name
  , column_position
/




PROMPT 
PROMPT +------------------------------------------------------------------------+
PROMPT | CONSTRAINTS                                                            |
PROMPT +------------------------------------------------------------------------+


COLUMN constraint_name     FORMAT a30                HEADING "Constraint Name"
COLUMN constraint_type     FORMAT a13                HEADING "Constraint|Type"
COLUMN search_condition    FORMAT a30                HEADING "Search Condition"
COLUMN r_constraint_name   FORMAT a30                HEADING "R / Constraint Name"
COLUMN delete_rule         FORMAT a12                HEADING "Delete Rule"
COLUMN status                                        HEADING "Status"


BREAK ON constraint_name ON constraint_type


SELECT 
    a.constraint_name
  , DECODE(a.constraint_type
             , 'P', 'Primary Key'
             , 'C', 'Check'
             , 'R', 'Referential'
             , 'V', 'View Check'
             , 'U', 'Unique'
             , a.constraint_type
    ) constraint_type
  , b.column_name
  , a.search_condition
  , NVL2(a.r_owner, a.r_owner || '.' ||  a.r_constraint_name, null) r_constraint_name
  , a.delete_rule
  , a.status
FROM 
    dba_constraints  a
  , dba_cons_columns b
WHERE
      a.owner            = UPPER('&schema')
  AND a.table_name       = UPPER('&table_name')
  AND a.constraint_name  = b.constraint_name
  AND b.owner            = UPPER('&schema')
  AND b.table_name       = UPPER('&table_name')
ORDER BY
    a.constraint_name
  , b.position
/




PROMPT 
PROMPT +------------------------------------------------------------------------+
PROMPT | PARTITIONS (TABLE)                                                     |
PROMPT +------------------------------------------------------------------------+


COLUMN partition_name                                HEADING "Partition Name"
COLUMN column_name         FORMAT a30                HEADING "Column Name"
COLUMN tablespace_name     FORMAT a30                HEADING "Tablespace"
COLUMN composite           FORMAT a9                 HEADING "Composite"
COLUMN subpartition_count                            HEADING "Sub. Part.|Count"
COLUMN logging             FORMAT a7                 HEADING "Logging"
COLUMN high_value          FORMAT a13                HEADING "High Value" TRUNC


BREAK ON partition_name


SELECT
    a.partition_name
  , b.column_name
  , a.tablespace_name
  , a.composite
  , a.subpartition_count
  , a.logging
FROM 
    dba_tab_partitions    a
  , dba_part_key_columns  b
WHERE
      a.table_owner        = UPPER('&schema')
  AND a.table_name         = UPPER('&table_name')
  AND RTRIM(b.object_type) = 'TABLE'
  AND b.owner              = a.table_owner
  AND b.name               = a.table_name
ORDER BY
    a.partition_position
  , b.column_position
/




PROMPT 
PROMPT +------------------------------------------------------------------------+
PROMPT | PARTITIONS (INDEX)                                                     |
PROMPT +------------------------------------------------------------------------+


COLUMN index_name              FORMAT a30                HEADING "Index Name"
COLUMN partitioning_type       FORMAT a9                 HEADING "Type"
COLUMN partition_count         FORMAT 99999              HEADING "Part.|Count"
COLUMN partitioning_key_count  FORMAT 99999              HEADING "Part.|Key Count"
COLUMN locality                FORMAT a8                 HEADING "Locality"
COLUMN alignment               FORMAT a12                HEADING "Alignment"


SELECT
    a.owner || '.' || a.index_name   index_name
  , b.column_name
  , a.partitioning_type
  , a.partition_count
  , a.partitioning_key_count
  , a.locality
  , a.alignment
FROM 
    dba_part_indexes      a
  , dba_part_key_columns  b
WHERE
      a.owner              = UPPER('&schema')
  AND a.table_name         = UPPER('&table_name')
  AND RTRIM(b.object_type) = 'INDEX'
  AND b.owner              = a.owner
  AND b.name               = a.index_name
ORDER BY
    a.index_name
  , b.column_position
/






PROMPT 
PROMPT +------------------------------------------------------------------------+
PROMPT | TRIGGERS                                                               |
PROMPT +------------------------------------------------------------------------+


COLUMN trigger_name            FORMAT a30                HEADING "Trigger Name"
COLUMN trigger_type            FORMAT a18                HEADING "Type"
COLUMN triggering_event        FORMAT a9                 HEADING "Trig.|Event"
COLUMN referencing_names       FORMAT a65                HEADING "Referencing Names" newline
COLUMN when_clause             FORMAT a65                HEADING "When Clause" newline
COLUMN trigger_body            FORMAT a65                HEADING "Trigger Body" newline


SELECT
    owner || '.' || trigger_name  trigger_name
  , trigger_type
  , triggering_event
  , status
  , referencing_names
  , when_clause
  , trigger_body
FROM
    dba_triggers
WHERE
      table_owner = UPPER('&schema')
  AND table_name  = UPPER('&table_name')
ORDER BY
     trigger_name
/