Menu de navegação

[Marcos Donizete BoscoParticipante]

boa tarde, sou novo no forum, e não sei se esse e o caminho correto, mas vamos la, estou com o seguinte problema.

eu tenho um ambiente com Windows Server 2008 Entreprise R2 64 bits e Oracle 11g product 11.2.0

e tenho outro ambiente com Windows 7 Ultimate 32 Bits com o mesmo Oracle 11g product 11.2.0

eu tenho um sistema de terceiros que carrega muitos dados, dentro da minha tablespace de dados eu tinha 58 milhoes de registros, apos uma limpeza consegui trazer isso para 11 milhoes, ai comecou o problema, eu segui alguns procedimentos que eu conheços os quais no ambiente Windows 7 funcionaram perfeitamente, quais foram :

Apos a exclusão dos dados, fiz os seguintes procedimentos

1o- Analyze Table ???? Compute Statistics;
2o- Alter Table ???? Enable Row Movement;
3o- Alter Table ???? Shrink Compact;
4o- Alter Table ???? Shrink Compact Space;
5o- Alter Table ???? Shrink Space Cascade;
6o- Analyze Table ???? Compute Statistics;
7o- Alter Index ???? Rebuild;
8o- Alter Tablespace ???? COALESCE;

depois fiz o sequinte:

Alter Database Datafile ‘??????’ resize ??G;

no meu equipamento consegui trazer de 39G para 15G;
no servidor consegui trazer de 39G para 36G;

fiz o que ?

expdp, dropei todas as tabelas e impdp

nada, não consegui reduzir

quando tento diminuir o Datafile ele me diz que ja tem dados alocados ali,

dai a minha pergunta, na minha maquina e no servidor tem os mesmo dados,
como na minha pode ficar com 15G e no servidor o minimo que consigo e 32G ?

e claro, obviamente, fiz desfragmentacao de disco, limpeza e rodei o purge recyclebin

mas mesmo assim nada

desde já agradeco qualquer ajuda.
obrigado
MArcos

[José Laurindo ChiappaModerador]

Tudo jóia, friend ? Então, eu tenho alguns reparos aos seus conceitos/aos comandos que vc aplicou aí : muito Provavelmente teu RDBMS tá funcionando 100% ok, vc só não aplicou os procedimentos corretos E não conhecia os conceitos corretos….
O PRIMEIRO deles é que desfragmentação e Limpeza feitos PELO SISTEMA OPERACIONAL são absolutamente INÓCUOS no RDBMS Oracle – um datafile Oracle é um conjunto de BLOCOS, blocos esses com um formato próprio que o Windows (ou seja qual for o SO) absolutamente Não Entende, então DIFICILMENTE vc vai conseguir algo pelo sistema operacional : o SO simplesmente NÃO SABE COMO remover espaço inusado dentro dos blocos Oracle, já que ele NÃO ENTENDE o formato internamente usado… Isso é TOTAL E COMPLETAMENTE DIFERENTE de um arquivo comum, que usa o espaço em disco cfrme formatado e controlado diretamente pelo SO….
O SEGUNDO conceito que te falta é : além da questão de reuso/controle de espaço dentro e cada bloco, devido à performance e facilidade de administração e controle por parte do RDBMS, quando é necessário alocar o espaço o RDBMS Oracle não reserva dentro do datafile os Blocos necessários, mas sim reserva um PEDAÇÃO de blocos contínous, o chamado EXTENT… O tamanho desse EXTENT é variável, vc escolhe qual quer : assim, se vc precisa gravar, digamos, 100 bytes de dados numa tabela MAS essa tabela está setada para usar EXTENTs de 1 Megabyte (digamos), o RDBMS Oracle VAI ALOCAR um Extent de 1 MB, ie, vai solicitar e reservar uma quantidade de blocos que some UM MEGABYTE – se vc só precisava de 100 bytes, o espaço TODINHO sobrando nesse 1 MB vai ficar em branco, sem uso… Okdoc ?? E Não Adianta vc dropar e recriar a tabela, se ela está Configurada pra alocar extents/pedaços de 1 Mb é ISSO que ela vai alocar em disco, NÂO IMPORTA se os dados na verdade precisavam de muito menos….
E o TERCEIRO e ÚLTIMO conceito que vc TEM que saber pra administrar/liberar espaço em disco no RDBMS Oracle é que vc só pode diminuir (ie, fazer um RESIZE prum valor menor) até logo imediatamente após o último byte do último extent que tenha dados….
Vamos tentar exemplificar graficamente : suponha que no gráfico abaixo, cada ‘.’ é um bloco livre e eu tenho 5 extents com 20 blocos em cada extent, tipo :

