Complete a 3 Question Survey and Earn a re:Post Badge

Help improve AWS Support Official channel in re:Post and share your experience - complete a quick three-question survey to earn a re:Post badge!

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

8 minute read

I have an Amazon Relational Database Service (Amazon RDS) for Oracle DB instance that uses more space than I expect it to. I want to know whether I can free up space on the instance.

Short description

Multiple components use the storage for Amazon RDS for Oracle instances. The components include tablespaces, archive logs, log files, online redo log files, and data pump files.

To manage storage growth in your instance, take the following actions to identify how much storage space your components use:

Find the amount of space that's allocated to data in all tablespaces, including objects such as temporary tablespaces.
Check the space allocation for archive logs or trace files.
Check the space allocation for the data pump directory.

Note: The storage space that's allocated for an RDS instance represents the data volume. When you create an instance, Amazon RDS maps the allocated storage to the data volume. This process also uses a small percentage of raw disk space to create the filesystem in addition to the physical storage volume.

Resolution

Create an archivelog directory

To create an archivelog directory, run the following SQL code:

EXEC rdsadmin.rdsadmin_master_util.create_archivelog_dir;

Find the amount of space that's allocated to data in the tablespaces

To determine the spread of the space that's allocated to different components of the Oracle database, run the following SQL code:

SET pages 200  
SELECT  
'===========================================================' || CHR(10) ||  
'Total Database Physical Size = ' || ROUND(redolog_size_gib + dbfiles_size_gib + tempfiles_size_gib + archlog_size_gib + ctlfiles_size_gib, 2) || ' GiB' || CHR(10) ||  
'===========================================================' || CHR(10) ||  
' Redo Logs Size : ' || ROUND(redolog_size_gib, 3) || ' GiB' || CHR(10) ||  
' Data Files Size : ' || ROUND(dbfiles_size_gib, 3) || ' GiB' || CHR(10) ||  
' Temp Files Size : ' || ROUND(tempfiles_size_gib, 3) || ' GiB' || CHR(10) ||  
' Archive Log Size : ' || ROUND(archlog_size_gib, 3) || ' GiB' || CHR(10) ||  
' Control Files Size : ' || ROUND(ctlfiles_size_gib, 3) || ' GiB' || CHR(10) ||  
'===========================================================' || CHR(10) ||  
' Used Database Size : ' || used_db_size_gib || ' GiB' || CHR(10) ||  
' Free Database Size : ' || free_db_size_gib || ' GiB' || CHR(10) ||  
' Data Pump Directory Size : ' || dpump_db_size_gib || ' GiB' || CHR(10) ||  
' BDUMP Directory Size : ' || bdump_db_size_gib || ' GiB' || CHR(10) ||  
' ADUMP Directory Size : ' || adump_db_size_gib || ' GiB' || CHR(10) ||  
'===========================================================' || CHR(10) ||  
'Total Size (including Dump and Log Files) = ' || ROUND(ROUND(redolog_size_gib, 2) + ROUND(dbfiles_size_gib, 2) + ROUND(tempfiles_size_gib, 2) + ROUND(archlog_size_gib, 2) + ROUND(ctlfiles_size_gib, 2) + ROUND(adump_db_size_gib, 2) + ROUND(dpump_db_size_gib, 2) + ROUND(bdump_db_size_gib, 2), 2) || ' GiB' || CHR(10) ||  
'===========================================================' AS summary  
FROM (SELECT sys_context('USERENV', 'DB_NAME')  
db_name,  
(SELECT SUM(bytes) / 1024 / 1024 / 1024 redo_size  
FROM (SELECT bytes FROM v$log UNION ALL SELECT bytes FROM v$standby_log))  
redolog_size_gib,  
(SELECT SUM(bytes) / 1024 / 1024 / 1024 data_size  
FROM dba_data_files)  
dbfiles_size_gib,  
(SELECT NVL(SUM(bytes), 0) / 1024 / 1024 / 1024 temp_size  
FROM dba_temp_files)  
tempfiles_size_gib,  
(SELECT ROUND(SUM(filesize) / 1024 / 1024 / 1024, 3)  
FROM TABLE(rdsadmin.rds_file_util.listdir('ARCHIVELOG_DIR')))  
archlog_size_gib,  
(SELECT SUM(block_size * file_size_blks) / 1024 / 1024 / 1024  
controlfile_size  
FROM v$controlfile)  
ctlfiles_size_gib,  
ROUND(SUM(used.bytes) / 1024 / 1024 / 1024, 3)  
db_size_gib,  
ROUND(SUM(used.bytes) / 1024 / 1024 / 1024, 3) - ROUND(  
free.f / 1024 / 1024 / 1024)  
used_db_size_gib,  
ROUND(free.f / 1024 / 1024 / 1024, 3)  
free_db_size_gib,  
(SELECT ROUND(SUM(filesize) / 1024 / 1024 / 1024, 3)  
FROM TABLE(rdsadmin.rds_file_util.listdir('BDUMP')))  
bdump_db_size_gib,  
(SELECT ROUND(SUM(filesize) / 1024 / 1024 / 1024, 3)  
FROM TABLE(rdsadmin.rds_file_util.listdir('ADUMP')))  
adump_db_size_gib,  
(SELECT ROUND(SUM(filesize) / 1024 / 1024 / 1024, 3)  
FROM TABLE(rdsadmin.rds_file_util.listdir('DATA_PUMP_DIR')))  
dpump_db_size_gib  
FROM (SELECT bytes  
FROM v$datafile  
UNION ALL  
SELECT bytes  
FROM v$tempfile) used,  
(SELECT SUM(bytes) AS f  
FROM dba_free_space) free  
GROUP BY free.f);

To determine the spread of the space that's allocated to different user directories of the Oracle database, run the following command:

SET SERVEROUTPUT ON;  
  DECLARE  
    v_result NUMBER;  
  v_directory_name VARCHAR2(100);  
BEGIN  
     DBMS_OUTPUT.PUT_LINE('                            ');  
     DBMS_OUTPUT.PUT_LINE('------------------------------');  
     DBMS_OUTPUT.PUT_LINE('ADDITIONAL DIRECTORY FOUND IN DATABASE');  
     DBMS_OUTPUT.PUT_LINE('------------------------------');  
  
    FOR rec IN (SELECT directory_name, directory_path  
                FROM dba_directories  
                WHERE directory_name NOT IN ('OPATCH_INST_DIR','JAVA$JOX$CUJS$DIRECTORY$','RDS$TEMP','DATA_PUMP_DIR','ADUMP','RDS$DB_TASKS','OPATCH_SCRIPT_DIR','OPATCH_LOG_DIR','BDUMP','SDO_DIR_WORK','SDO_DIR_ADMIN','BDUMP_A') )  
    LOOP  
    v_directory_name := rec.directory_name;  
        -- Output directory details  
        EXECUTE IMMEDIATE 'SELECT ROUND(SUM(filesize) / 1024 / 1024 / 1024, 3) FROM TABLE(rdsadmin.rds_file_util.listdir(''' || v_directory_name || '''))' INTO v_result;  
                DBMS_OUTPUT.PUT_LINE('Directory Name: ' || rec.directory_name);  
        DBMS_OUTPUT.PUT_LINE('Directory Path: ' || rec.directory_path);  
         DBMS_OUTPUT.PUT_LINE('Total Size (GB) for ' || v_directory_name || ': ' || v_result);  
        DBMS_OUTPUT.PUT_LINE('------------------------------');  
    END LOOP;  
END;  
/

By default, Amazon RDS for Oracle DB instances turn on auto-extend for all tablespaces. This includes data tablespaces, undo tablespaces, and temporary tablespaces. Each type of tablespace grows to accommodate more data. Your tablespaces grow until you don't need more storage or until the tables use all the allocated storage space.

Resize tablespaces

Data tablespace and undo tablespace

To resize data tablespaces and undo tablespaces, see How do I resize the tablespace for my Amazon RDS for Oracle DB instance?

Temporary tablespace

To resize temporary tablespaces, complete the following steps:

To view information about temporary tablespace usage, run the following command on the DBA_TEMP_FREE_SPACE view:
```
SELECT * FROM dba_temp_free_space;
```
To resize the temporary tablespace, run the following command based on the output of the tablespace usage query:
```
ALTER TABLESPACE temp RESIZE 10G;
```
Note: Replace 10G with the amount that you want to resize to. The preceding command fails when the allocated tablespace exceeds the 10 GB threshold.

If the previous command fails, then run the following command to decrease space on the temporary tablespace:

ALTER TABLESPACE temp SHRINK SPACE KEEP 10g;

To check for long-running sessions that perform active sorting to disk and have allocated temporary segments, run the following command:
```
SELECT * FROM v$sort_usage;
```

If the application logic lets you end the session, then end the session. After you end the session, resize the temporary tablespace again.
If you can't end your sessions, then create a new temporary tablespace. After you create the tablespace, set it as the default tablespace.
Then, remove the earlier temporary tablespace:

SELECT property_name, property_value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';  
CREATE TEMPORARY TABLESPACE temp2;  
EXEC rdsadmin.rdsadmin_util.alter_default_temp_tablespace(tablespace_name => 'temp2');  
  
SET pages 2000  
COLUMN username FRO a30  
SELECT username, TEMPORARY_TABLESPACE FROM dba_users;  
DROP TABLESPACE temp including contents and datafiles;

If your DB instance class has NVMe-based SSD storage, then create Oracle temporary tablespaces in an instance store to save the RDS storage space. For more information, see Storing temporary data in an RDS for Oracle instance store.

Check the space allocation for archive logs or trace files

Complete the following steps:

To check the current archive log retention, run the following SQL command:
```
SELECT value FROM rdsadmin.rds_configuration WHERE name ='archivelog retention hours';
```
Note: In Amazon RDS for Oracle instances, archive log retention is set to 0 by default. After archivelogs uploads to Amazon S3, Amazon RDS automatically deletes the logs from the underlying host. To use archivelogs with other services, such as Oracle LogMiner or GoldenGate, increase archivelog retention.

Calculate the space that archivelogs use on the underlying host.
First, run the following command to create an archivelog directory:

EXEC rdsadmin.rdsadmin_master_util.create_archivelog_dir;

Then, run the following command to identify how much space archivelog uses on an RDS instance:

SELECT SUM(FILESIZE)/1024/1024/1024 archivelog_usage_GiB FROM TABLE(rdsadmin.rds_file_util.listdir(p_directory => 'ARCHIVELOG_DIR'));

If the allocated space is more than you expect, then update the retention policy value. Then, allow Amazon RDS automation to clear earlier archive log files.
The following example command configures the RDS for Oracle instance to keep 24 hours of archive logs:

BEGIN rdsadmin.rdsadmin_util.set_configuration(name => 'archivelog retention hours', value => '24');  
END;   
/  
COMMIT;

For more information, see Purging trace files.

Check the space allocation for the data pump directory

Complete the following steps:

If the allocated space of the data pump directory is more than you expect, then find the .dmp files that you can remove. Run the following command:

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

If the preceding command finds .dmp files, then run the following command for each file to delete them:
```
EXEC utl_file.fremove('DATA_PUMP_DIR', 'file_name');
```
Note: In the preceding command, replace file_name with the names of your .dmp files.