Como faço para redimensionar o espaço de tabela da minha instância de banco de dados Amazon RDS para Oracle?

Breve descrição

O tipo de espaço de tabela padrão para instâncias de banco de dados Amazon RDS para Oracle é bigfile. Não é uma prática recomendada usar espaços de tabela de smallfile para suas instâncias do RDS for Oracle.

Uma instância do RDS para Oracle que usa um espaço de tabela smallfile tem as seguintes limitações:

• Como o comando ALTER DATABASE não é suportado no RDS for Oracle, não é possível executar a consulta ALTER DATABASE para redimensionar ou alterar as configurações do arquivo de dados. Para obter mais informações, consulte Limitações dos privilégios do Oracle DBA.
• Você deve gerenciar manualmente o parâmetro db_files para definir o número máximo de arquivos de dados no banco de dados. Quando o número de arquivos de dados estiver próximo desse limite, altere o parâmetro db_files.

Se você não especificar um tamanho de arquivo de dados, o AUTOEXTEND ON será ativado por padrão ao criar espaços de tabela. O tamanho máximo dos espaços de tabela bigfile é 16 TiB (tebibytes). Quando você insere dados no espaço de tabela, o espaço de tabela aumenta até o limite máximo configurado que o espaço de tabela exige. Ou o espaço de tabela aumenta até o limite máximo de armazenamento alocado para a instância do RDS.

Se o armazenamento alocado para a instância do RDS estiver cheio, a instância mudará para o estado STORAGE_FULL e os espaços de tabela não poderão ser estendidos. Para corrigir esse problema, você deve adicionar espaço de armazenamento à sua instância. Para obter mais informações, consulte Como resolver problemas que ocorrem quando instâncias de banco de dados do Amazon RDS esgotam o armazenamento?

Quando os dados são excluídos de um espaço de tabela, o tamanho do espaço de tabela não diminui. Os blocos livres podem ser reutilizados quando novos dados são inseridos. Você deve redimensionar manualmente o espaço de tabela para recuperar o espaço não utilizado.

Resolução

Para redimensionar o espaço de tabela da sua instância do RDS for Oracle, conclua as etapas a seguir.

Verifique a configuração do espaço de tabela

Conclua as seguintes etapas:

1. Para identificar os tipos de espaço de tabela, execute uma consulta semelhante ao exemplo a seguir:

   SQL> SELECT TABLESPACE_NAME, CONTENTS, BIGFILE FROM DBA_TABLESPACES;

   Observe que os tipos de espaço de tabela podem ser permanentes, desfeitos, temporários, smallfil ou bigfile.
2. Para verificar o tamanho do arquivo de dados, o limite máximo configurado e verificar se o atributo de extensão automática está ativado, execute as consultas a seguir. Para espaços de tabela permanentes e desfeitos, execute a seguinte consulta:

   SQL> SELECT TABLESPACE_NAME, ROUND(SUM(BYTES)/(1024*1024*1024),2) SUM_GB, ROUND(MAXBYTES/(1024*1024*1024),2) MAX_GB, AUTOEXTENSIBLE FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME, MAXBYTES,AUTOEXTENSIBLE;

   Para espaços de tabela temporários, execute a seguinte consulta:

   SQL> SELECT TABLESPACE_NAME, ROUND(SUM(BYTES)/(1024*1024*1024),2) SUM_GB, ROUND(MAXBYTES/(1024*1024*1024),2) MAX_GB, AUTOEXTENSIBLE FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME, MAXBYTES,AUTOEXTENSIBLE;

É possível ver os seguintes detalhes na saída dessas consultas:

• Se o atributo de extensão automática não estiver ativado, o valor de MAX_GB será igual a 0.
• Se o tipo de espaço de tabela for smallfile, o valor de MAX_GB dependerá do tamanho do bloco usado para criar o espaço de tabela. Por exemplo, se o tamanho do bloco usado for 8K, o valor de MAX_GB será 32 GB. Para obter mais informações, consulte Tamanhos de blocos não padrão no site da Oracle.
• Se o tipo de espaço de tabela for bigfile, o valor de MAX_GB aparecerá como 32 TB. O tamanho máximo de um único arquivo no RDS para instâncias de banco de dados Oracle é 16 TiB.

