Why is my Amazon RDS for Oracle DB instance using more storage than expected?
I have an Amazon Relational Database Service (Amazon RDS) for Oracle DB instance that uses more space than I expect it to.
"I have an Amazon Relational Database Service (Amazon RDS) for Oracle DB instance that uses more space than I expect it to.Short descriptionVarious components can use the underlying storage for Amazon RDS for Oracle instances. These components include tablespaces, archive logs, log files, online redo log files, and data pump files.To manage storage growth in your instance, identify how much storage space your components use:1.    Find the amount of space that's allocated to data in all tablespaces, including temporary tablespaces.2.    Check the space allocation for archive logs or trace files.3.    Check the space allocation for the data pump directory.Note: The allocated storage space 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 on top of the physical storage volume.ResolutionFind the amount of space allocated to data in the tablespacesTo determine the spread of the space that's allocated to different components of the Oracle database, use the following query:set pages 200select'===========================================================' || chr(10) ||'Total Database Physical Size = ' || round(redolog_size_gib+dbfiles_size_gib+tempfiles_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 - Approx only : ' || 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(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 summaryFROM (SELECT sys_context('USERENV', 'DB_NAME')db_name,(SELECT SUM(bytes) / 1024 / 1024 / 1024 redo_sizeFROM v$log)redolog_size_gib,(SELECT SUM(bytes) / 1024 / 1024 / 1024 data_sizeFROM dba_data_files)dbfiles_size_gib,(SELECT nvl(SUM(bytes), 0) / 1024 / 1024 / 1024 temp_sizeFROM dba_temp_files)tempfiles_size_gib,(SELECT SUM(blocks * block_size / 1024 / 1024 / 1024) size_gibFROM v$archived_logWHERE first_time >= SYSDATE - ((SELECT valueFROM rdsadmin.rds_configurationWHERE name ='archivelog retention hours') /24 ))archlog_size_gib,(SELECT SUM(block_size * file_size_blks) / 1024 / 1024 / 1024controlfile_sizeFROM 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_gibFROM (SELECT bytesFROM v$datafileUNION ALLSELECT bytesFROM v$tempfile) used,(SELECT SUM(bytes) AS fFROM dba_free_space) freeGROUP BY free.f);By default, Amazon RDS for Oracle DB instances turn on auto-extend for all tablespaces. This includes data tablespaces, UNDO tablespaces, and temporary tablespaces. This means that each tablespace grows to accommodate more data. This feature continues until you no longer need more storage or you use all allocated storage space.Resize tablespacesData tablespace and UNDO tablespaceTo resize data and UNDO tablespaces, see How do I resize the tablespace for my Amazon RDS for Oracle DB instance?Temporary tablespace1.    To view information about temporary tablespace usage, run the following query on the view DBA_TEMP_FREE_SPACE:SQL> SELECT * FROM dba_temp_free_space;2.    To resize the temporary tablespace (for example, to 10 GB), run the following query based on the output of the tablespace usage query:SQL> ALTER TABLESPACE temp RESIZE 10g;If the allocated tablespace extends beyond the 10 GB threshold, then this command might fail.3.    If the command fails, then shrink space on the temporary tablespace:SQL> ALTER TABLESPACE temp SHRINK SPACE KEEP 10g;4.    Check for long-running sessions that perform active sorting to disk and have temporary segments allocated. To do this, run the following query:SQL> SELECT * FROM v$sort_usage;5.    If the application logic and business allow you to end the session, then end the session. Then, resize the temporary tablespace again, as shown in step 2.6.    If you can't end your sessions, then create a new temporary tablespace. Then, set the new tablespace as the default and drop the old temporary tablespace:SQL> SELECT property_name,property_value FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';SQL> create temporary tablespace temp2;SQL> exec rdsadmin.rdsadmin_util.alter_default_temp_tablespace(tablespace_name => 'temp2');<wait for a few minutes and verify if the default temporary tablespace for all users have been updated>SQL> set pages 2000SQL> column username for a30SQL> select username, TEMPORARY_TABLESPACE from dba_users;SQL> drop tablespace temp including contents and datafiles;Check the space allocation for archive logs or trace files1.    Check the current archive log retention:SQL> SELECT value FROM rdsadmin.rds_configuration WHERE name ='archivelog retention hours';In Amazon RDS for Oracle instances, archive log retention is set to 0 by default. This means that after archivelogs upload to Amazon S3, Amazon RDS automatically deletes them from the underlying host. If you need to use archivelogs with products like Oracle LogMiner or GoldenGate, then increase archivelog retention.2.    Calculate the space that archivelogs use on the underlying host. First, create an archivelog directory:SQL> EXEC rdsadmin.rdsadmin_master_util.create_archivelog_dir;Then, identify the exact usage of archivelog on an RDS instance:SQL> SELECT sum(FILESIZE)/1024/1024/1024 archivelog_usage_GiB FROM TABLE(rdsadmin.rds_file_util.listdir(p_directory => 'ARCHIVELOG_DIR'));3.    If the allocated space for archive logs is more than expected, then update the retention policy value. Then, allow Amazon RDS automation to clear older archive log files. The following example configures the RDS for Oracle instance to retain 24 hours of archive logs:begin rdsadmin.rdsadmin_util.set_configuration(name => 'archivelog retention hours', value => '24');end; /commit;For more information about listing and purging trace files, see Purging trace files.Check the space allocation for the data pump directory1.    If the allocated space of the data pump directory is more than expected, then find the .dmp files that can be removed:SQL> SELECT * FROM TABLE(RDSADMIN.RDS_FILE_UTIL.LISTDIR('DATA_PUMP_DIR')) ORDER by mtime;2.    If this query finds .dmp files, then delete them with the following query. Replace the file name with the name of the .dmp files:SQL> EXEC utl_file.fremove('DATA_PUMP_DIR','[file name]');Related informationWorking with storage for Amazon RDS DB instancesTerminating a sessionMonitoring metrics in an Amazon RDS instanceAmazon RDS DB instance running out of storageFollow"