自拍偷在线精品自拍偷,亚洲欧美中文日韩v在线观看不卡

Oracle 分區(qū)表之在線重定義

作者：Lucifer
數(shù)據(jù)庫(kù) Oracle
在線重定義表是從oracle9i開始提供的一個(gè)功能，能在線完成對(duì)一個(gè)表結(jié)構(gòu)或存儲(chǔ)的重定義，并且不影響當(dāng)前應(yīng)用的使用，是oracle高可用性中的一個(gè)很有用的特性。

一、介紹

「DBMS_REDEFINITION(在線重定義)：」

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

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

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

「DBMS_REDEFINITION包：」

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

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

1、構(gòu)建測(cè)試數(shù)據(jù)創(chuàng)建測(cè)試表空間和用戶：

  1. sqlplus / as sysdba 
  2. create tablespace PAR; 
  3. create user par identified by par; 
  4. grant dba to par; 

創(chuàng)建測(cè)試表：

  1. sqlplus par/par 
  2. create table lucifer( 
  3. id number(8) PRIMARY KEY, 
  4. name varchar2(20) not null, 
  5. par_date date) 
  6. tablespace PAR; 
  7. comment on table lucifer is 'lucifer表'; 
  8. comment on column lucifer.name is '姓名'; 
  9. comment on column lucifer.par_date is '分區(qū)日期'; 
  10. create index id_name on lucifer(name) tablespace par; 

 

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

  1. sqlplus par/par 
  2. begin 
  3.   for i in 0 .. 24 loop 
  4.     insert into lucifer values 
  5.       (i, 
  6.        'lcuifer_' || i, 
  7.        add_months(to_date('2021-1-1', 'yyyy-mm-dd'), i)); 
  8.   end loop; 
  9.   commit; 
  10. end; 
  11. / 

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

2、查看是否能夠重定義

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

  1. sqlplus / as sysdba 
  2. ##查看主鍵 
  3. 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)是否可以重定義，沒(méi)有主鍵用 rowid：

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

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

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

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

  1. sqlplus par/par 
  2. BEGIN 
  3.   ctas_par(p_tab        => 'lucifer', 
  4.            p_part_colum => 'par_date', 
  5.            p_part_nums  => 24, 
  6.            p_tablespace => 'par'); 
  7. END; 
  8. / 

創(chuàng)建中間分區(qū)表 lucifer_par：

  1. create table lucifer_par 
  2. ( 
  3.   id       NUMBER(8), 
  4.   name     VARCHAR2(20), 
  5.   par_date DATE 
  6. ) 
  7. partition BY RANGE(par_date)( 
  8. 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, 
  9. 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, 
  10. 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, 
  11. 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, 
  12. 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, 
  13. 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, 
  14. 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, 
  15. 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, 
  16. 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, 
  17. 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, 
  18. 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, 
  19. 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, 
  20. 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, 
  21. 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, 
  22. 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, 
  23. 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, 
  24. 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, 
  25. 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, 
  26. 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, 
  27. 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, 
  28. 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, 
  29. 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, 
  30. 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, 
  31. 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, 
  32. partition lucifer_MAX values less than (maxvalue) tablespace par) 
  33.              enable row movement 
  34.              tablespace par; 

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

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

  1. select row_movement from dba_tables where table_name='LUCIFER' and owner='PAR'; 
  2. 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ì)信息收集：

  1. sqlplus / as sysdba 
  2. exec dbms_stats.gather_table_stats(ownname => 'PAR',tabname => 'LUCIFER',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE,degree => '8') ; 
  3. 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、開始在線重定義

  1. sqlplus / as sysdba 
  2. 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)注索引是否遺漏，無(wú)需關(guān)注觸發(fā)器，權(quán)限，約束等依賴。
  • 缺點(diǎn)：需要手動(dòng)創(chuàng)建索引，并且結(jié)束后手動(dòng)rename索引。
  1. sqlplus par/par 
  2. SET SERVEROUTPUT ON 
  3. DECLARE 
  4.   l_errors  NUMBER; 
  5. BEGIN 
  6.   DBMS_REDEFINITION.copy_table_dependents( 
  7.     uname            => USER, 
  8.     orig_table       => 'LUCIFER', 
  9.     int_table        => 'LUCIFER_PAR', 
  10.     copy_indexes     => 0, 
  11.     copy_triggers    => TRUE, 
  12.     copy_constraints => TRUE, 
  13.     copy_privileges  => TRUE, 
  14.     ignore_errors    => FALSE, 
  15.     num_errors       => l_errors, 
  16.     copy_statistics  => FALSE, 
  17.     copy_mvlog       => FALSE); 
  18.      
  19.   DBMS_OUTPUT.put_line('Errors=' || l_errors); 
  20. END; 
  21. / 

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

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

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

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

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

