ORACLE: ORA-03297: file contains used data beyond requested RESIZE value
- Siltech Consult
- 7 de out. de 2019
- 3 min de leitura
Problema: Tenho espaço livre no tablespace/datafile, porém, quando vou reduzir o tamanho do datafile, ocorre o erro ORA-03297.
TABLESPACE_NAME ALOCADO LIVRE MAXIMO
------------------------------ ---------- ---------- ----------
UPDT 50000 29368.25 64000SQL> select file_id, file_name, bytes/1024/1024 mbytes
2 from dba_data_files
3* where tablespace_name = 'MEGA'
FILE_ID FILE_NAME MBYTES
---------- --------------------------------------------- ----------
5 +DATA/or1/datafile/updt.266.746037907 7000
13 +DATA/or1/datafile/updt.274.746039413 6000
14 +DATA/or1/datafile/updt.275.746039459 7000
15 +DATA/or1/datafile/updt.276.746039507 6000
16 +DATA/or1/datafile/updt.277.746039553 6000
19 +DATA/or1/datafile/updt.280.746039693 6000
20 +DATA/or1/datafile/updt.281.746039741 6000
21 +DATA/or1/datafile/updt.282.746039787 6000
8 rows selected.SQL> alter database datafile '+DATA/or1/datafile/updt.266.746037907' resize 4000m;
alter database datafile '+DATA/or1/datafile/updt.266.746037907' resize 4000m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE valueIsso ocorre pois internamente, o datafile é construído como um arquivo contínuo. Na medida em que o mesmo é utilizado através da criação de extents de objetos estes extents vão sendo criados na área livre disponível no datafile ocupando assim o espaço que estava disponível no final do mesmo criando assim no último bloco usado o que chamamos de ‘marca d’agua’ ou HWM (High Water Mark). Acima da HWM só existe blocos livres.
O comando resize pode ser executado, desde que, seja feito o resize recuperando o espaço acima da HWM. Se em algum momento os objetos sofreram delete ou mesmo foram dropados, teremos um espaço livre disponível no datafile que poderá ser usado posteriormente para outros comandos INSERT/UPDATE. No entanto, como estas áreas disponíveis estão abaixo da HWM não é possível reduzir o espaço do arquivo.
Para que consigamos reduzir o tamanho do arquivo, neste caso, teremos que ‘mover’ a HWM. E para movê-la será necessário reorganizar os extents que estão no final do arquivo para os blocos livres no começo do arquivo o que muitos chamam de ‘desfragmentação’.
Como fazer isso ?
1) Temos que descobrir quais blocos devem ser movidos. No exemplo abaixo vamos trabalhar com um datafile por vez. Primeiro precisamos identificar quais segmentos precisam ser reorganizados:
SQL> select file_id, block_id, blocks*8192/1024 MB,
2 owner || '.' || segment_name "Name", block_id*8192/1024 "Position MB"
3 from sys.dba_Extents
4 where file_id = 21
5 union
6 select file_id, block_id, blocks*8192/1024, 'Free' "Name", block_id*8192/1024 "P
7 osition MB"
8 from sys.dba_free_space
9 where file_id = 21
10* order by 1,2,3
SQL> FILE_ID BLOCK_ID MB Name Position MB
---------- ---------- ---------- ------------------------- -----------
(...)
5 893537 64 TESTE01.HISTORICOS 7148296
5 893545 64 TESTE01.INDENIITENS 7148360
5 893553 64 TESTE01.INDENIOCOR 7148424
5 893561 64 UPDT.VDAITMVD_IDX1 7148488
5 893569 64 TESTE01.ITENS_GRADE 7148552
5 893577 8192 CARGAS.VDAOCOPD_1 7148616
5 894601 8192 CARGAS.VDAOCOPD_1 7156808
5 895625 2048 CARGAS.VDAOCOPD_1 7165000
5 895881 64 CARGAS.VDAOCOPD_1_IDX1 7167048
5 895889 64 CARGAS.VDAOCOPD_1_IDX1 7167112
5 895897 64 CARGAS.VDAOCOPD_1_IDX1 7167176
5 895905 64 CARGAS.VDAOCOPD_1_IDX1 7167240
5 895913 64 CARGAS.VDAOCOPD_1_IDX1 7167304
5 895921 64 CARGAS.VDAOCOPD_1_IDX1 7167368
5 895929 64 CARGAS.VDAOCOPD_1_IDX1 7167432
5 895937 64 TESTE01.KIT 7167496
5 895945 64 UPDT.CTASALDO 7167560
5 895953 64 CARGAS.UPDT_E_MAIL_PESSOA 7167624
5 895961 64 CARGAS.UPDT_E_MAIL_PESSOA 7167688
5 895969 64 TESTE01.LINHA_CORREIO 7167752
5 895977 64 CARGAS.UPDT_E_MAIL_PESSOA 7167816
5 895985 64 CARGAS.UPDT_E_MAIL_PESSOA 7167880
5 895993 64 CARGAS.UPDT_E_MAIL_PESSOA 7167944Observe que no final do arquivo temos blocos sendo usados pelas tabelas e índices acima.
2) Após identificar quais objetos devem ser movidos, vamos movê-los para os blocos que estão livres antes da HWM. Existem diversas formas de fazermos a reorganização destes objetos, e, no exemplo abaixo vou usar um método que é o move para tabelas e rebuild para os índices:
SQL> alter table CARGAS.UPDT_E_MAIL_PESSOA move;
Table altered.
SQL> alter table CARGAS.UPDT_E_MAIL_PESSOA move;
Table altered.(...)
SQL> alter table TESTE01.LINHA_CORREIO move;
Table altered.
SQL> alter table UPDT.CTASALDO move;
Table altered.3) Quando fazemos o move de uma tabela os índices da mesma ficam inválidos, portanto, temos que realizar o rebuild nos mesmos:
SQL> select 'alter index ' || owner || '.'|| index_name || ' rebuild; '
2 from dba_indexes
3 where status = 'UNUSABLE';
'ALTERINDEX'||OWNER||'.'||INDEX_NAME||'REBUILD;'
------------------------------------------------------------------------------------------------------------------------
alter index CARGAS.VDAOCOPD_1_IDX1 rebuild;
SQL> alter index CARGAS.VDAOCOPD_1_IDX1 rebuild;
Index altered.
SQL> (Deve-se repetir o comando para todos os outros índices que estiverem inválidos)4) Ao final do move das tabelas e rebuild dos índices, pode-se executar novamente o procedimento 1 observando que o último registro informará quanto de espaço livre temos no final do datafile para fazermos a redução do mesmo:
FILE_ID BLOCK_ID MB Name Position MB
---------- ---------- ---------- ---------------------------------------- -----------
05 512009 2047936 Free 4096072Pode-se observar que na campo MB teremos indicado QUANTO em MB o datafile pode ser reduzido. (no exemplo acima este datafile possuía 6G
SQL> alter database datafile '+DATA/or1/datafile/updt.274.746039413' resize 4100m;
Database altered.
SQL>5) Repete-se os passos de 1 à 4 para todos os datafiles do tablespace que pretende-se reduzir.
6) Pronto. Por vía das dúvidas, pode-se analisar no dba_free_space se existe algum outro datafile que possa ter alguma redução de tamanho.
Dúvidas? silverio@siltechconsult.com.br
Comentarios