SQL> SELECT column_name, segment_name, compression FROM user_lobs WHERE table_name = ‘DOCUMENTS’;COLUMN_NAME SEGMENT_NAME COMPRESS
—————————— ————————- ——–
DOC_CONTENT SYS_LOB0000100008C00003$$ ENABLED

— 1. 创建带BLOB压缩的表
CREATE TABLE images (
image_id NUMBER,
image_name VARCHAR2(100),
image_data BLOB
) LOB(image_data) STORE AS (COMPRESS);– 2. 创建带指定压缩级别的BLOB
CREATE TABLE images_high_compress (
image_id NUMBER,
image_name VARCHAR2(100),
image_data BLOB
) LOB(image_data) STORE AS (COMPRESS HIGH);– 3. 为现有BLOB列启用压缩
ALTER TABLE images MODIFY LOB(image_data) (COMPRESS);– 4. 修改BLOB压缩级别
ALTER TABLE images MODIFY LOB(image_data) (COMPRESS MEDIUM);– 5. 查看BLOB列的压缩状态
SELECT column_name, segment_name, compression FROM user_lobs WHERE table_name = ‘IMAGES’;– 6. 监控BLOB存储空间
SELECT segment_name, bytes, blocks FROM user_segments WHERE segment_name IN (
SELECT segment_name FROM user_lobs WHERE table_name = ‘IMAGES’
);– 7. 压缩现有BLOB数据
ALTER TABLE images MOVE LOB(image_data) STORE AS (COMPRESS);– 8. 测试BLOB压缩效果
— 插入一个测试图像数据
DECLARE
l_blob BLOB;BEGIN
DBMS_LOB.CREATETEMPORARY(l_blob, TRUE);DBMS_LOB.WRITEAPPEND(l_blob, 1000, RPAD(‘X’, 1000, ‘X’));INSERT INTO images VALUES (1, ‘Test Image’, l_blob);DBMS_LOB.FREETEMPORARY(l_blob);COMMIT;END;/– 9. 查看BLOB压缩前后的大小
SELECT segment_name, bytes FROM user_segments WHERE segment_name IN (
SELECT segment_name FROM user_lobs WHERE table_name = ‘IMAGES’
);– 10. 为分区表的BLOB列启用压缩
CREATE TABLE images_partitioned (
image_id NUMBER,
image_date DATE,
image_data BLOB
) PARTITION BY RANGE (image_date) (
PARTITION p2023 VALUES LESS THAN (TO_DATE(‘2024-01-01’, ‘YYYY-MM-DD’)),
PARTITION p2024 VALUES LESS THAN (TO_DATE(‘2025-01-01’, ‘YYYY-MM-DD’))
) LOB(image_data) STORE AS (COMPRESS);

SQL> SELECT column_name, segment_name, compression FROM user_lobs WHERE table_name = ‘IMAGES’;COLUMN_NAME SEGMENT_NAME COMPRESS
—————————— ————————- ——–
IMAGE_DATA SYS_LOB0000100008C00003$$ ENABLED

— 1. 创建带NCLOB压缩的表
CREATE TABLE multilingual_documents (
doc_id NUMBER,
doc_name VARCHAR2(100),
doc_content NCLOB
) LOB(doc_content) STORE AS (COMPRESS);– 2. 创建带指定压缩级别的NCLOB
CREATE TABLE multilingual_documents_high_compress (
doc_id NUMBER,
doc_name VARCHAR2(100),
doc_content NCLOB
) LOB(doc_content) STORE AS (COMPRESS HIGH);– 3. 为现有NCLOB列启用压缩
ALTER TABLE multilingual_documents MODIFY LOB(doc_content) (COMPRESS);– 4. 修改NCLOB压缩级别
ALTER TABLE multilingual_documents MODIFY LOB(doc_content) (COMPRESS MEDIUM);– 5. 查看NCLOB列的压缩状态
SELECT column_name, segment_name, compression FROM user_lobs WHERE table_name = ‘MULTILINGUAL_DOCUMENTS’;– 6. 监控NCLOB存储空间
SELECT segment_name, bytes, blocks FROM user_segments WHERE segment_name IN (
SELECT segment_name FROM user_lobs WHERE table_name = ‘MULTILINGUAL_DOCUMENTS’
);– 7. 压缩现有NCLOB数据
ALTER TABLE multilingual_documents MOVE LOB(doc_content) STORE AS (COMPRESS);– 8. 测试NCLOB压缩效果
INSERT INTO multilingual_documents VALUES (1, ‘Test Multilingual Document’, RPAD(‘测试 ‘, 10000, ‘测试 ‘));COMMIT;– 9. 查看NCLOB压缩前后的大小
SELECT segment_name, bytes FROM user_segments WHERE segment_name IN (
SELECT segment_name FROM user_lobs WHERE table_name = ‘MULTILINGUAL_DOCUMENTS’
);– 10. 为分区表的NCLOB列启用压缩
CREATE TABLE multilingual_documents_partitioned (
doc_id NUMBER,
doc_date DATE,
doc_content NCLOB
) PARTITION BY RANGE (doc_date) (
PARTITION p2023 VALUES LESS THAN (TO_DATE(‘2024-01-01’, ‘YYYY-MM-DD’)),
PARTITION p2024 VALUES LESS THAN (TO_DATE(‘2025-01-01’, ‘YYYY-MM-DD’))
) LOB(doc_content) STORE AS (COMPRESS);

