Para melhor visualização, recomendo resolução de no mínimo 1024 x 768 e navegador Mozilla Firefox


terça-feira, 17 de junho de 2008

Reorganizando o Tablespace ...

Por Eduardo Legatti

Olá,

Dando prosseguimento ao artigo de Março de 2008 sobre introdução ao conceito de tablespaces, sabemos que o armazenamento dentro de um tablespace é organizado em segmentos e extents. Um extent pode ser alocado para um segmento e, portanto, é considerado usado; caso contrário, quando um extent não é alocado a nenhum segmento, ele é considerado livre. Pode ser um pouco complicado de entender, mas com o tempo, nos tablespaces gerenciados por dicionário, a alocação e desalocação dos extents fazia com que o espaço livre se tornasse fragmentado dando origem a fragmentação do tipo "honeycomb" na qual os extents livres ficavam adjacentes uns aos outros, e a fragmentação do tipo "buble" na qual os extents livres ficavam separados uns dos outros pelo espaço usado. A fragmentação "honeycomb" podia ser removida por um processo chamado aglutinação utilizando o comando ALTER TABLESPACE ... COALESCE para aglutinar o espaço livre contíguo explicitamente em um tablespace. Portanto, esta técnica era utilizada porque apenas o espaço contíguo poderia ser usado para alocar os extents. Já para remover a fragmentação do tipo "buble" era necessário fazer uma reorganização completa do tablespace utilizando os utilitários export (com o parâmetro COMPRESS=Y) e import para reorganizar em um único extent grande aqueles objetos que estavam fragmentados em um número grande de extents pequenos. A partir do Oracle 8i, este trabalho se tornou mais fácil com a inclusão do comando ALTER TABLE ... MOVE para mover a tabela dentro do próprio tablespace ou em outro tablespace. Em resumo, não estou querendo dizer que o comando COALESCE foi aposentado, mas com o advento do tablespace gerenciado localmente, foi eliminada a necessidade de aglutinar as extensões livres porque o gerenciamento local de extensões rastreia automaticamente o espaço livre adjacente. De qualquer forma, agora os tablespaces gerenciados por dicionário são coisas do passado.

O motivo de eu estar escrevendo este artigo partiu de uma discussão no fórum OTN da Oracle sobre algumas dúvidas relacionadas ao uso dos comandos ALTER TABLE ... MOVE (disponível a partir do Oracle 8i) e ALTER TABLE ... SHRINK (disponível a partir do Oracle 10g) no que diz respeito a realocação e compactação de tabelas de forma a poder ajustar a "High Water Mark" das mesmas, bem como a de utilizar de forma eficiente o espaço livre no tablespace sem a necessidade de ter que redimensioná-lo toda vez que não houver espaço suficiente para novas tabelas, etc... Outra coisa importante que irei mostrar é como podemos utilizar estes dois comandos (que tem propósitos diferentes) juntos para ajustar a HWM de um arquivo de dados a fim de poder diminuir o seu tamanho evitando receber as famosas mensagens "ORA-01658: não é possível criar a extensão INITIAL para o segmento no espaço de trabalho " ou "ORA-03297: o arquivo contém dados usados além do valor solicitado de RESIZE" ou até mesmo evitando receber o erro "ORA-01652: não é possível estender o segmento temp. em 8 no [tablespace]" ao tentar mover uma tabela. Na verdade, o comando ALTER TABLE ... MOVE pode ser usado para reorganizar as extensões das tabelas realocando os dados em um segmento novo (normalmente o mais próximo possível do início do datafile) no mesmo tablespace ou em outro tablespace eliminando assim problemas de linhas migradas e/ou encadeadas, além de ajustar a HWM. Portanto, o armazenamento existente da tabela não é dropado até que a tabela seja totalmente realocada, e para isso, é necessário um espaço livre no tablespace do tamanho da tabela que está sendo movida. Se não houver este espaço livre, então o erro ORA-01652 será emitido pelo Oracle. Vale a pena salientar que os usuários podem executar consultas na tabela durante a realocação, entretanto, as operações DML tais como aquelas que inserem, atualizam ou excluem dados, não são permitidas até que a realocação esteja completa.

Com relação ao espaço alocado mas não utilizado resultante de operações de update e delete dentro de um segmento é chamado de espaço livre fragmentado. Para reclamar este espaço para ser utilizado por outras tabelas, poderíamos utilizar técnicas que eu chamo de ETI (exportar, truncar e importar a tabela) ou o método CTAS (create table as select ...), entre outros, mas isso não será necessário se você estiver usando o Oracle 10g, porque é aí que o comando SHRINK entra em ação. Este comando realiza duas tarefas: O primeiro passo faz a compactação do segmento movendo as linhas para os blocos livres o máximo possível para o início do segmento e o segundo passo ajusta a "High Water Mark".

