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

分区表的分区键和分区类型都可以通过获取创建该分区表的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 <code>ALL_PART_KEY_COLUMNS)和</code>dba_part_tables(displays the object-level partitioning information for all partitioned tables in the database. Its columns are the same as those in <code>ALL_PART_TABLES</code>)这2个视图:

<a href="http://blog.51cto.com/maclean/1277567#">?</a>

<code>SQL&gt; </code><code>select</code> <code>* </code><code>from</code> <code>v$version;</code>

<code>BANNER</code>

<code>--------------------------------------------------------------------------------</code>

<code>Oracle </code><code>Database</code> <code>11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production</code>

<code>PL/SQL Release 11.2.0.2.0 - Production</code>

<code>CORE    11.2.0.2.0      Production</code>

<code>TNS </code><code>for</code> <code>Linux: Version 11.2.0.2.0 - Production</code>

<code>NLSRTL Version 11.2.0.2.0 - Production</code>

<code>SQL&gt; </code><code>desc</code> <code>dba_part_tables;</code>

<code> </code><code>Name</code>                                      <code>Null</code><code>?    Type</code>

<code> </code><code>----------------------------------------- -------- ----------------------------</code>

<code> </code><code>OWNER                                              VARCHAR2(30)</code>

<code> </code><code>TABLE_NAME                                         VARCHAR2(30)</code>

<code> </code><code>PARTITIONING_TYPE                                  VARCHAR2(9)</code>

<code> </code><code>SUBPARTITIONING_TYPE                               VARCHAR2(9)</code>

<code> </code><code>PARTITION_COUNT                                    NUMBER</code>

<code> </code><code>DEF_SUBPARTITION_COUNT                             NUMBER</code>

<code> </code><code>PARTITIONING_KEY_COUNT                             NUMBER</code>

<code> </code><code>SUBPARTITIONING_KEY_COUNT                          NUMBER</code>

<code> </code><code>STATUS                                             VARCHAR2(8)</code>

<code> </code><code>DEF_TABLESPACE_NAME                                VARCHAR2(30)</code>

<code> </code><code>DEF_PCT_FREE                                       NUMBER</code>

<code> </code><code>DEF_PCT_USED                                       NUMBER</code>

<code> </code><code>DEF_INI_TRANS                                      NUMBER</code>

<code> </code><code>DEF_MAX_TRANS                                      NUMBER</code>

<code> </code><code>DEF_INITIAL_EXTENT                                 VARCHAR2(40)</code>

<code> </code><code>DEF_NEXT_EXTENT                                    VARCHAR2(40)</code>

<code> </code><code>DEF_MIN_EXTENTS                                    VARCHAR2(40)</code>

<code> </code><code>DEF_MAX_EXTENTS                                    VARCHAR2(40)</code>

<code> </code><code>DEF_MAX_SIZE                                       VARCHAR2(40)</code>

<code> </code><code>DEF_PCT_INCREASE                                   VARCHAR2(40)</code>

<code> </code><code>DEF_FREELISTS                                      NUMBER</code>

<code> </code><code>DEF_FREELIST_GROUPS                                NUMBER</code>

<code> </code><code>DEF_LOGGING                                        VARCHAR2(7)</code>

<code> </code><code>DEF_COMPRESSION                                    VARCHAR2(8)</code>

<code> </code><code>DEF_COMPRESS_FOR                                   VARCHAR2(12)</code>

<code> </code><code>DEF_BUFFER_POOL                                    VARCHAR2(7)</code>

<code> </code><code>DEF_FLASH_CACHE                                    VARCHAR2(7)</code>

<code> </code><code>DEF_CELL_FLASH_CACHE                               VARCHAR2(7)</code>

<code> </code><code>REF_PTN_CONSTRAINT_NAME                            VARCHAR2(30)</code>

<code> </code><code>INTERVAL                                           VARCHAR2(1000)</code>

<code> </code><code>IS_NESTED                                          VARCHAR2(3)</code>