……………….. ……………….. ……………….. ……………….. ………………..

(tem um espaço em branco entre os extents, ignore-o : é só pra melhor visual)…

ok, suponha que preciso gravar dados de uma tabela X e na ocasião desse INSERT o Oracle achou espaço no primeiro extent, depois precisei gravar ainda mais dados pra uma tabela Y que consumiram vários extents, depois precisei gravar blocos de um índice Z que acabou caindo no extent 9, fiquei com a seguinte situação :

XXXXXXXXXXXXXXXXXXXX YYYYYYYYYYYYYYYYYYYY YYYYYYYYYYYYYYYYYYYY YYYYYYYYYYYYYYYYYYYY ZZZ……………..

Ok, suponha que agora fiz um DELETE na tabela Y e que liberei vários e vários blocos, ficando assim :
que couberam em cinco blocos e que o RDBMS encontrou espaço nos dois últimos blocos do primeiro extent, aí vieram montes de DMLs, apaga, insere, etc, vai, volta, e depois precsei gravar dados que couberam em um bloco só, e o extent onde o Oracle achou bloco livre foi o décimo, então estou na seguinte situação :

XXXXXXXXXXXXXXXXXXXX YYYY…………….. ……………….. ……………….. ZZZ……………..

==> No cenário acima, apesar de eu ter os extents 3 e 4 completamente livres, eu ** NÂO VOU CONSEGUIR ** fazer o RESIZE para menor, pois há SIM extents acima deles no datafile que Estão Sendo usados… Captou o “problema” ???

Muito bem, como Soluciono ?? Antes de mais nada vc TEM que ter um RETRATO, um ‘raio-X’ de como estão distribuídos/alocados teus extents, para achar esses ‘buracos’ entre o início do datafile extent mais alto usado E achar quem é o objeto dono desse extent. : uma vez localizado o objeto (no meu exemplo é o índice Z) eu tenho que RECRIAR esse índice, forçando o RDBMS Oracle a mover os dados pra outros blocos….
Pra tirar um raio-x/consultar a situação dos extents/encontrar ‘buracos’ eu uso um script sqlplus do tipo :

Eu costumo usar o seguinte script no sqlplus pra isso :

C:Usersjlchi_000>type C:DBA_SCRIPTSdatafile_holes_white_space_finder.sql
/*
No script abaixo a coluna do case mostra HOLE, o bloco de início e qtdade de blocos
dos “buracos” num datafile, ie, áreas vazias mas com extents à frente dela o que impede shrink.
*/
column sb heading “Start Block”
column eb heading “End Block”
column nsb heading “NextExt Start Block”
column FILE_ID format 99999
accept v_tablespace prompt “Nome da Tablespace, assumirá %% e Maiusc:”
select *
from
(
with x as (select file_id, block_id sb, block_id+blocks-1 eb,
lead(block_id,1) over(partition by file_id
order by block_id) nsb
from dba_extents
where tablespace_name like ‘%’ || upper(‘&v_tablespace’) || ‘%’
order by 1,2,3
)
select file_id, sb, eb, nsb, case when (nsb-eb) > 1 then ‘HOLE – ‘ ||
(nsb-eb) || ‘ blocks’ else ‘OK’ end stat
from x
)
where stat ‘OK’
/
undefine v_tablespace

C:Usersjlchi_000>

==> Exemplo de execução, onde informei que queria consultar os datafiles da tablespace USERS no meu caso :

SYSTEM:@XE:SQL>@expanddatafile_holes_white_space_finder.sql
Nome da Tablespace, assumirá %% e Maiusc:users
antigo 8: where tablespace_name like ‘%’ || upper(‘&v_tablespace’) || ‘%’
novo 8: where tablespace_name like ‘%’ || upper(‘users’) || ‘%’

FILE_ID Start Block End Block NextExt Start Block STAT
——- ———– ———- ——————- ———————–
4 632 639 768 HOLE – 129 blocks
4 4984 4991 892672 HOLE – 887681 blocks
4 893392 893399 893424 HOLE – 25 blocks
4 893480 893487 893512 HOLE – 25 blocks
5 136 143 256 HOLE – 113 blocks
5 280 287 312 HOLE – 25 blocks

6 linhas selecionadas.

SYSTEM:@XE:SQL>

