Com os scripts abaixo conseguimos reduzir a marca dagua movendo as tabelas que estao
no final dos datafiles. O que fazemos \u00e9 sempre repetir o script de move table, e reindexando…
Os scripts abaixo geram novos scripts que devem ser rodados..<\/p>\n
########################################
Verificar o tamanho do bloco do banco
########################################
set pagesize 1000
set head off<\/p>\n
column value new_val blksize
select value
from v$parameter
where name = ‘db_block_size’
\/<\/p>\n
##############################################################
Usei o tamanho do bloco de 4096 conforme na consulta abaixo.
Essa consulta ela simplesmente lista quais sao os datafiles
que podem sofrer encolhimento, e gera o comando para ja
reduzir o tamanho conforme a marca dagua.
Essa consulta deve ser repetida toda vez que voce rodar a
consulta que gera o alter table, ou seja movendo a tabela
que esta na marca dagua podemos entao encolher ainda mais
os datafiles. Lembrando que se mover a tabela de lugar
devemos reindexar os indices com status UNUSABLE.
##############################################################<\/p>\n
select ‘alter database datafile ”’ ||
file_name || ”’ resize ‘ ||
ceil( (nvl(hwm,1)*4096)\/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*4096\/1024\/1024)-
ceil((nvl(hwm,1)*
4096)\/1024\/1024 ) > 0
\/ <\/p>\n
##############################################################
Cria o script para fazer o alter table das tabelas que estao
na marca dagua permitindo o encolhimento do datafile,
lembrando que temos que sempre trocar os datafiles
para tentar promover o encolhimento o primeiro que estou
investigando \u00e9 o ‘\/u01\/banco\/dados01.dbf’
##############################################################
select * from
( select’alter table ‘||owner||’.’||segment_name||’ move tablespace ‘||TABLESPACE_NAME||’;’ dados
from dba_extents
where segment_type = ‘TABLE’
and file_id = ( select file_id from dba_data_files
where file_name = ‘\/u01\/logix10\/dados02.dbf’ )
order by block_id desc )
where rownum <=25;<\/p>\n
##############################################################
Cria o script para fazer o rebuild dos indices UNUSABLE
##############################################################
select ‘alter index ‘||owner ||’.’||index_name||’ rebuild tablespace ‘||TABLESPACE_NAME||’;’
from dba_indexes
where status = ‘UNUSABLE’;<\/p>\n","protected":false},"excerpt":{"rendered":"
Com os scripts abaixo conseguimos reduzir a marca dagua movendo as tabelas que estao no final dos datafiles. O que fazemos \u00e9 sempre repetir o script de move table, e reindexando…Os scripts abaixo geram novos scripts que devem ser rodados.. … Continue lendo