Cấu trúc lại online bảng với DBMS_REDEFINITION trong Oracle Database

Mục đích: Cấu trúc lại bảng online (thường chuyển bảng non-partition thành partition hoặc do sai loại partition phải tạo lại kiểu partition khác) với package DBMS_REDEFINITION (từ phiên bản 9i đã hỗ trợ):

Tạo bảng test

CONN test/test

CREATE TABLE redef_tab (
  id           NUMBER,
  description  VARCHAR2(50),
  CONSTRAINT redef_tab_pk PRIMARY KEY (id)
);

-- Insert du liệu
select * from dba_users;

insert into test.redef_tab  select user_id,username from dba_users;
commit;

select * from test.redef_tab;

CREATE INDEX redef_tab_desc_i ON redef_tab(description);

CREATE SEQUENCE redef_tab_seq;

CREATE OR REPLACE TRIGGER redef_tab_bir
BEFORE INSERT ON redef_tab
FOR EACH ROW
WHEN (new.id IS NULL)
BEGIN
  SELECT redef_tab_seq.NEXTVAL
  INTO   :new.id
  FROM   dual;
END;
/

Các object của bảng:

COLUMN object_name FORMAT A20
SELECT object_name, object_type, status FROM user_objects;

OBJECT_NAME          OBJECT_TYPE         STATUS
-------------------- ------------------- -------
REDEF_TAB_DESC_I     INDEX               VALID
REDEF_TAB_SEQ        SEQUENCE            VALID
REDEF_TAB_PK         INDEX               VALID
REDEF_TAB            TABLE               VALID
REDEF_TAB_BIR        TRIGGER             VALID

SQL>

Cấu trúc lại bảng gồm cả các thành phần liên quan(COPY_TABLE_DEPENDENTS)

Thực hiện Cấu trúc lại bảng:

CONN / AS SYSDBA

-- Kiểm tra bảng cần cấu trúc lại
EXEC DBMS_REDEFINITION.can_redef_table('TEST', 'REDEF_TAB');

-- Tạo bảng mới (có thể là bảng partition)
CREATE TABLE test.redef_tab2 (
  id           NUMBER,
  description  VARCHAR2(50)
);

-- Dùng parallel nếu bảng lớn
--ALTER SESSION FORCE PARALLEL DML PARALLEL 8;
--ALTER SESSION FORCE PARALLEL QUERY PARALLEL 8;

-- Bắt đầu cấu trúc lại bảng
EXEC DBMS_REDEFINITION.start_redef_table('TEST', 'REDEF_TAB', 'REDEF_TAB2');

-- Copy dependent bảng:
SET SERVEROUTPUT ON
DECLARE
  l_num_errors PLS_INTEGER;
BEGIN
  DBMS_REDEFINITION.copy_table_dependents(
    uname             => 'TEST',
    orig_table        => 'REDEF_TAB',
    int_table         => 'REDEF_TAB2',
    copy_indexes      => DBMS_REDEFINITION.cons_orig_params, -- Non-Default
    copy_triggers     => TRUE,  -- Default
    copy_constraints  => TRUE,  -- Default
    copy_privileges   => TRUE,  -- Default
    ignore_errors     => FALSE, -- Default
    num_errors        => l_num_errors); 
  DBMS_OUTPUT.put_line('l_num_errors=' || l_num_errors);
END;
/
l_num_errors=0

PL/SQL procedure successfully completed.

SQL>

-- Thông tin các object của schema, chú ý các object mới:
COLUMN object_name FORMAT A25
SELECT object_name, object_type, status FROM dba_objects WHERE owner = 'TEST';

OBJECT_NAME               OBJECT_TYPE         STATUS
------------------------- ------------------- -------
REDEF_TAB                 TABLE               VALID
REDEF_TAB_PK              INDEX               VALID
REDEF_TAB_SEQ             SEQUENCE            VALID
REDEF_TAB_DESC_I          INDEX               VALID
REDEF_TAB2                TABLE               VALID
MLOG$_REDEF_TAB           TABLE               VALID
RUPD$_REDEF_TAB           TABLE               VALID
TMP$$_REDEF_TAB_PK0       INDEX               VALID
TMP$$_REDEF_TAB_DESC_I0   INDEX               VALID
REDEF_TAB_BIR             TRIGGER             VALID
TMP$$_REDEF_TAB_BIR0      TRIGGER             VALID

SQL> 

-- Option: Đồng bộ dữ liệu sang bảng mới trước khi tạo index 

select * from test.redef_tab2;

select * from test.redef_tab;

EXEC DBMS_REDEFINITION.sync_interim_table('TEST', 'REDEF_TAB', 'REDEF_TAB2'); 

-- Kiểm tra lại dữ liệu
select * from test.redef_tab2;

