Oracle数据分区管理--把非分区表改为分区表适用于历史表，以及间隔分区转换V1

--说明：把非分区表改为分区表适用于历史表
---1、创建分区表（结构和非分区表TBL_STOCK_BALANCE_LOG相同）
CREATE TABLE TBL_STOCK_BALANCE_LOG_PART1
(
  ACCOUNT_ID       VARCHAR2(20 BYTE),
  OCCUR_DATE       DATE,
  BRANCH_ID        NUMBER(10),
  MONEY_TYPE       VARCHAR2(10 BYTE),
  MONEY_TYPE_NAME  VARCHAR2(20 BYTE),
  MARKET_CODE      VARCHAR2(10 BYTE),
  MARKET_NAME      VARCHAR2(30 BYTE),
  STOCK_CODE       VARCHAR2(10 BYTE),
  STOCK_NAME       VARCHAR2(60 BYTE),
  STOCK_TYPE_CODE  VARCHAR2(40 BYTE),
  STOCK_TYPE_NAME  VARCHAR2(60 BYTE),
 QTY             NUMBER(18),
  STOCKVALUE       NUMBER(18,4),
  INDUST_NUM       VARCHAR2(20 BYTE),
  CLOSE_PRICE      NUMBER(18,4),
  ASSET_ACCT       NUMBER(18,4),
  REFER_COST       NUMBER(18,4),
  REFER_BREAK      NUMBER(18,4),
  REFER_BREAK_RA   NUMBER(18,4),
  INDUST_ID        VARCHAR2(20 BYTE),
  DEPTID          VARCHAR2(10 BYTE)
)
Partition by range (OCCUR_DATE)   
(
  partition p_other values less than (maxvalue)
 );

---2、交换数据（数据从非分区表到分区表）
alter table TBL_STOCK_BALANCE_LOG_PART1 exchange partition p_other with table TBL_STOCK_BALANCE_LOG;
---SELECT * FROM TBL_STOCK_BALANCE_LOG_PART1;
---3、查询分区表数据
select to_char(OCCUR_DATE, 'YYYY-MM'), count(*) fromTBL_STOCK_BALANCE_LOG_PART1 group by to_char(OCCUR_DATE, 'YYYY-MM');

---辅助脚本
select
'alter table TBL_STOCK_BALANCE_LOG_PART1 split partition p_other at(to_date('''||to_char(OCCUR_DATE,'yyyy-mm')||''',''yyyy-mm'')) into (partitionp'||to_char(OCCUR_DATE,'yyyymm')|| ',partition p_other);'
from TBL_STOCK_BALANCE_LOG_PART1
group by to_char(OCCUR_DATE,'yyyy-mm'),to_char(OCCUR_DATE,'yyyymm')
order by to_char(OCCUR_DATE,'yyyymm')

---执行脚本生成语句
alter table TBL_STOCK_BALANCE_LOG_PART1 split partition p_other at(to_date('2011-08','yyyy-mm'))
into (partition p201108,partition p_other);
alter table TBL_STOCK_BALANCE_LOG_PART1 split partition p_other at(to_date('2011-09','yyyy-mm'))
into (partition p201109,partition p_other);

---4、删除非分区表
DROP TABLE TBL_STOCK_BALANCE_LOG PURGE;
---5、将分区表重命名为原分区表名
ALTER TABLE TBL_STOCK_BALANCE_LOG_PART1 RENAME TO TBL_STOCK_BALANCE_LOG;
---6、测试
select table_name, partition_name from user_tab_partitions where table_name ='TBL_STOCK_BALANCE_LOG';
select COUNT(*) from TBL_STOCK_BALANCE_LOG partition(P201109);

--创建普通范围分区表
CREATE TABLE clg_range_table2
(req_num NUMBER NOT NULL,
 req_date DATE NOT NULL,
 status     VARCHAR2(2) not NULL
)
PARTITION BY RANGE(req_date)
(PARTITION p1 VALUES LESS THAN(to_date('20140101','yyyymmdd')) TABLESPACE p1,
PARTITION p2 VALUES LESS THAN (to_date('20140201','yyyymmdd')) TABLESPACE p2,
PARTITION p3 VALUES LESS THAN (to_date('20140301','yyyymmdd')) TABLESPACE p3,
PARTITION p4 VALUES LESS THAN (MAXVALUE) TABLESPACE p4
)

--添加表空间
create tablespace p1 datafile 'D:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\PORD\p1.dbf' size 10M;
create tablespace p2 datafile 'D:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\PORD\p2.dbf' size 10M;
create tablespace p3 datafile 'D:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\PORD\p3.dbf' size 10M;
create tablespace p4 datafile 'D:\APP\ADMINISTRATOR\VIRTUAL\ORADATA\PORD\p4.dbf' size 10M;

--将分区表转化为间隔分区
alter table CLG_RANGE_TABLE2 drop partition p4;
alter table CLG_RANGE_TABLE2 set interval(numtodsinterval(1,'DAY')) 
alter table CLG_RANGE_TABLE2 set STORE IN (P1,P2,P3,P4)

--测试

INSERT INTO clg_range_table2 VALUES(20170101,SYSDATE-1,'Y');
INSERT INTO clg_range_table2 VALUES(20170101,SYSDATE-2,'Y');
INSERT INTO clg_range_table2 VALUES(20170101,SYSDATE,'Y');
COMMIT;
--查询
SELECT TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME=UPPER('clg_range_table2');
--禁用interval
alter table CLG_RANGE_TABLE2 set interval();
alter table CLG_RANGE_TABLE2 add partition pmax values LESS THAN (MAXVALUE) TABLESPACE p4;