SQL> SELECT column_name, segment_name, compression FROM user_lobs WHERE table_name = ‘MULTILINGUAL_DOCUMENTS’;COLUMN_NAME SEGMENT_NAME COMPRESS
—————————— ————————- ——–
DOC_CONTENT SYS_LOB0000100008C00003$$ ENABLED

— 案例：为文档管理系统配置LOB压缩

— 1. 检查当前LOB大小
SELECT segment_name, bytes / 1024 / 1024 AS size_mb
FROM user_segments
WHERE segment_name IN (
SELECT segment_name FROM user_lobs WHERE table_name = ‘DOCUMENTS’
);– 2. 为CLOB列启用压缩
ALTER TABLE documents MODIFY LOB(doc_content) (COMPRESS HIGH);– 3. 压缩现有CLOB数据
ALTER TABLE documents MOVE LOB(doc_content) STORE AS (COMPRESS HIGH);– 4. 检查压缩后的LOB大小
SELECT segment_name, bytes / 1024 / 1024 AS size_mb
FROM user_segments
WHERE segment_name IN (
SELECT segment_name FROM user_lobs WHERE table_name = ‘DOCUMENTS’
);– 5. 测试CLOB性能
SET TIMING ON
SELECT doc_id, doc_name FROM documents WHERE doc_content LIKE ‘%important%’;SET TIMING OFF

— 6. 测试DML操作性能
SET TIMING ON
UPDATE documents SET doc_content = doc_content || ‘ Updated content’ WHERE doc_id = 1;INSERT INTO documents VALUES (1001, ‘New Document’, ‘This is a new document with compressed content’);COMMIT;SET TIMING OFF

— 7. 监控LOB访问情况
SELECT table_name, logical_reads, physical_reads, row_accesses
FROM v$segment_statistics
WHERE owner = ‘APP’ AND object_name = ‘DOCUMENTS’;– 8. 备份压缩LOB
BACKUP TABLE app.documents TO ‘/backup/documents.dmp’;– 9. 验证备份大小
SELECT BYTES / 1024 / 1024 AS backup_size_mb
FROM dba_data_files
WHERE tablespace_name = ‘LOB_DATA’;– 10. 总结压缩效果
— 比较压缩前后的LOB大小、查询性能和备份大小

— 1. 创建测试表
CREATE TABLE test_lob_compression (
id NUMBER,
name VARCHAR2(100),
clob_data CLOB,
blob_data BLOB
);– 2. 插入测试数据
DECLARE
l_clob CLOB;l_blob BLOB;BEGIN
FOR i IN 1..1000 LOOP
DBMS_LOB.CREATETEMPORARY(l_clob, TRUE);DBMS_LOB.WRITEAPPEND(l_clob, 10000, RPAD(‘X’, 10000, ‘X’));DBMS_LOB.CREATETEMPORARY(l_blob, TRUE);DBMS_LOB.WRITEAPPEND(l_blob, 10000, RPAD(‘X’, 10000, ‘X’));INSERT INTO test_lob_compression VALUES (i, ‘Test ‘ || i, l_clob, l_blob);DBMS_LOB.FREETEMPORARY(l_clob);DBMS_LOB.FREETEMPORARY(l_blob);END LOOP;COMMIT;END;/– 3. 检查未压缩LOB大小
SELECT segment_name, bytes / 1024 / 1024 AS size_mb
FROM user_segments
WHERE segment_name IN (
SELECT segment_name FROM user_lobs WHERE table_name = ‘TEST_LOB_COMPRESSION’
);– 4. 测试未压缩LOB性能
SET TIMING ON
SELECT id, name FROM test_lob_compression WHERE clob_data LIKE ‘%XXX%’;SET TIMING OFF

