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'