Para recuperar a linguagem de descrição de dados (DDL) do espaço de tabela, execute as seguintes consultas:

SQL> SET LINESIZE 400

SQL> SET LONG 99999

SQL> SELECT DBMS_METADATA.GET_DDL('TABLESPACE','example-tablespace') FROM DUAL;

É possível recuperar as informações necessárias sobre o espaço de tabela do DDL retornado.

Aumentar o tamanho do espaço de tabela

Se você ativar o atributo de extensão automática, não precisará aumentar o tamanho do espaço de tabela.

No entanto, ao ativar o atributo de extensão automática e redimensionar o espaço de tabela, você deve preencher os seguintes requisitos.

Para espaços de tabela bigfile, para redimensionar o espaço de tabela, execute o comando ALTER TABLESPACE:

SQL> ALTER TABLESPACE example-tablespace RESIZE 50G;

Para obter mais informações, consulte ALTER TABLESPACE no site da Oracle. Especifique o tamanho em kilobytes (K), megabytes (M), gigabytes (G) ou terabytes (T). O espaço de tabela bigfile tem um único arquivo de dados e o comando ALTER TABLESPACE redimensiona o arquivo de dados que pertence ao espaço de tabela.

Para espaços de tabela smallfile, adicione arquivos de dados para aumentar o tamanho do espaço de tabela:

SQL> ALTER TABLESPACE example-tablespace ADD DATAFILE SIZE 1G AUTOEXTEND ON;

Para redimensionar um arquivo de dados original em um espaço de tabela smallfile, execute rdsadmin_util.resize_datafile:

SQL> select file_id,file_name, bytes/1024/1024/1024 gb from dba_data_files where tablespace_name='TEST';

Sua saída é semelhante a esta:

FILE_ID FILE_NAME                                                   GB
=======================================================================
6      /rdsdbdata/db/TESTDB_A/datafile/o1_mf_test_m03xlfq8_.dbf      1
SQL> exec rdsadmin.rdsadmin_util.resize_datafile(6,'2G')

PL/SQL procedure successfully completed.
SQL> select file_id,file_name, bytes/1024/1024/1024 gb from dba_data_files where tablespace_name='TEST';
FILE_ID FILE_NAME                                                     GB
=========================================================================
 6      /rdsdbdata/db/TESTDB_A/datafile/o1_mf_test_m03xlfq8_.dbf      2

Reduza o tamanho do espaço de tabela

Para reduzir o tamanho de um espaço de tabela smallfile, conclua as seguintes etapas:

1. Para reduzir o tamanho de um espaço de tabela smallfile, execute rdsadmin_util.resize_datafile.
   Observação: não é possível reduzir o tamanho de um arquivo de dados para um valor menor que a marca d'água alta do arquivo de dados.
2. Crie um novo espaço de tabela e configure o espaço conforme necessário. Em seguida, mova manualmente todos os seus dados para o novo espaço de tabela.

Se seu espaço de tabela for bigfile, para diminuir o tamanho do espaço de tabela com base no seu tipo de espaço de tabela, escolha um dos métodos a seguir.

Para espaços de tabela permanentes, não é possível diminuir o tamanho de um espaço de tabela permanente para um valor menor que a marca d'água alta do espaço de tabela. Quando você tenta redimensionar um espaço de tabela permanente, sua operação de redimensionamento falha. Em seguida, aparece o seguinte erro:

ORA-03297: file contains used data beyond requested RESIZE value

No entanto, para diminuir o tamanho do espaço de tabela para 40 GB quando o espaço de tabela tiver o tamanho de 50 GB e uma marca d'água alta de 40 GB, execute a seguinte consulta:

SQL> ALTER TABLESPACE example-tablespace RESIZE 40G;

Se você não conseguir diminuir o tamanho do espaço de tabela para um valor menor que a marca d'água alta, considere as seguintes opções:

• Reorganize os objetos no espaço de tabela.
• Crie um novo espaço de tabela e mova todos os objetos para o novo espaço de tabela.
• Abandone o espaço de tabela antigo.

