ORA-03297: file contains used data beyond requested RESIZE value

Ora-03297 hatası datafile resize etmeye çalışırken alınan bir hata mesajıdır. Hatanın nasıl çözülebileceğine geçmeden önce bu hatayı neden alındığı üzerine biraz duralım. 

Database içerisinde daha önceden oluşturulmuş ve kullanılmış olan bazı tabloların drop veya truncate edilmesinden dolayı datafile’ in kullanılan alanı küçülmüş olabilir. Dba’ ler için yer sıkıntısı sanıyorum en fazla karşılaştıkları sorunlardan biridir desek yanlış olmaz.  Hata tam bu esnada, kullanılmayan alanın fiziksel olarak operating sisteme geri kazandırılmaya çalışdığı esnada alınıyor. Şimdi bir örnek üzerinden gidelim.

select a.file_name, a.file_id,

         (a.bytes/1024/1024) toplam_mb,

         (a.bytes/1024/1024) - (sum(b.bytes)/1024/1024) used_mb,  

         sum(b.bytes)/1024/1024 free_mb

          from dba_data_files a, dba_free_space b

where a.file_id = b.file_id

and a.file_id = 11

group by a.file_name, a.file_id,  a.bytes

FILE_NAME                              FILE_ID                         TOPLAM_MB    USED_MB        FREE_MB

/oradata/SET/set01.dbf               11                                  30000               11630,6875      18369,3125      

Yukarıdaki script ile 11 nolu datafile’ imizin toplam 30 gb yeri olduğunu ancak bunun sadece 11,6 gb’ ını kullandığını görüyoruz. Yapmak istediğimiz şu, bu datafile’ dan kullanılmayan 18 gb geri alıp daha efektif olarak başka yerlerde kullanmak.

Geri almaya çalışıyoruz;

alter database datafile '/oradata/SET/set01.dbf' resize 12000M ;

==> alter database datafile '/oradata/SET/set01.dbf' resize 12000M

alter database datafile '/oradata/SET/set01.dbf' resize 12000M

Error at line 1

ORA-03297: file contains used data beyond requested RESIZE value

Ve hatamızı alıyoruz. Datafile’ ı resize etmekdeki kural, datafile içerisinde allocate edilmiş olan blocklardan dolu olan en son bloğa kadar resize edilebilir kuralına burada takılıyor olmamızdan kaynaklanıyor. Yani 18 gb boş yerimiz olabilir ama son dolu olan blok datafile’ in allocate ettiği blocklardan sonların da yer aldığı için buraya kadar (18gb kadar) geri çekmemize izin vermiyor.

Burada yapılacak olan şu, son dolu olan bloğu bir şekilde aralardaki boş olan blockları kullandırıp çekebildiğimiz kadar geriye çekmek olacaktır. Sonrasında resize işlemini tekrar deneyebiliriz.  Bunun için yapılacak çeşitli yöntemler bulunmaktadır.

Burda birkaçını açıklayacağım ;

1) exp – imp yöntemi ;

Bu en kesin ve en kolay yöntemlerden biridir. Exp – imp datafile içerisindeki yapılandırmayı arada block atlamadan yapıtığından dolayı kazanılabilecek maxiumum alanı bu şekilde kazanabiliriz.

2) table shrink ;

Shrink yöntemi tabloların datafile içerisinde farklı blocklar iiçerisinde allocate etmiş oldukları kısımları aradaki  boşlukları atarak yeniden reorganize ettiğinden dolayı yer kazanmak için kullanılabilecek bir yöntemdir. Ancak çok büyük size’ ı tablolarda çokda başarılı olduğunu kendi testlerime dayanarak söyleyebilirim.  Tablo üzerinde shrink yapmadan önce o tablonun datalarının taşınabilmesi için row movement opsiyonunu enable yapmamız gerekiyor.

alter table test.test_table  enable row movement;

alter table test.test_table  shrink space;

Database içerisindeki bir tablespace’ e bağlı tüm tablolar için yapmak isterseniz ;

select 'alter table ' ||owner|| '.' ||table_name|| '  enable row movement;' from dba_tables where tablespace_name  = 'TEST_TABLESPACE'  ;

select 'alter table ' ||owner|| '.' ||table_name|| '  shrink space ;' from dba_tables

where tablespace_name  = 'TEST_TABLESPACE' ;

Sonrasında tekran disable yapmak için ;

select 'alter table ' ||owner|| '.' ||table_name|| '  disable row movement;' from dba_tables where tablespace_name  = 'TEST_TABLESPACE'  ;

scriptlerini kullanabilirsiniz.

3) table-index move ;

Tablo ve indexleri başka bir tablespace’ e taşımakda yer kazanmak için bir çözüm olacak yöntemlerden biridir. Tabloları ve indexleri yeni bir tablespace altına move edilmeside blockların tablolar tarafından sırayla allocate edilmesi ve arada kullanılmayan block olmamasını önleyeceğinden işimizi görecektir. Burada unutulmaması gereken tabloların move komutu taşınmasından sonra indexler invalid durumuna düşeceğinden işlem bittikden sonra mutlaka rebuild edilmesi gerektiğidir.

Bunun için aşağıdaki scriptlerden faydalanabiliriz ;

Tabloları taşımak için ;

select 'alter table ' ||owner|| '.' ||table_name|| '  move tablespace ‘TEST_TABLESPACE2’ ;' from dba_tables where tablespace_name  = 'TEST_TABLESPACE' ;

Lob segmentler için aşağıdaki scripti kullanabilirsiniz ;

select 'alter table ' || t.owner || '.' || t.table_name || ' move lob ('||column_name||') store as lobsegment (tablespace TEST_TABLESPACE2);'

from all_lobs l, dba_tables t

where l.owner=t.owner

and l.table_name = t.table_name

and l.SEGMENT_NAME in (

select segment_name

from dba_segments

where segment_type like 'LOBSEGMENT'

and tablespace_name = 'TEST_TABLESPACE')

order by t.owner, t.table_name;

İndexleri taşımak için ;

select 'alter index ' ||owner|| '.' ||index_name|| '  move tablespace "TEST_INDEX_TABLESPACE";'  from dba_indexes where tablespace_name  = 'TEST_TABLESPACE' ;

İndexleri rebuild etmek için ;

select 'alter index ' ||owner|| '.' ||index_name|| ' rebuild online parallel 8 nologging ;'  from dba_indexes where status <> 'VALID'

Yukarıdaki işlemlerden sonra database’ deki datafile’ lerin en son nereye kadar resize edileceğini görmek için ise ;

select 'alter database datafile '''||file_name||''' resize ' ||

ceil( (nvl(hwm,1)*8192)/1024/1024 ) || 'm;' cmd

from dba_data_files a,

( select file_id, max(block_id+blocks-1) hwm

from dba_extents

group by file_id ) b

where a.file_id = b.file_id(+)

and ceil( blocks*8192/1024/1024) -

ceil( (nvl(hwm,1)*8192)/1024/1024 ) > 0 ;

scripti kullanılabilir.  

son olarak recyclebin' i unutmamak gerek. Hangi yöntemi kullanıyorsak kullanalım, işlem öncesinde recyclebin' i purge etmemiz gerekir. 

purge dba_recyclebin;

Sonrasında hata almadan resize işlemini yapabildiğinizi göreceksiniz.

Kamil TÜRKYILMAZ

www.kamilturkyilmaz.com