Partitions

March 7th, 2012

Table partitions

COLUMN partition_name                                HEADING "Partition Name"
COLUMN column_name         FORMAT A20                HEADING "Column Name"
COLUMN tablespace_name     FORMAT A28                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('&sch')
  AND a.table_name         = UPPER('&tab')
  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
/


Comments are closed.