Thứ Sáu, 24 tháng 2, 2023

Lệnh ALTER TABLE ... SHRINK SPACE : Online Segment Shrink cho Tables, LOBs và IOTs

Lệnh ALTER TABLE ... SHRINK SPACE được giới thiệu từ phiên bản Oracle Database 10g để thực hiện shrink (thu nhỏ) segment trực tuyến cho các bảng, LOB và segment  tràn IOT.

  • Ví dụ về shink space
  • Xác định các segment lớn
  • Row Movement
  • SecureFile LOBs
  • Nhận xét và Hạn chế

Ví dụ về shink space

Dưới đây là một số ví dụ đơn giản của lệnh ALTER TABLE ... SHRINK SPACE.

-- Cho phép row movement.
ALTER TABLE scott.emp ENABLE ROW MOVEMENT;

-- Khôi phục không gian lưu trữ và sửa đổi high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE;

-- Khôi phục không gian lưu trữ nhưng không sửa đổi the high water mark (HWM).
ALTER TABLE scott.emp SHRINK SPACE COMPACT;

-- Recover không gian lưu trữ cho đối tượng và mọi đối tượng phụ thuộc
ALTER TABLE scott.emp SHRINK SPACE CASCADE;

Tùy chọn COMPACT cho phép chia hoạt động shrink thành hai giai đoạn:

- Đầu tiên, các hàng được di chuyển bằng tùy chọn COMPACT nhưng HWM cao không được điều chỉnh nên không có câu lệnh SQL được phân tích cú pháp nào bị vô hiệu hóa. 

- HWM có thể được điều chỉnh vào một ngày sau đó bằng cách phát hành lại câu lệnh mà không có tùy chọn COMPACT. Tại thời điểm này, mọi câu lệnh SQL phụ thuộc sẽ cần được phân tích cú pháp lại.

Các lệnh shrink được hiển thị bên dưới.

-- shrink một đoạn LOB (tệp cơ bản chỉ cho đến 21c).
ALTER TABLE table_name MODIFY LOB(lob_column) (SHRINK SPACE);
ALTER TABLE table_name MODIFY LOB(lob_column) (SHRINK SPACE CASCADE);

-- shrink phân đoạn tràn IOT.
ALTER TABLE iot_name OVERFLOW SHRINK SPACE;

Có thêm chi tiết về chức năng này dưới đây.

Xác định các segment lớn

Các view DBA|ALL|USER_SEGMENTS có thể được sử dụng để xác định các segment lớn. Ví dụ sau sử dụng truy vấn top-n để hiển thị 20 phân đoạn lớn nhất.

SET LINESIZE 200
COLUMN owner FORMAT A30
COLUMN segment_name FORMAT A30
COLUMN tablespace_name FORMAT A30
COLUMN size_mb FORMAT 99999999.00

SELECT *
FROM   (SELECT owner,
               segment_name,
               segment_type,
               tablespace_name,
               ROUND(bytes/1024/1024,2) size_mb
        FROM   dba_segments
        ORDER BY 5 DESC)
WHERE  ROWNUM <= 20;

Bạn có thể thấy nhiều segment lớn hơn là phân khúc LOB. Bạn có thể nhận thêm thông tin cụ thể về các phân đoạn LOB bằng cách sử dụng truy vấn top-n sau đây.

SET LINESIZE 200
COLUMN owner FORMAT A30
COLUMN table_name FORMAT A30
COLUMN column_name FORMAT A30
COLUMN segment_name FORMAT A30
COLUMN tablespace_name FORMAT A30
COLUMN size_mb FORMAT 99999999.00

SELECT *
FROM   (SELECT l.owner,
               l.table_name,
               l.column_name,
               l.segment_name,
               l.tablespace_name,
               ROUND(s.bytes/1024/1024,2) size_mb
        FROM   dba_lobs l
               JOIN dba_segments s ON s.owner = l.owner AND s.segment_name = l.segment_name
        ORDER BY 6 DESC)
WHERE  ROWNUM <= 20;

Các tập lệnh sau đây là ví dụ về các loại truy vấn này.

  • large_segments.sql: Hiển thị size segment lớn nhất
