当前位置：首页 > 技能相关 > ORACLE > 正文内容

Oracle 普通表在线重定义分区表

admin8个月前 (09-23)ORACLE2380 修订时间:2024-09-23 08:37:16

Oracle在线重定义分区表

DBMS_REDEFINITION.START_REDEF_TABLE

这个过程首先会创建一个快速刷新的物化视图作为临时表，然后将源表的数据加载到临时表中，并在源表上创建物化视图日志，以支持快速刷新同步数据。

DBMS_REDEFINITION.SYNC_INTERIM_TABLE

把源表中的数据同步到临时表。

DBMS_REDEFINITION.FINISH_REDEF_TABLE

这个过程的操作步骤比较多，也是做在线重定义时需要特别注意的，但其执行时间通常是非常短的：

（1）先调用一次DBMS_REDEFINITION.SYNC_INTERIM_TABLE，同步数据到临时表；

（2）锁定源表，锁定之后表数据不再允许发生变化；

（3）再调用一次DBMS_REDEFINITION.SYNC_INTERIM_TABLE，同步数据到临时表；

（4）交换源表和临时表的表名；

（5）删除物化视图和物化视图日志；

（6）释放表锁资源。

操作步骤

1、查询表空间使用情况和表空间DBF文件路径

开始在线重定义前需要先确保是否有足够的数据空间

-- 查看表空间使用情况
SELECT 
tablespace_name 表空间名,
sum(totalM) 总大小M,
sum(usedM) 已使用量M,
sum(surplusM) 剩余量M,
sum(usedM)/sum(totalM)*100 已使用率per
FROM
(
SELECT b.file_id ID,
b.tablespace_name tablespace_name ,
b.file_name file_name,
b.bytes/1024/1024 totalM,
(b.bytes-sum(nvl(a.bytes,0)))/1024/1024 usedM,
sum(nvl(a.bytes,0)/1024/1024) surplusM
FROM dba_free_space a,dba_data_files b
WHERE a.file_id = b.file_id
GROUP BY b.tablespace_name,b.file_name,b.file_id,b.bytes
ORDER BY b.tablespace_name
)
GROUP BY tablespace_name ;

--查询目标表表空间文件地址
SELECT T.TABLESPACE_NAME,
D.FILE_NAME,
D.AUTOEXTENSIBLE,
D.BYTES,
D.MAXBYTES,
D.STATUS
FROM DBA_TABLESPACES T, DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME = D.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME, FILE_NAME;

2、新增数据文件DBF

--表空间新增数据文件
ALTER TABLESPACE 表空间名 
ADD DATAFILE 'DBF文件路径' 
SIZE 512m AUTOEXTEND ON NEXT 10240m MAXSIZE UNLIMITED;

--约束文件最大大小
alter database datafile 'DBF文件路径' autoextend on next 200M maxsize 15240M;

3、查询目标表是否可以在线重定义

--使用rowid方式：
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('用户','原表',DBMS_REDEFINITION.CONS_USE_ROWID);
END;
--使用key方式（默认方式）:
BEGIN
DBMS_REDEFINITION.CAN_REDEF_TABLE('用户','原表',DBMS_REDEFINITION.CONS_USE_PK);
END;

注意：使用rowid方式，会产生名为M_ROW$$的unused列，可以在重定义后删除。不适合重定义会有相关弹窗提醒；

4、创建临时表（表结构需与原表一致）

--创建临时表
create table 临时表名
(
    表字段列表....
)
partition by range(分区参考字段名)
interval (numtoyminterval(3, 'MONTH'))  store in (表空间列表或单个)    -- 指定分区间隔，这里指定按三个月为间隔
(
 partition 分区名1 values less than (to_date('2014-04-01 00:00:01', 'yyyy-mm-dd hh24:mi:ss')) tablespace 表空间
);

5、开始表的在线重定义

使用rowid方式:
BEGIN
dbms_redefinition.start_redef_table('用户','原表','临时表',null,dbms_redefinition.cons_use_rowid);
END;

使用key方式:
BEGIN
dbms_redefinition.start_redef_table('用户','原表','临时表',null,dbms_redefinition.cons_use_pk);
END;

6、复制源表的属性到临时表

declare num_errors PLS_INTEGER; 
BEGIN 
   DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS('用户','原表','临时表',DBMS_REDEFINITION.CONS_ORIG_PARAMS, TRUE, TRUE, TRUE, TRUE, num_errors); 
END;

7、同步差异数据到临时表

BEGIN
dbms_redefinition.sync_interim_table('用户','原表','临时表');
END;

8、完成在线重定义（会短暂锁表）

BEGIN
DBMS_REDEFINITION.FINISH_REDEF_TABLE('用户','原表','临时表');
END;

9、收集表的统计信息，检查索引名、并行度等，检查无效对象

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname=>'用户',tabname=>'原表',estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE,cascade=>TRUE); 
END;

select degree,index_name,status from dba_indexes where table_name='原表' and owner='用户';