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. Bigfile tablespaces make datafiles transparent for users. Therefore, you can perform the operations on tablespaces directly instead of managing each of the underlying datafiles. Bigfile tablespaces simplify datafile management by removing the need to add new datafiles or deal with multiple files. It's a best practice to avoid using smallfile tablespaces for your RDS for Oracle instances.

If your RDS for Oracle instance has a smallfile tablespace, you have the following limitations:

• You can't resize the datafiles. However, you can resize the tablespace by adding new datafiles in the tablespace.
• You can’t run the ALTER DATABASE query to resize or change the datafile configurations. This is because the ALTER DATABASE command is unsupported in RDS for Oracle. For more information, see RDS for Oracle limitations.
• You must manually manage the db_files parameter to define the maximum number of datafiles in the database. The db_files might need to be tweaked when the number of datafiles reaches this limit.

By default, the tablespaces in RDS for Oracle are bigfile with AUTOEXTEND turned on. The maximum size of bigfile tablespaces is 16 TiB. When you insert data into the tablespace, the tablespace increases as required up to either the configured maximum limit for that tablespace or the configured allocated storage for the RDS instance, whichever is smaller. If the allocated storage for the RDS instance is fully utilized, then the instance switches to the STORAGE_FULL state, and tablespaces can't be extended. 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, do the following:

1. Check the configuration of the tablespace.
2. Increase or decrease the size of the tablespace based on your use case.

Check the configuration of the tablespace

1.    Run a query similar to the following to identify if the tablespace is:

• Permanent, undo, or temporary
• Smallfile or bigfile

SQL> SELECT TABLESPACE_NAME, CONTENTS, BIGFILE FROM DBA_TABLESPACES;

2.    Run a query similar to the following to check if the autoextend feature is turned on, the current size of the datafile, and the maximum configured limit:

For Permanent and undo tablespaces:

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:

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 notice the following in the output of these queries:

• If the autoextend feature is not turned on, then the value of MAX_GB is equal to 0.
• If the tablespace is smallfile, then the value of MAX_GB depends on the block size used to create the tablespace. For example, the value of MAX_GB is 32 GB if the block size used is 8K. For more information, see Oracle documentation for Nonstandard block sizes.
• If the tablespace is bigfile, then the value of MAX_GB is displayed as 32 TB. However, due to the OS limitations in RDS for Oracle, the file can only extend up to 16 TiB.

Optionally, you can run the following queries to get the DDL used to create the tablespace:

SQL> SET LINESIZE 400

SQL> SET LONG 99999

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

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

Increase the size of the tablespace

If you've turned on the autoextend feature, then you don't need to increase the size of the tablespace, irrespective of the type of tablespace. In this case, the tablespace is automatically resized as required.

If you haven't turned on the autoextend feature and want to resize the tablespace, then do the following:

For bigfile tablespaces: Resize the tablespace using the ALTER TABLESPACE command. You can specify the size in kilobytes (K), megabytes (M), gigabytes (G), or terabytes (T). The bigfile tablespace has a single datafile, and this command resizes the underlying single datafile associated with the tablespace.

SQL> ALTER TABLESPACE example-tablespace RESIZE 50G;

For smallfile tablespaces: You can resize the tablespace only by adding more datafiles to the tablespace. You can't resize or modify the configuration of the current datafiles.

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

Decrease the size of the tablespace

If the tablespace is smallfile, then you can't manage the underlying datafiles. To increase the tablespace size, add a new datafile. When you add a new data file, be sure that you choose the right values for AUTOEXTEND, SIZE, and MAXSIZE. These values can't be altered later. To reduce the size of the smallfile tablespace, create a new tablespace with the desired space and move all your data manually to the new tablespace.

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

Permanent tablespaces: You can't decrease the size of a permanent tablespace to a value less than the high water mark of the tablespace. If you attempt to do so, your resize operation fails with the following error:

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

However, suppose that a tablespace has the size of 50 GB with a high water mark of 40 GB. Then, you can decrease the size of the tablespace to 40 GB by running a query similar to the following:

SQL> ALTER TABLESPACE example-tablespace RESIZE 40G;

You can't decrease the size of the tablespace to a value less than the high water mark. In such cases, you can do either of the following:

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

Temporary tablespaces: You can decrease the size of your temporary tablespace using the SHRINK command.

Example:

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

For resizing temporary tablespaces in a read replica, use the rdsadmin.rdsadmin_util.resize_temp_tablespace package:

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

For more information, see Resizing the temporary tablespace in a read replica.

-or-

You can create another temporary tablespace with the size of your choice, and then set this new temporary tablespace as the default temporary tablespace.

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

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

2.    Run the following query to create a new temporary tablespace:

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

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

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

To modify the temporary tablespace for a specific user, you can do the following:

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

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

2.    Run the following query to change the default temporary tablespace for the user:

SQL> ALTER USER example_username TEMPORARY TABLESPACE example-tablespace;

Undo tablespaces: First, try to decrease the size of the undo tablespace using the ALTER TABLESPACE command.

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

SQL> SHOW PARAMETER UNDO_TABLESPACE;

Run a query similar to the following to decrease the size of the undo tablespace:

SQL> ALTER TABLESPACE example-tablespace RESIZE 500M;

This query runs successfully if there are no undo segments in the storage that must be removed.

If the above query doesn't run successfully, then do the following:

1.    Create a new undo tablespace:

Run a query similar to the following to 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:

Set the UNDO_TABLESPACE initialization parameter in the parameter group to point to the newly created tablespace. For more information, see Modifying parameters in a DB parameter group.

Example:

UNDO_TABLESPACE = example-new-tablespace

This parameter is a dynamic parameter and doesn't result in any downtime to apply the modification. However, it's a best practice to reboot the DB instance after this change. For more information, see Managing undo.

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

SQL> SHOW PARAMETER UNDO_TABLESPACE;

3.    Drop the old undo tablespace:

Run a query similar to the following to delete the old undo tablespace:

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

---

Related information

Managing tablespaces

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

How do I resolve problems with my Amazon RDS Oracle DB instance that's using more storage than expected?

Creating and sizing tablespaces