<code> </code><code>DEF_SEGMENT_CREATION                               VARCHAR2(4)</code>

<code>SQL&gt; </code><code>desc</code> <code>dba_part_key_columns;</code>

<code> </code><code>NAME</code>                                               <code>VARCHAR2(30)</code>

<code> </code><code>OBJECT_TYPE                                        </code><code>CHAR</code><code>(5)</code>

<code> </code><code>COLUMN_NAME                                        VARCHAR2(4000)</code>

<code> </code><code>COLUMN_POSITION                                    NUMBER</code>

<code>SQL&gt; col table_name </code><code>for</code> <code>a20</code>

<code>SQL&gt; col column_name </code><code>for</code> <code>a20</code>

<code>SQL&gt; col partition </code><code>for</code> <code>a20</code>

<code>SQL&gt; </code><code>select</code> <code>t.table_name, kc.column_name, t.partitioning_type</code>

<code>  </code><code>2    </code><code>from</code> <code>dba_part_key_columns kc, dba_part_tables t</code>

<code>  </code><code>3   </code><code>where</code> <code>kc.owner = t.owner</code>

<code>  </code><code>4     </code><code>and</code> <code>kc.</code><code>name</code> <code>= t.table_name</code>

<code>  </code><code>5     </code><code>and</code> <code>t.table_name=</code><code>'COSTS'</code><code>;</code>

<code>TABLE_NAME           COLUMN_NAME          PARTITION</code>

<code>-------------------- -------------------- ---------</code>

<code>COSTS                TIME_ID              RANGE</code>

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

<code>  </code><code>5     </code><code>and</code> <code>t.table_name=</code><code>'PRODUCTS'</code>

<code>  </code><code>6   </code><code>union</code> <code>all</code>

<code>  </code><code>7  </code><code>select</code> <code>u.table_name,skc.column_name,u.subpartitioning_type</code>

<code>  </code><code>8    </code><code>from</code> <code>dba_subpart_key_columns skc,dba_part_tables u</code>

<code>  </code><code>9   </code><code>where</code> <code>skc.owner=u.owner</code>

<code> </code><code>10   </code><code>and</code> <code>skc.</code><code>name</code><code>=u.table_name</code>

<code> </code><code>11   </code><code>and</code> <code>u.subpartitioning_type!=</code><code>'NONE'</code>

<code> </code><code>12   </code><code>and</code> <code>u.table_name=</code><code>'PRODUCTS'</code><code>;</code>

<code>PRODUCTS             T1                   RANGE</code>

<code>PRODUCTS             T2                   HASH</code>

<code>Script:</code>

<code>select</code> <code>t.table_name, kc.column_name, t.partitioning_type</code>

<code>  </code><code>from</code> <code>dba_part_key_columns kc, dba_part_tables t</code>

<code> </code><code>where</code> <code>kc.owner = t.owner</code>

<code>   </code><code>and</code> <code>kc.</code><code>name</code> <code>= t.table_name</code>

<code>   </code><code>and</code> <code>t.table_name = </code><code>'&amp;TABNAME'</code>

<code>   </code><code>and</code> <code>t.owner = </code><code>'&amp;OWNAME'</code>

<code>union</code> <code>all</code>

<code>select</code> <code>u.table_name, skc.column_name, u.subpartitioning_type</code>

<code>  </code><code>from</code> <code>dba_subpart_key_columns skc, dba_part_tables u</code>

<code> </code><code>where</code> <code>skc.owner = u.owner</code>

<code>   </code><code>and</code> <code>skc.</code><code>name</code> <code>= u.table_name</code>

<code>   </code><code>and</code> <code>u.subpartitioning_type != </code><code>'NONE'</code>

<code>   </code><code>and</code> <code>u.table_name = </code><code>'&amp;TABNAME'</code>

<code>   </code><code>and</code> <code>u.owner = </code><code>'&amp;OWNAME'</code><code>;</code>

本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1277567