Oracle在线重定义的方式创建表分区

--登录数据库服务器

--查看实例是不是xxxxdb

echo $ORACLE_SID

--如果不是，需要设置

export ORACLE_SID=xxxxdb

--管理员登录

sqlplus / as sysdba

--为用户授权操作 DBMS_REDEFINITION 的权限

grant execute on DBMS_REDEFINITION to user_name;

-- 为用户分配角色

grant EXECUTE_CATALOG_ROLE to user_name;

--为用户授权：CREATE ANY TABLE、ALTER ANY TABLE、DROP ANY TABLE、LOCK ANY TABLE和SELECT ANY TABLE

grant CREATE ANY TABLE to user_name;

grant ALTER ANY TABLE to user_name;

grant DROP ANY TABLE to user_name;

grant LOCK ANY TABLE to user_name;

grant SELECT ANY TABLE to user_name;

--连接到用户中心用户

conn user_name/password;

--创建操作分区的存储过程

create or replace procedure P_UC_PARTITION_CREATE(table_name in varchar2, -- 分区表

table_construction in varchar2, -- 分区表的表结构

user_name in varchar2 -- 用户名

) is

create_bak_sql varchar2(2000);

create_partition_sql varchar2(4000);

begin

dbms_output.put_line('分区表： ' || table_name || ';');

dbms_output.put_line('用户名： ' || user_name || ';');

dbms_output.put_line('分区操作开始时间： ' || to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));

--分区前进行备份

create_bak_sql:='create table '||table_name||'_0418 as select * from '||table_name;

execute immediate create_bak_sql;

--创建分区副本

create_partition_sql:='CREATE TABLE '||table_name||'_partition'||table_construction||' PARTITION BY RANGE(create_date) interval(NUMTOYMINTERVAL(1,

'||'''MONTH'''||'))(PARTITION '||table_name||'_P1 VALUES LESS THAN(TO_DATE('||'''2019-01-01'''||','||'''YYYY-MM-DD'''||')))';

execute immediate create_partition_sql;

--按create_date进行range-interval分区，需要确保 create_date 非空，否则报ora-14400：插入的数据未映射到任何分区

execute immediate 'update '||table_name||' set create_date = update_date where create_date is null';

commit;

execute immediate 'update '||table_name||' set create_date = sysdate where create_date is null';

commit;

--执行表重定义要求一定要有主键，否则会报错

dbms_redefinition.can_redef_table(user_name,table_name,dbms_redefinition.cons_use_pk);

--开始在线重定义

dbms_redefinition.start_redef_table(user_name,table_name,table_name||'_partition');

--如果在线重定义异常，需要重定义中止，否则会停留在物化视图，并且存在物化视图日志

--dbms_redefinition.abort_redef_table(user_name,table_name,table_name||'_partition');

--结束在线重定义

dbms_redefinition.finish_redef_table(user_name,table_name,table_name||'_partition');

--分区后，表索引和主键会丢失，需要重新创建

execute immediate 'alter table '||table_name||' add constraint PK'||table_name||' primary key(ID) using index';

execute immediate 'create index CUST_no'||table_name||' on '||table_name||'(CUST_NO)';

dbms_output.put_line('分区操作结束时间： ' || to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));

end P_UC_PARTITION_CREATE;

--调用存储过程进行分区

--客户信息表创建分区

call p_uc_partition_create('t_cust_info','(id NUMBER(18) not null,

cust_no VARCHAR2(32) not null,

name VARCHAR2(256),

en_name VARCHAR2(64),

idcard_type VARCHAR2(4),

idcard_no VARCHAR2(32),

sex VARCHAR2(2),

birthday VARCHAR2(16))','user_name');