0、线上表的创建表结构
create table a (bbri date,id number ,name vARchar2(20),qxbm number)
partITion by list (qxbm )(partition list_1 values (1),
partition list_2 values (2),
partition list_3 values (3),
partition list_4 values (4),
partition list_5 values (5)
)
1、创建临时表,临时表跟源表结构一致,但分区列进行更改、分区类型也进行更改
create table tmp (bbri date,id number ,name varchar2(20),qxbm number)
partition by range (bbri )
(partition p1 values less than (to_date('2020-06-01', 'yyyy-mm-dd')),
partition p2 values less than (to_date('2020-07-01', 'yyyy-mm-dd')),
partition p3 values less than (to_date('2020-08-01', 'yyyy-mm-dd')),
partition p4 values less than (to_date('2020-09-01', 'yyyy-mm-dd')),
partition p5 values less than (to_date('2020-10-01', 'yyyy-mm-dd'))
)
3、检查重定义的合理性;如果不能重定义,会显示具体的原因。
exec dbms_redefinition.can_redef_table('TEST', 'A');
ORA-12089: cannot online redefine table "TEST"."A" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 143
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1627
ORA-06512: at line 2
需要添加主键
ALTER TABLE A ADD CONSTRAINTS PK_A_id PRIMARY KEY (id) online parallel 8 ;
exec dbms_redefinition.can_redef_table('TEST', 'A');
4、重定义表结构(将在线的表刷新临时表中)
BEGIN
DBMS_REDEFINITION.start_redef_table(
uname => 'TEST',
orig_table => 'A',
int_table => 'tmp');
END;
如果中途出现意外,使用如下语句回滚:
execute dbms_redefinition.abort_redef_table('TEST','A','tmp');
5、同步临时表 (同步新增的数据)
BEGIN
dbms_redefinition.sync_interim_table(
uname => 'TEST',
orig_table => 'A',
int_table => 'tmp');
END;
6、创建新表的索引(在线重定义只定义数据,不建立索引)
用下面的SQL获取创建索引的语句,然后创建到临时表上面
select index_name from dba_indexes where owner='TEST' and table_name='A';
select dbms_metadata.get_ddl('INDEX','IDX_CON_CONTENT_H1') from dual;
select dbms_metadata.get_ddl('INDEX','PK_CON_CONTENT_H_CELL_ID1') from dual;
select dbms_metadata.get_ddl('INDEX','PK_CON_CONTENT_H_HISTORY') from dual;
7、收集临时表的统计信息
exec dbms_stats.gather_table_stats('TEST', 'tmp', cascade => true);
8、结束重定义
BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE(uname => 'TEST',
orig_table => 'A',
int_table => 'tmp'
);
END;
9、查看表的索引和约束,是否与之前一致
select * from user_tab_partitions where table_name='A';
select * from user_part_tables where table_name='A';
select * from user_PART_KEY_COLUMNS where name=upPEr('a')
10、编译失效的对象
SELECT 'ALTER ' || (CASE
WHEN OBJECT_TYPE = 'PACKAGE BODY' THEN
'PACKAGE'
ELSE
OBJECT_TYPE
END) || ' ' || owner || '.' || OBJECT_NAME || ' COMPILE ' || (CASE
WHEN OBJECT_TYPE = 'PACKAGE BODY' THEN
'BODY;'
ELSE
';'
END),
owner,
OBJECT_NAME,
OBJECT_TYPE,
STATUS,
O.CREATED,
LAST_DDL_TIME
FROM dba_OBJECTS O
WHERE STATUS = 'INVALID';
11、删除临时表
truncate table tmp;--因为是大表,建议使用该方式
drop table tmp; --删除临时表的定义
相关推荐