Computer >> Máy Tính >  >> Lập trình >> Cơ sở dữ liệu

Phân vùng bảng trực tuyến Oracle sử dụng gói DBMS_REDEFINITION

Bắt đầu với Oracle® 10g, bạn có thể phân vùng bảng trực tuyến mà không có bất kỳ thời gian ngừng ứng dụng nào bằng cách sử dụng gói DBMS_REDEFINITION.

Sử dụng các bước sau để thay đổi bảng không phân vùng thành bảng phân vùng bằng cách sử dụng DBMS_REDEFINITION. Ví dụ này thay đổi bảng không phân vùng, TABLEA, thành bảng phân vùng khoảng cách.

Bước 1:Sao lưu bảng bỏ phân vùng

Chạy mã sau để tạo bản sao lưu xuất đầy đủ của bảng, TABLEA. 

expdp  \"/ as sysdba\" directory=EXPDP_DIR dumpfile=tableA_UNPAR.dmp logfile=tableA_UNPAR.log TABLES=TEST.TABLEA

expdp  \"/ as sysdba\"  directory=EXPDP_DIR dumpfile=tableA_metaunpar.dmp logfile=tableA_metaunpar.log TABLES=TEST.TABLEA content=metadata_only

Bước 2:Kiểm tra các đối tượng cơ sở dữ liệu

Các đối tượng cơ sở dữ liệu (D) phụ thuộc sau đây có thể bị loại bỏ khi bảng ẩn:

  • CONSTRAINT (Ràng buộc) D

  • INDEX (Chỉ mục) D

  • MATERIALIZED_VIEW_LOG (Nhật ký chế độ xem cụ thể hóa) D

  • OBJECT_GRANT (Tài trợ đối tượng) D

  • TRIGGER (Trình kích hoạt) D

Chạy các lệnh SQL sau và lưu kết quả đầu ra trong tệp cuộn, chẳng hạn như cons_trig_indx.txt :

set LINESIZE 500
set PAGESIZE 1000
SQL> spool cons_trig_indx.txt
SQL> select name, type, owner from all_dependencies where referenced_owner = 'TEST' and referenced_name = 'TABLEA';

NAME                TYPE              OWNER
--------------      --------------    -------
PROC_TABLEA         PROCEDURE         TEST
TABLEA_TRIGG        TRIGGER           TEST
PKG_TABLEA          PACKAGE BODY      TEST


SQL> select OWNER, INDEX_NAME, TABLE_OWNER, TABLE_NAME, STATUS, TABLESPACE_NAME
from dba_indexes where TABLE_OWNER='TEST' and TABLE_NAME='TABLEA';

OWNER   INDEX_NAME       TABLE_OWNER  TABLE_NAME   STATUS   TABLESPACE_NAME
---------------------------------------------------------------------------
TEST    TABLEA_IDX_ID01    TEST        TABLEA      VALID    TABLEA_TBL
TEST    TABLEA_IDX_ID04    TEST        TABLEA      VALID    TABLEA_TBL
TEST    TABLEA_IDX_PK      TEST        TABLEA      VALID    TABLEA_TBL


SQL> select STATUS, OBJECT_TYPE, OBJECT_NAME  from dba_objects
where OWNER='TEST' and OBJECT_TYPE = 'TRIGGER' and STATUS='INVALID';

no rows selected

SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE from dba_constraints
where TABLE_NAME='TABLEA' and owner='TEST';
SQL> spool off

CONSTRAINT_NAME     C
------------------  -----
SYS_C002004601      C
SYS_C002004602      C
SYS_C002004603      C
IDX_PK              P
FK01                R

Bước 3:Chụp DDL của TABLEA

Chạy các lệnh sau để nắm bắt ngôn ngữ định nghĩa dữ liệu (DDL) củaTABLEA và lưu tập lệnh trong tệp cuộn DEF_TABLEA.sql trước khi bạn tạo bảng phân vùng:

set echo off
set feedback off
set linesize 160
set long 2000000
set pagesize 0
set trims on
column txt format a150 word_wrapped
SQL> spool DEF_TABLEA.sql
SQL> select DBMS_METADATA.GET_DDL('TABLE','TABLEA','TEST') txt FROM dual;
SQL> spool off

Bước 4:Sao chép tập lệnh DDL

Chạy lệnh sau để sao chép tập lệnh DDL mà bạn đã tạo ở bước 3. 

cp DEF_TABLEA.sql DEF_TABLEA_PAR.sql

Bước 5:Xem lại ngày trong bảng không phân vùng

Chạy lệnh sau để tìm ngày trong TABLEA:

SQL> select * from (select DT from TEST.TABLEA where rownum <15 order by DT DESC);

Bước 6:Chỉnh sửa tệp DEF_TABLEA_PAR.sql

Chỉnh sửa DEF_TABLEA_PAR.sql để thực hiện các thay đổi sau:

  • Thay đổi tất cả các lần xuất hiện của TABLEA thành TABLEA_PAR .

  • Xóa tất cả các ràng buộc, như NOT NULL hoặc bất kỳ ràng buộc nào khác.

  • Chèn lệnh sau để bảng được tạo trong một vùng bảng mới:

      TABLESPACE "TABLEA_TBL_PAR" LOGGING

  • Chèn các lệnh sau để thêm định nghĩa phân vùng dựa trên các ngày được xác định trong Bước 5:

      PARTITION BY RANGE(DT)
  interval (numtoyminterval(1,'MONTH'))
  (partition TABLEA_2004  values less than  (to_date('01/01/2005','DD/MM/YYYY')),
   partition TABLEA_2005 values less than  (to_date('01/01/2006','DD/MM/YYYY')));

DEF_TABLEA_PAR.sql tệp bây giờ sẽ giống như ví dụ sau:

CREATE TABLE "TEST"."TABLEA_PAR"
(    "ID" NUMBER(6,0),
     "CEID" NUMBER(6,0),
     "DT" DATE,
     "AMT" NUMBER(14,4),
     "RET" NUMBER(14,4),
     "CNT" NUMBER(4,0),
     "VCNT" NUMBER(4,0),
     "EXEDT" DATE,
     "LASTUPDBY" VARCHAR2(15),
     "VENUM" NUMBER(6,0),
     "LASTUPDDT" TIMESTAMP (6))
TABLESPACE "TABLEA_TBL_PAR" LOGGING
PARTITION BY RANGE(DT)
interval (numtoyminterval(1,'MONTH'))
(partition TABLEA_2004  values less than  (to_date('01/01/2005','DD/MM/YYYY')),
 partition TABLEA_2005  values less than  (to_date('01/01/2006','DD/MM/YYYY')));

Bước 7:Tạo bảng phân vùng

Tạo bảng phân vùng bằng cách chạy các bước sau để chạy DEF_TABLEA_PAR.sql tập lệnh:

SQL> spool DEF_TABLEA_PAR.outp.txt
SQL> @DEF_TABLEA_PAR.sql

Table Created.

SQL> spool off

Bước 8:Xác minh bảng phân vùng

Chạy các lệnh sau để xác minh bảng phân vùng và trả về các phân vùng đã xác định:

SQL> spool verify_partition.txt
SQL> select partition_name from DBA_tab_partitions where table_name ='TABLEA_PAR' and table_owner = 'TEST';
SQL> spool off

PARTITION_NAME
-----------------
TABLEA_2004
TABLEA_2005

Bước 9:Thu thập số liệu thống kê trên bảng không phân vùng

Chạy các lệnh sau để thu thập thống kê trên bảng không phân vùng và lưu chúng vào tệp cuộn. 

SQL> SPOOL gather_stats.txt
SQL> exec dbms_stats.gather_table_stats ('TEST', 'TABLEA',cascade => TRUE);
SQL> spool off

Bước 10:Kiểm tra tính khả thi của việc xác định lại

