一、介紹

DBMS_REDEFINITION（在線重定義）：

支持的數(shù)據(jù)庫版本：Oracle Database - Enterprise Edition - Version 9.2.0.4 and later
在線重定義是通過 物化視圖 實(shí)現(xiàn)的。

使用在線重定義的一些限制條件：

必須有足夠的表空間來容納表的兩倍數(shù)據(jù)量。
主鍵列不能被修改。
表必須有主鍵。
必須在同一個(gè)用戶下進(jìn)行在線重定義。
SYS和SYSTEM用戶下的表無法進(jìn)行在線重定義。
在線重定義無法采用nologging。
如果中間表有新增列，則不能有NOT NULL約束

DBMS_REDEFINITION包：

ABSORT_REDEF_TABLE：清理重定義的錯(cuò)誤和中止重定義；
CAN_REDEF_TABLE：檢查表是否可以進(jìn)行重定義,存儲(chǔ)過程執(zhí)行成功代表可以進(jìn)行重定義；
COPY_TABLE_DEPENDENTS：同步索引和依賴的對象（包括索引、約束、觸發(fā)器、權(quán)限等）；
FINISH_REDEF_TABLE：完成在線重定義；
REGISTER_DEPENDENTS_OBJECTS：注冊依賴的對象，如索引、約束、觸發(fā)器等；
START_REDEF_TABLE：開始在線重定義；
SYNC_INTERIM_TABLE：增量同步數(shù)據(jù)；
UNREGISTER_DEPENDENT_OBJECT：不注冊依賴的對象，如索引、約束、觸發(fā)器等；

二、實(shí)戰(zhàn)

安裝測試環(huán)境可以使用博主編寫的 Oracle 一鍵安裝腳本，同時(shí)支持單機(jī)和 RAC 集群模式！

開源項(xiàng)目：Install Oracle Database By Scripts！

更多更詳細(xì)的腳本使用方式可以訂閱專欄：Oracle一鍵安裝腳本。

1、構(gòu)建測試數(shù)據(jù)

創(chuàng)建測試表空間和用戶：

sqlplus / as sysdba
create tablespace PAR;
create user par identified by par;
grant dba to par;

創(chuàng)建測試表：

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 '分區(qū)日期';
create index id_name on lucifer(name) tablespace par;

插入測試數(shù)據(jù)：

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;
/

可以看到，測試數(shù)據(jù)已經(jīng)構(gòu)建完成，接下來開始實(shí)戰(zhàn)操作。

2、查看是否能夠重定義

需提前確認(rèn)表是否有主鍵，表空間是否足夠：

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';

確認(rèn)是否可以重定義，沒有主鍵用 rowid：

sqlplus / as sysdba
exec dbms_redefinition.can_redef_table('PAR', 'LUCIFER');

執(zhí)行沒有報(bào)錯(cuò)代表可以進(jìn)行表的在線重定義。

3、創(chuàng)建中間表（分區(qū)表結(jié)構(gòu)）

分區(qū)表腳本使用和獲取方式可以參考文章：

Oracle 通過腳本一鍵生成按月分區(qū)表

通過PL/SQL包一鍵生成分區(qū)表結(jié)構(gòu)：

sqlplus par/par
BEGIN
  ctas_par(p_tab        => 'lucifer',
           p_part_colum => 'par_date',
           p_part_nums  => 24,
           p_tablespace => 'par');
END;
/

📢 注意： PL/SQL包可參考：Oracle普通表按月轉(zhuǎn)分區(qū)表，通過PLSQL包一鍵生成分區(qū)表

創(chuàng)建中間分區(qū)表 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;

如上，唯一索引和約束不加，會(huì)自動(dòng)復(fù)制，分區(qū)表結(jié)構(gòu)的中間表已經(jīng)生成。

4、檢查中間表是否開啟行遷移

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';

5、收集表統(tǒng)計(jì)信息

為了確保數(shù)據(jù)準(zhǔn)確，開始前進(jìn)行統(tǒng)計(jì)信息收集：

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') ;

6、開始在線重定義

sqlplus / as sysdba
EXEC DBMS_REDEFINITION.START_REDEF_TABLE('PAR','LUCIFER','LUCIFER_PAR');

7、復(fù)制表屬性，排除索引

選擇自動(dòng)復(fù)制表屬性，手動(dòng)創(chuàng)建本地索引（local）：

優(yōu)點(diǎn)：只需要關(guān)注索引是否遺漏，無需關(guān)注觸發(fā)器，權(quán)限，約束等依賴。
缺點(diǎn)：需要手動(dòng)創(chuàng)建索引，并且結(jié)束后手動(dòng)rename索引。

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;
/