— 5. 为LOB列启用压缩
ALTER TABLE test_lob_compression MODIFY LOB(clob_data) (COMPRESS HIGH);ALTER TABLE test_lob_compression MODIFY LOB(blob_data) (COMPRESS HIGH);– 6. 压缩现有LOB数据
ALTER TABLE test_lob_compression MOVE LOB(clob_data) STORE AS (COMPRESS HIGH);ALTER TABLE test_lob_compression MOVE LOB(blob_data) STORE AS (COMPRESS HIGH);– 7. 检查压缩后LOB大小
SELECT segment_name, bytes / 1024 / 1024 AS size_mb
FROM user_segments
WHERE segment_name IN (
SELECT segment_name FROM user_lobs WHERE table_name = ‘TEST_LOB_COMPRESSION’
);– 8. 测试压缩LOB性能
SET TIMING ON
SELECT id, name FROM test_lob_compression WHERE clob_data LIKE ‘%XXX%’;SET TIMING OFF

— 9. 测试DML操作性能
SET TIMING ON
UPDATE test_lob_compression SET clob_data = clob_data || ‘ Updated’ WHERE id <= 100;DELETE FROM test_lob_compression WHERE id > 900;INSERT INTO test_lob_compression VALUES (1001, ‘Test 1001’, ‘New CLOB data’, ‘New BLOB data’);COMMIT;SET TIMING OFF

— 10. 分析压缩效果
SELECT
segment_name,
bytes / 1024 / 1024 AS size_mb
FROM user_segments
WHERE segment_name IN (
SELECT segment_name FROM user_lobs WHERE table_name = ‘TEST_LOB_COMPRESSION’
);– 11. 清理测试数据
DROP TABLE test_lob_compression;

— 1. 压缩后LOB性能下降
— 问题：启用压缩后LOB查询性能下降
— 解决方案：
— 1.1 检查LOB统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(‘SCOTT’, ‘DOCUMENTS’);– 1.2 考虑调整压缩级别
ALTER TABLE documents MODIFY LOB(doc_content) (COMPRESS MEDIUM);– 1.3 检查执行计划
EXPLAIN PLAN FOR SELECT doc_id, doc_name FROM documents WHERE doc_content LIKE ‘%important%’;SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);– 2. 压缩率不理想
— 问题：LOB压缩率低于预期
— 解决方案：
— 2.1 分析LOB数据特性
SELECT AVG(DBMS_LOB.GETLENGTH(doc_content)) / 1024 / 1024 AS avg_clob_size_mb
FROM documents;– 2.2 尝试不同的压缩级别
ALTER TABLE documents MODIFY LOB(doc_content) (COMPRESS HIGH);– 2.3 考虑使用SecureFile存储格式
ALTER TABLE documents MOVE LOB(doc_content) STORE AS SECUREFILE (COMPRESS HIGH);– 3. DML操作变慢
— 问题：启用压缩后DML操作性能下降
— 解决方案：
— 3.1 考虑使用较低的压缩级别
ALTER TABLE documents MODIFY LOB(doc_content) (COMPRESS LOW);– 3.2 分批执行大型DML操作
DECLARE
CURSOR c_docs IS SELECT id FROM documents WHERE status = ‘INACTIVE’;TYPE id_tab IS TABLE OF documents.id%TYPE;l_ids id_tab;l_batch_size NUMBER := 100;BEGIN
OPEN c_docs;LOOP
FETCH c_docs BULK COLLECT INTO l_ids LIMIT l_batch_size;EXIT WHEN l_ids.COUNT = 0;FORALL i IN 1..l_ids.COUNT
UPDATE documents SET status = ‘ARCHIVED’ WHERE id = l_ids(i);COMMIT;END LOOP;CLOSE c_docs;END;/– 4. LOB维护开销增加
— 问题：启用压缩后LOB维护开销增加
— 解决方案：
— 4.1 定期重建LOB
ALTER TABLE documents MOVE LOB(doc_content) STORE AS (COMPRESS HIGH);– 4.2 调整LOB存储参数
ALTER TABLE documents MODIFY LOB(doc_content) (STORAGE (PCTINCREASE 0));– 5. 压缩功能不可用
— 问题：无法启用LOB压缩
— 解决方案：
— 5.1 检查Oracle版本和许可
SELECT * FROM v$version;– 5.2 检查数据库兼容性
SELECT name, value FROM v$parameter WHERE name = ‘compatible’;– 5.3 确保表空间支持压缩
SELECT tablespace_name, block_size FROM dba_tablespaces;