Oracle OCP(22)：查询表信息

收集表信息脚本

-- | 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 PAGESIZE 9999
SET VERIFY  OFF
SET FEEDBACK OFF
SET LONG 9000

-- +----------------------------------------------------------------------------+
-- | PROMPT USER FOR SCHEMA AND TABLE                                           |
-- +----------------------------------------------------------------------------+

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


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

COLUMN owner              FORMAT A15                HEADING "Owner"
COLUMN table_name          FORMAT A30                HEADING "Table Name"
COLUMN tablespace_name    FORMAT A28                HEADING "Tablespace"
COLUMN last_analyzed      FORMAT A20                HEADING "Last Analyzed"
COLUMN num_rows            FORMAT 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('&sch')
  AND table_name = UPPER('&tab')
/

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

COLUMN object_id                                    HEADING "Object ID"
COLUMN data_object_id                                HEADING "Data Object ID"
COLUMN created            FORMAT A20                HEADING "Created"
COLUMN last_ddl_time      FORMAT A20                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('&sch')
  AND object_name = UPPER('&tab')
  AND object_type = 'TABLE'
/

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

COLUMN segment_type                                  HEADING "Segment Type"
COLUMN bytes              FORMAT 9,999,999,999,999  HEADING "Bytes"
COLUMN extents            FORMAT 999,999,999        HEADING "Extents"
COLUMN initial_extent      FORMAT 999,999,999,999    HEADING "Initial|Extent"
COLUMN next_extent        FORMAT 999,999,999,999    HEADING "Next|Extent"
COLUMN min_extents        FORMAT 999                HEADING "Min|Extents"
COLUMN max_extents        FORMAT 9,999,999,999      HEADING "Max|Extents"
COLUMN pct_increase        FORMAT 999.00            HEADING "Pct|Increase"
COLUMN freelists                                    HEADING "Free|Lists"
COLUMN freelist_groups                              HEADING "Free|List Groups"

SELECT
    segment_type    segment_type
  , bytes/1024/1024  Mbytes
  , extents          extents
  , initial_extent  initial_extent
  , next_extent      next_extent
  , min_extents      min_extents
  , max_extents      max_extents
  , pct_increase    pct_increase
  , freelists        freelists
  , freelist_groups  freelist_groups
FROM
    dba_segments
WHERE
      owner        = UPPER('&sch')
  AND segment_name = UPPER('&tab')
/


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

COLUMN column_name        FORMAT A20                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('&sch')
  AND table_name = UPPER('&tab')
ORDER BY
    column_id
/


PROMPT
PROMPT +----------------------------------------------------------------------------+
PROMPT | CONSTRAINTS                                                                |
PROMPT | UNCOMMENT THIS SECTION FROM THE SCRIPT TO GET THE INFO ABOUT CONST        |
PROMPT +----------------------------------------------------------------------------+
PROMPT

COLUMN constraint_name    FORMAT A18                HEADING "Constraint Name"
COLUMN constraint_type    FORMAT A11                HEADING "Constraint|Type"
COLUMN search_condition    FORMAT A15                HEADING "Search Condition"
COLUMN r_constraint_name  FORMAT A20                HEADING "R / Constraint Name"
COLUMN delete_rule        FORMAT A11                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('&sch')
  AND a.table_name      = UPPER('&tab')
  AND a.constraint_name  = b.constraint_name
  AND b.owner            = UPPER('&sch')
  AND b.table_name      = UPPER('&tab')
ORDER BY
    a.constraint_name
  , b.position
/


SET PAGESIZE 9999
SET VERIFY  OFF
SET FEEDBACK ON