oracle数据库表按年分区脚本实战例子

create table clearing_data_temp( ID VARCHAR2(32) not null, SUPPLIER_CODE VARCHAR2(32), PARTS_NO VARCHAR2(52), RECEIVE_PLACE VARCHAR2(112), LOCATION_CLASS VARCHAR2(112), ARRIVAL_DATE VARCHAR2(10), INVOICE_NO VARCHAR2(112), RESULT_QTY NUMBER(9) default 0, SPOT_FLAG VARCHAR2(112), UNIT VARCHAR2(112), SETTLEMENT_PRICE NUMBER(10,5) default 0, PLAN_PRICE NUMBER(10,5) default 0, VAT NUMBER(2,2) default 0.17, REMARK VARCHAR2(232), ARRIVAL_DATE_RANGE VARCHAR2(52), TIMING_MONTH VARCHAR2(6), ACCOUNT_MONTH VARCHAR2(6), PAYMENT_NO VARCHAR2(92), PAYMENT_DATE VARCHAR2(10), START_DATE CHAR(8), COUNT_FLAG_C VARCHAR2(152), PLANT VARCHAR2(152), PURCHASE_DEPARTMENT VARCHAR2(152), LACK_PRICE NUMBER(1) default 0, PAYMENT_STATUS NUMBER(1) default 0, MAIL_FLAG VARCHAR2(152), SUPPLIER_NAME VARCHAR2(152), PAYMENT_PRICE NUMBER(20,5) default 0, VAT_PRICE NUMBER(20,5) default 0, TOTAL_PAYMENTPRICE NUMBER(20,5) default 0, TOTAL_ACCEPTPRICE NUMBER(20,5) default 0, PARTS_NAME VARCHAR2(52), SUMMONS_NO VARCHAR2(52), PAYMENT_PRINTCOUNT NUMBER(5) default 0, PAYMENT_PRINTTIME VARCHAR2(20), PAYMENT_PRINTREMARK VARCHAR2(200), ACCEPT_PRINTCOUNT NUMBER(5) default 0, ACCEPT_PRINTTIME VARCHAR2(20), ACCEPT_PRINTREMARK VARCHAR2(200), PAY_SALE NUMBER(1) default 0, ACCEPT_STATUS NUMBER(1) default 0, ACCEPT_FLAG VARCHAR2(52), PLAN_CODE VARCHAR2(30), LACKPRICE NUMBER(1), DETAIL_OR_POST NUMBER(1), UPDATE_REMARK VARCHAR2(50), CLEARING_ACCEPT_ORDER_ID NVARCHAR2(200), CLEARING_ACCEPT_ORDER_KEY VARCHAR2(50), STATE VARCHAR2(50), PROCE_CLASSIFY VARCHAR2(50), ORDERNO VARCHAR2(50), INPUT_TIME VARCHAR2(25), INPUT_PERSON VARCHAR2(25), IS_ADJUST NUMBER(2) default 0, PRICE_BEFORE_ADJUST NUMBER(10,5), INVOICE_TITLE VARCHAR2(200), ADJUST_REASON VARCHAR2(200), payment_year AS (to_number(substr(ACCOUNT_MONTH,0,4))) ) PARTITION BY RANGE(payment_year) ( PARTITION part2015 VALUES LESS THAN(2015), PARTITION part2016 VALUES LESS THAN(2016), PARTITION Part2017 VALUES LESS THAN(2017), PARTITION Part2018 VALUES LESS THAN(2018), PARTITION partmax VALUES LESS THAN(MAXVALUE) ); --主键 alter table clearing_data_temp add constraint PK_ID primary key (ID); --查询临时表 select * from clearing_data_temp; --检查表是否可以在线重定义 exec dbms_redefinition.can_redef_table('payment_user170630', 'CLEARING_DATA',DBMS_REDEFINITION.CONS_USE_PK); --开始重定义 exec dbms_redefinition.start_redef_table('payment_user170630', 'CLEARING_DATA', 'CLEARING_DATA_TEMP'/*,DBMS_REDEFINITION.CONS_USE_PK*/); --复制原表上的依赖对象 var V_ERR number exec DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('payment_user170630', 'CLEARING_DATA', 'CLEARING_DATA_TEMP', NUM_ERRORS => :V_ERR); --同步临时表与原始表中的数据 exec dbms_redefinition.sync_interim_table('payment_user170630', 'CLEARING_DATA', 'CLEARING_DATA_TEMP'); --结束在线重定义 exec dbms_redefinition.finish_redef_table('payment_user170630', 'CLEARING_DATA', 'CLEARING_DATA_TEMP'); --放弃在线重定义 exec DBMS_REDEFINITION.abort_redef_table('payment_user170630', 'CLEARING_DATA', 'CLEARING_DATA_TEMP'); --添加表注释 --comment on column OXX_XXT_OWNER.ID_OXXXDX_MX.dim_code1 is 'XX代码1'; --重建含有stat_XXcle列的索引为本地分区索引 create index id_local on CLEARING_DATA(id) local; create index SUPPLIER_CODE_local on CLEARING_DATA(SUPPLIER_CODE) local; create index PAYMENT_DATE_local on CLEARING_DATA(PAYMENT_DATE) local; create index TIMING_MONTH_local on CLEARING_DATA(TIMING_MONTH) local; create index ACCOUNT_MONTH_local on CLEARING_DATA(ACCOUNT_MONTH) local; create index CLEARING_ACCEPT_ORDER_ID_local on CLEARING_DATA(CLEARING_ACCEPT_ORDER_ID) local; create index aaok_local on CLEARING_DATA(CLEARING_ACCEPT_ORDER_KEY) local; --查看索引信息 select INDEX_NAME, PARTITION_NAME from dba_ind_partitions; --重新统计表及索引信息 exec DBMS_STATS.gather_table_stats ('payment_user170630','CLEARING_DATA'); --开启行迁移 ALTER TABLE payment_user170630.CLEARING_DATA ENABLE ROW MOVEMENT; --验证 SELECT * FROM dba_tab_partitions WHERE table_owner = 'payment_user170630' AND table_name = 'CLEARING_DATA'; --------------------------------------------------warn line----------------------------------------------------------- --删除中间表 drop table CLEARING_DATA_TEMP purge; --删除分区表 DROP MATERIALIZED VIEW CLEARING_DATA_TEMP; drop table CLEARING_DATA_TEMP; --删除分区片 ALTER TABLE clearing_data DROP PARTITION PART2017; ALTER TABLE clearing_data DROP PARTITION PART2016; ALTER TABLE clearing_data DROP PARTITION PART2015; ALTER TABLE clearing_data DROP PARTITION PART2014; ALTER TABLE clearing_data DROP PARTITION PARTMAX; --查看分区的相关信息 select t.* from user_part_key_columns t where name='CLEARING_DATA'; select t.* from user_tab_partitions t where table_name='CLEARING_DATA'; --清除当前用户回收站所有的表 purge recyclebin; --------------------------------------------------warn line----------------------------------------------------------- --分区表查询 select * from clearing_data cd where cd.account_month='201705'; select * from clearing_data partition(Part2018) cd where cd.account_month='201705';