Oracle Partition - Metadata (View)

About

How to get information on the partition and sub-partitions

Type Metadata

View

Table

View Description
(ALL|DBA|USER)_TABLES the PARTITIONED column value is YES
(ALL|DBA|USER)_PART_TABLES type of partitioning scheme, number of partitions
(ALL|DBA|USER)_TAB_PARTITIONS details of each partition, such as the name and the upper boundary of the partition.
(ALL|DBA|USER)_TAB_SUBPARTITIONS
-- TRANS partition details
col partition_position format 999 head "Pos"
col partition_name format a10 head "Name"
col high_value format a50 head "High Value"
select partition_position, partition_name, high_value
from dba_tab_partitions
where table_name = 'TRANS' order by 1;
Pos   Name        High Value
---   --------    -----------------------------------------------------------------------
1     Y05Q1       TO_DATE(' 2005-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GRE...
2     Y05Q2       TO_DATE(' 2005-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GRE...
3     Y05Q3       TO_DATE(' 2005-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GRE...
4     Y05Q4       TO_DATE(' 2006-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GRE...
5     PMAX        MAXVALUE

Index

View Description
(ALL|DBA|USER)_INDEXES the PARTITIONED column value is YES
(ALL|DBA|USER)_ALL_PART_INDEXES Display partitioning information for partitioned indexes
(ALL|DBA|USER)_IND_PARTITIONS partition-level partitioning information
(ALL|DBA|USER)_IND_SUBPARTITIONS partition-level subpartitioning information
(ALL|DBA|USER)_SUBPARTITION_TEMPLATES Display information about existing subpartition templates

Table definition (DDL)

With the DBMS_METADATA package, you can get the DDL of a table without the partition clause through the PARTITIONING parameter of the SET_TRANSFORM_PARAM function.

Example: Printing the DDL information of the table 'myTableName' without partition clause

set serveroutput on;
BEGIN

  -- Set all transform param to default
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT',TRUE);
  
  -- No storage clause, it's not really beautiful :)
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);
  
  -- Suppress the partition clause
  DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PARTITIONING',FALSE);
  
  -- Print the DDL
  DBMS_OUTPUT.PUT_LINE(DBMS_METADATA.GET_DDL('TABLE','myTableName')); 
  
END;
/

Documentation / Reference


Powered by ComboStrap