- ALTER TABLE ... ENABLE ROW MOVEMENT -- habilita a movimentação de linhas (ROWID's)
- ALTER TABLE ... SHRINK SPACE COMPACT -- realiza apenas o primeiro passo
- ALTER TABLE ... SHRINK SPACE -- realiza os dois passos (compactação e ajuste da HWM)

A opção CASCADE pode ser utilizada para compactar objetos dependentes como por exemplo os índices. É importante lembrar que para reorganizar um segmento usando o comando SHRINK é necessário que o tablespace use o gerenciamento automático de segmento ou ASSM (Automatic Segment Space Management). Enfim, para um melhor entendimento sobre estes comandos, vamos a um exemplo prático.

SQL> connect / as sysdba
Conectado.

-- Irei criar um usuário e um tablespace de teste com o tamanho inicial de 1 MB
SQL> create tablespace tbs_teste
  2  datafile '/u01/oradata/BD01/teste01.dbf' size 1m
  3  extent management local
  4  segment space management auto;

Tablespace criado.

SQL> create user teste identified by teste
  2  default tablespace tbs_teste quota unlimited on tbs_teste;

Usuário criado.

SQL> grant connect,resource to teste;

Concessão bem-sucedida.

SQL> connect teste/teste
Conectado.

-- Irei criar e popular uma tabela que chamarei de T
SQL> create table t (id  number);

Tabela criada.

SQL> insert into t select level from dual connect by level <= 45000; 
45000 linhas criadas.
SQL> commit;
Validação completa.
-- Irei deletar todas as linhas desta tabela SQL> delete from t;
45000 linhas deletadas.
SQL> commit;
Validação completa.
-- Irei analisar esta tabela a fim de gerar algumas informações sobre o segmento SQL> analyze table t compute statistics; Tabela analisada. SQL> select 2 a.blocks, b.blocks hwm, b.empty_blocks 3 from 4 user_segments a, user_tables b 5 where 6 a.segment_name=b.table_name and b.table_name='T'; BLOCKS HWM EMPTY_BLOCKS ---------- ---------- ------------ 80 73 7

Podemos ver acima que foram alocados 80 blocos para o segmento T sendo que 73 blocos foram utilizados e 7 não foram utilizados, ou seja, 7 blocos que estão acima da HWM. Podemos concluir que mesmo após o comando DELETE, podemos ver que o espaço nas extensões continuam alocados para o segmento T.


SQL> select extent_id,blocks,block_id from dba_extents
  2  where segment_name='T' and owner='TESTE';

 EXTENT_ID     BLOCKS   BLOCK_ID
---------- ---------- ----------
         0          8          9
         1          8         17
         2          8         25
         3          8         33
         4          8         41
         5          8         49
         6          8         57
         7          8         65
         8          8         73
         9          8         81

10 linhas selecionadas.

Para complementar, executando uma consulta na view DBA_EXTENTS podemos ver que 10 extents foram alocados para segurar os 80 blocos, ou seja, cada extent contém 8 blocos de dados. Para uma melhor visualização, veja como ficou o mapa do tablespace TBS_TESTE



-- Irei agora criar e popular uma tabela que chamarei de X
SQL> create table x (id number);

Tabela criada.

SQL> insert into x select level from dual connect by level <= 20000; 
20000 linhas criadas.
SQL> commit; Validação completa.

Após a criação e inserção de dados na tabela T, podemos ver abaixo no mapa do tablespace TBS_TESTE, que o Oracle alocou novos extents para a tabela X, ou seja, mesmo que a tabela T esteja "vazia", ela ainda assim deterá os 80 blocos para futuras inserções, a menos que a HWM seja ajustada com o comando TRUNCATE, ou com os métodos ETI, CTAS ou com o comando MOVE, ou com o comando SHRINK que irei mostrar mais adiante.




SQL> alter table t move;
alter table t move
*
ERRO na linha 1:
ORA-01658: não é possível criar a extensão INITIAL para o segmento no espaço de
trabalho TBS_TESTE

Como o tablespace está com o AUTOEXTEND desabilitado, podemos ver acima que o comando MOVE falhou porque não há espaço disponível no tablespace para acomodar uma cópia temporária do segmento T que está sendo movido.

SQL> alter table t enable row movement;

Tabela alterada.

SQL> alter table t shrink space;

Tabela alterada.

Após executar o comando SHRINK, podemos ver no mapa do tablespace TBS_TESTE abaixo que a HWM do segmento T foi ajustada, e o espaço livre foi liberado para uso para outros segmentos.




SQL> select extent_id,blocks,block_id from dba_extents where segment_name='T'
  2  and owner='TESTE';

 EXTENT_ID     BLOCKS   BLOCK_ID
---------- ---------- ----------
         0          8          9

SQL> analyze table t compute statistics;

Tabela analisada.

SQL> select
  2      a.blocks, b.blocks hwm, b.empty_blocks
  3    from
  4      user_segments a, user_tables b
  5    where
  6      a.segment_name=b.table_name and b.table_name='T';

    BLOCKS        HWM EMPTY_BLOCKS
---------- ---------- ------------
         8          1            7

Podemos confirmar acima que, realmente, a HWM do segmento T foi resetada.

-- Irei mostrar o que acontecerá se movermos a tabela X
SQL> alter table x move;

Tabela alterada.

Podemos perceber no mapa do tablespace abaixo, que a tabela X foi movida o máximo possível para o início do arquivo de dados, possibilitando assim que o tamanho do mesmo possa ser redimensionado para baixo caso necessário. Por exemplo, de 1 MB para 500 KB.




SQL> drop table t;

Tabela eliminada.

SQL> drop table x;

Tabela eliminada.

Bem, de vez em quando é comum ouvir questões como alguém que não está conseguindo criar uma tabela nova ou algum segmento que não está conseguindo alocar um extent por falta de espaço no tablespace, e para piorar, não é possível aumentar o tamanho do arquivo de dados ou criar um novo arquivo e dados por falta de espaço em disco. Então, de acordo com o exemplo prático acima, mostrarei abaixo o que é possível fazer com estes dois comandos (SHRINK e MOVE) juntos, de forma a tirar melhor proveito do espaço desperdiçado dentro de um tablespace. Também irei mostrar como é possível tentar diminuir o tamanho de um arquivo de dados que está ocupando muito espaço em disco, mesmo quando o erro ORA-03297 é emitido pelo Oracle.

-- Irei habilitar a extensão automática do arquivo de dados
SQL> alter database datafile '/u01/oradata/BD01/teste01.dbf' autoextend on;

Banco de dados alterado.

-- Irei criar uma tabela nova A
SQL> create table a (id number);

Tabela criada.

SQL> insert into a select level from dual connect by level <= 100000; 
100000 linhas criadas.
-- Irei criar uma tabela nova B SQL> create table b as select * from a;
Tabela criada.
-- Irei criar uma tabela nova C SQL> create table c as select * from b; Tabela criada.

Após a criação das 3 tabelas acima, o tablespace ficou com um tamanho final de 6272 KB e abaixo podemos ver como ficou o mapa de blocos do tablespace:




SQL> connect / as sysdba
Conectado.

-- Irei desabilitar a extensão automática do arquivo de dados
SQL> alter database datafile '/u01/oradata/BD01/teste01.dbf' autoextend off;

Banco de dados alterado.

-- Obtendo informações do arquivo de dados para fins de redimensionamento. De agora
-- em diante, farei referência a este SQL usando o nome resize_information;
SQL> set verify off
SQL> column value new_val blksize
SQL> select value from v$parameter where name = 'db_block_size';

       VALUE
------------
        8192

SQL> select a.file_id,
  2         a.file_name,
  3         ceil((nvl(hwm,1)*&&blksize)/1024) smallest,
  4         ceil(blocks*&&blksize/1024) currsize,
  5         ceil(blocks*&&blksize/1024) -
  6         ceil((nvl(hwm,1)*&&blksize)/1024) savings
  7  from dba_data_files a,
  8       (select file_id, max(block_id+blocks-1) hwm
  9        from dba_extents where owner='TESTE'
 10        group by file_id) b
 11  where a.file_id = b.file_id;

 FILE_ID FILE_NAME                            SMALLEST   CURRSIZE    SAVINGS
-------- ---------------------------------- ---------- ---------- ----------
      15 /u01/oradata/BD01/teste01.dbf            6208       6272         64

Podemos verificar acima que o tamanho atual do arquivo de dados é de 6272 KB e que poderemos diminuir o tamanho do mesmo para no máximo 6208 KB, já que 64 KB é o espaço livre disponível acima da HWM do arquivo de dados.

SQL> alter database datafile 15 resize 6208k;

Banco de dados alterado.

Após reduzir o tamanho do arquivo de dados, podemos ver na figura abaixo como ficou o mapa de blocos do tablespace:




-- Irei agora deletar as linhas das tabelas A e B de forma a restar apenas 1 linha
-- para cada tabela
SQL> connect teste/teste
Conectado.

SQL> delete from a where rownum <= 99999; 
99999 linhas deletadas.
SQL> delete from b where rownum <= 99999;
99999 linhas deletadas.
SQL> commit;
Validação completa.
SQL> connect / as sysdba Conectado. -- Obtendo informações do arquivo de dados para fins de redimensionamento SQL> @resize_information; FILE_ID FILE_NAME SMALLEST CURRSIZE SAVINGS -------- ---------------------------------- ---------- ---------- ---------- 15 /u01/oradata/BD01/teste01.dbf 6208 6208 0

Podemos notar como já era esperado, que os blocos utilizados pelas linhas deletadas não foram desalocados após as operações de delete, isso porque que a HWM não é ajustada por comandos DML. Portanto, estes blocos ainda fazem parte das extensões pertencentes a estes segmentos (A e B), então este "espaço livre" só poderá ser utilizado pelos mesmos. Neste caso, como fazer então para que estes blocos livres fiquem disponíveis para outros segmentos? Abaixo irei demonstrar o uso do comando SHRINK.

-- Irei compactar (fazer com que os blocos fiquem adjacentes/contíguos) e
-- opcionalmente ajustar a HWM dos segmentos A e B
SQL> connect teste/teste
Conectado.

SQL> alter table a enable row movement;

Tabela alterada.

SQL> alter table a shrink space cascade;

Tabela alterada.

SQL> alter table b enable row movement;

Tabela alterada.

SQL> alter table b shrink space cascade;

Tabela alterada.

Acima eu utilizei a opção CASCADE apenas para demonstrar que a mesma pode ser utilizada para compactar objetos dependentes, como por exemplo, os índices caso existam. Então, após realizar a compactação dos dados e ajustar a HWM dos segmentos A e B, poderemos ver abaixo como ficou o mapa de blocos do tablespace:



Eu deixei a tabela C por último e propositalmente pelo seguinte motivo: Apesar de eu não ter realizado nenhuma operação DML na tabela C, podemos ver abaixo que a mesma está de alguma forma fragmentada pelo fato de o tablespace ter sido criado com a opção padrão AUTOALLOCATE, o que significa que toda vez que o segmento precisar de espaço para crescer a extensão alocada será de 1 MB, o que significa que foi alocado mais espaço do que o necessário como mostrado abaixo:

SQL> analyze table c compute statistics;

Tabela analisada.

SQL> select
  2      a.blocks, b.blocks hwm, b.empty_blocks
  3    from
  4      user_segments a, user_tables b
  5    where
  6      a.segment_name=b.table_name and b.table_name='C';

    BLOCKS        HWM EMPTY_BLOCKS
---------- ---------- ------------
       256        165           91

Deste modo, poderemos reclamar algum espaço livre compactando a tabela C como mostrado abaixo:

SQL> alter table c enable row movement;

Tabela alterada.

SQL> alter table c shrink space cascade;

Tabela alterada.

SQL> analyze table c compute statistics;

Tabela analisada.

SQL> select
  2      a.blocks, b.blocks hwm, b.empty_blocks
  3    from
  4      user_segments a, user_tables b
  5    where
  6      a.segment_name=b.table_name and b.table_name='C';

    BLOCKS        HWM EMPTY_BLOCKS
---------- ---------- ------------
       168        165            3

Após compactar a tabela C, poderemos ver abaixo como ficou o mapa de blocos do tablespace



Dando prosseguimento ao exemplo prático, e após compactar a tabela C, podemos verificar abaixo que o tamanho atual do arquivo de dados é de 6208 KB e que poderemos diminuir o tamanho do mesmo para no máximo 5504 KB, já que 704 KB é o espaço livre disponível acima da HWM do arquivo de dados.

SQL> connect / as sysdba
Conectado.

SQL> @resize_information;

 FILE_ID FILE_NAME                            SMALLEST   CURRSIZE    SAVINGS
-------- ---------------------------------- ---------- ---------- ----------
      15 /u01/oradata/BD01/teste01.dbf            5504       6208        704

SQL> alter database datafile 15 resize 5504k;

Banco de dados alterado.

Após reduzir o tamanho do arquivo de dados, podemos ver na figura abaixo como ficou o mapa de blocos do tablespace:



Após todas estas operações, irei verificar realmente quanto de espaço alocado existe para as tabelas A, B e C utilizando o SQL abaixo:

SQL> break on report
SQL> compute sum of blocks on report
SQL> compute sum of extents on report
SQL> compute sum of kbytes on report
SQL> select
  2     owner,
  3     segment_name,
  4     blocks,
  5     extents,
  6     bytes/1024 KBYTES
  7  from
  8     dba_segments
  9  where
 10     tablespace_name = 'TBS_TESTE';

OWNER                SEGMENT_NAME               BLOCKS    EXTENTS     KBYTES
-------------------- ---------------------- ---------- ---------- ----------
TESTE                A                               8          1         64
TESTE                B                               8          1         64
TESTE                C                             168         17       1344
                                            ---------- ---------- ----------
sum                                                184         19       1472

De acordo com o resultado acima, podemos notar que as tabelas A, B e C estão utilizando um total de 1472 KB de dados em 184 blocos. Será possível reduzir o tamanho do arquivo de dados para 1472 KB?

SQL> alter database datafile 15 resize 1472k;
alter database datafile 15 resize 1472k
*
ERRO na linha 1:
ORA-03297: o arquivo contém dados usados além do valor solicitado de RESIZE
Apesar de haver espaço livre suficiente no arquivo de dados para podermos reduzir o seu tamanho, vimos que não foi possível fazê-lo pelo fato deste espaço livre estar fragmentado dentro do arquivo de dados. Realmente, o que está impedindo a realização desta tarefa é o último bloco (HWM) utilizado pela tabela C. Neste caso, não adiantará utilizar novamente o comando SHRINK pelo fato de a mesma já estar totalmente compactada. É agora que entra em ação o comando ALTER TABLE ... MOVE para resolver este problema e mover os segmentos para o mais próximo possível do início do arquivo de dados.

SQL> alter table a move;

Tabela alterada.

SQL> alter table b move;

Tabela alterada.

SQL> alter table c move;

Tabela alterada.

SQL> @resize_information;

 FILE_ID FILE_NAME                            SMALLEST   CURRSIZE    SAVINGS
-------- ---------------------------------- ---------- ---------- ----------
      15 /u01/oradata/BD01/teste01.dbf            2432       5504       3072
Após realocarmos as tabelas, podemos verificar acima que o tamanho atual do arquivo de dados é de 5504 KB e que poderemos diminuir o tamanho do mesmo para no máximo 2432 KB, já que 3072 KB é o espaço livre disponível acima da HWM do arquivo de dados.



Podemos notar na figura acima, que as tabelas foram realocadas para o início do arquivo de dados, mas por algum motivo a tabela C não ficou alocada como eu esperava. Bem, isto apenas comprova que não temos controle sobre onde o Oracle alocará os blocos. Neste caso, irei reduzir o tamanho do arquivo de dados o máximo possível com o tamanho de 2432 KB.

SQL> alter database datafile 15 resize 2432k;

Banco de dados alterado.

SQL> @resize_information;

 FILE_ID FILE_NAME                            SMALLEST   CURRSIZE    SAVINGS
-------- ---------------------------------- ---------- ---------- ----------
      15 /u04/oradata/BD01/teste01.dbf            2432       2432          0

Pronto, podemos verificar que pelo menos conseguimos reduzir o tamanho do arquivo de dados de 6272 KB para 2432 KB apenas utilizando os comandos SHRINK e MOVE. Ao final de tudo, o mapa de blocos do tablespace ficou como mostrado na figura abaixo:



Para finalizar, no Oracle Enterprise Manager Database Control existe um utilitário chamado Supervisor de Segmento que pode ser utilizado para fornecer dicas sobre o fato de um objeto ser ou não um bom candidato para uma operação de compactação com base no nível de fragmentação de espaço de um objeto. Neste caso, o supervisor de segmento determina se os objetos possuem espaço não usado que pode ser liberado levando em consideração requisitos de espaço futuro. O cálculo de espaço futuro estimado é baseado em um histórico de tendências. O supervisor também gera relatórios sobre a tendência de crescimento histórico dos segmentos. É possível ainda utilizar usar essas informações para o planejamento da capacidade e para chegar a uma decisão informada sobre quais segmentos deverão ou não ser compactados.

Algumas considerações sobre o comando SHRINK no Oracle 10g:
Só é possível realizar a compactação de tabelas utilizando o comando SHRINK em tablespaces com ASSM.

Os tipos mais comuns de segmentos passíveis de compactação são:

- Tabelas heap e tabelas organizadas por índice
- Índices
- Partições e subpartições
- Views materializadas e logs de views materializadas
- Segmentos LOB

Não é possível compactar:

- Segmentos de UNDO

- Segmentos temporários (No oracle 11g já é possível compactar o tablespace temporário)
- Tabelas clusterizadas
- Tabelas que contenham colunas do tipo LONG

Com relação ao comando ALTER TABLE ... MOVE, vale a pena salientar que após finalizar a realocação da tabela, será necessário reconstruir os índices da tabela (rebuild) , caso os mesmos existam.


Maiores informações podem ser encontradas na documentação oficial da Oracle: Shrinking Database Segments Online e Moving a Table to a New Segment or Tablespace

Google+

33 comentários:

Vinicius Nogueira disse...

Olá amigo,

Meu nome é Vinicius, autor do site http://carreiradeti.com.br. O objetivo desse site é apresentar uma visão geral das profissões e certificações em TI e dicas de carreira, sendo bastante focado nisso. Às vezes sou questionado
com mais profundidade sobre uma determinada área, mas não tenho um
especialista para indicar... Em minhas pesquisas sobre blogs referente a tecnologia Oracle,constatei que o seu se destacou pelo profissionalismo e foco nessa tecnologia.
No meu site estou estruturando uma área de blogs especializados, onde
colocarei um link para cada especialista selecionado(certificado) em cada tecnologia, sendo somente um representante por tecnologia, com o intuito de ajudar o meu leitor com suas dúvidas mais avançadas, por isso tenho que escolher com cuidado. Teria imenso prazer em trocar link com você e assim poder referenciá-lo em meu site e vice-versa.

Caso aceite, peço a gentileza de incluir o meu link conforme abaixo:

Carreira e Certificações em TI

e me enviar seu link da forma que queira que apareça.

Independente de sua resposta, lhe desejo sucesso em seu blog!

Dá uma passada em meu blog, espero que seja digno de sua consideração!

Abraço!

Att.

Vinicius Nogueira
http://carreiradeti.com.br
MCP/MCDBA/MCITP SQL Server - DBA Sênior

Eduardo Legatti disse...

Olá Vinicius,

Realmente acho louvável qualquer iniciativa que leve ao compartilhamento e a difusão de informações relacionadas à tecnologia, em qualquer aspecto. Como pedido, adicionei o link do seu site no meu blog. Aliás, parabéns pelo seu site [Carreira e Certificações em TI] que com certeza, aborda assuntos interessantes e úteis para todos nós. No mais, fique à vontade para compartilhar e difundir o link do meu blog.

Atenciosamente,

Eduardo Legatti

Luiz disse...

Belo Trabalho.
Fonseca - Autor do Livro
DBA RAC 11g

Eduardo Legatti disse...

Olá Luiz Cláudio,

Obrigado pelo comentário. Aliás, parabéns pela publicação do seu livro "DBA RAC 11g Arquitetura", que com certeza, será de grande utilidade e uma valiosa fonte de consulta para todos nós!

Até mais ...

Anônimo disse...

Excelente trabalho, material de muita qualidade e de alto teor de conhecimento e explicações objetivas e diretas. Meus parabéns.

David Ricardo
Dba Oracle

JJeffman disse...

Parabéns pelo seu trabalho. O Blog está muito bom.
Gostaria de aproveitar a oportunidade de lhe fazer uma pergunta, para a qual não achei resposta na documentação do Oracle nem em sites relacionados.
Preciso avisar um usuário, que não possui grants de DBA, de que a tablespace onde sua tabela está armazenada ultrapassou 80% do tamanhoo disponível. Há como obter esta informação conectado como usuário comum ? Um abraço. Jayme.

Eduardo Legatti disse...

Olá Jayme,

Por questões de segurança, não é bom que um usuário "comum" tenha privilégios DBA ou quaisquer outros privilégios de acesso nas views de dicionário de dados DBA_*.

Neste caso, o administrador de banco de dados, ciente de tal "problema" poderá avisar o usuário (seja por e-mail, etc...) que o limite de sua tablespace está estourando e que necessitará tomar algumas providências como (criar um novo datafile, redimensionar o datafile existente, compactar alguns segmentos, etc...).

No mais, respondendo a sua pergunta, um usuário comum precisará ter privilégios de SELECT em algumas views DBA_* de dicionário de dados para obter tais informações. Para obter outras informações sobre problemas de espaço em tablespaces, você também poderá checar as views DBA_OUTSTANDING_ALERTS e DBA_ALERT_HISTORY.

Abraços e até mais ...

Juliano disse...

Primeiramente parabéns pelo seu trabalho. O Blog é realmente muito bom.
Estou com uma dificuldade no Oracle XE e gostaria de saber se podes me ajudar. Estou tentando liberar espaço de uma tabela que realizei um delete de muitas linhas, no entanto quando executo o comando “alter table TABELA shrink space” o seguinte erro é apresentado: SQL Error: ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS02'
*Action: Add more space to the undo tablespace before retrying the operation. An alternative is to wait until active transactions to commit.

Por se tratar do Oracle XE o tablespace UNDO só cresce até 700MB.
Então gostaria de saber se existe alguma forma de executar um comando sem utilizar o tablespace UNDO, ou seja, sem a possibilidade de rollback.

Eduardo Legatti disse...

Olá Juliano,

Onde foi que você leu que o tablespace de UNDO no Oracle XE é limitado em até 700 MB?

SYS@xe> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SYS@xe> select file_id,tablespace_name,bytes/1024/1024 MB from dba_data_files where file_id=2;

FILE_ID TABLESPACE_NAME MB
---------- ------------------------------ ----------
2 UNDO 255

SYS@xe> alter database datafile 2 resize 2G;

Banco de dados alterado.

SYS@xe> select file_id,tablespace_name,bytes/1024/1024 MB from dba_data_files where file_id=2;

FILE_ID TABLESPACE_NAME MB
---------- ------------------------------ ----------
2 UNDO 2048


Pelo jeito você criou outro tablespace de UNDO (UNDOTBS02) ... Tente aumentar o datafile correspondente ao tablespace de UNDO (corrente), ou habilite a extensão automática para o mesmo (AUTOEXTEND ON). No mais, não há motivos para que o comando SHRINK não funcione. Caso você tenha problemas, você poderá também utilizar outras técnicas:

CTAS

1 - create table t1_bkp as select * from t1;
2 - truncate table t1;
3 - insert into t1 as select * from t1_bkp;
4 - drop table t1_bkp;

ETI

1 - exp user/password file=t1.dmp tables=t1
2 - truncate table t1;
3 - imp user/password file=t1.dmp full=y ignore=y


Boa sorte e até mais ...

Fernanda disse...

Eduardo,
Muito clara a sua explicação. Boa mesmo.
Aproveitando, tenho uma dúvida: e quando se tratar do Oracle 9i? Como podemos fazer o reorg, uma vez que o SHRINK só está disponível a partir do 10g?

Obrigada,
Fernanda

Eduardo Legatti disse...

Olá Fernanda,

No artigo eu comentei sobre o comando ALTER TABLE ... MOVE que poderá ser usado no Oracle 8i/9i para reorganizar as extensões das tabelas realocando os dados em um segmento novo (normalmente o mais próximo possível do início do datafile) no mesmo tablespace ou em outro tablespace eliminando assim problemas de linhas migradas e/ou encadeadas, além de ajustar a HWM. Para reclamar este espaço para ser utilizado por outras tabelas, pode se também utilizar técnicas que eu chamo de ETI (exportar, truncar e importar a tabela) ou o método CTAS (create table as select ...), entre outros, como comentei no artigo, mas realmente, isso deve ser bem trabalhoso. Por fim, a solução mais rápida no meu ponto de vista, seria exportar (exp) e importar (imp) os schemas do banco de dados.

Abraços e até mais ...

Anônimo disse...

Parabéns pelo artigo Eduardo, acabei caindo nele porque estou com um problema.
Tudo que eu tenho feito em uma determinada tabela tem gerado muita espera em disco, é uma tabela enorme com mais de 200 milhões de registros e sempre tenho que ficar criando datafiles.
Gosataria de saber se essa criação de datafiles pode está gerando essa espera enorme em disco?
E atualmente tenho deletado alguns dados antigos desta tabela, vi que necessito compactar essa tabela pelo que vi no seu artigo, essa compactação teria algum impacto negativo? Porque pelo que entendi do seu artigo, ela só gera ganhos.
Desde de já agradeço a ajuda. Abs!

Eduardo Legatti disse...

Olá Anônimo,

Acredito que os datafiles em si não seja problema. Disco lento? De qual "wait event" especificamente você está falando? Problemas com I/O realmente é bem chato e acredito que uma reorganização da tabela possa ter um efeito positivo. Verifique o quê realmente está lento: Select, DML (update,delete, insert)? Algum índice na tabela ajudaria na performance da consulta, ou a tabela tem muitos índices que são desnecessários e poderiam ser eliminados? Existe alguma política de expurgo dos dados? Já verificou se a tabela é passível de particionamento? No mais, tente responder essas questões.

Abraços e até mais ...

Anônimo disse...

A espera é gerada no DML, mas no insert.

Estou criando uma política de expurgo agora.

Em relação ao particionamento não tem como fazer, mas a tabela exige um particionamento, infelizmente, aqui na empresa não querem arcar com este custo... paciência.

O wait que é gerado é "User I/O", quando se tem algum DML na tabela.

A minha preocupação em compactar uma tabela é acabar deixando mais lento do que antes, tipo para ler um registro, internamente, o banco teria que descompactar o registro. Pelo que eu li do seu artigo isso não aconte, certo?

Eduardo Legatti disse...

Olá Anônimo,

Não confunda a reorganização da qual estou falando no artigo com o termo compressão "alter table t1 move compress". São coisas diferentes. Esta compressão não acontecerá.

No mais, User I/O é uma categoria. Tente identificar dentro desta categoria qual wait está ocorrendo com mais freqüência: db file scattered read, db file sequential read, direct path read, and direct path write, etc...

Isso é um processo batch? Enfim, se você que acelerar o Insert, tente verificar se existem índices que podem ser dropados, ou até mesmo verifique a possibilidade de realizar carga direta utilizando hint /*+ append */, verifique também se existem sequences de banco envolvidas (aumentar o cache da mesma?) e a freqüência de commits.

Boa sorte e até mais ...

Róli Ferreira disse...

Muito bom artigo!

Parabéns pela escrita, organização e trasmissão do conhecimento!

Dio disse...

Eduardo, tudo bem?
Achei seu blog no google,,, uso o Oracle mas apenas com conhecimentos de dml, não sou DBA nem desenvolvedor Oracle, de qualquer forma, iniciei alguns estudos em APEX, gostaria de saber se você se aprofundou nessa tecnologia, algumas dicas... Desde já te agradeço

Eduardo Legatti disse...

Olá Dio,

Tive pouco contato com o APEX. Há uns 5 anos atrás cheguei a fazer uma a pequena aplicação com ele, usando inclusive, o Oracle Express.

Para estudos, acredito que você possa acessar o link http://apex.oracle.com

Abraços e até mais...

Legatti

wender cruz disse...

Olá Eduardo,
Referente a compressão de Tabela, apenas gostaria de tirar uma dúvida com você. No caso de uma tabela que já esta populada e exemplo seu tamanho é cerca de 80 GB. A mesma esta definida como nocompress, então irei fazer a compressão para operações DML. Até onde sei os registros serão comprimidos a partir da alteração que fiz (alter table), os registros que já estão na tabela não serão comprimidos, nesse caso para fazer uma compressão da tabela inteira, teria que mover esses registros para outra tabela, fazer o alter table compress for all operations e fazer o insert de volta para a tabela original? Teria outra forma de fazer um compress para a tabela inteira ou até mesmo para o tablespace comprimindo os dados que já existem.
Abraços.

Eduardo Legatti disse...

Olá Wender,

Exatamente, a compressão não é realizada nos dados atuais da tabela, somente nos novos dados. Um jeito é esse que você citou. Particularmente, nunca realizei essas operações, mas existem outras formas, como criar uma nova tablespace e mover as tabelas e índices para essa nova tablespace já fazendo o ALTER TABLE. Após o MOVE, você poderá dropar a tablespace antiga e renomear a nova com o nome da antiga. Basicamente seria algo como:

create tablespace [ts_novo] ... ;
alter table [table_name] move tablespace [ts_novo] compress for all operations;
alter index [index_name] rebuild tablespace [ts_novo] compress;

Até o Oracle 11g R1 não pode comprimir tabelas no nível de tablespace, mas a partir do Oracle 11g R2 pode. Inclusive, no Oracle 11g R2 se não me engano, a sintaxe mudou de "COMPRESS FOR ALL OPERATIONS" para "COMPRESS FOR OLTP". Enfim, seria algo como:

create tablespace [ts_novo] default compress ... ;
alter table [table_name] move tablespace [ts_novo];
alter index [index_name] rebuild tablespace [ts_novo];

Enfim, faça todos os testes necessários antes de realizar isso em produção. Analise se realmente a compressão vai valer a pena. Se não me engano, colunas do tipo LOB não são comprimidas nesse processo. Para tal, verifique se SecureFiles seria uma boa opção.

Abaixo tem os links para os artigos sobre SecureFile LOBS.

http://eduardolegatti.blogspot.com.br/2012/11/um-pouco-sobre-securefile-lobs-no.html

http://eduardolegatti.blogspot.com.br/2012/12/migrando-para-o-securefile-lobs-no.html

Abraços

Legatti

Anônimo disse...

Olá Legatti,
Muito bom o artigo, você está de parabéns!
Legatti, estou com uma duvida: preciso diminuir o tamanho do datafile,e fiz o shrink space cascade em todas as tabelas e depois o move em todas as tabelas, e depois o rebuild nos indices dessas tabelas, porém ao tentar diminuir o datafile, o erro ainda persiste: ORA-03297: o arquivo contém dados usados além do valor solicitado de RESIZE. Como faço para diminuir o datafile?
Muito obrigada.

Eduardo Legatti disse...

Olá Anônimo,

Você já tentou limpar a recyclebin com o comando "purge dba_recylebin"? O ideal seria você tentar identificar quais segmentos estão no final do datafile que não estão permitindo fazer o resize. Se o Oracle persistir em dar essa mensagem, é porque algum segmento insiste em não deixar o final do datafile. Se nessa tablespace tem índices, LOBs e dados de tabelas, eu recomendo a você mover cada segmento para uma outra tablespace:

1-Crie uma tablespace nova
2-Mova os índices para a tablespace nova (rebuild)
3-Mova as tabelas para a tablespace nova
4-Mova os LOBs para a tablespace nova
5-Faça o resize na tablespace antiga
6-Mova novamente os segmentos da tablespace nova para a antiga ou deixe como está e remova a tablespace antiga.

Abraços

Legatti

Kelvin Carvalho de Oliveira disse...

Bom Dia

Estou com este problema no Oracle 11, e queria saber se existe como fazer o shrink da tablespace inteira e não tabela por tabela, pois tenho muuitas tabelas e seria inviável realizar tabela por tabela.

Eduardo Legatti disse...

Olá Kelvin,

Não existe um comando único para a tablespace, mas você pode gerar os scripts para as tabelas pertencentes a uma tablespace.

select 'alter table '||table_name||' enable row movement;' from dba_tables where tablespace_name='XXX';
select 'alter table '||table_name||' shrink space cascade;' from dba_tables where tablespace_name='XXX';

No mais, avalie qual estratégia é melhor e que irá resolver o seu problema. Apenas o SHRINK irá ser suficiente?

1- Mover as tabelas para uma tablespace nova (MOVE) e realizar rebuild nos índices
2- Exportar e importar os objetos do usuários

No mais, leia os comentários postados neste artigo, talvez podem te ajudar em alguma coisa.

Abraços,

Legatti

charles disse...

Ola, Eduardo.

Primeiramente,

Gostaria de dizer, que, sempre acompanho seus posts e estão todos de parabéns (muito obrigado)!!

Enfim, se possível aproveito para tirar uma dúvida, por favor.

Explicando..:
Por falta de espaço no disco. Executei o comando alter table tb_exp move nos segmentos com as maiores HWM analisadas.

Depois tive que parar pois não havia mais espaço no disco (pelo fato do comando ter feito uma cópia temporária dos segmentos na própria tablespace)

Após isso consegui diminuir cerca de 400M do datafile com o comando resize (porém o tamanho do diretório aonde esta o datafile redimensionado ficou bem maior do que era antes).

Gostaria de saber como faço para esvaziar o tamanho alocado de forma temporária alocado pelo comando alter table .. move?


Abraços e att+

Eduardo Legatti disse...

Olá Charles,

O que você quer dizer com " O tamanho do diretório ficou maior "? Se você fez um resize no datafile e liberou 400MB e mesmo assim o tamanho do diretório ficou maior, quer dizer que um ou mais arquivos aumentaram de tamanho nesse local. Existem outros datafiles (UNDO, TEMP) ou outros arquivos neste diretório? Verifique isso e você achará a causa do problema.

Quanto ao comando MOVE, após a execução do comando, não fica nada temporário no datafile. Você utilizou o comando SHRINK nas tabelas antes de realizar o MOVE?

De qualquer forma, em vez de usar a própria Tablespace, eu prefiro criar uma Tablespace auxiliar, mover todos os segmentos para ela, fazer o resize na tablespace original para menor tamanho possível ou pelo menos 100 MB, mover os segmentos de volta, e assim dropar a tablespace auxiliar.

Abraços,

Legatti

dbbrasil disse...

Legatti, boa tarde. Excelente artigo.

Tenho uma pequena duvida: Sabe onde posso encontrar um script que retorna todas as tablespaces fragmentadas com o grau de fragmentação apontando um threshold indicando a criticidade.

Obrigado,

Ricardo Sá

Eduardo Legatti disse...

Olá Ricardo,

Precisamos entender onde o termo fragmentação se encaixa nesse contexto. Espaços preenchidos vs espaços vazios? Não sei se é o que você quer, nas pesquise no Google pelas 2 frases abaixo.

script to list top 20 fragmented tables

e

Script to find fragmentation in Oracle tablespaces

Abraços,

Legatti

dbbrasil disse...

Era isto mesmo que eu queria.
Muito obrigado.

Guilherme disse...

Parabéns! Como sempre muito boa sua explicação.

Porém estou com algumas dúvidas:

- Há algum impacto em deixar habilitado row movement de uma tabela?

- Compactar índices também recomendado, certo? Há algum dano em realizar esta compactação?

- Pode-se realizar a compactação e liberação de espaço baseado sempre na "avaliação" criada pelo Enterprise Manager (OEM)?

- É normal termos de compactar e liberar espaço seguidamente de uma tabela especifica?

- Qual ferramenta você utilizou para demonstrar os espaços vazios? Pode-se aplicar em banco de dados ou foi somente pra ilustrar os exemplos?

Muito obrigado!

Abraços

Eduardo Legatti disse...

Olá Guilherme,

Depende de cada situação. Particularmente, eu não executo as recomendações do Segment Advisor. Em alguns ambientes eu verifico as recomendações apenas para análise. Em alguns bancos de dados eu até mesmo desabilitei ele de executar nas janela de manutenção. Na verdade, são raras às vezes que eu precise fazer isso (shrink, move) em ambientes de produção, a não ser para resolver algum problema de espaço ou algo pontual em alguns segmentos (tabelas, índices) do banco de dados. Já em ambientes de DEV pode ocorrer com mais frequência porque a todo momento ocorrem migrações, etc.. que ocupam muito espaço e depois tem que reaproveitar o espaço utilizado e desalocar esse espaço para outros objetos (Ajuste da HWM). Não tem problema em deixar o "row movement" da tabela habilitado, mas não vejo necessidade de realizar tal procedimento de "compactação" a todo momento. No caso de índices também depende da situação, talvez seja o caso de fazer o ALTER INDEX ... COALESCE em vez de SHRINK. Você pode pesquisar melhor essas diferenças. Em relaçao à ferramenta, na época do artigo eu acho que utilizei o DBArtisan para fazer as ilustrações.

Abraços,

Legatti

Guilherme disse...

Executar seguidamente a compactação de índices/tabelas e liberação de espaço não otimiza querys? Além claro de otimizar o espaço em disco...

Eduardo Legatti disse...

Olá Guilherme,

Depende. Se a tabela for muito volátil (grandes cargas de dados), muitos DELETES, UPDATE. Antigamente, no Oracle 8 era bom reorganizar os objetos de vez em quando pelo fato de não ter ainda a Tablespace Gerenciada Localmente (exp/imp). Para performance de instruções SQL, a melhor forma de otimizá-las é descobrir o motivo dela estar lenta, e para isso o melhor caminho é verificar o plano de execução, verificar o caminho de acesso, saber os Wait events envolvidos, verificar se as estatísticas estão atualizadas, se os índices estão corretos para a consulta em questão, etc.. Analisar o AWR pode ser um bom começo para verificar o que está ruim no banco de dados. Dependendo do tamanho de uma tabela (100 GB ou mais), reorganizá-la (SHRINK) pode ser uma tarefa demorada ou não. É necessário avaliar se vale a pena ou não executar tal operação nela. Se o objetivo é baixar a HWM por algum motivo, teria que ver os ganhos em relação a performance. Pode ser que tenha, pode ser que não. Em relação aos índices, existem vários artigos na WEB que demonstram que reorganizar um índice (REBUILD) não trás ganhos de performance, bem que dependendo do caso, eu prefiro reconstruir alguns índices (REBUILD) e não apenas realizar um SHRINK.

No mais, você verá opiniões diversas sobre o mesmo assunto, mas vale a pena adquirir o conhecimento para tomar as melhores decisões ;-)

Abraços,

Legatti

Postagens populares