==> Ok, no meu caso no datafile 4 tenho um grande espação livre que vai do bloco 4992 (ie, logo após o bloco 4991) até o bloco 892671 (ie, o bloco imediatamente anterior ao 892672) : todo os extents que comecem acima desse bloco 892671 E que estejam alocados para um objeto qualquer estão Impedindo meu RESIZE, no meu caso seriam :

SYSTEM:@XE:SQL>select owner, segment_name, segment_type, partition_name from dba_extents where file_id=4
2 and block_id > 893391 order by block_id;

OWNER SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME
—————- ———————————– —————— ——————————
HR IDX_B INDEX
HR TABELA_1 TABLE
HR TABELA_2 TABLE
HR TABELA_3 TABLE
HR TABELA_4 TABLE
HR TABELA_5 TABLE
HR TABELA_6 TABLE
OE RC_DATABASE TABLE
SCOTT TAB_LOG2 TABLE
SYSTEM TB_TESTE TABLE
SCOTT TAB_LOG2 TABLE
SCOTT ITENS_COMPRA TABLE
SCOTT PROD_ESTOQ TABLE
SCOTT PROD_EQUIV TABLE
HR TAB_TESTE TABLE
SYSTEM TEST_TRAILING_ZEROCHAR TABLE

16 linhas selecionadas.

SYSTEM:@XE:SQL>

Blz ??? Esses caras teriam que ser movidos/rebuilded pra aí sim eu poder fazer o resize do datafile…. LOGICAMENTE, eu tanto posso fazer o MOVE/REBUILD direto na mesma tabelspace quanto eu posso também fazer um MOVE ou um REBUILD pra uma OUTRA tablespace (liberando assim necessariamente os blocos) e depois um novo MOVE/REBUILD pra tablespace definitiva…

[]s

Chiappa

[José Laurindo ChiappaModerador]

OBVIAMENTE, não é esse situação de extents em uso ACIMA do espaço livre apenas que causa essa situação de resize impossibilitado : como eu disse, uma OUTRA situação comum é EXTENTs de tamanho absurdamente grande.. Manda um :

SYSTEM:@XE:SQL>select DISTINCT tablespace_name, file_id, bytes from dba_extents where tablespace_name not like ‘UNDO%’ order by 1,2;

er by 1,2;

==> no meu caso isso resultou em :

TABLESPACE_NAME FILE_ID BYTES
—————————— ——- ———-
DM 10 65536
SYSAUX 2 1048576
SYSAUX 2 65536
SYSTEM 1 65536
SYSTEM 1 1048576
TEST_DATA 7 65536
TESTE 9 1048576
TESTE 9 65536
TS_NON_ASSM 8 65536
USERS 4 131072
USERS 4 65536
USERS 4 1048576
USERS 5 65536
USERS 6 65536

15 linhas selecionadas.

SYSTEM:@XE:SQL>

==> Ou seja, tenho POUCOS tamanhos de extents diferentes, não tenho extents de tamanhos absurdos (tipo, dezenas e dezenas ou mesmo CENTENAS de megabytes) E esses extent sizes são múltiplos entre si (tipo, o RDBMS se precisar pode fundir 16 desses extents de 64 KB e montar um de 1 MB)…
Neste meu banco portanto extent size não é problema, se eu não conseguisse fazer o RESIZE pro valor mais baixo que quero eu Sei que é o caso da resposta anterior….

[]s

Chiappa

[Marcos Donizete BoscoParticipante]

bom dia, agradeço a sua explicação, porém conheço os conceitos do oracle na forma de tratar os arquivos independentes do SO existente, conheço o conceito de alocação de arquivos que o oracle faz definindo o tamanho maximo do arquivo desde 1.986 (quando usava cobol) e os arquivos tinham que ter seu tamanho definido previamente e a quantidade de registros tambem, então o conceito não me é estranho, exemplifiquei os dados dos SO´s só para deixar o mais claro possível os ambientes e o porque dos erros, conheço a questão dos blocks e extents, como eu disse, não sou especialista em oracle, bem longe disso, mas uso desde o oracle 8 para AIX IBM SYSTEM V, eu já tentei tudo que citou acima, o que eu não conheço e gostaria se possível você me passe o comando, será mover os dados dentro da própria tabela (pois isso eu não fiz), quanto ao resize, não sei se não entendi direito, mas eu posso tanto aumentar um datafile como diminui-lo, não importa muito, desde que eu tenha o espaço necessário.
mesmo assim, agradeço muito o retorno e a explicação técnica, gostaria que se possível me passasse o comando para mover dentro da tabela, o REBUILD dos INDEX fiz também ganhei um pouco de espaço e consegui um pouco do REZISE o outro espaço que ganhei foi fazendo o PURGE RECYCLEBIN, e consegui diminuir um pouco também, talvez consiga chegar no meu objetivo, movendo os dados dentro da própria tabela, e fazendo um novo RESIZE, fico no aguardo.
desde já
muito obrigado
MArcos

