Oracle组合分区中如何查询子分区信息？？？
现有一范围列表组合分区如下：
我想查询其中列表子分区的信息SQL该如何写?
SQL> select * from popcount partition (p9_subp1);
select * from popcount partition (p9_subp1)
*
ERROR 位于第 1 行:
ORA-02149: 指定的分区不存在
----------------------------------------
create table popcount
(
id number(3),
name varchar2(20),
sex varchar2(4),
age number(3)
)
partition by range (age)
subpartition by list (sex)
(
partition p1 values less than (10)
(
subpartition p1_subp1 values( 'boy '),
subpartition p1_subp2 values( 'girl ')
),
partition p2 values less than (20)
(
subpartition p2_subp1 values( 'boy '),
subpartition p2_subp2 values( 'girl ')
),
partition p3 values less than (30)
(
subpartition p3_subp1 values( 'boy '),
subpartition p3_subp2 values( 'girl ')
),
partition p4 values less than (40)
(
subpartition p4_subp1 values( 'boy '),
subpartition p4_subp2 values( 'girl ')
),
partition p5 values less than (50)
(
subpartition p5_subp1 values( 'boy '),
subpartition p5_subp2 values( 'girl ')
),
partition p6 values less than (60)
(
subpartition p6_subp1 values( 'boy '),
subpartition p6_subp2 values( 'girl ')
),
partition p7 values less than (70)
(
subpartition p7_subp1 values( 'boy '),
subpartition p7_subp2 values( 'girl ')
),
partition p8 values less than (80)
(
subpartition p8_subp1 values( 'boy '),
subpartition p8_subp2 values( 'girl ')
),
partition p9 values less than (90)
(
subpartition p9_subp1 values( 'boy '),
subpartition p9_subp2 values( 'girl ')
),
partition p10 values less than (100)
(
subpartition p10_subp1 values( 'boy '),
subpartition p10_subp2 values( 'girl ')
)
);
insert into popcount values(1, 'aaa ', 'boy ',23);
insert into popcount values(2, 'bbb ', 'girl ',45);
insert into popcount values(3, 'ccc ', 'boy ',87);
insert into popcount values(4, 'ddd ', 'boy ',56);
insert into popcount values(5, 'eee ', 'girl ',83);
insert into popcount values(6, 'fff ', 'boy ',35);
insert into popcount values(7, 'ggg ', 'boy ',90);
insert into popcount values(8, 'hhh ', 'boy ',76);
insert into popcount values(9, 'iii ', 'girl ',16);
insert into popcount values(10, 'jjj ', 'boy ',36);
insert into popcount values(11, 'kkk ', 'girl ',88);
select * from popcount;
ID NAME SEX AGE
---------- -------------------- ---- ----------
9 iii girl 16
1 aaa boy 23
6 fff boy 35
10 jjj boy 36
2 bbb girl 45
4 ddd boy 56
8 hhh boy 76
3 ccc boy 87
5 eee girl 83
11 kkk girl 88
7 ggg boy 90
已选择11行。
[解决办法]
如果是subpartition则请查询all_tab_subpartitions