select * from test.redef_tab;


-- Hoàn thành cấu trúc lại bảng
EXEC DBMS_REDEFINITION.finish_redef_table('TEST', 'REDEF_TAB', 'REDEF_TAB2');

-- Hiển thị object cảu schema, chú ý các object thay đổi
COLUMN object_name FORMAT A25
SELECT object_name, object_type, status FROM dba_objects WHERE owner = 'TEST';

OBJECT_NAME               OBJECT_TYPE         STATUS
------------------------- ------------------- -------
REDEF_TAB_SEQ             SEQUENCE            VALID
REDEF_TAB                 TABLE               VALID
REDEF_TAB2                TABLE               VALID
REDEF_TAB_DESC_I          INDEX               VALID
TMP$$_REDEF_TAB_DESC_I0   INDEX               VALID
REDEF_TAB_PK              INDEX               VALID
TMP$$_REDEF_TAB_PK0       INDEX               VALID
TMP$$_REDEF_TAB_BIR0      TRIGGER             INVALID
REDEF_TAB_BIR             TRIGGER             INVALID

SQL>

-- Bỏ bảng cũ với tên là bảng mới
DROP TABLE test.redef_tab2;

-- Hiển thị các object của schema, chú ý trạng thái trigger INVALID
COLUMN object_name FORMAT A25
SELECT object_name, object_type, status FROM dba_objects WHERE owner = 'TEST';

OBJECT_NAME               OBJECT_TYPE         STATUS
------------------------- ------------------- -------
REDEF_TAB_SEQ             SEQUENCE            VALID
REDEF_TAB                 TABLE               VALID
REDEF_TAB_DESC_I          INDEX               VALID
REDEF_TAB_PK              INDEX               VALID
REDEF_TAB_BIR             TRIGGER             INVALID

SQL>

-- Comnpile lại trigger
ALTER TRIGGER test.redef_tab_bir COMPILE;

Nếu có lỗi khi thực hiện thủ tục COPY_TABLE_DEPENDENTS, bạn nên xử lý lỗi đó và chạy lại thủ tục.

Hy vọng hữu ích với bạn.

=============================
* KHOÁ HỌC ORACLE DATABASE A-Z ENTERPRISE trực tiếp từ tôi giúp bạn bước đầu trở thành những chuyên gia DBA, đủ kinh nghiệm đi thi chứng chỉ OA/OCP, đặc biệt là rất nhiều kinh nghiệm, bí kíp thực chiến trên các hệ thống Core tại VN chỉ sau 1 khoá học.
* CÁCH ĐĂNG KÝ: Gõ (.) hoặc để lại số điện thoại hoặc inbox https://m.me/tranvanbinh.vn hoặc Hotline/Zalo 090.29.12.888
* Chi tiết tham khảo:
https://bit.ly/oaz_w
hoặc
https://bit.ly/oaz_fp
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile: 0902912888
⚡️ Skype: tranbinh48ca
👨 Facebook: https://www.facebook.com/BinhOracleMaster
👨 Inbox Messenger: https://m.me/101036604657441 (profile)
👨 Fanpage: https://www.facebook.com/tranvanbinh.vn
👨 Inbox Fanpage: https://m.me/tranvanbinh.vn
👨👩 Group FB: https://www.facebook.com/groups/DBAVietNam
👨 Website: https://www.tranvanbinh.vn
👨 Blogger: https://tranvanbinhmaster.blogspot.com
🎬 Youtube: http://bit.ly/ytb_binhoraclemaster
👨 Tiktok: https://www.tiktok.com/@binhoraclemaster?lang=vi
👨 Linkin: https://www.linkedin.com/in/binhoracle
👨 Twitter: https://twitter.com/binhoracle
👨 Địa chỉ: Tòa nhà Sun Square - 21 Lê Đức Thọ - Phường Mỹ Đình 1 - Quận Nam Từ Liêm - TP.Hà Nội

=============================
học oracle database
database
Tự học Oracle
Tài liệu Oracle 12c tiếng Việt
Hướng dẫn sử dụng Oracle Database
Oracle SQL cơ bản
Oracle SQL là gì
Khóa học Oracle Hà Nội
Học chứng chỉ Oracle ở đầu
Khóa học Oracle online
khóa học pl/sql
học dba
học dba ở việt nam
khóa học dba
khóa học dba sql
tài liệu học dba oracle
Khóa học Oracle online
học oracle sql
học oracle ở đâu tphcm
học oracle bắt đầu từ đâu
học oracle ở hà nội
oracle database tutorial
oracle database 12c
oracle database là gì
oracle database 11g
oracle download
oracle database 19c
oracle dba tutorial
oracle dba roles and responsibilities