SET LINESIZE 500 VERIFY OFF
COLUMN owner FORMAT A30
COLUMN segment_name FORMAT A30
COLUMN tablespace_name FORMAT A30
COLUMN size_mb FORMAT 99999999.00

SELECT *
FROM   (SELECT owner,
               segment_name,
               segment_type,
               tablespace_name,
               ROUND(bytes/1024/1024,2) size_mb
        FROM   dba_segments
        ORDER BY 5 DESC)
WHERE  ROWNUM <= &1;

SET VERIFY ON
  • large_lob_segments.sql: Hiển thị size segment LOB lớn nhất
SET LINESIZE 500 VERIFY OFF
COLUMN owner FORMAT A30
COLUMN table_name FORMAT A30
COLUMN column_name FORMAT A30
COLUMN segment_name FORMAT A30
COLUMN tablespace_name FORMAT A30
COLUMN size_mb FORMAT 99999999.00

SELECT *
FROM   (SELECT l.owner,
               l.table_name,
               l.column_name,
               l.segment_name,
               l.tablespace_name,
               ROUND(s.bytes/1024/1024,2) size_mb
        FROM   dba_lobs l
               JOIN dba_segments s ON s.owner = l.owner AND s.segment_name = l.segment_name
        ORDER BY 6 DESC)
WHERE  ROWNUM <= &1;

SET VERIFY ON

Row Movement

Lệnh ALTER TABLE ... SHRINK SPACE di chuyển các row (hàng) giữa các block hiện có để thu gọn dữ liệu, vì vậy trước khi cố gắng shrink 1 segment bảng, bạn cần kích hoạt row movement. Bạn có thể kiểm tra row movement đã được bật chưa bằng cách truy vấn cột ROW_MOVEMENT của view [DBA|ALL|USER]_TABLES

SELECT row_movement
FROM   user_tables
WHERE  table_name = 'EMP';

ROW_MOVE
--------
DISABLED

SQL>

Row movement được bật bằng lệnh sau.

ALTER TABLE emp ENABLE ROW MOVEMENT;

Việc lặp lại truy vấn trước đó cho thấy row movement hiện đã được bật.

SELECT row_movement
FROM   user_tables
WHERE  table_name = 'EMP';

ROW_MOVE
--------
ENABLED

SQL>

SecureFile LOBs

Khi sử dụng basicfile LOB, các lệnh shrink hoạt động như mong đợi. Để chứng minh điều này, chúng ta cần tạo bảng sau có chứa cột basicfile LOB .

DROP TABLE lob_tab PURGE;

CREATE TABLE lob_tab (
  id NUMBER,
  data CLOB
)
LOB(data) STORE AS BASICFILE (DISABLE STORAGE IN ROW);

INSERT INTO lob_tab VALUES (1, 'ONE');
COMMIT;

Chúng ta có thể thấy cả hai lệnh shrink hoàn thành mà không có lỗi.

SQL> ALTER TABLE lob_tab MODIFY LOB(data) (SHRINK SPACE);

Table altered.

SQL> ALTER TABLE lob_tab MODIFY LOB(data) (SHRINK SPACE CASCADE);

Table altered.

SQL>

Bây giờ hãy tạo lại bảng bằng cột securefile LOB.

DROP TABLE lob_tab PURGE;

CREATE TABLE lob_tab (
  id NUMBER,
  data CLOB
)
LOB(data) STORE AS SECUREFILE (DISABLE STORAGE IN ROW);

INSERT INTO lob_tab VALUES (1, 'ONE');
COMMIT;

Bây giờ lệnh đầu tiên không thành công, nhưng việc thêm tùy chọn CASCADE xuất hiện để làm cho nó hoạt động.

SQL> ALTER TABLE lob_tab MODIFY LOB(data) (SHRINK SPACE);
ALTER TABLE lob_tab MODIFY LOB(data) (SHRINK SPACE)
*
ERROR at line 1:
ORA-10635: Invalid segment or tablespace type


SQL> ALTER TABLE lob_tab MODIFY LOB(data) (SHRINK SPACE CASCADE);

Table altered.

SQL>

Thật không may, lệnh thứ hai không hoạt động và phân đoạn LOB tệp bảo mật không bị shrink.

