分区表的分区键和分区类型都可以通过获取创建该分区表的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'
;
|
本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1277567