Para espaços de tabela temporários, para diminuir o tamanho do espaço de tabela temporário, execute o comando SHRINK:

SQL> ALTER TABLESPACE example-tablespace SHRINK SPACE KEEP 100M;

Para redimensionar os espaços de tabela temporários em uma réplica de leitura, execute rdsadmin.rdsadmin_util.resize_temp_tablespace:

SQL> EXEC rdsadmin.rdsadmin_util.resize_temp_tablespace('example-tablespace','100M');

-or-

Crie outro espaço de tabela temporário e configure o tamanho necessário. Em seguida, defina o novo espaço de tabela temporário como o espaço de tabela temporário padrão.

1. Para visualizar o espaço de tabela temporário padrão atual, realize a seguinte consulta:

   SQL> SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

   Para obter mais informações, consulte Exibindo informações sobre espaços de tabela no site da Oracle.

2. Para criar um novo espaço de tabela temporário, execute a seguinte consulta:

   SQL> CREATE TEMPORARY TABLESPACE example-tablespace TEMPFILE SIZE 100M;

3. Para definir o novo espaço de tabela temporário como o espaço de tabela temporário padrão, execute a seguinte consulta:

   SQL> EXEC RDSADMIN.RDSADMIN_UTIL.ALTER_DEFAULT_TEMP_TABLESPACE(TABLESPACE_NAME => 'example-tablespace');

Para modificar o espaço de tabela temporário para um usuário específico, conclua as seguintes etapas:

1. Para visualizar o espaço de tabela temporário padrão atual para o usuário, execute a seguinte consulta:

   SQL>  SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS where USERNAME='example_username';

2. Para alterar o espaço de tabela temporário padrão para o usuário, realize a seguinte consulta:

   SQL> ALTER USER example_username TEMPORARY TABLESPACE example-tablespace;

Para espaços de tabela desfazer, use o comando ALTER TABLESPACE para diminuir o tamanho do espaço de tabela desfazer.

Para identificar o espaço de tabela desfazer que está atualmente em uso, realize a seguinte consulta:

SQL> SHOW PARAMETER UNDO_TABLESPACE;

Para diminuir o tamanho do espaço de tabela desfazer, execute uma consulta semelhante ao exemplo de consulta a seguir:

Se a consulta não for executada com êxito, conclua as seguintes etapas:

1. Crie um novo espaço de tabela desfazer:

   SQL> CREATE UNDO TABLESPACE example-new-tablespace DATAFILE SIZE 1G AUTOEXTEND ON MAXSIZE 100G;

2. Defina o espaço de tabela recém-criado como o espaço de tabela desfazer padrão:

   UNDO_TABLESPACE = example-new-tablespace

   Defina o parâmetro de inicialização UNDO_TABLESPACE no grupo de parâmetros para apontar para o espaço de tabela recém-criado.
   Para obter mais informações, consulte Trabalhar com grupos de parâmetros.
   O parâmetro de inicialização UNDO_TABLESPACE é um parâmetro dinâmico e não resulta em nenhum tempo de inatividade quando você aplica a modificação. No entanto, é uma prática recomendada reinicializar a instância de banco de dados depois de fazer uma alteração. Para obter mais informações, consulte Gerenciar desfazer no site da Oracle.
   Para verificar se o novo espaço de tabela desfazer é o padrão, execute a seguinte consulta:

   SQL> SHOW PARAMETER UNDO_TABLESPACE;

3. Para excluir o antigo espaço de tabela desfazer, realize uma consulta do espaço de tabela drop:

   SQL> DROP TABLESPACE example-tablespace INCLUDING CONTENTS AND DATAFILES;

Informações relacionadas

Como criar alarmes do CloudWatch para monitorar o espaço de armazenamento gratuito do Amazon RDS e evitar problemas de armazenamento cheio?

Gerenciando a capacidade automaticamente com o escalonamento automático de armazenamento do Amazon RDS

Por que minha instância de banco de dados do Amazon RDS para Oracle está usando mais armazenamento que o esperado?