Thay vào đó, để thu nhỏ phân đoạn securefile LOB, bạn cần di chuyển (move) nó. Trong ví dụ sau, việc di chuyển đến cùng 1 tablespace (hoặc có thể khác tablespace)

ALTER TABLE lob_tab MOVE LOB(data) STORE AS (TABLESPACE users);

Nhận xét và Hạn chế

Dưới đây là một số điều cần xem xét trước khi thực hiện các hoạt động shrink:

  • Row movement có thể gây ra sự cố với rowid 
  • Rowid materialized view phải được xây dựng lại sau một hoạt động shrink.
  • Quy trình shrink chỉ khả dụng cho các đối tượng trong tablespace có bật quản lý không gian phân đoạn tự động (automatic segment-space management).
  • Bạn không thể kết hợp mệnh đề SHRINK SPACE với bất kỳ mệnh đề ALTER TABLE nào khác.
  • Bạn không thể shrink cluster hoặc clustered table.
  • Bạn không thể shrink bất kỳ đối tượng nào bằng một cột LONG.
  • Bạn không thể shrink các bảng có chỉ mục dựa trên chức năng phụ thuộc, chỉ mục domain hoặc chỉ mục joint bitmap.
  • Bạn không thể shrink  các bảng là bảng chính của chế độ ON COMMIT materialized view
  • Các bảng ánh xạ của các bảng được tổ chức theo chỉ mục không bị ảnh hưởng bởi việc shrink.
  • Không thể sử dụng tính năng shrink  cho các bảng đã nén, ngoại trừ những bảng sử dụng nén row nâng cao (ROW STORE COMPRESS ADVANCED).
  • Thao tác thu nhỏ đối với bảng không xếp tầng vào các phân đoạn LOB. Cần phải xử lý riêng.
  • Bạn không thể shrink các segment securefile LOB .
  • Việc thay đổi cách sắp xếp các hàng trong bảng có thể có tác động tiêu cực đến hiệu suất trong một số trường hợp. Kiểm tra kỹ lưỡng trước khi đưa ra bất kỳ quyết định nào.
  • Sau bất kỳ thay đổi cấu trúc nào, chẳng hạn như di chuyển, hãy nhớ kiểm tra các chỉ mục không sử dụng được. Bạn có thể sử dụng tập lệnh unusuable_indexes.sql để tìm chúng. Nếu bạn có bất kỳ, xây dựng lại chúng.

-- Description  : Hiển thị các index unusable của mọi schema trong DB
-- -----------------------------------------------------------------------------------
SET VERIFY OFF LINESIZE 200

COLUMN owner FORMAT A30
COLUMN index_name FORMAT A30
COLUMN table_owner FORMAT A30
COLUMN table_name FORMAT A30

SELECT owner,
       index_name,
       index_type,
       table_owner,
       table_name
       table_type
FROM   dba_indexes
WHERE  owner = DECODE(UPPER('&1'), 'ALL', owner, UPPER('&1'))
AND    status NOT IN ('VALID', 'N/A')
ORDER BY owner, index_name;

Để biết thêm thông tin xem:

Hi vọng có ích cho 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
=============================
KẾT NỐI VỚI CHUYÊN GIA TRẦN VĂN BÌNH:
📧 Mail: binhoracle@gmail.com
☎️ Mobile/Zalo: 0902912888
👨 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

=============================
oracle tutorial, học oracle 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,sql tutorial, khóa học pl/sql tutorial, 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 tunning, sql tunning , oracle 12c, oracle multitenant, Container Databases (CDB), Pluggable Databases (PDB), oracle cloud, oracle security, oracle fga, audit_trail,oracle RAC, ASM, oracle dataguard, oracle goldengate, mview, oracle exadata, oracle oca, oracle ocp, oracle ocm , oracle weblogic, postgresql tutorial, mysql tutorial, mariadb tutorial, sql server tutorial, nosql, mongodb tutorial, oci, cloud, middleware tutorial, hoc solaris tutorial, hoc linux tutorial, hoc aix tutorial, unix tutorial, securecrt, xshell, mobaxterm, putty
By lúc
Labels: , ,

ĐỌC NHIỀU
Trần Văn Bình - Oracle Database Master