用户mis_audit下有一张audit_log的业务表,用于记录审计日志,建表初期为普通表,现在想将表扩展为分区表.
1.查看原表结构
select TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH from user_tab_columns where TABLE_NAME='AUDIT_LOG'; TABLE_NAME COLUMN_NAME DATA_TYPE DATA_LENGTH ----------------- ----------------- ----------------- ----------- AUDIT_LOG AUDIT_LOG_ID NUMBER 22 AUDIT_LOG PROCEDURE_ID NUMBER 22 AUDIT_LOG PROCEDURE_DATE DATE 7 AUDIT_LOG PROCESS_ID NUMBER 22 AUDIT_LOG JOB_ID NUMBER 22 AUDIT_LOG TASK_ID NUMBER 22 AUDIT_LOG TYPE VARCHAR2 32 AUDIT_LOG MESSAGE VARCHAR2 2000 AUDIT_LOG STATUS VARCHAR2 32 AUDIT_LOG CREATE_TIME DATE 7 AUDIT_LOG CREATED_BY VARCHAR2 64 AUDIT_LOG LOCATION VARCHA 256 12 rows selected. select count(*) from audit_log; COUNT(*) ---------- 6361345
select distinct(to_char(CREATE_TIME,'yyyy')) years from audit_log order by 1 asc; YEARS ------------ 2010 2011 2012 2013 2014 2015 2016 2017 8 rows selected.
2.创建分区表需要的表空间
create tablespace p2010 datafile '/u01/app/oracle/oradata/userdata/p2010.dbf' size 100m autoextend on; create tablespace p2011 datafile '/u01/app/oracle/oradata/userdata/p2011.dbf' size 100m autoextend on; create tablespace p2012 datafile '/u01/app/oracle/oradata/userdata/p2012.dbf' size 100m autoextend on; create tablespace p2013 datafile '/u01/app/oracle/oradata/userdata/p2013.dbf' size 100m autoextend on; create tablespace p2014 datafile '/u01/app/oracle/oradata/userdata/p2014.dbf' size 100m autoextend on; create tablespace p2015 datafile '/u01/app/oracle/oradata/userdata/p2015.dbf' size 100m autoextend on; create tablespace p2016 datafile '/u01/app/oracle/oradata/userdata/p2016.dbf' size 100m autoextend on; create tablespace p2017 datafile '/u01/app/oracle/oradata/userdata/p2017.dbf' size 100m autoextend on; create tablespace pothers datafile '/u01/app/oracle/oradata/userdata/pothers.dbf' size 100m autoextend on; alter user mis_audit quota unlimited on p2010; alter user mis_audit quota unlimited on p2011; alter user mis_audit quota unlimited on p2012; alter user mis_audit quota unlimited on p2013; alter user mis_audit quota unlimited on p2014; alter user mis_audit quota unlimited on p2015; alter user mis_audit quota unlimited on p2016; alter user mis_audit quota unlimited on p2017; alter user mis_audit quota unlimited on pothers;
3.获取建表语句,并创建分区表
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE); EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR',True); EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'PRETTY',true); select dbms_metadata.get_ddl('TABLE','AUDIT_LOG','MIS_AUDIT') from dual; DBMS_METADATA.GET_DDL('TABLE','AUDIT_LOG','MIS_AUDIT') -------------------------------------------------------------------------------- CREATE TABLE "MIS_AUDIT"."AUDIT_LOG" ( "AUDIT_LOG_ID" NUMBER(*,0) NOT NULL ENABLE, "PROCEDURE_ID" NUMBER(*,0) NOT NULL ENABLE, "PROCEDURE_DATE" DATE, "PROCESS_ID" NUMBER(*,0) NOT NULL ENABLE, "JOB_ID" NUMBER(*,0) NOT NULL ENABLE, "TASK_ID" NUMBER(*,0) NOT NULL ENABLE, "TYPE" VARCHAR2(32) NOT NULL ENABLE, "MESSAGE" VARCHAR2(2000), "STATUS" VARCHAR2(32) NOT NULL ENABLE, "CREATE_TIME" DATE NOT NULL ENABLE, "CREATED_BY" VARCHAR2(64) NOT NULL ENABLE, "LOCATION" VARCHAR2(256), CONSTRAINT "PK_AUDIT_LOG" PRIMARY KEY ("AUDIT_LOG_ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STAT ISTICS NOLOGGING TABLESPACE "TOOL" ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING TABLESPACE "TOOL" ;
4.创建分区表
CREATE TABLE "MIS_AUDIT"."AUDIT_LOG_RANGE"( "AUDIT_LOG_ID" NUMBER(*,0) NOT NULL ENABLE, "PROCEDURE_ID" NUMBER(*,0) NOT NULL ENABLE, "PROCEDURE_DATE" DATE, "PROCESS_ID" NUMBER(*,0) NOT NULL ENABLE, "JOB_ID" NUMBER(*,0) NOT NULL ENABLE, "TASK_ID" NUMBER(*,0) NOT NULL ENABLE, "TYPE" VARCHAR2(32) NOT NULL ENABLE, "MESSAGE" VARCHAR2(2000), "STATUS" VARCHAR2(32) NOT NULL ENABLE, "CREATE_TIME" DATE NOT NULL ENABLE, "CREATED_BY" VARCHAR2(64) NOT NULL ENABLE, "LOCATION" VARCHAR2(256), CONSTRAINT "AUDIT_LOG_RANGE" PRIMARY KEY ("AUDIT_LOG_ID")) PARTITION BY RANGE(CREATE_TIME)( PARTITION P2010 VALUES LESS THAN (TO_DATE('01-JAN-2011','DD-MON-YYYY')) TABLESPACE P2010, PARTITION P2011 VALUES LESS THAN (TO_DATE('01-JAN-2012','DD-MON-YYYY')) TABLESPACE P2011, PARTITION P2012 VALUES LESS THAN (TO_DATE('01-JAN-2013','DD-MON-YYYY')) TABLESPACE P2012, PARTITION P2013 VALUES LESS THAN (TO_DATE('01-JAN-2014','DD-MON-YYYY')) TABLESPACE P2013, PARTITION P2014 VALUES LESS THAN (TO_DATE('01-JAN-2015','DD-MON-YYYY')) TABLESPACE P2014, PARTITION P2015 VALUES LESS THAN (TO_DATE('01-JAN-2016','DD-MON-YYYY')) TABLESPACE P2015, PARTITION P2016 VALUES LESS THAN (TO_DATE('01-JAN-2017','DD-MON-YYYY')) TABLESPACE P2016, PARTITION P2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','DD-MON-YYYY')) TABLESPACE P2017, PARTITION POTHERS VALUES LESS THAN (MAXVALUE) TABLESPACE POTHERS );
5.将原表数据迁移至新的分区表
insert into audit_log_range select * from audit_log; 6361345 rows created.
6.查看分区表数据
select distinct(to_char(CREATE_TIME,'yyyy')) years from audit_log_range partition(p2010); YEARS ------------ 2010 select distinct(to_char(CREATE_TIME,'yyyy')) years from audit_log_range partition(p2011); YEARS ------------ 2011 select distinct(to_char(CREATE_TIME,'yyyy')) years from audit_log_range partition(p2012); YEARS ------------ 2012 select distinct(to_char(CREATE_TIME,'yyyy')) years from audit_log_range partition(p2013); YEARS ------------ 2013 select distinct(to_char(CREATE_TIME,'yyyy')) years from audit_log_range partition(p2014); YEARS ------------ 2014 select distinct(to_char(CREATE_TIME,'yyyy')) years from audit_log_range partition(p2015); YEARS ------------ 2015 select distinct(to_char(CREATE_TIME,'yyyy')) years from audit_log_range partition(p2016); YEARS ------------ 2016 select distinct(to_char(CREATE_TIME,'yyyy')) years from audit_log_range partition(p2017); YEARS ------------ 2017 select count(*) from audit_log_range partition(p2010); COUNT(*) ---------- 415 select count(*) from audit_log_range partition(p2011); COUNT(*) ---------- 1318924 select count(*) from audit_log_range partition(p2012); COUNT(*) ---------- 1458807 select count(*) from audit_log_range partition(p2013); COUNT(*) ---------- 1180616 select count(*) from audit_log_range partition(p2014); COUNT(*) ---------- 811240 select count(*) from audit_log_range partition(p2015); COUNT(*) ---------- 670474 select count(*) from audit_log_range partition(p2016); COUNT(*) ---------- 520912 select count(*) from audit_log_range partition(p2017); COUNT(*) ---------- 399957
7.插入新数据测试
select count(*) from audit_log_range partition(pothers); COUNT(*) ---------- 0 insert into audit_log_range values(11111111,11111111,to_date('12-JAN-2018','dd-mm-yyyy'),11111111,11111111,11111111,'test','test','test',to_date('12-JAN-2018','dd-mm-yyyy'),'test','test'); 1 row created. select count(*) from audit_log_range partition(pothers); COUNT(*) ---------- 1 select distinct(to_char(CREATE_TIME,'yyyy')) years from audit_log_range partition(pothers); YEARS ------------ 2018
8.查看分区信息
- user_part_tables
select TABLE_NAME,PARTITIONING_TYPE,PARTITION_COUNT,PARTITIONING_KEY_COUNT,STATUS from user_part_tables; TABLE_NAME PARTITIONING_TYPE PARTITION_COUNT PARTITIONING_KEY_COUNT STATUS --------------- --------------------------- --------------- ---------------------- ------------------------ AUDIT_LOG_RANGE RANGE 9 1 VALID
- user_tab_partitions
column TABLE_NAME format a15 column PARTITION_NAME format a15 column HIGH_VALUE format a100 column TABLESPACE_NAME format a30 select TABLE_NAME,PARTITION_NAME,HIGH_VALUE,TABLESPACE_NAME from user_tab_partitions; TABLE_NAME PARTITION_NAME HIGH_VALUE TABLESPACE_NAME --------------- --------------- ---------------------------------------------------------------------------------------------------- ------------------------------ AUDIT_LOG_RANGE P2011 TO_DATE(' 2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') P2011 AUDIT_LOG_RANGE P2010 TO_DATE(' 2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') P2010 AUDIT_LOG_RANGE P2012 TO_DATE(' 2013-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') P2012 AUDIT_LOG_RANGE P2013 TO_DATE(' 2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') P2013 AUDIT_LOG_RANGE P2014 TO_DATE(' 2015-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') P2014 AUDIT_LOG_RANGE P2015 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') P2015 AUDIT_LOG_RANGE P2016 TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') P2016 AUDIT_LOG_RANGE P2017 TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') P2017 AUDIT_LOG_RANGE POTHERS MAXVALUE POTHERS 9 rows selected.
9.分区维护
增加p2018分区存放2018年的数据
此时需要查看最后一个分区,如果有数据需要先做备份,再删除最后一个分区,如果没有数据,可以删除该分区,添加分区前先创建新的表空间,我们采用之前创建的p2018表空间
select count(*) from audit_log_range partition(pothers); COUNT(*) ---------- 1 alter table audit_log_range drop partition pothers; Table altered. alter table audit_log_range add partition P2018 values less than(TO_DATE('01-JAN-2019','DD-MON-YYYY')) TABLESPACE P2018; Table altered. alter table audit_log_range add partition pothers values less than (maxvalue) tablespace pothers; Table altered.
再次插入数据测试
select count(*) from audit_log_range partition(p2018); COUNT(*) ---------- 0 insert into audit_log_range values(11111111,11111111,to_date('12-JAN-2018','dd-mm-yyyy'),11111111,11111111,11111111,'test','test','test',to_date('12-JAN-2018','dd-mm-yyyy'),'test','test'); insert into audit_log_range values(11111111,11111111,to_date('12-JAN-2018','dd-mm-yyyy'),11111111,11111111,11111111,'test','test','test',to_date('12-JAN-2018','dd-mm-yyyy'),'test','test') * ERROR at line 1: ORA-01502: index 'MIS_AUDIT.AUDIT_LOG_RANGE' or partition of such index is in unusable state alter index MIS_AUDIT.AUDIT_LOG_RANGE rebuild; Index altered. insert into audit_log_range values(11111111,11111111,to_date('12-JAN-2018','dd-mm-yyyy'),11111111,11111111,11111111,'test','test','test',to_date('12-JAN-2018','dd-mm-yyyy'),'test','test'); 1 row created. select count(*) from audit_log_range partition(p2018); COUNT(*) ---------- 1
参考文档
http://www.blogjava.net/rabbit/archive/2013/01/08/393955.html
http://tianzt.blog.51cto.com/459544/171759