從視圖查詢表分區的相關資訊

分區表的分區鍵和分區類型都可以通過擷取建立該分區表的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