Monitor and Manage Tablespaces and Datafiles
You can monitor the tablespaces and datafiles stored in each tablespace in the Managed Database, and perform storage management tasks such as creating, editing, and dropping tablespaces and adding and editing datafiles.
To view tablespaces, go to the Managed database details page and click Tablespaces on the left pane under Resources.
In the Tablespaces section, you can view the list of tablespaces and monitor the space used and allocated (in GB) for all the datafiles within the system and user tablespaces at the top right of the section. You can also monitor each individual tablespace, which can be further expanded to view all the datafiles in the tablespace. In this section, you can view bar graphs that provide a quick insight into space usage, information such as the status of the tablespaces and datafiles, the type of tablespace, whether the tablespaces and datafiles can be automatically extended, and the directory location.
For Autonomous Databases Serverless, you cannot perform tablespace management tasks such as creating a tablespace, dropping a tablespace, and resizing data files.
For more information on tablespaces, see Overview of Tablespaces in Oracle Database Concepts.
Privileges Required to Work with Tablespaces
You must have the CREATE TABLESPACE
system privilege to
create a tablespace. To create the SYSAUX
tablespace, you must have
the SYSDBA
system privilege. In addition, you must have the
following privileges:
ALTER TABLESPACE
to edit a tablespaceDROP TABLESPACE
to drop a tablespaceMANAGE TABLESPACE
to update the status of the tablespaceALTER DATABASE
to set a default tablespace or resize datafiles
Create a Tablespace
You can create additional tablespaces to store user data, so that not all data is
stored in the USERS
tablespace. The following are some reasons 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 or maintenance reasons. For example, if you must recover all application data from backup due to a hardware or software failure, and you want to perform an offline recovery. If the application data is kept in a separate tablespace, then you can take just that tablespace offline and recover it, without affecting the operation of other database applications.
- Some applications, such as those with large partitioned tables, 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.
To create a tablespace:
For more information on the fields and attributes to be specified in the Create tablespace panel, see CREATE TABLESPACE in Oracle Database SQL Language Reference.
Perform Other Storage Management Tasks
You can perform additional storage management tasks in the Tablespaces section.
Tablespace Tasks
Click the Actions icon () available for each tablespace and perform the following tasks. Note that the availability of the options in the Actions menu is dependent on the conditions set for the tablespace and not all the options are displayed for all tablespaces. For example, if the tablespace is already a default tablespace, then the Set as default tablespace option is not displayed in the Actions menu.
- View details: Click to view the general and storage details of the tablespace.
- Edit tablespace: Click to edit the tablespace details. For a smallfile tablespace, you can only edit the name of the tablespace, however, for a bigfile tablespace, you can edit the name, file size, and Auto extend settings.
- Drop tablespace: Click to drop the
tablespace. In the Drop tablespace panel, you're provided
with the options to drop the datafiles and drop the contents of the
tablespace.
Note
If a tablespace is dropped, then the objects and data in it are no longer available, and to recover them can be a time-consuming process. It's recommended that you perform a backup before you drop a tablespace. - Set tablespace to read only or Set tablespace to read write: Click to set tablespace to read only or read write.
- Set as default tablespace: Click to set the tablespace as the default tablespace.
- Add datafile: Click to add datafiles to the tablespace.
Datafile Tasks
Expand the list of datafiles and click the Actions icon () available for a datafile and perform the following tasks. Note that the Drop datafile option is not displayed in the Actions menu if there's only one datafile in the tablespace.
- Edit: Click to edit the file size and the Auto extend settings of the datafile.
- Drop datafile: Click to drop the
datafile.
Note
Dropping a datafile may cause data loss. It's recommended that you perform a backup before you drop a datafile.