Fonte: http://www.dba-oracle.com/art_dbazine_weeg_datafile_resizing_tips.htm
É possível liberar espaço de arquivos de dados, mas apenas para o primeiro bloco de dados.
Isto é feito com o comando "ALTER DATABASE ".
Ao invés de passar pelo tedioso processo de descobrir manualmente o comando cada vez que é usado, faz mais sentido para escrever um script que irá gerar este comando, se necessário.
Alter database name datafile 'file_name' resize size;
Onde name é o nome do banco de dados, file_name é o nome do arquivo e tamanho é o novo tamanho para o resize deste arquivo.
Podemos ver a mudança de tamanho na tabela DBA_DATA_FILES, bem como a partir do servidor.
Em primeiro lugar, puxar o nome do banco de dados:
Select 'alter database '||a.name
From v$database a;
Feito isso, é hora de adicionar os datafiles:
select 'alter database '||a.name||' datafile '''||b.file_name||''''
from v$database a
,dba_data_files b;
Embora este seja mais perto da solução final, não é bastante lá ainda.
A questão permanece: quais arquivos de dados que você quer alterar?
Neste ponto, você pode usar um padrão geralmente aceito, o que permite que os espaços de tabela a ser de 70 por cento para 90 por cento completo.
Se uma tabela está abaixo da marca de 70 por cento, uma maneira de trazer o número para cima é de-alocar uma parte do espaço.
É necessário ver a porcentagem usada.
Quantidade de espaço nos datafiles utilizada:
Select tablespace_name,sum(bytes) bytes_full
From dba_extents
Group by tablespace_name;
mais completa:
SET LINESIZE 200
SET PAGES 150
COL TABLESPACE_NAME FORMAT A15
SELECT A.TABLESPACE_NAME TABLESPACE,
round(A.TAMANHO_MAXIMO,2) TAMANHO_MAXIMO,
round(A.TAMANHO_ATUAL,2) TAMANHO_ATUAL,
A.AUTOEXTENSIBLE EXTEND,
round(B.USADO,2) "USADO(MB)",
round(CASE WHEN A.TAMANHO_MAXIMO<a.TAMANHO_ATUAL THEN A.TAMANHO_ATUAL-B.USADO
ELSE A.TAMANHO_MAXIMO-B.USADO END,2) "LIVRE(MB)",
round(CASE WHEN A.TAMANHO_MAXIMO<a.TAMANHO_ATUAL THEN
(B.USADO/A.TAMANHO_ATUAL)*100 ELSE (B.USADO/A.TAMANHO_MAXIMO)*100 END,2)
"USADO%"
FROM
(SELECT TABLESPACE_NAME,AUTOEXTENSIBLE,
SUM(BYTES/1024/1024) TAMANHO_ATUAL,
SUM(MAXBYTES/1024/1024) TAMANHO_MAXIMO
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME,AUTOEXTENSIBLE) A,
(SELECT TABLESPACE_NAME,
SUM(BYTES/1024/1024) USADO
FROM DBA_SEGMENTS S
GROUP BY TABLESPACE_NAME ) B
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME
/
Total de espaço disponível nos datafiles:
Select tablespace_name,sum(bytes) bytes_total
From dba_data_files
Group by tablespace_name;
Assim, se somarmos isso com a nossa declaração original, podemos selecionar em pct_used (menos de 70 por cento):
select 'alter database '||a.name||' datafile '''||b.file_name||''''
from v$database a
,dba_data_files b
,(Select tablespace_name,sum(bytes) bytes_full
From dba_extents
Group by tablespace_name) c
,(Select tablespace_name,sum(bytes) bytes_total
From dba_data_files
Group by tablespace_name) d
Where b.tablespace_name = c.tablespace_name
And b.tablespace_name = d.tablespace_name
And bytes_full/bytes_total < .7 ;
De acordo com o comando, a seleção foi feita com base na tabela.
E se você deseja redimensionar baseado em arquivo?
É fundamental lembrar que vários arquivos podem existir em qualquer espaço de tabela.
Além disso, apenas o espaço que é depois do último bloco de dados pode ser de-alocado.
Assim, o próximo passo deve ser o de encontrar o último bloco de dados:
select tablespace_name,file_id,max(block_id) max_data_block_id
from dba_extents
group by tablespace_name,file_id;
Agora que o comando para encontrar o último bloco de dados foi inserido,
é hora de encontrar o espaço livre em cada arquivo acima desse último bloco de dados:
Select a.tablespace_name,a.file_id,b.bytes bytes_free
From (select tablespace_name,file_id,max(block_id) max_data_block_id
from dba_extents
group by tablespace_name,file_id) a
,dba_free_space b
where a.tablespace_name = b.tablespace_name
and a.file_id = b.file_id
and b.block_id > a.max_data_block_id;
Como é possível, então, para combinar comandos para garantir a quantidade correta será redimensionada?
select 'alter database '||a.name||' datafile '''||b.file_name||'''' ||
' resize '||(bytes_total-bytes_free)
from v$database a
,dba_data_files b
,(Select tablespace_name,sum(bytes) bytes_full
From dba_extents
Group by tablespace_name) c
,(Select tablespace_name,sum(bytes) bytes_total
From dba_data_files
Group by tablespace_name) d
,(Select a.tablespace_name,a.file_id,b.bytes bytes_free
From (select tablespace_name,file_id
,max(block_id) max_data_block_id
from dba_extents
group by tablespace_name,file_id) a
,dba_free_space b
where a.tablespace_name = b.tablespace_name
and a.file_id = b.file_id
and b.block_id > a.max_data_block_id) e
Where b.tablespace_name = c.tablespace_name
And b.tablespace_name = d.tablespace_name;
Double check na compactação dos datafiles:
select 'alter database '||a.name||' datafile '''||b.file_name||'''' ||
' resize '||greatest(trunc(bytes_full/.7)
,(bytes_total-bytes_free))
from v$database a
,dba_data_files b
,(Select tablespace_name,sum(bytes) bytes_full
From dba_extents
Group by tablespace_name) c
,(Select tablespace_name,sum(bytes) bytes_total
From dba_data_files
Group by tablespace_name) d
,(Select a.tablespace_name,a.file_id,b.bytes bytes_free
From (select tablespace_name,file_id
,max(block_id) max_data_block_id
from dba_extents
group by tablespace_name,file_id) a
,dba_free_space b
where a.tablespace_name = b.tablespace_name
and a.file_id = b.file_id
and b.block_id > a.max_data_block_id) e
Where b.tablespace_name = c.tablespace_name
And b.tablespace_name = d.tablespace_name
And bytes_full/bytes_total < .7
And b.tablespace_name = e.tablespace_name
And b.file_id = e.file_id ;
Uma última coisa a fazer: Adicione uma instrução para indicar o que está sendo alterado.
Esta query gera os comandos necessários para o resize:
select 'alter database '||a.name||' datafile '''||b.file_name||'''' ||
' resize '||greatest(trunc(bytes_full/.7)
,(bytes_total-bytes_free))||chr(10)||
'--tablespace was '||trunc(bytes_full*100/bytes_total)||
'% full now '||
trunc(bytes_full*100/greatest(trunc(bytes_full/.7)
,(bytes_total-bytes_free)))||'%'
from v$database a
,dba_data_files b
,(Select tablespace_name,sum(bytes) bytes_full
From dba_extents
Group by tablespace_name) c
,(Select tablespace_name,sum(bytes) bytes_total
From dba_data_files
Group by tablespace_name) d
,(Select a.tablespace_name,a.file_id,b.bytes bytes_free
From (select tablespace_name,file_id
,max(block_id) max_data_block_id
from dba_extents
group by tablespace_name,file_id) a
,dba_free_space b
where a.tablespace_name = b.tablespace_name
and a.file_id = b.file_id
and b.block_id > a.max_data_block_id) e
Where b.tablespace_name = c.tablespace_name
And b.tablespace_name = d.tablespace_name
And bytes_full/bytes_total < .7
And b.tablespace_name = e.tablespace_name
And b.file_id = e.file_id ;
exemplo da saída:
'ALTERDATABASE'||A.NAME||'DATAFILE'''||B.FILE_NAME||''''||'RESIZE'||GREATEST(TRU
--------------------------------------------------------------------------------
alter database ORCL datafile '/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf
' resize 31831771
--tablespace was 12% full now 70%
Enfim, este é um script que irá criar o script. Mesmo assim, é importante prestar atenção ao aplicar o script criado.
Por quê? Porque reversão, Sistema e espaços de tabela temporários são muito diferentes criaturas, e cada um não deve necessariamente ser realizada com a regra de 70 por cento.
Da mesma forma, pode haver uma razão muito boa para um espaço de tabela a ser alocado ao longo - como a carga gigante que vai triplicar o volume de noite.
Por fim , cheque os tamanhos das tablespaces novamente:
Select tablespace_name,sum(bytes) bytes_full
From dba_extents
Group by tablespace_name;
SET LINESIZE 200
SET PAGES 150
COL TABLESPACE_NAME FORMAT A15
SELECT A.TABLESPACE_NAME TABLESPACE,
round(A.TAMANHO_MAXIMO,2) TAMANHO_MAXIMO,
round(A.TAMANHO_ATUAL,2) TAMANHO_ATUAL,
A.AUTOEXTENSIBLE EXTEND,
round(B.USADO,2) "USADO(MB)",
round(CASE WHEN A.TAMANHO_MAXIMO<a.TAMANHO_ATUAL THEN A.TAMANHO_ATUAL-B.USADO
ELSE A.TAMANHO_MAXIMO-B.USADO END,2) "LIVRE(MB)",
round(CASE WHEN A.TAMANHO_MAXIMO<a.TAMANHO_ATUAL THEN
(B.USADO/A.TAMANHO_ATUAL)*100 ELSE (B.USADO/A.TAMANHO_MAXIMO)*100 END,2)
"USADO%"
FROM
(SELECT TABLESPACE_NAME,AUTOEXTENSIBLE,
SUM(BYTES/1024/1024) TAMANHO_ATUAL,
SUM(MAXBYTES/1024/1024) TAMANHO_MAXIMO
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME,AUTOEXTENSIBLE) A,
(SELECT TABLESPACE_NAME,
SUM(BYTES/1024/1024) USADO
FROM DBA_SEGMENTS S
GROUP BY TABLESPACE_NAME ) B
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME
/
Uma palavra de cautela, também: Certifique-se de que as extensões ainda pode ser alocada em cada tabela.
Pode haver espaço livre suficiente, mas pode ser muito fragmentada para ser útil.
Fonte:http://www.dba-oracle.com/art_dbazine_weeg_datafile_resizing_tips.htm
