DBMS_REDEFINITION(在线重定义):
使用在线重定义的一些限制条件:
DBMS_REDEFINITION包:
安装测试环境可以使用博主编写的 Oracle 一键安装脚本,同时支持单机和 RAC 集群模式!
开源项目:Install Oracle Database By Scripts!
更多更详细的脚本使用方式可以订阅专栏:Oracle一键安装脚本。
创建测试表空间和用户:
sqlplus / as sysdba
create tablespace PAR;
create user par identified by par;
grant dba to par;
sqlplus par/par
create table lucifer(
id number(8) PRIMARY KEY,
name varchar2(20) not null,
par_date date)
tablespace PAR;
comment on table lucifer is 'lucifer表';
comment on column lucifer.name is '姓名';
comment on column lucifer.par_date is '分区日期';
create index id_name on lucifer(name) tablespace par;
sqlplus par/par
begin
for i in 0 .. 24 loop
insert into lucifer values
(i,
'lcuifer_' || i,
add_months(to_date('2021-1-1', 'yyyy-mm-dd'), i));
end loop;
commit;
end;
/
需提前确认表是否有主键,表空间是否足够:
sqlplus / as sysdba
##查看主键
select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P' and au.table_name = 'LUCIFER';
sqlplus / as sysdba
exec dbms_redefinition.can_redef_table('PAR', 'LUCIFER');
分区表脚本使用和获取方式可以参考文章:
Oracle 通过脚本一键生成按月分区表
通过PL/SQL包一键生成分区表结构:
sqlplus par/par
BEGIN
ctas_par(p_tab => 'lucifer',
p_part_colum => 'par_date',
p_part_nums => 24,
p_tablespace => 'par');
END;
/
创建中间分区表 lucifer_par:
create table lucifer_par
(
id NUMBER(8),
name VARCHAR2(20),
par_date DATE
)
partition BY RANGE(par_date)(
partition lucifer_P202101 values less than (TO_DATE(' 2021-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202102 values less than (TO_DATE(' 2021-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202103 values less than (TO_DATE(' 2021-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202104 values less than (TO_DATE(' 2021-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202105 values less than (TO_DATE(' 2021-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202106 values less than (TO_DATE(' 2021-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202107 values less than (TO_DATE(' 2021-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202108 values less than (TO_DATE(' 2021-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202109 values less than (TO_DATE(' 2021-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202110 values less than (TO_DATE(' 2021-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202111 values less than (TO_DATE(' 2021-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202112 values less than (TO_DATE(' 2022-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202201 values less than (TO_DATE(' 2022-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202202 values less than (TO_DATE(' 2022-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202203 values less than (TO_DATE(' 2022-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202204 values less than (TO_DATE(' 2022-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202205 values less than (TO_DATE(' 2022-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202206 values less than (TO_DATE(' 2022-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202207 values less than (TO_DATE(' 2022-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202208 values less than (TO_DATE(' 2022-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202209 values less than (TO_DATE(' 2022-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202210 values less than (TO_DATE(' 2022-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202211 values less than (TO_DATE(' 2022-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_P202212 values less than (TO_DATE(' 2023-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) tablespace par,
partition lucifer_MAX values less than (maxvalue) tablespace par)
enable row movement
tablespace par;
select row_movement from dba_tables where table_name='LUCIFER' and owner='PAR';
select row_movement from dba_tables where table_name='LUCIFER_PAR' and owner='PAR';
为了确保数据准确,开始前进行统计信息收集:
sqlplus / as sysdba
exec dbms_stats.gather_table_stats(ownname => 'PAR',tabname => 'LUCIFER',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE,degree => '8') ;
exec dbms_stats.gather_table_stats(ownname => 'PAR',tabname => 'LUCIFER_PAR',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE,degree => '8') ;
sqlplus / as sysdba
EXEC DBMS_REDEFINITION.START_REDEF_TABLE('PAR','LUCIFER','LUCIFER_PAR');
选择自动复制表属性,手动创建本地索引(local):
sqlplus par/par
SET SERVEROUTPUT ON
DECLARE
l_errors NUMBER;
BEGIN
DBMS_REDEFINITION.copy_table_dependents(
uname => USER,
orig_table => 'LUCIFER',
int_table => 'LUCIFER_PAR',
copy_indexes => 0,
copy_triggers => TRUE,
copy_constraints => TRUE,
copy_privileges => TRUE,
ignore_errors => FALSE,
num_errors => l_errors,
copy_statistics => FALSE,
copy_mvlog => FALSE);
DBMS_OUTPUT.put_line('Errors=' || l_errors);
END;
/
中间表LUCIFER_PAR创建索引:
create index ID_NAME_PAR on LUCIFER_PAR(NAME) tablespace PAR local parallel 8;
如果创建索引时,开启并行创建,则需要取消索引并行度:
sqlplus / as sysdba
select 'alter index '||owner||'.'||index_name||' noparallel;'
from dba_indexes
where table_name = 'LUCIFER_PAR' and owner= 'PAR';
sqlplus / as sysdba
BEGIN
dbms_redefinition.sync_interim_table(
uname => 'PAR',
orig_table => 'LUCIFER',
int_table => 'LUCIFER_PAR');
END;
/
为了下面同步数据做准备,收集中间表统计信息:
sqlplus / as sysdba
exec dbms_stats.gather_table_stats(ownname => 'PAR',tabname => 'LUCIFER_PAR',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE,degree => '8') ;
sqlplus / as sysdba
BEGIN
dbms_redefinition.finish_redef_table(
uname => 'PAR',
orig_table => 'LUCIFER',
int_table => 'LUCIFER_PAR');
END;
/
sqlplus par/par
select owner,table_name,partitioned from user_tables where table_name in ('LUCIFER','LUCIFER_PAR');
此时,原表名的表已经转换为中间表,需要先将原表的索引,rename到其他名字,本次是BAK,需要注意索引名称长度不能过长
sqlplus / as sysdba
ALTER index PAR.ID_NAME RENAME TO ID_NAME_BAK;
rename新分区表索引,由于新分区表的索引名称还是中间表的索引名称,所以需要手动rename:
sqlplus / as sysdba
ALTER index PAR.ID_NAME_PAR RENAME TO ID_NAME;
sqlplus / as sysdba
SELECT owner index_owner, index_name, index_type,'N/A' partition_name,status,table_name,tablespace_name,
'alter index '||owner||'.'||index_name||' rebuild;' rebuild_index
FROM dba_indexes
WHERE status = 'UNUSABLE'
UNION ALL
SELECT a.index_owner,a.index_name,b.index_type,a.partition_name,a.status,b.table_name,a.tablespace_name,
'alter index '||a.index_owner||'.'||a.index_name||' rebuild partition '||a.partition_name||' ;' rebuild_index
FROM dba_ind_partitions a, dba_indexes b
WHERE a.index_name = b.index_name
AND a.index_owner = b.owner
AND a.status = 'UNUSABLE'
UNION ALL
SELECT owner index_owner,a.index_name,b.index_type,'N/A' partition_name,a.status,b.table_name,NULL,
'alter index '||a.index_owner||'.'||a.index_name||' rebuild subpartition '||a.subpartition_name||';' rebuild_index
FROM dba_ind_subpartitions a, dba_indexes b
WHERE a.index_name = b.index_name
AND a.index_owner = b.owner
AND a.status = 'UNUSABLE';
sqlplus / as sysdba
select owner,table_name,row_movement from dba_tables where table_name in ('LUCIFER','LUCIFER_PAR') and owner='PAR';
##无效对象编译
sqlplus / as sysdba
@?/rdbms/admin/utlrp.sql
select 'alter '||object_type||' '||owner||'.'||object_name||' compile;'
from dba_objects t
where t.status = 'INVALID' order by 1;
sqlplus / as sysdba
exec dbms_stats.gather_table_stats(ownname => 'PAR',tabname => 'LUCIFER',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE,degree => '8') ;
sqlplus par/par
begin
for i in 100 .. 124 loop
insert into lucifer values
(i,
'lcuifer_' || i,
add_months(to_date('2021-5-1', 'yyyy-mm-dd'), i));
end loop;
commit;
end;
/
sqlplus par/par
SELECT COUNT(*) FROM LUCIFER;
SELECT * FROM LUCIFER PARTITION(LUCIFER_P202101);
SELECT * FROM LUCIFER PARTITION(LUCIFER_P202201);
SELECT * FROM LUCIFER PARTITION(LUCIFER_MAX);
参考MOS文档:
联系客服