執(zhí)行過程沒有任何報(bào)錯(cuò)，代表正常。

8、中間表創(chuàng)建本地索引

中間表LUCIFER_PAR創(chuàng)建索引：

create index ID_NAME_PAR on LUCIFER_PAR(NAME) tablespace PAR local parallel 8;

注意：索引名稱需要與原索引名稱不一致。

9、取消索引并行度

如果創(chuàng)建索引時(shí)，開啟并行創(chuàng)建，則需要取消索引并行度：

sqlplus / as sysdba
select 'alter index '||owner||'.'||index_name||' noparallel;'
from dba_indexes 
where table_name = 'LUCIFER_PAR' and owner= 'PAR';

10、同步數(shù)據(jù)（可以減少結(jié)束重定義過程的鎖表時(shí)間）

sqlplus / as sysdba
BEGIN
dbms_redefinition.sync_interim_table(
uname => 'PAR',
orig_table => 'LUCIFER',
int_table => 'LUCIFER_PAR');
END;
/

📢 注意： 這一步操作是為了在結(jié)束重定義的時(shí)候，減少鎖表的時(shí)間。

11、收集中間表統(tǒng)計(jì)信息

為了下面同步數(shù)據(jù)做準(zhǔn)備，收集中間表統(tǒng)計(jì)信息：

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') ;

12、結(jié)束重定義（結(jié)束重定義需要鎖表，具體時(shí)間根據(jù)表的大小決定）

sqlplus / as sysdba
BEGIN
dbms_redefinition.finish_redef_table(
uname => 'PAR',
orig_table => 'LUCIFER',
int_table => 'LUCIFER_PAR');
END;
/

13、查看分區(qū)表是否已轉(zhuǎn)換

sqlplus par/par
select owner,table_name,partitioned from user_tables where table_name in ('LUCIFER','LUCIFER_PAR');

如上，LUCIFER表已經(jīng)在線重定義為分區(qū)表結(jié)構(gòu)。

14、手動(dòng)修改重命名索引

此時(shí)，原表名的表已經(jīng)轉(zhuǎn)換為中間表，需要先將原表的索引，rename到其他名字，本次是BAK，需要注意索引名稱長度不能過長

sqlplus / as sysdba
ALTER index PAR.ID_NAME RENAME TO ID_NAME_BAK;

rename新分區(qū)表索引，由于新分區(qū)表的索引名稱還是中間表的索引名稱，所以需要手動(dòng)rename：

sqlplus / as sysdba
ALTER index PAR.ID_NAME_PAR RENAME TO ID_NAME;

15、查看是否存在無效索引

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';

16、檢查切換后是否開啟row_movement

sqlplus / as sysdba
select owner,table_name,row_movement from dba_tables where table_name in ('LUCIFER','LUCIFER_PAR') and owner='PAR';

17、檢查無效對象

##無效對象編譯
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;

18、收集統(tǒng)計(jì)信息

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') ;

19、插入測試數(shù)據(jù)

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;
/

20、查詢分區(qū)表數(shù)據(jù)分布

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);

可以發(fā)現(xiàn)，數(shù)據(jù)已經(jīng)根據(jù)日期均勻分布在不同的子分區(qū)中。至此，在線重定義已經(jīng)完成，分區(qū)表已成功轉(zhuǎn)換。

參考MOS文檔：

How To Partition Existing Table Using DBMS_REDEFINITION (Doc ID 472449.1)

本站僅提供存儲(chǔ)服務(wù)，所有內(nèi)容均由用戶發(fā)布，如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請點(diǎn)擊舉報(bào)。

免费视频淫片aa毛片_日韩高清在线亚洲专区vr_日韩大片免费观看视频播放_亚洲欧美国产精品完整版

一、介紹

二、實(shí)戰(zhàn)

1、構(gòu)建測試數(shù)據(jù)

2、查看是否能夠重定義

3、創(chuàng)建中間表（分區(qū)表結(jié)構(gòu)）

4、檢查中間表是否開啟行遷移

5、收集表統(tǒng)計(jì)信息

6、開始在線重定義

7、復(fù)制表屬性，排除索引

8、中間表創(chuàng)建本地索引

9、取消索引并行度

10、同步數(shù)據(jù)（可以減少結(jié)束重定義過程的鎖表時(shí)間）

11、收集中間表統(tǒng)計(jì)信息

12、結(jié)束重定義（結(jié)束重定義需要鎖表，具體時(shí)間根據(jù)表的大小決定）