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

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