About
How to get information on the partition and sub-partitions
Articles Related
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;
/