Lima Dba: julho 2014

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

Lima Dba

terça-feira, 1 de julho de 2014

Resize do banco - artigo do Burleson Traduzido

restore total de banco

Denunciar abuso