-- 建立表的各个分区的表空间:
CREATE TABLESPACE ts_sale1999q1
DATAFILE 'E:\oracle\product\10.2.0\oradata\orcl\sales1999_q1.dat'
SIZE 100M
DEFAULT STORAGE (INITIAL 30m NEXT 30m MINEXTENTS 3 PCTINCREASE 0) CREATE TABLESPACE ts_sale1999q2
DATAFILE 'E:\oracle\product\10.2.0\oradata\orcl\sales1999_q2.dat'
SIZE 100M
DEFAULT STORAGE (INITIAL 30m NEXT 30m MINEXTENTS 3 PCTINCREASE 0)
-- STEP2、建立基于分区的表:
CREATE TABLE sales
(invoice_no NUMBER,
...
sale_date DATE NOT NULL
)
PARTITION BY RANGE (sale_date)
(PARTITION sales1999_q1
VALUES LESS THAN (TO_DATE('1999-04-01','YYYY-MM-DD') )
TABLESPACE ts_sale1999q1,
PARTITION sales1999_q2
VALUES LESS THAN (TO_DATE('1999-07-01','YYYY-MM-DD') )
TABLESPACE ts_sale1999q2
); 问题:想扩大sales1999_q2 区的范围,使得时间大于1999-04-01的数据都能插入到该分区中,如何解决?
CREATE TABLESPACE ts_sale1999q1
DATAFILE 'E:\oracle\product\10.2.0\oradata\orcl\sales1999_q1.dat'
SIZE 100M
DEFAULT STORAGE (INITIAL 30m NEXT 30m MINEXTENTS 3 PCTINCREASE 0) CREATE TABLESPACE ts_sale1999q2
DATAFILE 'E:\oracle\product\10.2.0\oradata\orcl\sales1999_q2.dat'
SIZE 100M
DEFAULT STORAGE (INITIAL 30m NEXT 30m MINEXTENTS 3 PCTINCREASE 0)
-- STEP2、建立基于分区的表:
CREATE TABLE sales
(invoice_no NUMBER,
...
sale_date DATE NOT NULL
)
PARTITION BY RANGE (sale_date)
(PARTITION sales1999_q1
VALUES LESS THAN (TO_DATE('1999-04-01','YYYY-MM-DD') )
TABLESPACE ts_sale1999q1,
PARTITION sales1999_q2
VALUES LESS THAN (TO_DATE('1999-07-01','YYYY-MM-DD') )
TABLESPACE ts_sale1999q2
); 问题:想扩大sales1999_q2 区的范围,使得时间大于1999-04-01的数据都能插入到该分区中,如何解决?
PARTITION sales1999_q2
VALUES LESS THAN (TO_DATE('1999-07-01','YYYY-MM-DD') )
TABLESPACE ts_sale1999q2
你不是写了范围少于TO_DATE('1999-07-01','YYYY-MM-DD') 只要是大于(TO_DATE('1999-04-01','YYYY-MM-DD') 都会插入到sales1999_q2
Table altered
SQL> ALTER TABLE sales MERGE PARTITIONS sales1999_q2,sales1999_q3 INTO PARTITION sales1999_q3;
Table altered
SQL>
那就加个分区表在合并两个成一个
alter table sales
add partition sales2000 values less than(to_date('2000-01-01','yyyy-mm-dd')) tablespace ts_sale1999q2alter table sales merge partitions sales1999_q2,sales2000 into sales2000