Previously, we announced the tablespace monitoring feature as part of the Oracle Database Management service. Now we are featuring new tablespace management support in Oracle Cloud Infrastructure (OCI) Database Management service for on-premises and cloud databases. The feature enhancement allows you to perform common database tasks such as:
- Create, Modify and Drop Tablespaces
- Support for OMF and ASM
- Support for Big Files
- Set Auto Extend for datafiles
- Set Default Tablespace
Effective tablespace management for Database Administrators
A common task for a database administrator (DBA) is to monitor and effectively manage tablespaces and their underlying data files. These tasks include features such as creating, modifying and dropping tablespaces.
Creating a tablespace to store data use cases
You can create additional tablespaces to store user data so that not all data is stored in the USERS tablespace. The following are some typical use-cases to create additional tablespaces:
- For certain users, groups of users, or applications, it may be convenient to keep all application data in a separate tablespace or set of tablespaces for backup and recovery, isolation, or maintenance reasons.
- Some applications, such as those with large partitioned – large tables (partitioned/non-partitioned), may benefit from distributing data across multiple tablespaces. This approach allows the optimal use of the available storage because frequently accessed data can be placed on high-performance disks, and infrequently retrieved data can be placed on less expensive storage.
The feature supports basic management features around creating new and managing existing tablespaces within a database. It also provides the necessary perimeters to tweak the features around the name, default tablespace, file type, status, segment space management, compression, and extent allocation and change the same settings on existing tablespaces. On the data files front, it supports both Oracle Managed Files (OMF) and Automatic Storage Management (ASM) based formats.
Modifying a tablespace to increase data file sizes, add data files, or automatically extend
This task provides us with the flexibility to modify a tablespace. For example, you can extend it by increasing data file sizes or adding another data file, setting it to automatically extend, change its space usage alert thresholds.
Dropping a tablespace to delete from the data
Lastly, one can use this feature to drop (delete) a tablespace. After a tablespace has been dropped, the objects and data in it are no longer available. To recover them can be a time-consuming process. Oracle recommends performing a backup before and after dropping a tablespace.
Having a standardization strategy for your database deployments is important. Using the tablespace management features, you can achieve easy life-cycle management, increased security, and reduced human error.
Resources
- Sign up for an Oracle Cloud Infrastructure trial account today!
- For more information about these features and how you can use them, see a demo and the Monitor and Manage Tablespaces and Datafiles documentation.