Environment friendly storage administration is essential for sustaining the efficiency, reliability, and cost-effectiveness of your Oracle databases operating on Amazon RDS. As your knowledge grows and your workloads evolve, it’s important to proactively monitor and optimize your storage utilization. On this submit, we discover numerous strategies and finest practices for successfully managing storage in RDS for Oracle Databases.
Amazon Relational Database Service (Amazon RDS) for Oracle offers a completely managed database service that makes it easy to arrange, function, and scale Oracle databases within the AWS Cloud. Amazon RDS for Oracle manages time-consuming database administration duties so you may focus in your functions. When you develop and enhance your core functions, RDS handles important backend operations: provisioning new databases, implementing patches, operating backups, managing restoration processes, monitoring for failures, and performing repairs. With this complete database administration, you may spend extra time constructing functions and fewer time on administrative duties. It presents a variety of occasion varieties and storage choices to fulfill your efficiency and value necessities and offers built-in excessive availability options resembling Multi-AZ(Availability Zone) deployments for enhanced availability.
Overview
On this submit, we talk about other ways to watch, optimize, and handle free storage in Amazon RDS.
1. Monitor the storage
Amazon CloudWatch empowers you to carefully monitor the well being of your RDS cases and observe modifications to the underlying infrastructure and database workloads. Utilizing CloudWatch you may monitor a variety of metrics over particular time intervals, resembling disk utilization, CPU utilization, reminiscence utilization, disk I/O, and community site visitors. You should utilize this complete visibility to rapidly determine and tackle points which may come up.
You can even additional create a customized SQL question just like one talked about in: Why does my Amazon RDS for Oracle DB occasion use extra storage than anticipated? and ship an e-mail to the required recipients utilizing engine options resembling UTL_MAIL as talked about in: How do I ship an e-mail from my Amazon RDS for Oracle DB occasion?
RDS for Oracle is absolutely managed and consists of an information volumes, binary(bin) quantity and root quantity. The basis and bin volumes comprise the OS and the Oracle Database binaries respectively. These volumes are monitored and maintained by the interior Amazon RDS automation system. As an Amazon RDS for Oracle buyer, you could monitor the information volumes. This is similar storage that’s provisioned in the course of the Amazon RDS occasion creation time. The info volumes for RDS for Oracle occasion hosts the information recordsdata, redo and archive log recordsdata, and hint recordsdata. That is the placement for the database listing and so it should host the dump file for knowledge pump export and import, RMAN backup items, and recordsdata referred to by exterior tables. Moreover, exterior tables could be saved in Amazon Easy Storage Service (Amazon S3), with S3 integration, offering extra flexibility for knowledge storage and entry choices.
The CloudWatch metric to watch free cupboard space per quantity is FreeStorageSpace. It offers the quantity of obtainable cupboard space per quantity and is measured in bytes, as proven within the following determine.

Moreover, you may arrange CloudWatch alarms to obtain notifications when a metric exceeds a predefined threshold. Utilizing this proactive method you may take well timed motion to resolve issues earlier than they escalate and influence your operations. CloudWatch metric alarms are highly effective monitoring instruments that monitor a single CloudWatch metric or the result of a mathematical expression derived from a number of CloudWatch metrics. It may set off numerous actions, resembling sending notifications to an Amazon Easy Notification Service (Amazon SNS) subject, primarily based on the metric or expression worth in relation to a specified threshold over an outlined time interval. This helps proactive monitoring and fast identification and backbone of points to take care of the reliability, availability, and optimum efficiency of your AWS surroundings.
To watch an Amazon RDS for Oracle occasion, use the next:
The previous AWS Command Line Interface (AWS CLI) command creates a CloudWatch alarm named “FreeSpace” to watch the free cupboard space of an RDS database occasion known as “testrds” within the US West (Oregon) AWS Area. The alarm is ready to set off when the common free cupboard space falls under 50 GB for 2 consecutive 1-minute intervals. When activated, it sends a notification by means of an Amazon SNS subject. This setup helps you proactively handle database storage by alerting them earlier than the database runs critically low on area. The command makes use of a shared AWS profile for authentication, permitting for standardized entry throughout a staff or group.
Monitor Further Storage Volumes (ASVs)
For monitoring extra storage volumes in CloudWatch, a brand new dimension “RDS -> DB Occasion Identifier, Quantity Identify (per-volume metrics)” is obtainable.

From the filtering, you possibly can additional drill all the way down to database identifier and quantity title. The amount title for added storage volumes would observe the naming conference rdsdbdata

Within the following screenshot, we take a look at the metrics of the extra storage quantity: rdsdbdata2

