How do I resize the tablespace for my Amazon RDS for Oracle DB instance?

Short description

The default tablespace type for an Amazon RDS for Oracle DB instances is bigfile. It isn't a best practice to use smallfile tablespaces for your RDS for Oracle DB instances.

An RDS for Oracle DB instance that uses a smallfile tablespace has the following limitations:

• You can't run the ALTER DATABASE command to resize or change the datafile configurations. For more information, see Limitations for DBA privileges in RDS for Oracle.
• You must manually manage the db_files parameter to define the maximum number of datafiles in the database. If the number of datafiles is close to its quota, then you must change the db_files parameter.

When you create tablespaces and don't specify a datafile size, Amazon RDS turns on AUTOEXTEND ON by default. The maximum size of bigfile tablespaces is 16 TiB. When you insert data into the tablespace, the tablespace size increases to the maximum quota that you configure or the maximum quota for allocated storage.

If the allocated storage for the RDS for Oracle DB instance is full, then the instance switches to the STORAGE_FULL state and tablespaces can't extend. To resolve this issue, you must add storage space to your instance. For more information, see How do I resolve issues that occur when Amazon RDS DB instances run out of storage?

When you delete data from a tablespace, the size of the tablespace doesn't decrease. When you insert new data, you can reuse the free blocks. You must manually resize the tablespace to reclaim the unused space.

Resolution

Note: In the following commands, replace TABLESPACE_NAME or example-tablespace with the name of your tablespace.

Check the configuration of the tablespace

To identify the tablespace type, run the following query:

SELECT TABLESPACE_NAME, CONTENTS, BIGFILE FROM DBA_TABLESPACES;

The preceding command might return the following output:

• The BIGFILE column can have a value of YES for BIGFILE or NO for SMALL FILE.
• The CONTENTS column can be permanent, undo or temporary.

To check the size of the datafile, the maximum quota that you configured, and whether the autoextend feature is turned on, run one of the following queries.

For permanent and undo tablespaces, run the following query:

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;

For temporary tablespaces, run the following query:

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;

The preceding commands might return the following outputs:

• If the autoextend feature isn't turned on, then the MAX_GB value is equal to 0.
• If the tablespace type is smallfile, then the MAX_GB value depends on the block size that you used to create the tablespace. For example, if the block size is 8 K, then the MAX_GB value is 32 GB. For more information, see Nonstandard block sizes on the Oracle website.
• If the tablespace type is bigfile, then the value of MAX_GB appears as 32 TB. The maximum size of a single file on RDS for Oracle DB instances is 16 TiB.

To retrieve the required information about the tablespace from the data description language (DDL), run the following commands:

SET LINESIZE 400;

SET LONG 99999;

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

Increase the size of the tablespace

If you turn on the autoextend feature, then you don't need to increase the size of the tablespace.

However, if you turn on the autoextend feature and then resize the tablespace, then you must take the following actions when you're resizing:

• For bigfile tablespaces, run the following command to resize the tablespace:

  ALTER TABLESPACE example-tablespace RESIZE 50G;

  Note: Specify the size in kilobytes, megabytes, gigabytes, or terabytes. The bigfile tablespace has a single datafile, and the ALTER TABLESPACE command resizes the datafile that belongs to the tablespace. For more information, see ALTER TABLESPACE on the Oracle website.

• For smallfile tablespaces, run the following command to add datafiles to increase the size of the tablespace:

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

• For smallfile tablespaces, run the following command to use the rdsadmin_util.resize_datafile procedure so that you can resize an original datafile:

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

  Note: Replace TEST with the name of the tablespace.

  Example procedure output:

  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

Reduce the size of a smallfile tablespace

To reduce the size of a smallfile tablespace, complete the following steps:

1. Use the rdsadmin_util.resize_datafile procedure.
   Note: You can't reduce the size of a datafile to a value that's less than the high water mark of the datafile.
2. Create a new tablespace, and then configure the space for your requirements.
3. Manually move all your data to the new tablespace.

Reduce the size of a bigfile tablespace

If you use a bigfile tablespace, then choose one of the following methods to reduce the size of the tablespace.

Permanent tablespaces

When you try to reduce the permanent tablespace size to a value that's less than the high water mark of the tablespace, your resize operation fails. Then, you receive the following error message:

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

If the size of the tablespace is greater than the water mark, then you can reduce the size of the permanent tablespace to the water mark value.

For example, if the high water mark is 40 GB and the size of the tablespace is 50 GB, then you reduce the tablespace to 40 GB.

Example command to resize the tablespace:

ALTER TABLESPACE example-tablespace RESIZE 40G;

If you can't reduce the size of the tablespace to a value that's equal the high water mark, then take the following actions:

• Reorganize the objects in the tablespace.
• Create a new tablespace and move all objects to the new tablespace. Then, delete the old tablespace.

Temporary tablespaces

Run the following command to reduce a temporary tablespace:

ALTER TABLESPACE example-tablespace SHRINK SPACE KEEP 100M;

To resize the temporary tablespace in a read replica, include rdsadmin.rdsadmin_util.resize_temp_tablespace in the following command:

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

Or, create another temporary tablespace and set the new tablespace as default.

Complete the following steps:

1. Run the following query to view the current default temporary tablespace:

   SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

   Note: For more information, see Viewing information about tablespaces on the Oracle website.

2. Run the following command to create another temporary tablespace and configure the required size:

   CREATE TEMPORARY TABLESPACE example-tablespace TEMPFILE SIZE 100M;

3. Run the following command to set the new temporary tablespace as the default temporary tablespace:

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

To modify a temporary tablespace for a specific user, complete the following steps:

1. Run the following query to view the current default temporary tablespace for the user:

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

2. Run the following command to change the default temporary tablespace:

   ALTER USER example_username TEMPORARY TABLESPACE example-tablespace;

Undo tablespaces

Complete the following steps:

1. Run the following query to identify the undo tablespace that's currently in use:

   SHOW PARAMETER UNDO_TABLESPACE;

2. Run the following command to reduce the size of the undo tablespace:

   `ALTER TABLESPACE example-tablespace RESIZE 40G;`

If the query doesn't run, then complete the following steps:

1. Run the following command to create a new undo tablespace:

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

   Note: Replace example-new-tablespace with the name of the new undo tablespace.

2. Run the following command to set the undo tablespace as the default tablespace in the parameter groups:

   UNDO_TABLESPACE = example-new-tablespace

   Note: Because UNDO_TABLESPACE is a dynamic parameter, downtime doesn't occur when you modify it. However, it's a best practice to reboot the DB instance after you modify a parameter. For more information, see Managing undo on the Oracle website.

3. Run the following query to verify that the new undo tablespace parameter is the default tablespace:

   SHOW PARAMETER UNDO_TABLESPACE;

4. Run the following command to delete the old undo tablespace:

   DROP TABLESPACE example-tablespace INCLUDING CONTENTS AND DATAFILES;

Related information

How do I create CloudWatch alarms to monitor Amazon RDS free storage space and prevent storage-full issues?

Managing capacity automatically with Amazon RDS storage autoscaling

Why does my Amazon RDS for Oracle DB instance use more storage than I expect?