[José Laurindo ChiappaModerador]

Blz ? Então, independente de qquer coisa o ponto é : se a soma em bytes dos dados apenas está MUITÍSSIMO MENOR do que o espaço de hoje no datafile E ao pedir um RESIZE para um tamanho menor vc obteve um ORA-03297: file contains used data beyond requested RESIZE value, OBRIGATORIAMENTE ** ou ** vc tem extents com um tamanho tão grandes que mesmo alocando só o mínimo deles (um pra tabela ou dois pra índices) ainda assim o espaço não dá pra baixar mais ** ou ** vc tem extents lá perto do fim do datafile que IMPEDEM o datafile de diminuir de tamanho – não importa SO, não importa versão ou teu nível de conhecimento, esses conceitos que citei são parte integrante do RDBMS…. Pra vc ver que não estou INVENTANDO nada disso, leia https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:137212348065 , https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:153612348067 e https://asktom.oracle.com/pls/apex/asktom.search?tag=reclaim-space-200601 além do manual de Concepts…
E no caso NÂO HÁ UM comando único que ‘resolva’ isso pra vc ok ?? O que vc TEM que fazer primeiro, como eu disse, é IDENTIFICAR qual das duas causas tá causando a tua issue..

==> SE vc identificar que é problema de EXTENT SIZE, vc OU vai extrair o DDL da tabela (via DBMS_METADATA, por exemplo) , alterar o INITIAL e NEXT e exportar os dados/dropar/recriar com INITAL e NEXT alteradps e trazer os dados OU (o melhor imho) vc vai criar uma nova tablespace local-managed que gerencie os extent sizes automaticamente…
Pra consultar tamanhos de extents alocados na tablespace, USE A QUERY que te dei na 2a resposta…