To view the metrics in Enhanced Monitoring, choose “Enhanced Monitoring” choice from the drop down beneath the Monitoring tab.

You may open the Handle Graphs choice and choose the required metrics:

When you select save, you may view the quantity metrics of all of the volumes together with any extra storage volumes. The Complete Filesystem(/rdsdbdata*) metric offers the combination sum of the first and extra storage volumes.

To view the metrics in Database Insights Superior, go to Database Telemetry -> Metrics -> Create Widget

Enter a widget title and seek for “rdsdbdata2” within the choose metrics to pick the related metrics for the ASV.

When you create the widget, you must have the ability to view the small print.

2. Storage autoscaling
Storage autoscaling is a handy characteristic that allows Amazon RDS to mechanically scale up your storage when it detects that you simply’re operating out of free cupboard space.
Be aware: Storage autoscaling helps forestall sudden storage getting full outdoors of labor hours or monitoring home windows. It’s really useful to scale the storage of RDS for Oracle occasion to the anticipated measurement wanted plus buffer area. That is to keep away from going into the storage optimization state, which might take 24 hours or extra to finish.
You may examine if in case you have storage autoscaling enabled in your RDS for Oracle occasion, through the use of the AWS Administration Console for Amazon RDS or utilizing the AWS CLI to explain the occasion and examine if the MaxAllocatedStorage parameter has a worth. It’s vital to know how Storage autoscaling works and its limitations.
Amazon RDS initiates a storage modification for an autoscaling-enabled DB occasion when the next situations are met:
- Free accessible area is lower than or equal to 10% of the allotted storage.
- The low-storage situation lasts for no less than 5 minutes.
- At the least 6 hours have handed for the reason that final storage modification or storage optimization has accomplished, whichever is longer.
The extra storage is added in increments of whichever is larger:
- 10 GiB
- 10% of the presently allotted storage
- Predicted storage development exceeding the present allotted storage measurement inside the subsequent 7 hours primarily based on the FreeStorageSpace metric from the previous hour.
Keep in mind, whereas storage autoscaling is a helpful characteristic, it shouldn’t be relied upon solely. Figuring out the constraints of this characteristic, implementing sturdy monitoring practices, and commonly reviewing storage necessities is essential for sustaining a wholesome and well-performing RDS for Oracle database.
Storage autoscaling is presently not accessible for added storage volumes. If an occasion has extra storage volumes, you may nonetheless use storage autoscaling for the first quantity. Storage autoscaling shouldn’t be supported just for “extra storage volumes”. Nevertheless, you may monitor the storage as described in Monitor the storage, setup alarms and manually enhance the storage or release area as mentioned in Eradicating undesirable recordsdata.
3. Eradicating undesirable recordsdata
Database operations resembling RMAN backups, knowledge pumps, archive logs, audit and hint recordsdata repeatedly generate recordsdata that may rapidly eat useful cupboard space. When left unmanaged, these recordsdata can result in efficiency degradation, failed operations, and pointless prices as RDS storage is charged primarily based on allotted area.
To delete a file in database listing like DATAPUMP_DIR, you need to use the UTL_FILE.FREMOVE package deal in RDS for Oracle. This package deal offers a handy approach to take away recordsdata from directories and launch area again to the working system. See the Performing miscellaneous duties for Oracle DB cases for extra data on find out how to handle database directories in an RDS for Oracle occasion. It is strongly recommended to have an audit coverage to trace actions carried out utilizing UTL_FILE package deal for safety objective.
For DATAPUMP_DIR:
For hint recordsdata, audit recordsdata and listener logs, the default retention in Amazon RDS is 7 days. For alert logs, it’s 30 days. It’s vital to evaluation the rententon schedule and make needed adjustements in accordance with your necessities.
For extra details about sustaining log recordsdata, see Amazon RDS for Oracle database log recordsdata.
The administration of archive log recordsdata in RDS for Oracle follows particular retention patterns primarily based on the occasion configuration and replication setup. The default retention interval for archive logs in a standalone RDS for Oracle occasion is ready to 0 hours, which means these logs are mechanically purged from the host system instantly after being efficiently uploaded to Amazon S3 for backup functions.
In RDS for Oracle, the retention habits modifications considerably beneath completely different replication eventualities. When learn replicas are carried out, the retention interval defaults to 2 hours as a result of logs have to be stored till they’re utilized to all replicas, resulting in elevated storage necessities. For cross Area replicas, RDS for Oracle retains the transaction logs on the supply DB occasion till they’ve been transmitted and utilized to all cross-Area learn replicas. Therefore, it is very important monitor the duplicate lag metric.
For extra details about retention for archive log recordsdata, see Retaining archived redo logs.
Managing audit trails
It’s vital to correctly handle audit trails in your databases to take care of environment friendly efficiency and optimum use of disk area. As audit trails in your databases develop in quantity, querying an audit path with a big quantity of audit knowledge can influence efficiency and result in area scalability points. It’s finest to archive the previous information and purge them from the net audit path periodically.
For extra data, see Safety auditing in Amazon RDS for Oracle: Half 1.
4. Desk and tablespace reorganization
Desk reorganization in Oracle is the method of restructuring a desk to enhance its efficiency, reclaim unused area, or modify its construction. Right here’s an outline of desk reorganization and find out how to do it:
Reorganize tables
Desk reorganization in Oracle databases serves a number of vital functions that instantly influence database efficiency and effectivity. The first motivation for desk reorganization is to handle fragmentation points that naturally happen over time as knowledge is inserted, up to date, and deleted, resulting in scattered knowledge blocks and inefficient area utilization. Via reorganization, unused area could be reclaimed, decreasing the general storage footprint and bettering area effectivity. This course of considerably enhances question efficiency by optimizing the bodily storage of information, ensuring that associated knowledge is saved contiguously the place potential.
Moreover, reorganization permits for the reordering of information to higher align with frequent entry patterns, resembling sorting knowledge primarily based on ceaselessly used columns or index keys. From a structural perspective, desk reorganization offers a possibility to change desk buildings, resembling including or dropping columns, with out the necessity for separate operations which may influence efficiency. This complete method to desk upkeep not solely optimizes present efficiency but additionally prepares the database for future development and altering enterprise necessities.
Common desk reorganization ought to be a part of a database upkeep technique, notably for tables experiencing excessive volumes of DML operations or these vital to enterprise operations.
Verify for desk fragmentation
For details about checking for desk fragmentation, see Find out how to Discover Fragmentation for Tables and LOBs (Doc ID KB138882)
Run the next process with schema title and desk title:
What choices can be found in Oracle to reorganize tables?
Be aware: The next ought to be examined in lower-priority environments first. Some strategies may require downtime and ought to be carried out throughout upkeep home windows.
Oracle offers a number of approaches for desk reorganization, every with distinct benefits and trade-offs: CTAS, ALTER TABLE MOVE, DBMS_REDEFINITION, export/import, and ALTER TABLE SHRINK SPACE.
CTAS technique
Desk reorganization in Oracle could be achieved by means of a number of strategies, with CREATE TABLE AS SELECT (CTAS) being one of the vital used approaches. The CTAS technique includes a three-step course of:
- Creating a brand new desk with the specified construction and copying knowledge from the prevailing desk utilizing a
SELECTassertion. - Dropping the unique desk.
- Renaming the brand new desk to match the unique desk title.
Whereas this technique presents benefits resembling simplicity, straightforwardness, and the pliability to implement structural modifications rapidly, it comes with notable concerns and limitations.
The first disadvantage is the requirement for double the cupboard space in the course of the reorganization course of, as a result of each the unique and new tables exist concurrently. Moreover, the desk turns into unavailable for DML operations in the course of the reorganization, which might influence enterprise operations in manufacturing environments. One other important consideration is that dependent objects resembling indexes, triggers, and grants have to be recreated after the reorganization, requiring cautious planning and extra steps within the course of.
Regardless of these limitations, CTAS stays a well-liked selection for smaller tables or throughout upkeep home windows the place downtime is appropriate. Right here’s the essential syntax:
The ALTER TABLE MOVE command presents an easy technique for reorganizing tables and reclaiming area.
Through the use of this method, you may transfer tables to completely different tablespaces, compress knowledge, and reorganize the desk’s storage construction. The MOVE operation is advantageous for its directness in defragmenting area, potential to vary storage parameters, and choice to maneuver tables throughout tablespaces. Nevertheless, it requires downtime as a result of the desk is locked in the course of the operation, and all indexes have to be rebuilt afterward. The operation could be enhanced with the ONLINE key phrase in Enterprise Version to reduce disruption, although this may influence efficiency.
The next is a pattern ALTER TABLE MOVE implementation:
DBMS_REDEFINITION represents a extra subtle method, permitting for on-line desk reorganization with minimal downtime. This technique is especially useful in manufacturing environments the place steady availability is vital. Utilizing DBMS_REDEFINITION, you may carry out complicated structural modifications whereas sustaining dependent objects mechanically. The method begins with DBMS_REDEFINITION.START_REDEF_TABLE, specifying the unique and interim desk particulars. Whereas this technique presents important benefits for manufacturing techniques, it requires cautious implementation, extra cupboard space in the course of the course of, and is perhaps slower than offline alternate options.
The export/import technique offers essentially the most complete reorganization choice, enabling full desk restructuring and knowledge migration between completely different Oracle variations or techniques. Nevertheless, this method comes with appreciable drawbacks, together with important downtime necessities, extra storage wants for export recordsdata, and probably lengthy execution occasions for big tables.
Shrink
The ALTER TABLE SHRINK SPACE command presents an environment friendly on-line technique for reclaiming unused area in Oracle tables and their related indexes, making it a useful software for database area administration. This operation is especially efficient for tables experiencing important area fragmentation due to frequent DELETE or UPDATE operations, as a result of it really works by transferring rows to the start of the desk or section and releasing free area on the finish for return to the tablespace. Earlier than implementing a shrink operation, confirm the desk’s eligibility and allow row motion utilizing the next command.
The shrink course of usually follows a two-step method:
- Use the
COMPACTchoice to defragment the area. - Use
CASCADEto shrink each the desk and its dependent objects.
Whereas this technique presents a number of benefits, together with on-line operation with minimal disruption, no extra storage necessities, and automated index upkeep, there are vital concerns to remember. The operation requires row motion to be enabled, may quickly influence efficiency throughout execution, and isn’t appropriate for tables with long-running transactions or sure Giant Objects (LOB) segments. Tables with triggers or international key constraints may require particular consideration, and it’s really useful to carry out shrink operations throughout low-activity intervals. The standard implementation sequence appears like the next:
Weekly monitoring of area utilization and fragmentation ranges might help you establish the optimum timing for shrink operations, making it a necessary a part of proactive database upkeep methods.
Oracle Desk Reorganization Strategies: Execs and Cons
| Technique | Execs | Cons |
| CTAS (Create Desk As Choose) | • Easy and simple implementation • Versatile for implementing structural modifications • Good for smaller tables • Full management over new desk construction |
• Requires double cupboard space throughout course of • Desk unavailable for DML operations throughout reorganization • Dependent objects (indexes, triggers, grants) have to be recreated • Causes downtime – finest for upkeep home windows |
| ALTER TABLE MOVE | • Direct technique for defragmenting area • Can change storage parameters • Capacity to maneuver tables throughout tablespaces • Less complicated syntax than different strategies |
• Desk is locked throughout operation (downtime required) • All indexes have to be rebuilt afterward • Restricted structural modifications potential • Efficiency influence throughout operation |
| DBMS_REDEFINITION | • On-line reorganization with minimal downtime • Maintains dependent objects mechanically • Permits complicated structural modifications • Supreme for manufacturing environments • Steady availability throughout course of |
• Requires cautious implementation • Wants extra cupboard space throughout course of • Extra complicated to implement than different strategies • Doubtlessly slower than offline alternate options • Requires Enterprise Version options |
| Export/Import | • Most complete reorganization choice • Full desk restructuring potential • Helps knowledge migration between completely different Oracle variations |
• Vital downtime necessities • Further storage wanted for export recordsdata • Lengthy execution occasions for big tables • Most disruptive of all strategies |
| ALTER TABLE SHRINK SPACE | • Environment friendly on-line technique with minimal disruption • No extra storage necessities • Computerized index upkeep • Good for reclaiming area after mass deletes • Can embrace CASCADE choice for dependent objects |
• Requires row motion to be enabled • Might quickly influence efficiency • Not appropriate for tables with long-running transactions • Restricted effectiveness for sure LOB segments • Particular consideration wanted for tables with triggers or international keys |
Keep in mind, one of the best technique to make use of relies on your particular wants, desk measurement, accessible downtime, and Oracle model. Take a look at in a non-production surroundings earlier than deploying to manufacturing.
Tablespace administration and reorganization in Oracle: A complete method
Tablespace reorganization in Oracle databases represents a vital facet of database upkeep geared toward optimizing storage utilization, bettering efficiency, and managing area effectively. Tablespaces, being logical storage models that group associated knowledge segments, require common upkeep to assist forestall fragmentation and keep optimum efficiency. Widespread indicators indicating the necessity for reorganization embrace degraded question efficiency, elevated area fragmentation, and inefficient area utilization. Oracle offers a number of approaches to tablespace administration, together with transferring datafiles, resizing tablespaces, including new datafiles, and implementing space-saving options like desk compression.
The coalesce operation represents one method inside this broader upkeep technique. Tablespace coalesce operations in Oracle present a mechanism for combining adjoining areas of free area inside a tablespace to create bigger contiguous blocks, essentially bettering area utilization and decreasing fragmentation. This operation is particularly designed for regionally managed tablespaces with automated section area administration, working by scanning bitmap blocks to determine and merge adjoining free extents. The coalesce operation presents a number of important benefits:
- It may be carried out whereas the tablespace stays on-line and accessible
- Requires no extra cupboard space for execution
- It may be both mechanically triggered by Oracle or manually initiated utilizing the command
Whereas the operation successfully improves area utilization and might improve efficiency by means of decreased fragmentation, it does include sure limitations. The method won’t be as efficient as a whole reorganization when coping with 80% or extra fragmented tablespaces, can eat important system sources notably for big tablespaces, and notably doesn’t compact or transfer present knowledge. For extra data, see Resizing tablespaces, knowledge recordsdata, and temp recordsdata.
Common monitoring and proactive upkeep of tablespaces, together with periodic reorganization and coalescing operations, are important parts of a complete database administration technique. You need to develop a scientific method to tablespace administration, contemplating components resembling peak utilization intervals, development patterns, and efficiency necessities whereas implementing these upkeep operations.
It’s vital to notice that whereas tablespace coalesce might help optimize area utilization, it’s not an alternative to correct sizing and administration of tablespaces. Common monitoring and proactive administration are nonetheless needed for optimum database efficiency.
In some circumstances when knowledge is faraway from an Oracle tablespace in an Amazon RDS database, the allotted area for that tablespace doesn’t mechanically shrink. The database marks the information blocks that have been beforehand occupied as free area, permitting them to be reused for brand spanking new knowledge insertions inside the identical tablespace. Nevertheless, the general allotted measurement of the tablespace stays unchanged. To reclaim the unused area and scale back the tablespace’s footprint, you could manually resize the tablespace or its related knowledge recordsdata by means of particular procedures.
5. Scale back the dimensions of the tablespace
To resize a tablespace, see How do I resize the tablespace for my Amazon RDS for Oracle DB occasion?
Remember that resizing operations can influence efficiency, so it’s really useful to carry out them throughout upkeep home windows or low exercise intervals. Take backups and check the procedures in a non-production surroundings earlier than making use of them to your manufacturing databases.
SYSAUX utilization
In Amazon RDS for Oracle, carefully monitoring the SYSAUX tablespace is vital, as a result of it will probably develop quickly resulting from numerous components such because the Computerized Workload Repository (AWR), optimizer statistics, or auditing. By default, the SYSAUX tablespace is ready as auto-extensible, making it much more vital to watch its area utilization.
To successfully handle the SYSAUX tablespace, collect detailed details about which parts are consuming area inside the tablespace, the retention interval of AWR experiences, and the section distribution. Oracle offers “Ideas if Your SYSAUX Tablespace Grows Quickly or Too Giant (Doc ID 1292724.1)” which presents useful steerage on addressing SYSAUX tablespace development points. Be aware that if the dimensions of the AUDIT tablespace is full, you won’t have the ability to log in to the database if audit_sys_operations is ready to true.
Advisable practices to keep away from or mitigate SYSAUX development issues:
- Make sure that no utility or consumer objects are current within the SYSAUX tablespace, as a result of it’s meant for Oracle-managed parts solely.
- If auditing is enabled, use a separate tablespace for auditing and measurement it appropriately. You should utilize the next command to set the audit path location:
- Due to Amazon RDS limitations, you can’t instantly reorganize objects inside the SYSAUX tablespace to launch free area. Nevertheless, you may think about the next choices:
- Flip off
AUTOEXTENDfor the SYSAUX tablespace, so the free area inside the tablespace might be reused for different segments. - Use AWS Database Migration Service (AWS DMS) or Information Pump emigrate to a brand new occasion and monitor future development on SYSAUX.
- Flip off
- Monitor SYSAUX development commonly to determine the parts contributing to its development, and take applicable actions, resembling adjusting AWR retention intervals, rebuilding optimizer statistics, or resizing the tablespace as wanted.
By following these practices and utilizing Oracle’s documentation, you may proactively handle the SYSAUX tablespace, assist forestall sudden development points, and keep optimum efficiency and stability of your Oracle database surroundings.
Conclusion
Efficient storage administration is crucial for sustaining the efficiency, reliability, and cost-effectiveness of your Oracle databases operating on Amazon RDS. By implementing sturdy monitoring practices, utilizing options like storage autoscaling (for RDS for Oracle), eradicating undesirable recordsdata, and performing desk reorganizations, you may optimize your storage utilization and ensure your databases function at peak effectivity.
See the Working with Storage for Amazon RDS for Oracle Situations for the newest updates and finest practices.
In regards to the authors
