Bir önceki yazımda database’ deki tablolar üzerindeki fragmantasyonu nasıl tespit edebileceğimizden ve oluşan bu fragmantasyonaları nasıl elimine edebilceğimizden bahsetmiştim.
(http://www.kamilturkyilmaz.com/2012/01/15/oracle%E2%80%99-da-shrink-operasyonu-nasil-yapilir/)
Aslında aynı konu içerisinde değerlendirilmesi gereken ama yapısı itibari ile bir takım farklılıklar gösteren “lob segment” lerin üzerinde bu tarz işlemleri nasıl yapabiliriz, ondan bahsedeceğim ;

Öncelikle örnek lob data oluşturmak için; http://www.idevelopment.info/data/Oracle/DBA_tips/LOBs/LOBS_85.shtml linkindeki scriptlerden faydalandığımı belirtmek istiyorum. Buradaki scriptleri bizim testimizde kullanabileceğimiz şekliyle editleyip kullanıyor olacağım.

Lob segementler üzerinde shrink işlemine geçmeden önce üzerinde çalışacağımız test data datasını oluşturalım ;

— tablomuzu create edelim;

SQL> CREATE TABLE test_lob (
id NUMBER
, file_name VARCHAR2(45)
, image BLOB
, timestamp DATE
)
Table created.

— Tabloyu doldururken kullanacağımız sequence’ imizi create edelim;

SQL> CREATE SEQUENCE test_lob_seq
Sequence created.

Lob alanını doldurmak için kullanacağımız imaje dosyamız için bir directory oluşturuyoruz ;

SQL> CREATE OR REPLACE DIRECTORY images_dir AS ‘D:\oracle\images’
Directory created.

— aşağıdaki pl/sql aracılığı ile lob alanımızı dolduruyoruz;

CREATE OR REPLACE PROCEDURE Load_BLOB_From_File_Image
AS
dest_loc BLOB;
file_name TEST_LOB.FILE_NAME%TYPE := ‘iDevelopment_info_logo_2.tif’;
src_loc BFILE := BFILENAME(‘IMAGES_DIR’, file_name);

BEGIN
— +————————————————————-+
— | INSERT INITIAL BLOB VALUE (an image file) INTO THE TABLE |
— +————————————————————-+
INSERT INTO test_lob (id, file_name, image, timestamp)
VALUES (test_lob_seq.nextval, file_name, empty_blob(), sysdate)
RETURNING image INTO dest_loc;
— +————————————————————-+
— | OPENING THE SOURCE BFILE IS MANDATORY |
— +————————————————————-+
DBMS_LOB.OPEN(src_loc, DBMS_LOB.LOB_READONLY);
— +————————————————————-+
— | OPENING THE LOB IS OPTIONAL |
— +————————————————————-+
DBMS_LOB.OPEN(dest_loc, DBMS_LOB.LOB_READWRITE);
— +————————————————————-+
— | SIMPLY CALL “loadfromfile” TO LOAD FILES INTO A LOB COLUMN |
— +————————————————————-+
DBMS_LOB.LOADFROMFILE(
dest_lob => dest_loc
, src_lob => src_loc
, amount => DBMS_LOB.getLength(src_loc));
— +————————————————————-+
— | CLOSING ANY LOB IS MANDATORY IF YOU HAVE OPENED IT |
— +————————————————————-+
DBMS_LOB.CLOSE(dest_loc);
DBMS_LOB.CLOSE(src_loc);
COMMIT;
END;
/
Procedure created.

— Tablomuz içerisine image file’ imizi kullanarak 1000 tane kayıt oluşturabiliriz ;

BEGIN
FOR i IN 1 .. 1000
LOOP
Load_BLOB_From_File_Image();
END LOOP;
END;
/
PL/SQL procedure successfully completed.

— Örnek lob datamız artık hazır.

SQL> SELECT count(*) FROM test_lob

COUNT(*)
———-
1000
1 row selected.

Tablomuzun size’ ını kontrol edelim. Ve sonrasında lob datasını sildikden sonraki durumla karşılaştıralım. Yani fragmantasyon oluşturup üzerine konuşalım.

SELECT table_name, column_name, segment_name, a.bytes/1024/1024 mb
FROM dba_segments a JOIN dba_lobs b
USING (owner, segment_name)
WHERE b.table_name = ‘TEST_LOB’;

TABLE_NAME COLUMN_NAME SEGMENT_NAME MB
TEST_LOB IMAGE SYS_LOB0000070680C00003$$ 64

Tablonun dba_segments’ den kotrol ettiğimizdeki size’ ı 64 MB,

SELECT NVL((SUM(DBMS_LOB.GETLENGTH(image))),0)/1024/1024 MB FROM kamil.test_lob ;

MB
———-
15.9320831
1 row selected.

Ancak dbms_lob package’ ı ile olması gereken gerçek size’ ını sorguladığımızda 16 MB çıkmaktadır. Şimdi tüm datayı silip tekrar kontrol edelim.

SQL> DELETE FROM kamil.test_lob
1000 rows deleted.

SQL> commit
Commit complete.

SELECT table_name, column_name, segment_name, a.bytes/1024/1024 mb
FROM dba_segments a JOIN dba_lobs b
USING (owner, segment_name)
WHERE b.table_name = ‘TEST_LOB’;

TABLE_NAME COLUMN_NAME SEGMENT_NAME MB
TEST_LOB IMAGE SYS_LOB0000070680C00003$$ 64

Kapladığı alan size’ ında değişen birşey olmadı.

SELECT NVL((SUM(DBMS_LOB.GETLENGTH(image))),0)/1024/1024 MB FROM kamil.test_lob ;

MB
———-
0
1 row selected.

Asıl olması gereken size 0’ a indi. Ama buna rağmen şu anda 64 MB’ lık kısım bu segment tarafından kullanılmaya devam ediyor.

Oluşan fragmantasyonu gidermek için tablolarda yapmış olduğumuz shrink işleminin bir benzerinide burada yapıyor olacağız.

ALTER TABLE kamil.test_lob MODIFY LOB (image) (SHRINK SPACE);
Table altered.

Size’ ları şimdi tekrar kontrol edelim;

SELECT table_name, column_name, segment_name, a.bytes/1024/1024 mb
FROM dba_segments a JOIN dba_lobs b
USING (owner, segment_name)
WHERE b.table_name = ‘TEST_LOB’;

TABLE_NAME COLUMN_NAME SEGMENT_NAME MB
TEST_LOB IMAGE SYS_LOB0000070680C00003$$ 0,0625

Kullanılan 64 MB’ lık alan tekrardan kullanılabilmesi için dbf içerisinde free alana kazandırılmış oldu.

SELECT NVL((SUM(DBMS_LOB.GETLENGTH(image))),0)/1024/1024 MB FROM kamil.test_lob ;

MB
———-
0
1 row selected.

Burada da olması gereken size’ ı doğru olarak görebiliyoruz.

Aslında bu tarz oparasyonlar maalesef her zaman burada yaptığımız testlerdeki gibi hızlı ve problemsiz olamayabiliyor. Lob segmentler üzerinde çalışırken biraz daha dikkatli olmakda fayda var. Çünkü evdeki hesabın çarşıda uymadığına çok tanık olduğumu söyleyebilirim. Lob segmentlerin shrink edilmesi ile ilgili yayınlanmış bir dizi bug vardır. Öncesinde bir sürprizle karşılamak istemiyorsanız mutlaka kullandığınız oracle veriyonu ile yayınlanmış olan bugları karşılaştırmanınızı öneririm. Öncesinde fix etmeniz gereken patchler olabilir.

Bug 8538842 – Exclusive lock on table segment is required when LOB partitition is being shrunk [ID 8538842.8]

Bug 5636728: FALSE ORA-1555 WHEN READING LOBS AFTER SHRINK

Bug 5212539: LOB CORRUPTION ON ROLLFORWARD DUE TO ROLLBACK TO SAVEPOINT DURING DIRECT WRITES

Bug 5768710 – ALTER TABLE SHRINK slow with LOB [ID 5768710.8]

Bug 5565887: SHRINK SPACE IS REQUIRED TWICE FOR RELEASING SPACE.

BUG:4867081 SHRINK SPACE HAS TO BE EXECUTED TWICE FOR LOB COLUMNS (INTERNAL)

Bug 11927248 – ORA-600 [16613] from ALTER TABLE with SHRINK SPACE option and missing LOB keyword [ID 11927248.8]

Referans ;
http://www.idevelopment.info/data/Oracle/DBA_tips/LOBs/LOBS_85.shtml