Oralce分区表查询

1、创建表空间

create tablespace user_data  
logging  
datafile 'D:\oracle\user_data.dbf' 
size 50m  
autoextend on  
next 50m maxsize 20480m  
extent management local; 

2、创建分区表

create table sbyangyingying(id varchar2(1000)) partition by range(id)
(
partition part_01 values less than(10) tablespace user_data,
partition part_02 values less than(100) tablespace user_data,
partition part_03 values less than(maxvalue) tablespace user_data
);
insert into sbyangyingying values(1);
insert into sbyangyingying values(15);
insert into sbyangyingying values(150);
commit;

3、查询分区表信息

SELECT p.table_name AS 表名, decode(p.partitioning_key_count, 1, '主分区') AS 分区类型,
p.partitioning_type AS 分区类型, p.column_name AS 分区键,
decode(nvl(q.subpartitioning_key_count, 0), 0, '无子分区', 1, '子分区') AS 有无子分区,
q.subpartitioning_type AS 子分区类型, q.column_name AS 子分区键
FROM (SELECT a.table_name, a.partitioning_type, b.column_name, a.partitioning_key_count
FROM user_part_tables a, user_part_key_columns b
WHERE a.table_name = b.NAME
AND b.object_type = 'TABLE') p,
(SELECT a.table_name, a.subpartitioning_type, b.column_name, a.subpartitioning_key_count
FROM user_part_tables a, user_subpart_key_columns b
WHERE a.table_name = b.NAME
AND a.subpartitioning_key_count <> 0
AND b.object_type = 'TABLE') q
WHERE p.table_name = q.table_name(+)
ORDER BY 5,4,1;

4、查询分区表的分区信息

select * from user_tab_partitions
where table_name='SBYANGYINGYING'