从视图查询表分区的相关信息

分区表的分区键和分区类型都可以通过获取创建该分区表的DDL定义语句来了解，那么可不可以直接查询字典视图来获取这些信息呢？常用的dba_tab_partitions视图并没有包含我们想要的分区属性信息；这里我们可以用到dba_part_key_columns(describes the partitioning key columns for all partitioned objects in the database. Its columns are the same as those in ALL_PART_KEY_COLUMNS)和dba_part_tables(displays the object-level partitioning information for all partitioned tables in the database. Its columns are the same as those in ALL_PART_TABLES)这2个视图:

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> desc dba_part_tables;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 TABLE_NAME                                         VARCHAR2(30)
 PARTITIONING_TYPE                                  VARCHAR2(9)
 SUBPARTITIONING_TYPE                               VARCHAR2(9)
 PARTITION_COUNT                                    NUMBER
 DEF_SUBPARTITION_COUNT                             NUMBER
 PARTITIONING_KEY_COUNT                             NUMBER
 SUBPARTITIONING_KEY_COUNT                          NUMBER
 STATUS                                             VARCHAR2(8)
 DEF_TABLESPACE_NAME                                VARCHAR2(30)
 DEF_PCT_FREE                                       NUMBER
 DEF_PCT_USED                                       NUMBER
 DEF_INI_TRANS                                      NUMBER
 DEF_MAX_TRANS                                      NUMBER
 DEF_INITIAL_EXTENT                                 VARCHAR2(40)
 DEF_NEXT_EXTENT                                    VARCHAR2(40)
 DEF_MIN_EXTENTS                                    VARCHAR2(40)
 DEF_MAX_EXTENTS                                    VARCHAR2(40)
 DEF_MAX_SIZE                                       VARCHAR2(40)
 DEF_PCT_INCREASE                                   VARCHAR2(40)
 DEF_FREELISTS                                      NUMBER
 DEF_FREELIST_GROUPS                                NUMBER
 DEF_LOGGING                                        VARCHAR2(7)
 DEF_COMPRESSION                                    VARCHAR2(8)
 DEF_COMPRESS_FOR                                   VARCHAR2(12)
 DEF_BUFFER_POOL                                    VARCHAR2(7)
 DEF_FLASH_CACHE                                    VARCHAR2(7)
 DEF_CELL_FLASH_CACHE                               VARCHAR2(7)
 REF_PTN_CONSTRAINT_NAME                            VARCHAR2(30)
 INTERVAL                                           VARCHAR2(1000)
 IS_NESTED                                          VARCHAR2(3)
 DEF_SEGMENT_CREATION                               VARCHAR2(4)

SQL> desc dba_part_key_columns;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 NAME                                               VARCHAR2(30)
 OBJECT_TYPE                                        CHAR(5)
 COLUMN_NAME                                        VARCHAR2(4000)
 COLUMN_POSITION                                    NUMBER

SQL> col table_name for a20
SQL> col column_name for a20
SQL> col partition for a20

SQL> select t.table_name, kc.column_name, t.partitioning_type
  2    from dba_part_key_columns kc, dba_part_tables t
  3   where kc.owner = t.owner
  4     and kc.name = t.table_name
  5     and t.table_name='COSTS';

TABLE_NAME           COLUMN_NAME          PARTITION
-------------------- -------------------- ---------
COSTS                TIME_ID              RANGE

/* 针对存在子分区的表，需要用到dba_subpart_key_columns视图 */

SQL> select t.table_name, kc.column_name, t.partitioning_type
  2    from dba_part_key_columns kc, dba_part_tables t
  3   where kc.owner = t.owner
  4     and kc.name = t.table_name
  5     and t.table_name='PRODUCTS'
  6   union all
  7  select u.table_name,skc.column_name,u.subpartitioning_type
  8    from dba_subpart_key_columns skc,dba_part_tables u
  9   where skc.owner=u.owner
 10   and skc.name=u.table_name
 11   and u.subpartitioning_type!='NONE'
 12   and u.table_name='PRODUCTS';

TABLE_NAME           COLUMN_NAME          PARTITION
-------------------- -------------------- ---------
PRODUCTS             T1                   RANGE
PRODUCTS             T2                   HASH

Script:

select t.table_name, kc.column_name, t.partitioning_type
  from dba_part_key_columns kc, dba_part_tables t
 where kc.owner = t.owner
   and kc.name = t.table_name
   and t.table_name = '&TABNAME'
   and t.owner = '&OWNAME'
union all
select u.table_name, skc.column_name, u.subpartitioning_type
  from dba_subpart_key_columns skc, dba_part_tables u
 where skc.owner = u.owner
   and skc.name = u.table_name
   and u.subpartitioning_type != 'NONE'
   and u.table_name = '&TABNAME'
   and u.owner = '&OWNAME';