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

Short description

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

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

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

If you don't specify a data file size, then AUTOEXTEND ON is turned on by default when you create tablespaces. The maximum size of bigfile tablespaces is 16 TiB (tebibytes). When you insert data into the tablespace, the tablespace increases up to the configured maximum limit that the tablespace requires. Or, the tablespace increases to the maximum limit for allocated storage for the RDS instance.

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

When data is deleted from a tablespace, the size of the tablespace doesn't shrink. The free blocks can be reused when new data is inserted. You must manually resize the tablespace to reclaim the unused space.

Resolution

To resize the tablespace for your RDS for Oracle instance, complete the following steps.

Check the configuration of the tablespace

Complete the following steps:

1. To identify the tablespace types, run a query similar to the following example:

   SQL> SELECT TABLESPACE_NAME, CONTENTS, BIGFILE FROM DBA_TABLESPACES;

   Note that tablespace types can be permanent, undo, temporary, smallfile, or bigfile.
2. To check the size of the datafile, the maximum configured limit, and check whether the autoextend feature is turned on, run the following queries. For permanent and undo tablespaces, run the following query:

   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;

   For temporary tablespaces, run the following query:

   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;

You might see the following details in the output of these queries:

• If the autoextend feature isn't turned on, then the value of MAX_GB is equal to 0.
• If the tablespace type is smallfile, then the value of MAX_GB depends on the block size used to create the tablespace. For example, if the block size used is 8K, then the value of MAX_GB 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 data description language (DDL) for the tablespace, run the following queries:

SQL> SET LINESIZE 400

SQL> SET LONG 99999

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

You can retrieve the required information about the tablespace from the DDL that's returned.

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, when you turn on the autoextend feature and then resize the tablespace, you must complete following requirements.

For bigfile tablespaces, to resize the tablespace, run the ALTER TABLESPACE command:

SQL> ALTER TABLESPACE example-tablespace RESIZE 50G;

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

For smallfile tablespaces, add datafiles to increase the size of the tablespace:

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

To resize an original datafile in a smallfile tablespace, run rdsadmin_util.resize_datafile:

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

Your output looks similar to this:

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 the tablespace

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

1. To reduce the size of a smallfile tablespace, run rdsadmin_util.resize_datafile.
   Note: You can't reduce the size of a datafile to a value less than the high watermark of the datafile.
2. Create a new tablespace and configure the space as needed. Then, manually move all your data to the new tablespace.

If your tablespace is bigfile, then to decrease the size of the tablespace based on your tablespace type, choose one of the following methods.

For permanent tablespaces, you can't decrease the size of a permanent tablespace to a value less than the high watermark of the tablespace. When you try to resize a permanent tablespace, your resize operation fails. Then, you see the following error:

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

However, to decrease the size of the tablespace to 40 GB when the tablespace has the size of 50 GB and a high watermark of 40 GB, run the following query:

SQL> ALTER TABLESPACE example-tablespace RESIZE 40G;

If you can't decrease the size of the tablespace to a value less than the high watermark, then consider the following options:

• Reorganize the objects in the tablespace.
• Create a new tablespace and move all objects to the new tablespace.
• Drop the old tablespace.

For temporary tablespaces, to decrease the size of your temporary tablespace, run the SHRINK command:

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

To resize the temporary tablespaces in a read replica, run rdsadmin.rdsadmin_util.resize_temp_tablespace:

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

-or-

Create another temporary tablespace and configure the required size. Then, set the new temporary tablespace as the default temporary tablespace.

1. To view the current default temporary tablespace, run the following query:

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

   For more information, see Viewing Information About Tablespaces on the Oracle website.

2. To create a new temporary tablespace, run the following query:

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

3. To set the new temporary tablespace as the default temporary tablespace, run the following query:

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

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

1. To view the current default temporary tablespace for the user, run the following query:

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

2. To change the default temporary tablespace for the user, run the following query:

   SQL> ALTER USER example_username TEMPORARY TABLESPACE example-tablespace;

For undo tablespaces, use the ALTER TABLESPACE command to decrease the size of the undo tablespace.

To identify the undo tablespace that's currently in use, run the following query:

SQL> SHOW PARAMETER UNDO_TABLESPACE;

To decrease the size of the undo tablespace, run a query similar to the following example query:

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

1. Create a new undo tablespace:

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

2. Set the newly created tablespace as the default undo tablespace:

   UNDO_TABLESPACE = example-new-tablespace

   Set the UNDO_TABLESPACE initialization parameter in the parameter group to point to the newly created tablespace.
   For more information, see Working with parameter groups.
   The UNDO_TABLESPACE initialization parameter is a dynamic parameter and doesn't result in any downtime when you apply the modification. However, it's a best practice to reboot the DB instance after you make a change. For more information, see Managing Undo on the Oracle website.
   To verify that the new undo tablespace is the default tablespace, run the following query:

   SQL> SHOW PARAMETER UNDO_TABLESPACE;

3. To delete the old undo tablespace, run a drop tablespace query:

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

Related information

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

Managing capacity automatically with Amazon RDS storage autoscaling

Why is my Amazon RDS for Oracle DB instance using more storage than expected?