【SQL开源代码栏目提醒】:文章导读:在新的一年中,各位网友都进入紧张的学习或是工作阶段。网学会员整理了SQL开源代码-22.10.sql的相关内容供大家参考,祝大家在新的一年里工作和学习顺利!
--分区表的管理
select a.table_name,b.partition_name,a.partitioning_type,
a.subpartitioning_type,a.partition_count
from user_part_tables a
inner join user_tab_partitions b
on a.table_name=b.table_name
where a.table_name like 'SALES_INTERVAL_HASH';
--add partition
ALTER TABLE sales_interval ADD
PARTITION P4 values less than (TO_DATE('1-4-2004', 'dd-mm-yyyy'));
ALTER TABLE systab ADD PARTITION p5;
ALTER TABLE order_items ADD PARTITION Q2_2006 VALUES LESS THAN
(TO_DATE('01-07-2006','DD-MM-YYYY'));
ALTER TABLE sales_range_range ADD
PARTITION VALUES LESS THAN (TO_DATE('1-04-2000','DD-MM-YYYY'));
alter table sales add
subpartition sp4 values less than (200000);
--split partition
ALTER TABLE customers_range_list SPLIT PARTITION p3 at (4000)
into (partition p31,partition p32)
ALTER TABLE sales_interval_range SPLIT PARTITION P2 at (TO_DATE('15-05-1999','DD-MM-YYYY'))
into (partition p21,partition p22);
--merge partition
ALTER TABLE orders merge partitions q4_2005,q1_2006
into partition q5_2005;
--move partition
alter table systab move partition p4 tablespace user1;
select a.table_name,b.partition_name,a.partitioning_type,
a.subpartitioning_type,a.partition_count
from user_part_tables a
inner join user_tab_partitions b
on a.table_name=b.table_name
where a.table_name like 'SALES_INTERVAL_RANGE';
select table_name,partition_name,tablespace_name
from user_tab_partitions
where table_name like 'SALES_INTERVAL_RANGE';
select table_name,partition_name,subpartition_name,tablespace_name
from user_tab_subpartitions
where table_name like 'SALES_INTERVAL_RANGE';