Lưu ý :Bảng nguồn (không phân vùng) không cần khóa chính trước khi bạn sử dụng gói định nghĩa lại.

Chạy các lệnh sau để xem liệu có thể xác định lại được không và lưu kết quả vào tệp cuộn:

SQL> spool check_the_redefinition.txt
SQL> EXEC DBMS_Redefinition.can_redef_table ('TEST', 'TABLEA');
SQL> spool off

Bước 11:Bắt đầu xác định lại

Nếu không có lỗi nào được liệt kê trong check_the_redefinition.txt , bắt đầu định nghĩa lại bằng cách sử dụng lệnh chạy dài sau:

SQL> spool start_redef_table.txt
SQL>begin
    dbms_redefinition.start_redef_table
    (
     uname => 'TEST',
     orig_table => 'TABLEA',
     int_table => 'TABLEA_PAR');
     end;
   /
SQL> spool off

Bước 12:Đề phòng lỗi vùng bảng trong quá trình xác định lại

Thao tác định nghĩa lại ở bước 11 có thể dẫn đến cảnh báo vùng bảng trong ví dụ sau:

ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-01688: unable to extend table TEST.TABLEA_PAR
partition SYS_P42 by 1024 in tablespace TABLEA_TBL
ORA-06512: at "SYS.DBMS_REDEFINITION", line 52
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1646
ORA-06512: at line 2

ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-14400: inserted partition key does not map to any partition
ORA-06512: at "SYS.DBMS_REDEFINITION", line 52
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1646
ORA-06512: at line 2

Nếu bạn thấy lỗi vùng bảng tương tự như ví dụ trước, thì bạn nên thực hiện các bước sau:

  1. Chạy lệnh sau để dừng quá trình xác định lại. 

     SQL> spool abort_redef_table.txt
 SQL> begin
      dbms_redefinition.abort_redef_table
      (
      uname => 'TEST',
      orig_table => 'TABLEA',
      int_table => 'TABLEA_PAR');
      end;
     /
 SQL> spool off

  2. Thả bảng phân vùng và chế độ xem cụ thể hóa.

  3. Tăng kích thước của vùng bảng. Trong ví dụ này, bạn nên tăng kích thước của vùng bảng TABLEA_TBL.

  4. Chạy lại bước 11.

Bước 13:Kiểm tra lỗi xác định lại

Sau khi quá trình xác định lại hoàn tất thành công, hãy chạy các lệnh sau để kiểm tra bất kỳ lỗi nào:

SQL> spool copy_table_dependents.txt
SQL> SET SERVEROUTPUT ON
     DECLARE
     l_num_errors PLS_INTEGER;
     BEGIN
       DBMS_REDEFINITION.copy_table_dependents(
           uname             => 'TEST',
           orig_table        => 'TABLEA',
           int_table         => 'TABLEA_PAR',
           copy_indexes      => DBMS_REDEFINITION.cons_orig_params, -- Non-Default
           num_errors        => l_num_errors);
           DBMS_OUTPUT.put_line('l_num_errors=' || l_num_errors);
     END;
/
SQL> spool off

Nếu xác định lại thành công, bạn sẽ thấy kết quả tương tự như sau trong copy_table_dependents.txt tệp:

l_num_errors=0
PL/SQL procedure successfully completed.

Bước 14:(Tùy chọn) Đồng bộ hóa lại bảng phân vùng

Nếu bạn muốn, hãy chạy các lệnh sau để đồng bộ hóa lại phân vùng có thể phân vùng với một tên tạm thời:

SQL> spool sync_interim_table.txt
SQL>
     BEGIN
       DBMS_REDEFINITION.sync_interim_table
       (
           uname => 'TEST',
           orig_table => 'TABLEA',
           int_table => 'TABLEA_PAR');
      END;
/
SQL> spool off

Bước 15:Thu thập số liệu thống kê trên bảng phân vùng

