CNDBA社区
1.查看该表是否可以做在线重定义
SQL> exec dbms_redefinition.can_redef_table('HBHE', 'FUNCTION_LOG');
PL/SQL procedure successfully completed.
2.创建分区结构的中间分区表
-- Create table
create table HBHE.FUNCTION_LOG_1
(
id NVARCHAR2(36) not null,
controller NVARCHAR2(100) not null,
action NVARCHAR2(100) not null,
function_name NVARCHAR2(150),
parameters NVARCHAR2(2000),
userinfo NVARCHAR2(2000),
username NVARCHAR2(20) not null,
logtime DATE not null
) tablespace USERS
partition by range(LOGTIME)
INTERVAL (numtoyminterval(1, 'month'))
(
partition P202209 values less than (to_date('2022-09-01','yyyy-mm-dd')),
partition P202210 values less than (to_date('2022-10-01','yyyy-mm-dd')),
partition P202211 values less than (to_date('2022-11-01','yyyy-mm-dd')),
partition P202212 values less than (to_date('2022-12-01','yyyy-mm-dd')),
partition P202301 values less than (to_date('2023-01-01','yyyy-mm-dd')),
partition P202302 values less than (to_date('2023-02-01','yyyy-mm-dd')),
partition P202303 values less than (to_date('2023-03-01','yyyy-mm-dd')),
partition P202304 values less than (to_date('2023-04-01','yyyy-mm-dd')),
partition P202305 values less than (to_date('2023-05-01','yyyy-mm-dd'))
);
-- Create/Recreate primary, unique and foreign key constraints
alter table HBHE.FUNCTION_LOG_1 add primary key (ID) using index tablespace USERS;
3.创建索引
-- Create/Recreate indexes
create index HBHE.IX_FUNCTION_LOG_1 on HBHE.FUNCTION_LOG_1(YW,CREATED_AT) local tablespace USERS online parallel 4;
create unique index HBHE.FUNCTION_LOG_1_PK on HBHE.FUNCTION_LOG_1(ID) local tablespace USERS online parallel 4;
-- Create/Recreate primary, unique and foreign key constraints
alter table HBHE.FUNCTION_LOG_1 add constraint FUNCTION_LOG_1_PK primary key (ID) using index HBHE.FUNCTION_LOG_1_PK;
4.删除索引
drop index HBHE.IX_MES_STEP_MOVE_RAW_PROCESS_TIME_YW_ttss;
alter table HBHE.FUNCTION_LOG_1 drop constraint FUNCTION_LOG_1_PK;
drop index HBHE.FUNCTION_LOG_1_PK;
5:操作前检查
5.1、确认源表赋权情况
select 'grant ' || privilege || ' on ' || owner || '.' || table_name || ' to ' || grantee || ';' grant_sql from dba_tab_privs where table_name = 'FUNCTION_LOG';
5.2、确认源表创建同义词情况
select * from dba_synonyms where table_owner='HBHE' and table_name = 'FUNCTION_LOG';
5.3、检查源表comment
select * from user_tab_comments where table_name='FUNCTION_LOG'
select * from dba_col_comments where owner='EABC' and table_name='FUNCTION_LOG'
5.4、确认操作前对象状态
select count(*),object_type,status from dba_objects group by object_type,status;
5.5、检查表是否存在外键
select owner,constraint_name,constraint_type from dba_constraints where owner='GQHB' AND table_name='FUNCTION_LOG'
5.6、检查表的唯一索引是否是其他表的外键
select * from dba_constraints where R_CONSTRAINT_NAME='MES_STEP_MOVE_RAW_PROCESS_TIME_PK_1';
select a.constraint_type,a.table_name,a.status, b.table_name,b.column_name,b.constraint_name from dba_constraints a
inner join dba_cons_columns b
on a.constraint_name = b.constraint_name
where a.r_constraint_name='MES_STEP_MOVE_RAW_PROCESS_TIME_PK_1'
检查中间表是否开启行迁移
SQL> select row_movement from dba_tables where table_name='FUNCTION_LOG' and owner='HBHE';
ROW_MOVE
--------
DISABLED
SQL> select row_movement from dba_tables where table_name='FUNCTION_LOG_1' and owner='HBHE';
ROW_MOVE
--------
DISABLED
6.收集统计信息
SQL> exec dbms_stats.gather_table_stats(ownname => 'HBHE',tabname => 'FUNCTION_LOG',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE,degree => '8') ;
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(ownname => 'HBHE',tabname => 'FUNCTION_LOG_1',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE,degree => '8') ;
PL/SQL procedure successfully completed.
7.开始在线重定义
SQL> EXEC DBMS_REDEFINITION.START_REDEF_TABLE('HBHE','FUNCTION_LOG','FUNCTION_LOG_1');
PL/SQL procedure successfully completed.
8.复制原始表的依赖对象
SET SERVEROUTPUT ON
DECLARE
l_errors NUMBER;
BEGIN
DBMS_REDEFINITION.copy_table_dependents(
uname => 'HBHE',
orig_table => 'FUNCTION_LOG',
int_table => 'FUNCTION_LOG_1',
copy_indexes => 0,
copy_triggers => TRUE,
copy_constraints => FALSE,
copy_privileges => TRUE,
ignore_errors => FALSE,
num_errors => l_errors,
copy_statistics => FALSE,
copy_mvlog => FALSE);
DBMS_OUTPUT.put_line('Errors=' || l_errors);
END;
/
9.查看是否有错误
SQL> select * from DBA_REDEFINITION_ERRORS;
no rows selected
10.中间表创建索引
alter table HBHE.FUNCTION_LOG_1 add primary key (ID) using index tablespace USERS;
11.取消索引并行度
SQL> select 'alter index '||owner||'.'||index_name||' noparallel;' from dba_indexes where table_name = 'FUNCTION_LOG' and owner= 'HBHE';
'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'NOPARALLEL;'
--------------------------------------------------------------------------------
alter index HBHE.SYS_C0014293 noparallel;
SQL> alter index HBHE.SYS_C0014293 noparallel;
Index altered.
12.同步数据（可以减少结束重定义过程的锁表时间）,这一步操作是为了在结束重定义的时候，减少锁表的时间。
sqlplus / as sysdba
BEGIN
dbms_redefinition.sync_interim_table(
uname => 'HBHE',
orig_table => 'FUNCTION_LOG',
int_table => 'FUNCTION_LOG_1');
END;
/
PL/SQL procedure successfully completed.
13.收集中间表统计信息
SQL> exec dbms_stats.gather_table_stats(ownname => 'HBHE',tabname => 'FUNCTION_LOG',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE,degree => '8') ;
PL/SQL procedure successfully completed.
14.结束重定义
BEGIN
dbms_redefinition.finish_redef_table(
uname => 'HBHE',
orig_table => 'FUNCTION_LOG',
int_table => 'FUNCTION_LOG_1');
END;
/
15.如果第9步中,DBA_REDEFINITION_ERRORS中有报错，需要执行如下abort命令
SQL> exec dbms_redefinition.abort_redef_table(uname => 'HBHE',orig_table => 'FUNCTION_LOG',int_table => 'FUNCTION_LOG_1');
PL/SQL procedure successfully completed.
16.查看是否有无效索引
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';
17.检查切换后是否开启row_movement
SQL> select owner,table_name,row_movement from dba_tables where table_name in ('FUNCTION_LOG','FUNCTION_LOG_1') and owner='HBHE';
18.检查无效对象
SQL> @?/rdbms/admin/utlrp.sql
SQL> select 'alter '||object_type||' '||owner||'.'||object_name||' compile;' from dba_objects t where t.status = 'INVALID' order by 1;
19.收集统计信息
SQL> exec dbms_stats.gather_table_stats(ownname => 'HBHE',tabname => 'FUNCTION_LOG',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE,degree => '8') ;
PL/SQL procedure successfully completed.
20.删除中间表
版权声明：本文为博主原创文章，未经博主允许不得转载。
oracle
