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

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104

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';