Chạy các lệnh sau để thu thập thống kê trên bảng phân vùng:

SQL> spool gather_statistics_par.txt
SQL> exec dbms_stats.gather_table_stats ('TEST', 'TABLEA_PAR',cascade => TRUE);
SQL> spool off

Bước 16:Tạo tập lệnh ràng buộc

Chạy các lệnh sau để chuẩn bị một tập lệnh để kích hoạt ràng buộc xác thực. 

SQL> spool constraint_enable_validate.txt
SET LINESIZE 500
SET PAGESIZE 1000

SQL> select 'alter table' ||' '||OWNER||'.'||TABLE_NAME||' enable validate constraint'||' '||CONSTRAINT_NAME||';' from dba_constraints where TABLE_NAME = 'TABLEA_PAR' and OWNER='TEST';

'ALTERTABLE'||''||OWNER||'.'||TABLE_NAME||'ENABLEVALIDATECONSTRAINT'||''||CONSTR
--------------------------------------------------------------------------------
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_SYS_C002004601;
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_SYS_C002004602;
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_SYS_C002004603;
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_IDX_PK;
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_FK01;

SQL> spool off

Bước 17:Bật ràng buộc xác thực

Chạy tập lệnh và lệnh được tạo theo bước 16, như được hiển thị trong ví dụ sau:

SQL> spool constraint_enable_execute.outp.txt
SQL>@constraint_enable.sql

alter table TEST.TABLEA_PAR enable validate constraint TMP$$_SYS_C002004601;
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_SYS_C002004602;
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_SYS_C002004603;
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_IDX_PK;
alter table TEST.TABLEA_PAR enable validate constraint TMP$$_FK01;

SQL> spool off

Bước 18:So sánh bảng không phân vùng và bảng phân vùng

So sánh bảng gốc, không phân vùng với bảng phân vùng mới để xác minh rằng tất cả các thuộc tính đều giống nhau.

Bước 19:Đổi tên bảng

Chạy các lệnh sau để đặt bảng tạm thời làm bảng thực để chuyển đổi tên bảng:

SQL> spool finish_redef_table.txt
     BEGIN
       DBMS_REDEFINITION.finish_redef_table
      (
        uname => 'TEST',
        orig_table => 'TABLEA',
        int_table => 'TABLEA_PAR');
     END;
/

--------------------------------------------
@?/rdbms/admin/utlrp.sql
--------------------------------------------

SQL>spool off

Bước 20:So sánh các bảng

Chạy các lệnh sau để so sánh số lượng bản ghi của cả hai bảng và đảm bảo chúng khớp với nhau:

SQL> spool table_count.outp.txt
SQL> select count(*) from TEST.TABLEA;

 COUNT(*)
----------
  890540

SQL> select count (*) from TEST.TABLEA_PAR;

 COUNT(*)
----------
  890540

SQL> spool off

Bước 21:Xác minh thành công phân vùng

Chạy các lệnh sau để xác minh rằng quá trình phân vùng đã thành công:

SQL> spool check_partition.txt
SQL> select partitioned from dba_tables where table_name = 'TABLEA' and owner='TEST';

PAR
------
YES

SQL> select partition_name , SUBPARTITION_COUNT, TABLESPACE_NAME from dba_tab_partitions where table_name='TABLEA' and table_owner='TEST';
SQL> select table_name, partition_name, high_value, partition_position from DBA_tab_partitions where table_name='TABLEA' and table_owner='TEST';
SQL> spool off

Bước 22:Kiểm tra lại các đối tượng cơ sở dữ liệu

Chạy các lệnh sau để kiểm tra các đối tượng cơ sở dữ liệu và so sánh kết quả với bước 2:

SET LINESIZE 500
SET PAGESIZE 1000
SQL> spool cons_indx_trigg.txt
SQL> select name, type, owner from all_dependencies where referenced_owner = 'TEST' and referenced_name = 'TABLEA';

