Temporary table space can grow significantly to accommodate complex queries, index operations, and sort operations, among other things. RDS Oracle databases are created with auto extend enabled for all tablespaces. This means that each tablespace keeps growing to accommodate more data, until no more is needed or the allocated storage is used, whichever comes first.

Oracle databases use disk space for several different types of files: control files, online redo log files, archived redo log files, data files, and temporary files (also called temp files). Usually, most of the disk space is used for data files and temp files.

Oracle RDS DB instances designate storage in terms of data file size, which might not have any bearing on the data actually stored in the data file. For example, it would not be unusual to allocate a 200 GB data file that contains only 100 GB of data (or less). In fact, the Amazon CloudWatch “FreeStorageSace” metric for the RDS service lists only free space not already stored in a data file. The following queries can help determine how the space for your RDS Oracle DB instance is allocated:

To determine the amount of space allocated to data compared to the amount of space allocated to temporary files, run the following queries:

SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024/1024 AS GBYTES FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME;
SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024/1024 AS GBYTES FROM dba_temp_files GROUP BY TABLESPACE_NAME;

When data is deleted from a database, the amount of free space allocated to the database increases by a corresponding amount, so the total amount of space allocated to the database is unchanged. To determine the amount of free space allocated to your databases, run this query:

SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024/1024 AS GBYTES FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME;

To determine the amount of space allocated for online redo logs, run this query:

SELECT SUM(bytes*members) bytes FROM v$log;

To determine the amount of space allocated for control files, run this query:

SELECT SUM(block_size * file_size_blks) bytes FROM v$controlfile;

To determine the amount of space allocated to the audit ADUMP and trace/log files BDUMP directories, run these queries:

1. Shrink any tablespaces that are consuming more storage than necessary. You can start by resizing a file to a more reasonable value if it is determined to be using much more storage than expected. For example, if the temp tablespace is consuming 100&nbsp;GB of storage, you could run the following command to shrink the temp tablespace to 10 GB:

ALTER TABLESPACE temp RESIZE 10g;

Note: This command will fail with an error if the tablespace has allocated extents beyond the 10 GB threshold. In that case, you might want to retry the command and specify an incrementally larger value for the resize parameter. You can also try running the following command:

ALTER TABLESPACE temp SHRINK SPACE KEEP 10g;

2. If resizing to incrementally larger values is not a viable option, you might consider killing any sessions with currently allocated temp segments. To determine which session has temp segments allocated, run the following query:

SELECT * FROM v$sort_usage;

3. If killing sessions is not a viable option, you might consider taking other steps, such as creating another default temp2 tablespace, dropping the original temp tablespace, re-creating a new default temp tablespace with desired parameters, and finally dropping the temp2 tablespace. For more information on this procedure, see Resizing Temporary Tablespace.

4. For non-temporary tablespace, it may be necessary to move segments aside to free up space at the end of the data file, thereby allowing the data file to be shrunk. When working with tablespace allocated for online redo logs, this process can become tedious, and some time may elapse before the space is automatically cleared so that the process of shrinking the data file can be completed.

5. To view the current archive log and trace file retention settings, run these commands:

SET SERVEROUTPUT ON;
EXEC rdsadmin.rdsadmin_util.show_configuration;

To get the size of archive log files on the instance, run the following command:

6. Check for the existence of any .dmp files that are no longer needed and can be removed to free up space. To check for .dmp file in the data_pump_dir that were not cleaned up after an import, run the following query:

SELECT * FROM TABLE(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR')) ORDER by mtime;

If this query finds .dmp files, they can be deleted with the following query by specifying the appropriate file name: