Oracle - zmenšovanie datafiles a defragmentácia

Pri boji s diskovými kapacitami obsadenými oracle databázami sa snažíme uvoľniť miesto odmazaním veľkého objektu (tabuľka + indexy napr) príkazom truncate. Miesto na disku sa však neuvoľní. Tu uvádzam ako získať alokované miesto jednoduchým skriptom prípadne presunom tabuliek v Oracle databázach.

Prvý krok je pokúsiť sa o zníženie veľkosti datafiles, ak sa voľné miesto nachádza za HWM (voľné bloky na konci súboru). Na toto poslúži skript:

--ZMENSENIE DATAFILES
-- VYBRAT NAJSKOR LEN VELKOST BLOKU (obycajne 8192)
select value from v$parameter where name = 'db_block_size' ;

-- POTOM PREPISAT blksize ZA VELKOST BLOKU A VYGENEROVAT RESIZE:
select 
       ceil( blocks*blksize/1024/1024) currsize,
       a.AUTOEXTENSIBLE,
       ceil(a.increment_by*blksize /1024/1024) next_EXTENT,
       ceil( blocks*blksize/1024/1024) - ceil( (nvl(hwm,1)*blksize)/1024/1024 ) saving,
      'alter database datafile ''' || file_name || ''' resize ' ||
       ceil( (nvl(hwm,1)*blksize)/1024/1024 )  || 'm;'
       || decode(autoextensible,'YES', chr(10)||'alter database datafile '''||file_name||''' autoextend on;','') 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*blksize/1024/1024) -
      ceil( (nvl(hwm,1)*blksize)/1024/1024 ) > 0
-- and TABLESPACE_NAME in ('INDEXLARGE')
order by file_name;

Toto nie vždy zaberie, objekt mohol byť rozprestrený po datafile, alebo za ním boli uložené 2-3 bloky iných tabuliek a teda miesto nieje možné uvolniť. Orientačne sa dajú vyhľadať súbory ktoré majú voľné bloky a sústrediť sa na ne.

Nasledujúci skript vyberie súbory ktoré majú v jednom súvislom bloku min 500MB, číslo bloku a suma koľko voľného je v celom súbore. Dá sa tu vhodne zvoliť (zvýšiť) počet blokov (60000) tak aby našiel jediný blok v súbore:

-- ZOZNAM DATAFILES, VOLNE MIESTO V JEDNOM BLOKU MIN.500MB
set linesize 120
column fname format a52
select s.file_id id, d.file_name fname, s.block_id bid,
 sum(s.blocks) * 8192 / 1024/1024/1024 free_GiB
from sys.dba_free_space s, sys.dba_data_files d
where s.file_id = d.file_id
and s.blocks > 60000
group by s.file_id, d.file_name, s.block_id
order by 1;

Po vyhľadaní máme zoznam datafiles s ich ID, môžme pozrieť aj ručne (nieje nutné, ďalej je skript pre výpis cez celú DB), ako vyzerá koniec súboru, či je voľné miesto na konci a vyplatí sa s defragmentáciou zabávať:

--MAPA BLOKOV PRE DANY DATAFILE (ID)
column Name format a50
select file_id, block_id, blocks * 8192/1024/1024 "MByte",
 owner||'.'||segment_name||' '||partition_name "Name"
from sys.dba_extents
where file_id = 31
UNION
select file_id, block_id, blocks * 8192/1024/1024 "MByte", 'Free'
from sys.dba_free_space
where file_id = 31
order by 2;

Po vyhľadaní rovnakým limitom (60000 alebo viac) spustiť hľadanie aké tabuľky sú za blokom nasledujúcim skriptom. Vygeneruje priamo alter table move príkazy, ak nevyplní partíciu, kopírovať príkaz po slovo partition. Uvádza na začiatku číslo datafile, veľkosť voľného bloku. Na konci koľko alokovaných MB je potrebné presunúť (veľmi orientačné, presúva sa celá partícia objektu).

set linesize 200
column Name format a100
select '/* '|| f.file_id ||' '||
 f.blocks *8192/1024/1024 ||'MB Free*/ alter table '||
 u.owner||'.'||u.segment_name||' move partition '||
 u.partition_name||';' "Name", sum(u.blocks) * 8192/1024/1024 MB_Used
from sys.dba_extents u, sys.dba_free_space f
where u.tablespace_name  = f.tablespace_name
and u.file_id = f.file_id
and u.block_id > f.block_id
and f.blocks > 60000
group by '/* '|| f.file_id ||' '||
 f.blocks *8192/1024/1024 ||'MB Free*/ alter table '||
 u.owner||'.'||u.segment_name||' move partition '||
 u.partition_name||';'
order by 1;

Ak sa rozhodneme presunúť tabuľku, po jej presune je potrebné obnoviť rozbité (unusable) indexy (pred a po move hádam rovnaký stav) (orafaq zdroj):

-- REBUILD UNUSABLE INDEXOV
select 'alter index '||owner||'.'||index_name||' rebuild tablespace '||
 tablespace_name ||';' sql
from dba_indexes where status = 'UNUSABLE'
union all
select 'alter index '||index_owner||'.'||index_name ||' rebuild partition '||
partition_name||' TABLESPACE '||tablespace_name ||';' sql_to_rebuild_index
from dba_ind_partitions where status = 'UNUSABLE';

Používam presun objektu ( alter table ... move [partition ....] [online] ), je však možné tiež použiť príkazy SHRINK, ktoré sú ale vraj veľmi pomalé a generujú veľa logov.

Linky ku štúdiu:

shrink LOBov: http://www.idevelopment.info/data/Oracle/DBA_tips/LOBs/LOBS_85.shtml
online defrag: https://asktom.oracle.com/pls/apex/f?p=100:11:::NO:RP:P11_QUESTION_ID:9532814300346843891
postup zmensovania: https://dba.stackexchange.com/questions/6593/simplified-automated-datafile-free-disk-space-reclaim
dalsi postup: https://www.4iapps.com/blogs/oracle-fusion-middleware-technology/database-de-fragmentation/
segment advisor, shrink:  https://oracle-base.com/articles/10g/space-object-transaction-management-10g
rozne techniky zmensovania: http://www.dba-oracle.com/t_reclaiming_disk_space.htm
dbms_redefinition: http://www.dba-oracle.com/t_online_table_reorganization.htm