9、取消索引并行度

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

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

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

  1. sqlplus / as sysdba 
  2. BEGIN 
  3. dbms_redefinition.sync_interim_table( 
  4. uname => 'PAR', 
  5. orig_table => 'LUCIFER', 
  6. int_table => 'LUCIFER_PAR'); 
  7. END; 
  8. / 

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

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

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

  1. sqlplus / as sysdba 
  2. 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ù)表的大小決定)

  1. sqlplus / as sysdba 
  2. BEGIN 
  3. dbms_redefinition.finish_redef_table( 
  4. uname => 'PAR', 
  5. orig_table => 'LUCIFER', 
  6. int_table => 'LUCIFER_PAR'); 
  7. END; 
  8. / 

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

  1. sqlplus par/par 
  2. 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，需要注意索引名稱長(zhǎng)度不能過(guò)長(zhǎng)

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

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

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

15、查看是否存在無(wú)效索引

  1. sqlplus / as sysdba 
  2. SELECT owner index_owner, index_name, index_type,'N/A' partition_name,status,table_name,tablespace_name, 
  3.   'alter index '||owner||'.'||index_name||' rebuild;' rebuild_index 
  4.   FROM dba_indexes 
  5. WHERE status = 'UNUSABLE' 
  6. UNION ALL 
  7. SELECT a.index_owner,a.index_name,b.index_type,a.partition_name,a.status,b.table_name,a.tablespace_name, 
  8. 'alter index '||a.index_owner||'.'||a.index_name||' rebuild partition '||a.partition_name||' ;' rebuild_index 
  9.   FROM dba_ind_partitions a, dba_indexes b 
  10. WHERE a.index_name = b.index_name 
  11.    AND a.index_owner = b.owner 
  12.    AND a.status = 'UNUSABLE' 
  13. UNION ALL 
  14. SELECT owner index_owner,a.index_name,b.index_type,'N/A' partition_name,a.status,b.table_name,NULL, 
  15. 'alter index '||a.index_owner||'.'||a.index_name||' rebuild subpartition '||a.subpartition_name||';' rebuild_index 
  16.   FROM dba_ind_subpartitions a, dba_indexes b 
  17. WHERE a.index_name = b.index_name 
  18.    AND a.index_owner = b.owner 
  19.    AND a.status = 'UNUSABLE'; 

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

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

17、檢查無(wú)效對(duì)象

  1. ##無(wú)效對(duì)象編譯 
  2. sqlplus / as sysdba  
  3. @?/rdbms/admin/utlrp.sql 
  4.  
  5. select  'alter  '||object_type||'   '||owner||'.'||object_name||'   compile;' 
  6. from  dba_objects t 
  7. where t.status = 'INVALID' order by 1; 

 

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

  1. sqlplus / as sysdba 
  2. exec dbms_stats.gather_table_stats(ownname => 'PAR',tabname => 'LUCIFER',estimate_percent => 10,method_opt=> 'for all indexed columns',cascade=>TRUE,degree => '8') ; 

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

  1. sqlplus par/par 
  2. begin 
  3.   for i in 100 .. 124 loop 
  4.     insert into lucifer values 
  5.       (i, 
  6.        'lcuifer_' || i, 
  7.        add_months(to_date('2021-5-1', 'yyyy-mm-dd'), i)); 
  8.   end loop; 
  9.   commit; 
  10. end; 
  11. / 

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

  1. sqlplus par/par 
  2. SELECT COUNT(*) FROM  LUCIFER; 
  3. SELECT * FROM  LUCIFER PARTITION(LUCIFER_P202101); 
  4. SELECT * FROM  LUCIFER PARTITION(LUCIFER_P202201); 
  5. 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)

 

責(zé)任編輯：姜華 來(lái)源： Lucifer三思而后行
Oracle 在線重定義數(shù)據(jù)庫(kù)
相關(guān)推薦

2009-06-24 10:26:41

Oracle約束分區(qū)表

2021-01-20 08:07:52

oracle分區(qū)單表

2017-07-04 11:02:40

Oracle新列分區(qū)

2023-10-11 13:42:21

2010-04-19 14:01:22

Oracle查看分區(qū)表

2011-04-11 11:32:29

Oracle分區(qū)表磁盤IO沖突

2023-01-30 09:13:17

Oracle分區(qū)表技術(shù)

2017-08-30 16:59:54

PostgreSQL分區(qū)表

2011-08-23 10:37:15

Oracle分區(qū)表本地索引

2010-10-11 10:16:17

Mysql分區(qū)表

2010-11-22 15:06:46

MySQL分區(qū)表

2011-03-22 14:49:35

Oracle數(shù)據(jù)庫(kù)重定義表

2011-03-22 14:57:52

Oracle數(shù)據(jù)庫(kù)普通表分區(qū)表

2010-10-11 09:50:32

Mysql分區(qū)表

2021-09-07 17:54:04

OpenGauss分區(qū)表索引

2021-04-19 08:16:38

Hive數(shù)據(jù)類型大數(shù)據(jù)技術(shù)

2010-04-16 13:41:14

Oracle表分區(qū)

2009-08-17 08:35:22

Linux掛載分區(qū)表fstab

2011-08-11 15:34:26

Oracle數(shù)據(jù)庫(kù)新增字段在線重定義

2016-09-23 09:29:08

mysq分區(qū)表l互聯(lián)網(wǎng)
點(diǎn)贊
收藏

51CTO技術(shù)棧公眾號(hào)