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.
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:
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 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