NAME                TYPE              OWNER
----------------    ---------------   ------------
PROC_TABLEA         PROCEDURE         TEST
TABLEA_TRIGG        TRIGGER           TEST
PKG_TABLEA          PACKAGE BODY      TEST

SQL> select OWNER, INDEX_NAME, TABLE_OWNER, TABLE_NAME, STATUS, TABLESPACE_NAME from dba_indexes where TABLE_OWNER='TEST' and TABLE_NAME='TABLEA';

OWNER  INDEX_NAME       TABLE_OWNER TABLE_NAME  STATUS   TABLESPACE_NAME
------------------------------------------------------------------------
TEST   TABLEA_IDX_ID01  TEST        TABLEA      VALID    TABLEA_TBL
TEST   TABLEA_IDX_ID04  TEST        TABLEA      VALID    TABLEA_TBL
TEST   TABLEA_IDX_PK    TEST        TABLEA      VALID    TABLEA_TBL

SQL> select STATUS, OBJECT_TYPE, OBJECT_NAME  from dba_objects where OWNER='TEST' and OBJECT_TYPE = 'TRIGGER' and STATUS='INVALID';

no rows selected

SQL> select CONSTRAINT_NAME, CONSTRAINT_TYPE from dba_constraints where TABLE_NAME='TABLEA' and owner='TEST';

CONSTRAINT_NAME        C
-------------------		----------
SYS_C002004601         C
SYS_C002004602         C
SYS_C002004603         C
IDX_PK                 P
FK01                   R

12 rows selected.

SQL> spool off

Bước 23:Tạo lại các chỉ mục

Chạy các lệnh sau để xây dựng lại các chỉ mục trên không gian bảng mới:

SQL> spool rebuild_indx.txt
SQL>@rebuild_index.sql

ALTER INDEX TEST.TABLEA_IDX_ID01 REBUILD TABLESPACE TABLEA_TBL_PAR ONLINE;
ALTER INDEX TEST.ITABLEA_IDX_ID04 REBUILD TABLESPACE TABLEA_TBL_PAR ONLINE;
ALTER INDEX TEST.TABLEA_IDX_PK REBUILD TABLESPACE TABLEA_TBL_PAR ONLINE;

SQL> spool off

Bước 24:Xác thực chỉ mục

Chạy các lệnh sau để xác minh rằng trạng thái là valid và không gian bảng cho tất cả các chỉ mục là TABLEA_TBL_PAR:

SQL> spool verify_indx.outp.txt
SQL> select OWNER, INDEX_NAME, TABLE_OWNER, TABLE_NAME, STATUS, TABLESPACE_NAME from dba_indexes where TABLE_OWNER='TEST' and TABLE_NAME='TABLEA';

OWNER  INDEX_NAME       TABLE_OWNER  TABLE_NAME   STATUS   TABLESPACE_NAME
---------------------------------------------------------------------------
TEST   TABLEA_IDX_ID01  TEST         TABLEA       VALID   	 TABLEA_TBL_PAR
TEST   TABLEA_IDX_ID04  TEST         TABLEA       VALID   	 TABLEA_TBL_PAR
TEST   TABLEA_IDX_PK    TEST         TABLEA       VALID     TABLEA_TBL_PAR

SQL>spool off

Bước 25:Bỏ bảng không phân vùng ban đầu

Sau khi các DBA đã xác nhận rằng mọi thứ đều ổn, hãy thực thi lệnh sau để xóa bảng gốc, bảng hiện có tên của bảng tạm thời, TEST.TABLEA_PAR:

SQL> DROP table TEST.TABLEA_PAR cascade constraints;

Kết luận

Các bước trước đó đã sử dụng bảng tạm thời, TEST.TABLEA_PAR, để phân vùng bảng, TEST.TABLEA, thành một bảng khoảng thời gian mà không có bất kỳ thời gian ngừng ứng dụng nào.

Sử dụng tab Phản hồi để đưa ra bất kỳ nhận xét hoặc đặt câu hỏi nào.