从视图查询表分区的相关信息-CSDN博客

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

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

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