分区表的分区键和分区类型都可以通过获取创建该分区表的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';
