Baixe Objetos administrativos no Oracle e outras Notas de estudo em PDF para Análise de Sistemas de Engenharia, somente na Docsity! OBJETOS ADMINISTRATIVOS – TABLESPACES CONCEITOS O ORACLE aloca espaço para todos os tipos de dados do banco de dados. Quando um objeto é alocado, também é assegurada área para armazenamento das informações. Outro momento de alocação de área é quando o objeto cresce, requerendo um aumento de sua área prevista. Estas reservas de espaço são feitas em TABLESPACES, que corresponde a uma área lógica para armazenamento de informações do banco de dados, no entanto, sabemos que o banco de dados armazena suas informações em DATAFILES. Resta-nos, então, conhecer como estes elementos se relacionam: ◊ DATABASE x DATAFILES – Um banco de dados Oracle consiste de um conjunto de arquivos de dados, chamados DATAFILES. O sistema operacional onde o banco de dados está instalado tem visibilidade destes arquivos, no entanto, sua organização interna é totalmente de responsabilidade do banco de dados. ◊ DATABASE x TABLESPACES – Um banco de dados Oracle consiste de um conjunto de TABLESPACES, isto é, áreas para armazenamento de dados. O TABLESPACE é uma estrutura lógica, interna ao banco de dados. Não é visível pelo sistema operacional. ◊ TABLESPACES x DATAFILES – Um TABLESPACE está diretamente relacionado a um DATAFILE, na medida em que quando criamos um TABLESPACE (lógico), criamos, simultaneamente, o DATAFILE (físico) que receberá a informação. Desta forma cada DATAFILE só está associado a um TABLESPACE, em outras palavras, em um DATAFILE todas as informações pertencem a um único TABLESPACE. No entanto, poderemos atribuir mais de um DATAFILE a um único TABLESPACE. Sendo assim, o relacionamento entre TABLESPACE e DATAFILE é do tipo 1(tablespace):N(datafiles). Na figura ao lado encontramos, graficamente, o relacionamento entre os elementos. Para o sistema operacional existem dois arquivos: ts_aluno1.ora e ts_aluno2.ora. Internamente, no Oracle, existe apenas o TABLESPACE TS_ALUNO. Os objetos que viermos alocar dentro deste tablespace podem ser acomodados pelo Oracle em qualquer dos arquivos físicos associados ou, até mesmo em partes. CARACTERÍSTICAS DOS TABLESPACES ◊ Cada TABLESPACE consiste de um ou mais arquivos do sistema operacional. ◊ Os TABLESPACES podem ser colocados online enquanto o banco de dados estiver em execução. ◊ Os TABLESPACES podem ser colocados offline (exceto system ou um tablespace que tenham um segmento de rollback ativo) enquanto o banco de dados estiver em execução. ◊ Os TABLESPACES podem alternar de situação passando de read-write para read-only e vice-versa. ◊ Os objetos criados em um tablespace nunca podem alocar espaço fora de seu TABLESPACE original. VANTAGENS ◊ Controlar a alocação de espaço e associar cotas de espaço para os usuários. ◊ Controlar a disponibilidade de dados, colocando o tablespace específico online ou offline. ◊ Possibilidade de distribuição de área através de diversos discos para adicionar performance (I/O) e reduzir a contenção de I/O (em vez de usar um único disco). ◊ Possibilidade de operações de backup e recuperação parcial. ◊ Colocação de grandes quantidades de dados estáticos em meios de armazenamento read-only. CRIANDO UM TABLESPACE A criação de um tablespace é feita com o comando CREATE TABLESPACE. SINTAXE CREATE TABLESPACE create_tablespace::= (permanent_tablespace_clause, temporary_tablespace_clause, undo_tablespace_clause) permanent_tablespace_clause::= (file_specification ::= , size_clause ::= , logging_clause ::= , table_compression ::= , storage_clause ::= , extent_management_clause ::= , segment_management_clause ::= , flashback_mode_clause ::= ) logging_clause::= table_compression::= (storage_clause ::= ) extent_management_clause ::= (size_clause ::= ) segment_management_clause ::= flashback_mode_clause ::= ◊ TEMPORARY - especifica que o tablespace será usado somente para armazenar objetos temporários. (por exemplo, segmentos usados para ordenação implícita causada pelo uso de uma cláusula ORDER BY). ◊ extent_management_clause – especifica como as extensões do TABLESPACE serão administradas: ◊ DICTIONARY – indica que o TABLESPACE será administrado usando tabelas dicionário. Esta é a opção default. ◊ L OCAL – indica que o TABLESPACE é “locally managed”. Isto indica que parte do TABLESPACE é controlado por um BITMAP. Quando usamos esta cláusula não podemos especificar DEFAULT storage_clause, MINIMUM EXTENT ou TEMPORARY. ◊ AUTOALLOCATE – especifica que o TABLESPACE é gerenciado pelo sistema. Os usuários não podem determinar o tamanho de uma extensão. Esta é a opção default. ◊ UNIFORM – especifica que o TABLESPACE é gerenciado com extensões de tamanho uniforme. Devemos determinar este tamanho (em Kbytes ou Mbytes). O tamanho default é 1Mbyte. OBS: ◊ Quando um TABLESPACE é criado, seu estado inicial é READ-WRITE. Isto pode ser alterado posteriormente. ◊ Se omitirmos as opções ONLINE e OFFLINE, o ORACLE cria o TABLESPACE ONLINE, por default. A view do dicionário de dados chamada DBA_TABLESPACES indica o estado de cada tablespace. ◊ Se a opção REUSE for especificada, o arquivo deve existir, caso contrário a opção SIZE deve ser especificada e o arquivo não deve existir. EXEMPLOS Criar o tablespace TS_TESTE com um arquivo de dados chamado TS_TESTE.DBF de tamanho 1M, tornando-o disponível imediatamente para uso. A opção PCTINCREASE tem um valor default de 50. No exemplo foi usado zero (0) a fim de controlarmos (impedirmos) o crescimento de segmentos criados no tablespace. Criar o tablespace TS_BIG com um arquivo de dados chamado TS_BIG.DBF de tamanho 1M. Este tablespace poderá se expandir automaticamente desde que não ultrapasse o limite de 3M. A alocação de espaço neste tablespace deverá obedecer a múltiplos de 50K. SQL> CREATE TABLESPACE ts_teste 2 DATAFILE '/opt/oracle/oradata/orcl/ts_teste.dbf' SIZE 1M 3 DEFAULT STORAGE (INITIAL 50K 4 NEXT 50K 5 MINEXTENTS 10 6 MAXEXTENTS 121 7 PCTINCREASE 0); Espaço de tabela criado. SQL> CREATE TABLESPACE ts_big 2 DATAFILE '/opt/oracle/oradata/orcl/ts_big.dbf' SIZE 1M 3 AUTOEXTEND ON MAXSIZE 3M 4* MINIMUM EXTENT 50K Espaço de tabela criado. GERENCIANDO ESPAÇO EM TABLESPACES Os TABLESPACES podem ter dois métodos de alocação interna de área para os objetos que viermos a criar dentro deles. Quando criamos o TABLESPACE, indicamos qual o método de alocação de área. Isto não poderá ser modificado posteriormente. DICTIONARY-MANAGED TABLESPACES Neste método o Oracle atualiza as tabelas apropriadas no dicionário de dados quando uma extensão é alocada ou liberada para reuso (neste processo o Oracle também atualiza informações de rollback referente às alterações realizadas no dicionário de dados). Este é o método default e o único disponível para as versões anteriores ao 9i. LOCALLY-MANAGED TABLESPACES Neste método, o tablespace gerencia suas próprias extensões através da manutenção de cada datafile de um BITMAP que indique o estado de cada bloco. Cada BIT no BITMAP corresponde a um bloco ou grupo de blocos. Quando uma extensão é alocada ou liberada para reuso, o Oracle altera os valores do BITMAP para indicar o novo estado dos blocos. Estas modificações não geram informação de rollback porque elas não atualizam as tabelas do dicionário de dados (exceto em casos especiais como “tablespace quota information”). Como vantagem adicional temos que este método dispensa a necessidade de “coalesce” para as extensões adjacentes e livres. O tamanho das extensões pode ser determinado automaticamente pelo sistema. OBS: se especificarmos a opção EXTENT MANAGEMENT LOCAL (no comando CREATE DATABASE) para o tablespace SYSTEM, os outros tablespaces no banco de dados podem variar na forma de armazenamento, no entanto, todos os segmentos de rollback devem ser criados em tablespaces gerenciados localmente. Onde : ◊ TEMPFILE ‘ filename ’ – corresponde ao nome do arquivo de dados. ◊ SIZE - especifica o tamanho do arquivo em K ou M. ◊ REUSE - permite que o ORACLE reutilize um arquivo existente. ◊ autoextend_clause – habilita ou desabilita o crescimento automático do TEMPFILE correspondente. ◊ OFF – desabilita o crescimento automático. NEXT e MAXSIZE recebem o valor zero. ◊ ON – habilita o crescimento automático. ◊ maxsize_clause – determina o máximo de espaço em disco permitido para alocação para o TEMPFILE. ◊ UNLIMITED – indica que não há limite para alocação de disco para o DATAFILE. ◊ EXTENT MANAGEMENT LOCAL – especifica que o tablespace será administrado localmente (em relação a espaço), indicando que uma parte do tablespace contém um índice BITMAP. ◊ UNIFORM – determina o tamanho da extensão para tablespaces temporários. Todas as extensões terão o mesmo tamanho (uniforme). Se não determinarmos este tamanho, o Oracle considera 1M como tamanho default. ◊ SIZE - especifica o tamanho da extensão em K ou M. OBS: A alocação e a liberação de espaço em um segmento de sort de um tablespace temporário pode ser vista na view V$SORT_SEGMENTS. EXEMPLO Criar um tablespace temporário em que cada extensão tenha exatamente 8M. SQL> CREATE TEMPORARY TABLESPACE TS_TEMP01 2 TEMPFILE '/opt/oracle/oradata/orcl/ts_temp01.dbf' SIZE 100M 3 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 8M; Espaço de tabela criado. TABLESPACES READ-ONLY O principal propósito de um tablespace read-only é eliminar a necessidade de execução de backup e recovery de grandes pedaços do banco de dados que são estáticos (não mudam). Desta forma estes arquivos podem residir em uma mídia read-only (CD-ROMs ou drives WORM). OBS: Quando criamos um novo tablespace, seu estado inicial é READ-WRITE. Antes do DBA tornar um tablespace READ-ONLY, as seguintes condições devem ser satisfeitas. OBS: É recomendado que atendamos estas restrições inicializando o banco de dados no modo restrito. ◊ O tablespace deve estar ONLINE. ◊ Não devem existir transações ativas. ◊ O tablespace não deve conter qualquer segmento de rollback ativo. ◊ O tablespace não deve estar envolvido (neste momento) em um backup online. É recomendado que o DBA remova (DROP) os segmentos de rollback presentes neste tablespace antes de torna-lo READ-ONLY. OBS: O tablespace SYSTEM nunca pode se tornar um tablespace read-only. EXEMPLOS Usar o comando de SQL ALTER TABLESPACE para tornar um tablespace para read-only. Usar o comando de SQL ALTER TABLESPACE para tornar um tablespace para read-write. OBS: Para alterarmos o estado de um tablespace READ-ONLY para READ-WRITE, todos os arquivos de dados do tablespace devem estar online. Podemos usar a opção DATAFILE ONLINE do comando ALTER DATABASE para colocar um arquivo de dados online. A view V$DATAFILE lista o estado corrente de um arquivo de dados. SQL> ALTER TABLESPACE tabspace_2 READ ONLY; Tablespace altered. SQL> ALTER TABLESPACE tabspace_2 READ WRITE; Tablespace altered. O comando ALTER TABLESPACE...READ ONLY coloca o tablespace no modo transitional read-only e aguarda que as transações existents (COMMIT ou rollback) para este tablespace se completem. Este estado não permite qualquer operação de gravação para o tablespace, exceto rollback de transações existentes. Após todas as transações terem completado (com COMMIT ou ROLLBACK), a operação solicitada (READ ONLY) é concluída e o estado do tablespace é modificado. OBS: Este estado “transitional” somente ocorre se o valor do parâmetro de inicialização COMPATIBLE for 8.1.0 ou maior. Para parâmetros inferiores a este valor, o comando ALTER TABLESPACE READ ONLY falha se houver qualquer transação em atividade. Uma vez que tablespaces read-only não podem ser modificados, eles não precisam ser salvos repetidamente. Quando necessitarmos recuperar o banco de dados, não precisamos nos preocupar, porque eles não foram modificados. Podemos remover itens (tabelas, índices, etc) de um tablespace read-only, da mesma forma que podemos remover itens de um tablespace offline, porém não podemos criar ou alterar objetos. Por outro lado, também não podemos adicionar DATAFILES a este tipo de tablespace, mesmo se o colocarmos offline (quando adicionamos um datafile, o Oracle deve atualizar o file header do arquivo e esta operação de gravação não é permitida para tablespaces read-only). tablespace_state_clauses ::= , autoextend_clause ::= , flashback_mode_clause ::= , tablespace_retention_clause ::= ) datafile_tempfile_clauses ::= (file_specification ::= ). tablespace_logging_clauses ::= (logging_clause ::= ) tablespace_group_clause::= tal 00—— —» TABLESPACE | GROUP tablespace state clauses::= ONLINE NORMAL TEMPORARY IMMEDIATE £ OFFLINE ONLY READ Dº WRITE -| PERMANENT TEMPORARY autoextend clause OFF — AUTOEX TEND | A NEXT |Gas. cause ) Em | ON (size claus: maxsize clause::= UNLIMITED —y MAXSIZE (size_clause ::= ) flashback_mode_clause ::= tablespace_retention_clause::= Onde: ◊ tablespace - é o nome do tablespace a ser alterado. ◊ LOGGING / NOLOGGING – indica se determinadas operações serão logadas (no Online redo Log) ou não. O default é LOGGING. Se especificarmos NOLOGGING, somente as operações de DML (direct-load INSERT – serial ou paralelo e Direct Loader do SQL*Loader) e DDL (create table as select, create index, alter index rebuild, alter index split partition, alter table split partition, alter index rebuild partition, alter table move partition) não serão registradas no LOG. As demais operações, independente da situação, continuarão a ser registradas. A intenção desta opção é permitir uma operação de construção de uma grande massa de dados mais rapidamente, porém, após o término desta, devemos efetuar uma salva para garantir a possibilidade de retrocesso. ◊ DATAFILE ‘ filename ’ ou TEMPFILE ‘ filename’ – corresponde ao nome do arquivo de dados. ◊ SIZE - especifica o tamanho do arquivo em K ou M. ◊ REUSE - permite que o ORACLE reutilize um arquivo existente. ◊ autoextend_clause – habilita ou desabilita o crescimento automático do DATAFILE correspondente. ◊ OFF – desabilita o crescimento automático. NEXT e MAXSIZE recebem o valor zero. ◊ ON – habilita o crescimento automático. ◊ maxsize_clause – determina o máximo de espaço em disco permitido para alocação para o DATAFILE. ◊ UNLIMITED – indica que não há limite para alocação de disco para o DATAFILE. REMOVENDO TABLESPACES Esta operação remove um tablespace do banco de dados quando o tablespace e seu conteúdo não é mais necessário para o banco de dados. SINTAXE DROP TABLESPACE Onde: ◊ tablespace - especifica o nome do tablespace a ser retirado. ◊ INCLUDING CONTENTS - Retira todo o conteúdo do tablespace. ◊ CASCADE CONSTRAINTS - Retira todas as restrições de integridade referencial das tabelas fora do tablespace que façam referência a tabelas de dentro do tablespace. OBS: ◊ Um tablespace que ainda contém dados não pode ser retirado sem a utilização da opção INCLUDING CONTENTS. ◊ Uma vez que o tablespace tenha sido retirado, seus dados não existirão mais para o banco de dados. ◊ Quando um tablespace é retirado, somente os ponteiros para arquivos no arquivo de controle são removidos do banco de dados. Os arquivos físicos continuam existindo e devem ser removidos, explicitamente, por comandos do sistema operacional. ◊ Mesmo que um tablespace seja movido para read only, ele pode ainda ser removido, assim como os segmentos dentro dele. Isto é possível porque o comando DROP simplesmente atualiza o dicionário de dados (que tem de ser read write) e não os arquivos físicos que compõem o tablespace. EXEMPLO Remover o tablespace TS_TEMP01. SQL> DROP TABLESPACE TS_TEMP01 2 INCLUDING CONTENTS CASCADE CONSTRAINTS; Espaço de tabela eliminado. DATAFILES Um tablespace em um banco de dados Oracle consiste de um ou mais arquivos físicos chamados DATAFILES. Um DATAFILE pode estar associado a somente um tablespace e somente um banco de dados. O Oracle cria um DATAFILE para um tablespace alocando uma quantidade específica de disco adicionado do overhead necessário para o header do arquivo. O sistema operacional no qual o Oracle está executando é responsável por realizar a alocação física real. Como o primeiro tablespace é sempre o SYSTEM, o Oracle, automaticamente, aloca o primeiro DATAFILE de qualquer database para o tablespace SYSTEM durante a criação do banco de dados. Quando incluímos tabelas e outros objetos em um tablespace o espaço interno vai sendo alocado de acordo com a requisição específica de espaço. O schema não está associado a um DATAFILE específico. Um DATAFILE é um repositório para dados de qualquer schema dentro de um tablespace específico. Se o tablespace específico tiver mais de um DATAFILE, o Oracle poderá alocar espaço em um ou mais DATAFILES do tablespace, ou seja, um objeto pode estar distribuído em mais de um DATAFILE. O DBA não tem controle sobre a alocação de espaço nos DATAFILES do tablespace. REDIMENSIONAMENTO AUTOMÁTICO O tamanho de um arquivo de dados pode ser alterado, automaticamente, através da opção AUTOEXTEND ou manualmente usando-se o comando ALTER DATABASE. O comando AUTOEXTEND habilita ou desabilita a extensão de arquivos de dados. Quando um arquivo de dados é criado, os comandos SQL abaixo podem ser usados para especificar que os arquivos cresçam automaticamente: ◊ CREATE DATABASE ◊ CREATE TABLESPACE ◊ ALTER TABLESPACE ◊ ALTER DATABASE Esta funcionalidade permite que tenhamos poucos DATAFILES por tablespace, o que pode simplificar a administração dos DATAFILES. CONSIDERAÇÕES FINAIS SOBRE TABLESPACES Existem três regras para gerenciamento de tablespaces. 1. Devemos usar múltiplos tablespaces. A utilização de múltiplos tablespace permite que tenhamos mais flexibilidade na execução de operações sobre o banco de dados. ◊ Separar dados do usuário de dados do dicionário de dados. ◊ Separar dados de uma aplicação de dados de outras aplicações. ◊ Armazenar arquivos de dados de diferentes tablespaces em discos diferentes para reduzir a contenção de I/O. ◊ Separar segmentos de rollback de segmentos de dados, prevenindo que uma simples falha de disco cause perda de dados. ◊ Possibilidade de colocar tablespaces individuais fora de uso (offline) enquanto outros permanecem online. ◊ Reservar tablespaces para um tipo particular de uso do banco de dados, tais como : grandes atividades de atualização, atividades somente de consulta, armazenamento de segmentos temporários. ◊ Backup de tablespaces individuais. 2. Devemos especificar os parâmetros de armazenamento (storage) para o tablespace. ◊ Especificar os parâmetros de armazenamento default (default storage) para objetos que serão criados no tablespace. ◊ Determina estes valores defaults para um tamanho típico de objeto que será criado no tablespace. 3. Devemos associar cotas de espaço para os usuários. ◊ Associar cotas de espaço, quando necessário, para os usuários do banco de dados. EXTENSÕES, BLOCOS DE DADOS E SEGMENTOS No esquema abaixo encontramos os principais componentes para administração do espaço interno em um Tablespace. O Oracle é responsável pela alocação de área para todos os dados no banco de dados. A unidade básica de alocação de área é o bloco Oracle, também chamado de bloco de dados, bloco lógico ou página. Um bloco Oracle corresponde à menor unidade de leitura ou gravação feita pelo banco de dados. Quando reservamos espaço para acomodação dos dados de uma tabela (por exemplo), informamos o tamanho a ser alocado em extensões. Uma extensão é uma porção de área contínua no Tablespace múltipla do bloco Oracle. Quando criamos a tabela, é alocada a extensão inicial. Quando a tabela cresce é alocada uma nova extensão para ela. Ao conjunto de extensões associadas a um mesmo objeto chamamos de segmento. Um segmento, portanto, é um conjunto de extensões, alocadas para uma estrutura de dados específica e armazenadas no mesmo tablespace. Por exemplo, cada tabela armazena seus dados em segmentos de dados, os índices armazenam seus dados em segmentos de índice e assim por diante. O tipo do segmento depende da informação armazenada. Um segmento, ou seja, todas as suas extensões são armazenadas em um único tablespace. No entanto, como um tablespace pode ser composto de mais de um datafile, um determinado segmento pode conter extensões acomodadas em datafiles diferentes. Um extensão, entretanto, só pode conter dados de um único datafile. Tablespace Bloco Oracle Extensão Segmento ◊ FREE SPACE - Consiste de um conjunto de bytes no bloco, que está disponível, para ser utilizado na inserção ou atualização de linhas. Na área livre dos blocos de dados de tabelas, clusters ou índices, também são armazenados TRANSACTION ENTRIES. Um TRANSACTION ENTY é necessária em um bloco para cada operação de INSERT, UPDATE, DELETE e SELECT ... FOR UPDATE que faça acesso a uma ou mais linhas do bloco. O tamanho de uma TRANSACTION ENTRY depende do sistema operacional mas, de um modo geral, fica em torno de 23 bytes. As transaction entries são necessárias para garantir o bloqueio a nível de linha. ◊ ROW DATA - Área para armazenamento dos dados em si. Uma linha pode ter pedaços em mais de um bloco. OS PARÂMETROS PARA ALOCAÇÃO DE ESPAÇO EM BLOCOS Para controlar o uso de espaço disponível para inclusões, atualizações ou exclusões de linhas em blocos do banco de dados devemos utilizar valores apropriados para os parâmetros PCTFREE e PCTUSED. Estes parâmetros são especificados a tempo de criação ou alteração de uma tabela ou cluster. Resumidamente, temos: ◊ PCTFREE - Especifica a percentagem de espaço, em cada bloco, a ser reservada para futuras atualizações de linhas da tabela (crescimento). O valor default para PCTFREE é de 10%. ◊ PCTUSED - Especifica o percentual mínimo de espaço usado que o Oracle mantém para cada bloco de dados de uma tabela. Um bloco torna-se candidato a novas inclusões quando a quantidade de espaço usado torna-se inferior a PCTUSED. O valor default para PCTUSED é de 40%. ◊ INITRANS - Indica o número de transaction entries alocadas inicialmente no header do bloco. O default é 1 para tabelas e 2 para clusters e índices. ◊ MAXTRANS - indica o número máximo de transações que podem fazer acesso ao bloco concorrentemente. OBS: ◊ Os parâmetros PCTFREE e PCTUSED permitem o controle de uso do espaço disponível para inclusões ou atualizações de linhas nos blocos de dados. ◊ Ambos os parâmetros podem ser atribuídos a tabelas, clusters e snapshots através dos comandos CREATE e ALTER. ◊ PCTFREE também pode ser especificado para índices. ◊ A soma de PCTFREE e PCTUSED deve ser menor ou igual a 100. PCTFREE Este valor indica a percentagem mínima de espaço a ser reservado (deixado livre) no bloco para possíveis atualizações de linha que já estejam contidas no bloco. Como características principais, temos: ◊ PCTFREE indica a percentagem de espaço não utilizável, reservado durante a fase de inserção para possíveis atualizações de linhas contidas no bloco. ◊ O valor default é de 10%. ◊ Após PCTFREE ser atingido, o bloco é considerado cheio e não é disponibilizado para novas inserções de linhas (sai da free list). ◊ O espaço remanescente no bloco após o valor de PCTFREE ter sido atingido é reservado para os comandos UPDATE em linhas de dentro do bloco. ◊ O parâmetro PCTFREE também pode ser especificado na criação ou alteração de índices. ◊ O espaço livre é preenchido pela inclusão de novas linhas, pelo crescimento das linhas existentes e pelo crescimento do header do bloco de dados. ◊ Quando especificado para índices, PCTFREE reserva espaço para inclusões e atualizações. Free space é calculado como uma porcentagem de DB_BLOCK_SIZE menos o header. No exemplo, quando a área livre atingir 20% (ou menos), o Oracle retirará este bloco da FREE LIST. O compromisso ideal de utilização de espaço e performance de I/O é uma soma de PCTFREE e PCTUSED que seja menor que 100% pela percentagem de espaço representado pelo tamanho médio de uma row. Por exemplo, suponhamos que um bloco tenha o tamanho de 2048 bytes menos 100 bytes de overhead, deixando 1948 bytes disponíveis para dados. Se o tamanho médio de uma linha é de 195 bytes (10% de 1948), então, uma combinação apropriada de PCTUSED e PCTFREE que some 90% resulta no melhor uso de espaço para o banco de dados. A fórmula a seguir formaliza a este resultado: PCTUSED + PCTFREE = (área para dados no bloco – tamanho médio de uma linha) * 100 área para dados no bloco EXEMPLO Neste exemplo utilizamos PCTFREE com 20% e PCTUSED com 40%. ◊As linhas são incluídas no bloco até que o total usado seja de 80%, já que 20% deve permanecer livre para atualização das linhas existentes. ◊A atualização das linhas existentes pode usar o espaço livre reservado no bloco. Nenhuma nova linha pode ser incluída até que a quantidade de espaço usado seja menor que 40%. ◊Quando a quantidade de espaço livre atinge 61% (aproximadamente), ou seja, a quantidade de espaço usado fica inferior a 40%, novas linhas podem ser incluídas novamente neste bloco. ◊Novamente retornamos à situação em que as inclusões podem ocorrer até que o total usado seja de 80%. Este ciclo continua indefinidamente. OBS: O incremento do parâmetro PCTFREE reduz a ocorrência de migração de linhas. Um valor muito pequeno para PCTFREE pode resultar na necessidade de migração de linhas. CONSIDERAÇÕES SOBRE PCTUSED E PCTFREE ◊Em um bloco recém alocado, a área disponível para inclusões corresponde ao tamanho do bloco menos o overhead (para o header) menos o valor de PCTFREE. ◊As atualizações, no bloco, podem utilizar a área livre, reduzindo a área disponibilizada para inclusões. ◊Para cada segmento de dados e de índice, o Oracle mantém uma ou mais FREE LISTS (listas de blocos de dados que tenham sido alocados para aquele segmento e que tenham espaço livre maior que PCTFREE). ◊Quando executamos um comando INSERT o ORACLE checa a FREE LIST da tabela para o primeiro bloco disponível e usa-o, se possível. ◊Se o espaço livre naquele bloco não é grande o suficiente para acomodar a nova linha e o bloco tem pelo menos PCTUSED, o ORACLE tira aquele bloco da FREE LIST. ◊Múltiplas FREE LISTS por segmento podem reduzir a contenção sobre as FREE LISTS quando inserts concorrentes ocorrerem. ◊Após executarmos um comando DELETE ou UPDATE, o ORACLE processa o comando e verifica se o espaço usado no bloco é, agora, menos que o PCTUSED. Se isto ocorrer, o bloco retorna para a FREE LISTe torna-se o primeiro bloco disponível a ser usado naquela transação. Quando a transação efetua um COMMIT, o espaço livre no bloco torna-se disponível para outras transações. ◊Dois tipos de comandos podem incrementar o espaço livre em um bloco de dados: DELE e UPDATE (quando diminui o valor de um campo). O espaço liberado estará disponível para subseqüentes comandos INSERTs se: o O comando INSERT estiver na mesma transação e for posterior ao comando que liberou o espaço. o O comando INSERT está em outra transação e a transação atual efetuou um COMMIT. ◊O espaço disponível no bloco pode ou não ser contínuo. O ORACLE junta este espaço no bloco de dados somente quando: o Um comando INSERT ou UPDATE tentou usar o bloco que contém o espaço disponível para adicionar um novo pedaço de linha e o espaço disponível está fragmentado impedindo que o pedaço de linha seja incluído em um trecho contínuo do bloco. O ORACLE somente realiza a compressão nesta situação porque, caso contrário, a performance do banco de dados poderia ser decrementada em função de um trabalho contínuo de compressão. ENCADEAMENTO E MIGRAÇÃO DE LINHAS Devemos monitorar (controlar) o encadeamento e migração para diminuir a possibilidade de degradação de performance. Em duas situações os dados de uma linha podem ser muito grandes para caberem em um único bloco: ◊No primeiro caso a linha é muito grande para caber no bloco de dados quando ela é incluída. Neste caso o Oracle armazena o dado para a linha em uma cadeia de blocos de dados (um ou mais). Este tipo de situação é comum quando temos colunas do tipo LONG / CLOB ou LONG RAW / BLOB. Neste caso não é possível ao DBA desfazer o encadeamento. ◊No segundo caso a linha, inicialmente, cabia no bloco e é atualizada (UPDATE), com seu tamanho sendo incrementado, não havendo espaço livre no bloco para a nova linha neste mesmo bloco. Neste caso o ORACLE migra o dado da linha inteira para um novo bloco (considerando-se que isto seja possível). O ORACLE preserva um pedaço da linha original da linha migrada a fim de apontar para o novo bloco contendo o restante da linha. O rowid da linha migrada não se modifica. ENCADEAMENTO ◊ Uma linha que não cabe dentro de um único bloco pode ser encadeada através de múltiplos blocos. ◊ A parte inicial da linha permanece no bloco onde a linha for inicialmente incluída. ◊ Cada pedaço adicional é colocado em um bloco encadeado. MIGRAÇÃO ◊ Uma linha migrada é movida inteiramente de seu bloco original para um bloco encadeado. ◊ Uma row migra quando uma operação de atualização modifica a linha de tal forma que ela precisa de mais espaço que aquele disponibilizado no bloco. ◊ Ao usarmos um valor alto em PCTFREE estaremos diminuindo a probabilidade de migração de linhas. OBS: conjuntos de colunas NULL (trailing NULL columns) ao fim de uma linha não são armazenados se adicionados à estrutura da tabela usando-se o comando ALTER TABLE. Quando uma linha é encadeada ou migrada, a performande (I/O) associada com esta linha decrementa porque o ORACLE deve pesquisar mais de um bloco de dados para recuperar toda a informação para a linha. EXTENSÕES Uma extensão é um conjunto contínuo de blocos de dados. Poderíamos dizer que uma extensão é uma unidade lógica de alocação de espaço no banco de dados de um número contínuo de blocos de dados. Uma ou mais extensões compõem um segmento. Quando o espaço existente para um determinado segmento é completamente usado, o Oracle aloca uma nova extensão para aquele segmento. CARACTERÍSTICAS ◊ Cada segmento no banco de dados é criado com pelo menos uma extensão para comportar seus dados. O segmento de rollback, porém, sempre possui pelos menos duas extensões. ◊ A primeira extensão de cada segmento é chamada de initial extent. ◊ Subseqüentes extensões são chamadas de extensões incrementais. ◊ Um objeto irá alocar somente uma nova extensão se todas as áreas nas extensões alocadas atualmente estiverem preenchidas. ◊ Freqüentemente a de-allocation de extensões pode levar à fragmentação do tablespace. ◊ A INITIAL EXTENT é um pedaço de espaço pré-reservado dentro do banco de dados. Quando a INITIAL EXTENT é preenchida, a NEXT EXTENT é alocada. Tablespace Bloco Oracle Extensão Segmento QUANDO AS EXTENSÕES SÃO ALOCADAS ◊ Quando um segmento é criado, o espaço inicial é alocado (INITIAL EXTENT). Apesar de nenhuma linha ter sido incluída no segmento, aquela área (INITIAL) já está reservada para ele. ◊ Quando o segmento cresce, há necessidade de alocarmos mais espaço para ele (NEXT EXTENT). Esta operação é automatica e realizada com o banco de dados em atividade. Com a finalidade de manutenção, o bloco HEADER de cada segmento contém um diretório das extensões alocadas para aquele segmento. ◊ A tabela ou cluster é alterada para alocar extensões. OBS: ◊Os segmentos de ROLLBACK possuem pelo menos duas extensões. ◊As informações que estudaremos neste capítulo consideram o uso de operações seriais no qual um processo servidor efetua parse e execute de um comando de SQL. As extensões são alocadas de forma diferente para comandos que são executados em paralelo. QUANDO AS EXTENSÕES SÃO LIBERADAS Em geral as extensões de um segmento não retornar para o TABLESPACE até que façamos a remoção do segmento (por exemplo DROP TABLE...). Existem, no entanto, outras situações em que isto é possível. A lista de possibildiades é a seguinte: ◊ O segmento ou cluster é removido (drop). ◊ Quando executamos um comando TRUNCATE....DROP STORAGE estamos liberando as extensões para o ambiente. ◊ O segmento é maior que o necessário (optimal) e contém extensões livres (somente para segmentos de rollback). Esta ação é realizada, automaticamente, pelo ORACLE. ◊ O DBA pode liberar extensões não usadas usando o comando ALTER TABLE xxx DEALLOCATE UNUSED. Quando extensões são liberadas o ORACLE modifica o BITMAO no DATAFILE (para LOCALLY MANAGED TABLESPACES) ou atualiza o dicionário de dados (para DICTIONARY MANAGED TABLESPACES) a fim de refletir a situação atual. Qualquer dado em bloco pertencente à extensão liberada torna-se inacessível. O ORACLE “limpará” este dado quando o bloco for realocado e reutilizado. CONSIDERAÇÕES SOBRE EXTENSÕES EXTENSÕES EM TABELAS NÃO CLUSTERED Para esta forma de alocação a extensão associada a uma tabela pertence a ela até que venhamos a remover ou truncar a tabela. Mesmo que a extensão fique totalmente sem uso (em funções de exclusões , por exemplo), o espaço não é retornado para o tablespace. Se removermos a tabela (DROP), o espaço anteriormente reservado para ela pode ser requisitado por outra extensão que necessite de espaço livre. Em tablespaces gerenciados através do dicionário de dados, quando um segmento necessita de uma extensão maior que aquelas disponíveis, o Oracle identifica (na tabela do dicionário de dados correpondente) e combina extensões contínuas para formar uma extensão mais. Esta operação chama-se COALESCE. Para tablespaces gerenciados localmente, esta operação não é necessária uma vez que isto já é realizado automaticamente pelo BITMAP. EXTENSÕES EM TABELAS CLUSTERED Tabelas armazenadas em CLUSTERs armazenam suas informações em segmentos de dados do CLUSTER. Assim, se removermos uma tabela de um CLUSTER, o segmento de dados permanece reservado para o CLUSTER. Como alternativa podemo TRUNCAR um CLUSTER (exceto HASH) para liberarmos extensões. EXTENSÕES EM VIEWS MATERIALIZADAS E SEUS LOGS O ORACLE libera as estensões das views materializadas e de seus logs (chamados de snapshots e snapshot logs em ambientes distribuídos) da mesma maneira que para tabelas e clusters. EXTENSÕES EM ÍNDICES Todas as extensões alocadas para um índice permanecem associadas a ele enquanto o mesmo existir. Somente quando remover o índice ou a tabela ao qual este está associado, as extensões reservadas para ele são retornadas ao TABLESPACES. REGRAS DE PRECEDÊNCIA ◊ Qualquer parâmetro especificado ao nível de objeto se sobrepõe à opção correspondente atribuída ao nível de tablespace. ◊ Quando os parâmetros de armazenamento não são explicitamente atribuídos ao nível de objeto, o valor utilizado para eles é aquele especificado ao nível de tablespace. ◊ Quando os parâmetros de armazenamento não são explicitamente atribuídos ao nível de tablespace, o ORACLE aplica os valores defaults do sistema. ◊ Se os parâmetros de armazenamento forem alterados, as novas opções se aplicam somente a extensões ainda não alocadas. ◊ O parâmetro OPTIMAL só é especificado para segmentos de rollback. ◊ Os parâmetros FREELISTS e FREELIST GROUPS não podem ser especificados como defaults para tablespaces. SIGNIFICADO DOS PARÂMETROS ◊INITIAL – Tamanho em BYTES da primeira extensão alocada para o segmento. O valor default é o equivalente a 5 blocos de dados. Podemos usar as letras K ou M para especificar Kbytes ou Mbytes. O mínimo é de 2 blocos para segmentos não BITMAP e 3 blocos para segmentos de BITMAPS adicionado de um para cada FREE LIST que viermos a especificar. O valor máximo depende do sistema operacional. O ORACLE arredonda os valores para um número inteiro de blocos para tamanhos de INITIAL inferiores a 5 blocos de dados e arredonda para valores múltiplos de 5 blocos de dados para tamanhos de INITIAL superiores a 5 blocos de dados. OBS : Não podemos especifica INITIAL para o comando ALTER. ◊NEXT – Tamanho em bytes das próximas extensões alocadas para o segmento. O valor default é o equivalente a 5 blocos de dados. Podemos usar as letras K ou M para especificar Kbytes ou Mbytes. O valor default é de 5 blocos. O tamanho mínimo é de um bloco de dados. O tamanho máximo depende do sistema operacional. O ORACLE arredonda o tamanho para o próximo múltiplo de bloco para valores menores que 5 blocos de dados e arredonda um valor que minimize a fragmentação para tamanhos maiores que 5 blocos de dados. Se modificarmos o valor do parâmetro NEXT (em um comando ALTER), a próxima extensão alocada terá este valor, independentemente do valor da última extensão alocada e de PCTINCREASE. ◊PCTINCREASE – Percentual que cada extensão cresce em relação à última extensão alocada (a partir da terceira). O valor default é 50%, o que significa que a próxima extensão terá valor 50% maior que a precedente. O valor mínimo para este parâmetro é 0, indicando que todas as extensões terão o mesmo tamanho. O ORACLE arredonda o valor calculado para que a próxima extensão seja múltipla do tamanho de bloco. Se modificarmos o valor deste parâmetro (em um comando ALTER), o ORACLE calcula o tamanho da próxima extensão baseado neste valor e no valor da última extensão alocada. Podemos impedir o processo SMON de efetuar a operação de COALESCE quando preenchemos este valor com zero. A ORACLE CORPORATION recomenda o uso de zero neste parâmetro como uma forma de minimizarmos a fragmentação e impedirmos a possibilidade de gerarmos segmentos temporários grandes durante o processamento. OBS: Segmentos de rollback não usam este parâmetro. O valor é sempre zero. ◊MINEXTENTS – Número total de extensões a serem alocadas quando o segmento é criado. O valor default e mínimo é 1 , sendo que para segmentos de rollback o mínimo é 2. O valor máximo depende do sistema operacional. Este parâmetro permite a alocação de uma grande quantidade de espaço quando criarmos um objeto, mesmo se o espaço disponível não for contínuo. Se o valor deste parâmetro for maior que 1, o Oracle calcula o tamanho das extensões subseqüentes baseado no valor dos parâmetros INITIAL, NEXT e PCTINCREASE. OBS: Não podemos especificar este parâmetro no comando ALTER. ◊MAXEXTENTS - Número total de extensões que podem ser alocadas para o segmento, incluindo a INITIAL. O máximo depende do tamanho do bloco do ORACLE. O valor mínimo é 1 (exceto para segmentos de ROLLBACK, que possui um mínimo de 2). O valor default depende do tamanho do bloco em uso. Se usarmos a palavra chave UNLIMITED estaremos indicando que extensões devem ser alocadas, automaticamente, sempre que necessário. A ORACLE CORPORATION recomenda a utilização desta opção como uma forma de minimizar fragmentação. Não devemos, no entanto, utilizar esta palavra chave em segmentos de ROLLBACK, uma vez que transações muito longas contendo INSERTS, UPDATES ou DELETES que prossigam por um longo tempo continuarão a criar novas extensões até o disco ficar cheio. OBS: Para um segmento de ROLLBACK criado sem a especificação da cláusula STORAGE, os parâmetros referentes a armazenamento serão herdados do TABLESPACE. Assim, se tivermos criado o TABLESPACE com a indicação do número máximo de extensões ilimitado, o segmento de ROLLBACK terá o mesmo default. ◊OPTIMAL - Especifica um tamanho ótimo em bytes para segmentos de ROLLBACK. Podemos usar as letras K ou M para indicar Kbytes ou Mbytes. O ORACLE tenta manter este tamanho através da liberação de área quando seus dados não são mais necessários para as transações ativas. O Oracle libera tantas extensões quantas forem necessárias para que o segmento de ROLLBACK retorne ao valor de OPTIMAL especificado. Se especificarmos NULL para um segmento de ROLLBACK, indicamos que o ORACLE NUNCA deverá liberar as extensões alocadas. Este é o valor default. O valor de OPTIMAL não pode ser menor que o espaço alocado inicialmente para o segmentomento de ROLLBACK especificado pelos parâmetros MINEXTENTS, INITIAL, NEXT e PCTINCREASE. O tamanho máximo depende do sistema operacional. O ORACLE arredonda o valor para o próximo múltiplo de bloco. EXEMPLO No exemplo a seguir criamos uma tabela e determinamos valores para os parâmetros de armazenamento. COMENTÁRIOS: ◊O valor do parâmetro MINEXTENTS é 1, sendo assim, apenas uma extensão será reservada para a tabela a tempo de criação. ◊O tamanho desta extensão inicial é de 100K. ◊Se a tabela crescer, o Oracle alocará uma segunda extensão com o tamanho de 50K. ◊Se os dados da tabela crescerem a ponto da segunda extensão ser totalmente preenchida, o Oracle aloca uma terceira extensão. Como o valor de PCTINCREASE default é 50%, a terceira extensão terá comprimento 50 + 25 = 75 Kbytes. Se considerarmos que o tamanho do bloco Oracle seja 2K, seu tamanho será de 76Kbytes. ◊Como o valor de MAXEXTENTS é 50, no máximo 50 extensões poderão ser alocadas para a tabela SQL> CREATE TABLE teste 2 (codigo NUMBER(2), 3 nome VARCHAR2(14), 4 sobrenome VARCHAR2(13) 5 ) 6 STORAGE (INITIAL 100K 7 NEXT 50K 8 MINEXTENTS 1 9 MAXEXTENTS 50 10 ) 11 / Tabela criada. COMO AS EXTENSÕES SÃO ALOCADAS O ORACLE usa diferentes algoritmos para a alocação de extensões, de acordo com o tipo de gerenciamento do TABLESPACE. TABLESPACES GERENCIADOS LOCALMENTE Para este tipo de tablespace, o Oracle verifica a área livre determinando um DATAFILE candidato e pesquisando no BITMAP do arquivo pelo número de blocos livres adjacentes. Se não houve espaço no DATAFILE escolhido, ele escolhe outro DATAFILE. TABLESPACES GERENCIADOS PELO DICIONÁRIO DE DADOS Neste tipo de tablespace a pesquisa por área livre é bem mais complexa. 1. O ORACLE pesquisa por área livre com o tamanho definido por NEXT usando o seguinte algoritmo: ◊O ORACLE pesquisa por um conjunto de blocos de dados contíguo que tenha o tamanho de NEXT mais um bloco a fim de reduzir a fragmentação interna (o tamanho é arredondado para o tamanho da extensão mínima para o tablespace, se necessário). Por exemplo se a nova extensão deve ter o tamano de 19 blocos de dados, o ORACLE procura por um espaço do tamanho de 20 blocos de dados contíguos. Se a nova extensão possui 5 blocos ou menos, o ORACLE não adicionar o bloco extra. ◊Se não for encontrada uma área com o tamanho necessário, ele pesquisa por uma área contígua maior que a quantidade necessária. Se ele encontrar um grupo de blocos contíguos que tenha pelo menos o tamanho de 5 blocos a mais que o tamanho procurado, ele divide o conjunto e reserva a extensão com o tamanho definido. Se ele encontrar um grupo de bloco que tenha um tamanho maior que o necessário, porém com uma diferença inferior a 5 blocos, ele aloca todos os blocos contíguos para a nova extensão. ◊Se ele não encontrar um conjunto de blocos contíguos maior ou igual ao tamanho definido, ele aciona o algoritmo de COALESCE e volta ao ponto inicial da pesquisa. ◊Se a extensão não puder ser alocada na segunda pesquisa, o ORACLE tenta redimensionar o DATAFILE se a opção de extensão (AUTOEXTEND) estiver autorizada. Caso isto não seja possível, ele retorna um erro. 2. Após a alocação da área o ORACLE atualiza o HEADER do segmento e o dicionário de dados para que reflitam a modificação ocorrida. Os blocos da extensão recém alocada, apesar de estarem livres, podem não estar vazios dos dados anteriores. Normalmente, o ORACLE não formata os blocos desta nova extensão no momento da alocação, somente quando necessário. Em algumas situações, no entanto, esta ação é feita, como ocorre quando o DBA força a alocação usando a opção ALLOCATE EXTENT dos comandos ALTER TABLE ou ALTER CLUSTER. OBTENDO INFORMAÇÕES Ao pesquisarmos as views do dicionário de dados obteremos informações sobre as extensões alocadas e sobre a quantidade de segmentos existentes. VIEWS DO DICIONÁRIO DE DADOS ◊ USER/DBA_EXTENTS ◊ USER/DBA_FREE_SPACE ◊ USER/DBA_SEGMENTS ◊ USER/DBA_TABLESPACES ◊ DBA_DATA_FILES JUNTANDO O ESPAÇO DISPONÍVEL O espaço tablespaces é gerenciado usando-se extensões, que são compostas de um número específico de blocos de dados contíguos. Se um grande número de extensões livres estiver fragmentado ou existirem vários pequenos espaços contíguos, o tablespace pode ser COALESCED. Isto é feito, por default, pelo System Monitor (SMON), mas também podemos acelerar o processo usando o comando de SQL ALTER TABLESPACE com a cláusula COALESCE. Este comando não modifica fisicamente o tablespace, apenas sua definição, juntando na tabela DBA_FREE_SPACE a percepção de blocos contínuos mostrados como mais de um registro. SQL> ALTER TABLESPACE users COALESCE; Espaço de tabela alterado. SEGMENTOS DE DADOS Os segmentos de dados (data segments) contêm os dados que foram incluídos em: ◊Tabelas ◊Partições de tabelas particionadas ◊Tabelas armazenadas em clusters ◊Tabelas do tipo NESTED O ORACLE cria estes segmentos de dados quando criamos a tabela ou cluster com o comando CREATE. Os parâmetros da cláusula STORAGE para uma tabela ou CLUSTER determinam como as extensões dos segmentos de dados são alocadas. Podemos especificar estes parâmetros, diretamente, com o comando CREATE ou ALTER correspondente. OBS: ◊ Clusters definem uma estrutura de armazenamento. ◊ Cada cluster contém uma ou mais definições de tabelas. ◊ Hash clusters podem conter mais de uma tabela, mas é raro. ◊ Tabelas em um cluster são usadas (consultadas, atualizadas, etc) por um usuário como se fossem tabelas isoladas. SEGMENTOS DE ÍNDICES Segmentos de índices contém os índices criados para aumento de performance na recuperação dos dados. Cada índice não particionado no banco de dados possui um único segmento de índice para acomodar todos os seus dados. Para um índice particionado, cada partição possui seu próprio segmento de índice. O ORACLE cria um segmento de índice para um índice ou partição quando executamos o comando CREATE INDEX. Neste comando podemos especificar os parâmetros de armazenamento para as extensões do segmento e o tablespace no qual o índice é criado. Para o índice de um LOB, a especificação das características físicas é feita com o uso da cláusula LOB na criação da tabela. O SEGMENTO DE ROLLBACK Um segmento de ROLLBACK é uma porção do banco de dados que registra as imagens dos dados antes da modificação pela transação, permitindo que as alterações sejam desmanchadas (ou desfeitas) sob certas circunstâncias. Uma transação é uma unidade de trabalho que causa modificações sobre o banco de dados. A cada transação é dado um único identificador e associado um e somente um segmento de ROLLBACK. Um segmento de ROLLBACK é um objeto circular em que cada transação pode ter várias entradas. CARACTERÍSTICAS ◊ Permite que as modificações feitas por uma transação sejam totalmente ou parcialmente (SAVEPOINT) desfeitas em caso de erro de operação. ◊ Provê o mecanismo de READ CONSISTENCY. ◊ São necessários para a recuperação do banco de dados em caso de catástrofe (DATABASE RECOVERY). ◊ Podem registrar modificações (sobre dados) feitas em qualquer tablespace, com exceção do segmento de rollback SYSTEM. ◊ Pode registrar alterações para diversas transações. ◊ Deve existir um segmento de rollback chamado SYSTEM no tablespace SYSTEM. ◊ É necessário pelo menos um segmento de ROLLBACK extra se diversos tablespaces forem usados. SEGMENTOS TEMPORÁRIOS Os segmentos temporários têm a finalidade de reservar espaço para operações de ordenação (sort). O ORACLE não cria um segmento se a operação de ordenação puder ser feita em memória ou se encontrar alguma outra forma de obter o mesmo resultado usando índices. As seguintes operações podem precisar de segmentos temporários: ◊CREATE INDEX ◊SELECT ... ORDER BY ◊SELECT DISTINCT ... ◊SELECT ... GROUP BY ◊SELECT ... UNION ◊SELECT ... INTERSECT ◊SELECT ... MINUS Algumas operações de JOIN e subqueries correlacionadas podem também requerer o uso de um segmento temporário. Se as aplicações freqüentemente executam comandos apresentados anteriormente, o DBA pode desejar incrementar a performance ajustando o parâmetro de inicialização SORT_AREA_SIZE. Um outro motivo para a alocação de segmento temporário é a criação de tabelas e índices temporários. Como estas tabelas armazenam dados somente durante a transação ou sessão, o ORACLE aloca estes dados em segmentos temporários. LIBERANDO ESPAÇO DISPONÍVEL Não é incomum alocarmos espaço para um segmento e posteriormente verificarmos que este espaço não está sendo usado. Assim, é possível que desejemos liberar espaço não usado para que outros segmentos possam fazer uso desta área. ◊ Dentro de um segmento existe uma marca d'água (high water mark) que separa a parte de espaço usado da parte não usada. ◊ Se excluirmos linhas de uma tabela, a marca d'água não é movida. ◊ A marca d'água pode ser encontrada usando a procedure UNUSED_SPACE do pacote DBMS_SPACE. ◊ Se um segmento estiver completamente vazio, o espaço pode ser liberado usando-se o comando de SQL TRUNCATE DROP STORAGE. O PACKAGE DBMS_SPACE Este package é composto de procedures relativas a utilização de espaço em objetos do banco de dados: ◊ UNUSED_SPACE - determina o espaço sem uso em um objeto (table, index, cluster). DBMS_SPACE.UNUSED_SPACE ( segment_owner IN VARCHAR2, segment_name IN VARCHAR2, segment_type IN VARCHAR2, total_blocks OUT NUMBER, total_bytes OUT NUMBER, unused_blocks OUT NUMBER, unused_bytes OUT NUMBER, last_used_extent_file_id OUT NUMBER, last_used_extent_block_id OUT NUMBER, last_used_block OUT NUMBER, partition_name IN VARCHAR2 DEFAULT NULL); ◊ FREE_BLOCKS - fornece informações sobre blocos livres em um objeto (table, index, cluster). DBMS_SPACE.FREE_BLOCKS ( segment_owner IN VARCHAR2, segment_name IN VARCHAR2, segment_type IN VARCHAR2, freelist_group_id IN NUMBER, free_blks OUT NUMBER, scan_limit IN NUMBER DEFAULT NULL, partition_name IN VARCHAR2 DEFAULT NULL); EXEMPLO SQL> VARIABLE MSG VARCHAR2(1000) SQL> SET AUTOPRINT ON SQL> DECLARE 2 total_blocks NUMBER; 3 total_bytes NUMBER; 4 unused_blocks NUMBER; 5 unused_bytes NUMBER; 6 last_used_extent_file_id NUMBER; 7 last_used_extent_block_id NUMBER; 8 last_used_block NUMBER; 9 BEGIN 10 DBMS_SPACE.UNUSED_SPACE ( 11 'ALUNO', -- segment_owner 12 'FUNC', -- segment_name 13 'TABLE', -- segment_type 14 total_blocks, 15 total_bytes, 16 unused_blocks, 17 unused_bytes, 18 last_used_extent_file_id, 19 last_used_extent_block_id, 20 last_used_block, 21 NULL); -- partition_name 22 :MSG := 'total_blocks = '||total_blocks||chr(10)|| 23 'total_bytes = '||total_bytes||chr(10)|| 24 'unused_blocks = '||unused_blocks||chr(10)|| 25 'unused_bytes = '||unused_bytes||chr(10)|| 26 'last_used_extent_file_id = '|| 27 last_used_extent_file_id||chr(10)|| 28 'last_used_extent_block_id = '|| 29 last_used_extent_block_id||chr(10)|| 30 'last_used_block = '||last_used_block||chr(10); 31 END; 32 / Procedimento PL/SQL concluído com sucesso. MSG ------------------------------------------------------------------- total_blocks = 4 total_bytes = 8192 unused_blocks = 0 unused_bytes = 0 last_used_extent_file_id = 2 last_used_extent_block_id = 823 last_used_block = 2 A OPÇÃO DEALLOCATE UNUSED Podemos usar o comando ALTER TABLE para liberar espaço não usado. A opção DEALLOCATE UNUSED libera espaço ao final de uma tabela, partição ou subpartiçao, segmento de overflow, segmento de LOB ou LOB index e o disponibiliza para outros segmentos no tablespace. Somente podemos liberar espaço acima da marca d’água (porção do objeto nunca utilizada, os blocos de dados ainda não foram formatados para recebe dados). A marca d'água não se modifica quando disponibilizamos espaço. ◊ Se não desejarmos liberar todos os espaços não usados, devemos usar a cláusula KEEP. ◊ Devemos verificar se o espaço disponível foi liberado através da view DBA_FREE_SPACE (colunas EXTENT_ID, FILE_ID e BLOCK_ID). ◊ ALTER TABLE XXX DEALLOCATE UNUSED - Se minextents for 1, o resultado será a reserva de 2 extensões com 10K cada. Se minextents for 2, o resultado é o mesmo anterior, mas se minextents é 3, nada é liberado. ◊ ALTER TABLE XXX DEALLOCATE UNUSED KEEP 10K - Neste caso serão preservados 10K acima da marca d'água. O resultado será composto de 2 extensões, sendo a primeira de 10K e a segunda de 20K. Se minextents for 3, este comando causará a modificação do minextents para 2. EXT1 10K EXT2 20K EXT3 30K 40K = Unused Space