Oracle Cloud Infrastructure Documentation

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 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 tablespace
  • DROP TABLESPACE to drop a tablespace
  • MANAGE TABLESPACE to update the status of the tablespace
  • ALTER 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:

  1. In the Tablespaces section on the Managed database details page, click Create.
  2. In the Create tablespace panel:
    1. Specify the following in the General section:
      1. Name: Enter a name for the tablespace.
      2. Default tablespace: Optionally, select this check box to set the tablespace as the default tablespace.
      3. Type: Select Permanent or Temporary to specify the type of tablespace. Permanent is selected by default.
      4. Bigfile: Select Smallfile or Bigfile to specify the volume of the tablespace. Smallfile is selected by default.
        Note

        If you select Bigfile, then the tablespace can have only one datafile.
      5. Status: Select Read write or Read only to specify the status of the tablespace. Read write is selected by default.
      6. Encryption: Select the Enabled check box to enable encryption and select the encryption standard in the Encryption algorithm field. The Enabled check box is selected by default and you can deselect it if encryption is not required.
        Note

        To enable encryption, Transparent Data Encryption must be enabled on the database and the wallet must be open. For more information, see Introduction to Transparent Data Encryption in Oracle Database Advanced Security Guide.
    2. Specify the following in the Add datafiles section:

      You can add datafiles to Oracle Automatic Storage Management (Oracle ASM) disk groups or in the file system storage. Also, you can enable the use of Oracle Managed Files or explicitly specify which datafiles or temp files must be added. For information on Oracle ASM, see Overview of Oracle Automatic Storage Management in Oracle Automatic Storage Management Administrator's Guide.

      1. Storage type: Review the storage type: File system or Automatic storage management.
      2. Use Oracle managed files: Select to use Oracle Managed datafiles in the tablespace. This check box is selected by default and the Oracle managed datafile is a 0.1 GB datafile, which is autoextensible and has an unlimited maximum size. For information on Oracle Managed Files, see Using Oracle Managed Files in Oracle Database Administrator’s Guide.

        If you deselect the Use Oracle managed files check box, then one of the following options is displayed depending on the Storage type.

        • Datafile path and name: Specify the absolute path and name of the datafiles and click Add. This field is displayed for the File system storage type.
        • Disk groups: Specify the disk group in which the datafiles must be stored and click Add. This field is displayed for the Automatic storage management storage type.
      3. File size (GB): Enter the initial size of the datafile in GB. By default, 1 GB is specified.
      4. Auto extend: Select to automatically extend the datafile when it reaches its maximum size limit. This check box is selected by default, and 100 MB is specified as the increment value in the Increment (MB) field, and the Unlimited check box under Maximum file size is selected. You can make changes to the Increment (MB) value and deselect the Unlimited check box and specify a maximum size for the datafile in the Specific size (MB) field.
    3. Specify the following in the Space section:
      1. Segment space management: Select Automatic or Manual to specify how to manage space within a segment. Automatic is selected by default.
      2. Compression: Select None or Basic to specify whether you want to use a compression option. None is selected by default.
      3. Extent allocation: Select Automatic or Uniform to specify extent allocation. Automatic is selected by default. If you select Uniform to specify that the extent allocation is in a fixed, uniform size, then you can optionally specify the extent size in the Extent size (KB) field.
      4. Block size: Review the default block size.
    4. Select one of the available options in the Credential type drop-down list in the Credentials section to specify database credentials to connect to the Managed Database. For information on credential types, see Use Credentials to Perform Database Management Tasks.
    5. Click Create.
The newly created tablespace is displayed in the Tablespaces section on the Managed database details page.

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 (Actions) 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 (Actions) 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.