Dba table info

Fiche express
Type	Script SQL*Plus de diagnostic
Domaine	Administration (métadonnées de table)
Voir aussi	Dba tables all · Dba related child tables · Dba row size

Dba table info interroge le propriétaire et le nom d'une table, puis affiche en une seule passe toutes ses métadonnées : informations générales, informations objet, segments (y compris partitions), colonnes, index, contraintes, partitions de table et d'index, triggers. Une fiche d'identité complète d'une table, utile en diagnostic ou avant une intervention structurelle.

Script

-- Fiche d'identité complète d'une table : table, objet, segments,
-- colonnes, index, contraintes, partitions, triggers.

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     PROMPT 'Enter table owner : '
ACCEPT table_name PROMPT 'Enter table name  : '

SET ECHO      OFF
SET FEEDBACK  6
SET HEADING   ON
SET LINESIZE  180
SET LONG      9000
SET PAGESIZE  50000
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"

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
/

Sections du rapport

Table Information — tablespace, date de dernière analyse (statistiques), nombre de lignes.
Object Information — identifiants d'objet, dates de création/dernier DDL, statut.
Segment Information — occupation physique, y compris détail par partition pour les tables partitionnées.
Columns — liste des colonnes avec type et nullabilité.
Indexes — colonnes indexées, tous index confondus.
Constraints — clés primaires, uniques, étrangères, contraintes CHECK, avec la contrainte référencée pour les clés étrangères.
Partitions (Table) et Partitions (Index) — vides si la table n'est pas partitionnée.
Triggers — déclencheurs associés à la table, avec leur code.

Voir aussi

Dba tables all — pour retrouver le nom exact d'une table avant d'en demander le détail
Dba related child tables — tables enfants liées par clé étrangère
Dba row size — estimation de la taille de ligne pour tout un schéma

Dba table info

Script

Sections du rapport

Voir aussi

Menu de navigation

Rechercher