==> SE vc identificar que os extent sizes estão Aceitáveis, use a OUTRA QUERY que te passei (ou construa a sua baseado nelas) e IDENTIFIQUE quais extents estão perto do fim do datafile/acima do espaço livre e MOVA ESSES EXTENTs – para mover extents, se forem extents pertcententes a uma tabela vc pede ALTER TABLE nomedatabela MOVE; e se forem extents pertencentes a um índice vc faz um ALTER INDEX nnn REBUILD; , é isso….
E como eu disse Também, se forem muitos objetos contendo extents acima do espaço livre, talvez seja mais prático vc crir uma NOVA tablespace que gerencie os extent sizes automaticamente (suponho que vc sabe como se faz isso), exportar as tabelas e índices alterando a tablespace de destino para ser a nova tablespace de gerenciamento automático E removendo cláusula de STORAGE (https://oraclemantra.wordpress.com/2013/03/11/transform-parameter-in-impdp/ e https://docs.oracle.com/cd/B19306_01/server.102/b14215/dp_import.htm mostram como) e só então dropar e importar, Eliminando a tablespace antiga logo depois…

Mas TORNO A REPETIR : primeiro execute as queries (começando pela última, que mostra tamanhos de extents) e VEJA QUAL É A SUA SITUAÇÂO, okdoc ?? Não acho produtivo sair movendo ou exportando ou fazendo seja o que for sem ENTENDER o que está acontecendo aí dentro desse banco…

[]s

Chiappa

IMPORTANTE :

tem que ficar Absolutamente Claro o que faz comandos tipo SHRINK de uma tabela – digamos que estou na seguinte condição :

XXXXXXXXXXXXXXXXXXXX YYYYYYYYYYYYYYYYYYYY YYYYYYYYYYYYYYYYYYYY YYYYYYYYYYYYYYYYYYYY ZZZ……………..

ou seja, tenho extents pertencendo a um objeto X, a um objeto Y (uma tabela, suponha) e a um objeto Z. Se eu fazer um largo DELETE de linhas na tabela Y, os extents que esses dados ocupavam antigamente vão CONTINUAR internamente RESERVADOS para essa tabela Y – o Objetivo do RDBMS Oracle ao fazer isso é que os próximos INSERTs na tabela Y já caiam direto nesse espaço reservado, agilizando a performance deles…
Muito bem : a funcionalidade do SHRINK é simplesmente DESMARCAR esses blocos que antigamente pertenceram a Y, tornando-os blocos livres : PORÉM, neste meu exemplo, os blocos contendo dados da tabela Z **** NÂO VÂO SER AFETADOS **** de forma ALGUMA, então vc vai CONTINUAR NÂO CONSEGUINDO diminuir o tamanho do datafile….

[José Laurindo ChiappaModerador]

Dois outros pontos :

a. a técnica que citei de criar outra tablespace gerenciada automaticamente, remover os tamanhos de EXTENT(ie, initial/next na cláusula de STORAGE) e exportar/reimportar com TRANSFORM ** não é invenção minha **, https://oracle-base.com/articles/misc/reclaiming-unused-space#shrink a demonstra

b. quando vc falou de ‘qual é o comando’, talvez vc estivesse pensando em ferramentas como a Reorganização de tablespaces do Oracle Enterprise Manager, veja o item “Enterprise Manager Tablespace Reorganization” nesse mesmo link acima, correto ? Faz muuito tempo que não uso mas veja lá se te ajuda – eu sempre prefiro SABER o que estou fazendo, mas de repente talvez te sirva… Só tome TODAS AS PRECAUÇÕES no referente á backup se decidir optar por tools do tipo…

[]s

Chiappa

[Marcos Donizete BoscoParticipante]

entendo e agradeço, não disse que nada é invenção sua, olhei os extents e os blocks e estão dentro do normal, verifiquei junto ao pessoal que desenvolve o software para diminuir os extents e me passaram que o ideal seria mante-los daquele tamanho pois o banco tem poucas tabelas (cerca de 100), porém no software existem alguns erros lógicos de análise, tabelas com mais de (200 campos) e tabelas que chegam a atingir 50.000.000 de registros, quanto a dropar a tablespace e recria-la novamente eu ja fiz deixando o RDBMS “tomar” conta dos extents e não funcionou também, vou fazer duas ultimas tentativas recriar as maiores tabelas dentro da mesma tablespace com outro nome e claro tipo, REGISTRO -> NOVOREGISTRO, e inserir, deletar a original, re-inserir e deletar a copia e tentar o resize, quanto ao metodo do shrink também conheço a lógica, se tudo não der certo, vou tentar outra TABLESPACE, no mesmo disco, pois esta me parecendo que possa estar tendo alguma problema fisico com alguns setores do disco, se também não der certo, vou tentar a tablespace em outro disco, e vou tentanto… tentando… tentando…, vou mais uma vez verificar pelos scripts que você me passou para ver se identifico algum tipo de problema.
desde ja
obrigado pela atenção e os ensinamentos
MArcos

[José Laurindo ChiappaModerador]

Não entendi muito bem o que vc quis dizer com ‘REGISTRO -> NOVOREGISTRO’, mas dá alguns esclarecimentos aí : a nova tablespace pra onde vc moveu os segmentos era local managed E foi criada com gerenciamento de extent size automático (SEGMENT SPACE MANAGEMENT AUTO, onde o tamanho dos extents é Automaticamente controlado pelo RDBMS) e como ASSM (Automatic Segment Space Management, onde o controle do espaço livre/usado dentro dos blocos para UPDATEs e INSERTs não é fixo) ?? Antes de mover, vc ALTEROU as tabelas (ou as recriou SEM indicar cláusula de STORAGE) para que seja Assumidos os defaults da nova tablespace com extent size auto e ASSM ??? APENAS SE vc fizer isso tudo é que o MOVE ou o REBUILD vai assumir novos extent size, caso o problema seja EXTENTS anormalmente grandes….
A outra Possibilidade, como eu disse, é que haja algum/alguns extents no ‘topo’ impossibilitando o RESIZE do datafile pra um tamanho menor, isso vc consulta FACILMENTE pela query na DBA_EXTENTS que te forneci…

Isso é importantante, pois se vc Não identificar a causa e a corrigir vc PODE mover pra outros discos, pode rodar defrag no disco, pode fazer o que quiser , pode fazer QUALQUER COISA do tipo que Não Vai Solucionar, sim sim sim ??? Ações do tipo são inócuas se o problema for tamanho de extent OU se for extent situado ACIMA do espaço livre, simples assim…

[]s

Chiappa

[Autor]

Posts