Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Working with tablespaces in RDS for Oracle

You can use tablespaces with RDS for Oracle, which is a logical storage unit that stores the database's data.

Specifying database file locations in RDS for Oracle

RDS for Oracle uses Oracle Managed Files (OMF) to name database files. When you create database files the database derives the setting based on the current setting of the DB_CREATE_FILE_DEST initialization parameter.

The default value of the DB_CREATE_FILE_DEST initialization parameter is /rdsdbdata/db for standalone databases and /rdsdbdata/db/pdb for containerized (CDB/MT) architecture. If your DB instance has additional storage volumes, then you can set DB_CREATE_FILE_DEST to your volume locations. For example, if your instance has a volume mounted on /rdsdbdata/db, you can set DB_CREATE_FILE_DEST to this value.

You can modify the DB_CREATE_FILE_DEST parameter at either the session level or Oracle database instance level.

Modifying DB_CREATE_FILE_SET at the instance level

To modify the parameter at the instance level, update the parameter in the parameter group assigned to your DB instance and apply it. For more information, see RDS for Oracle initialization parameters and Modifying parameters in a DB parameter group in Amazon RDS.

Modifying DB_CREATE_FILE_DEST at the session level

You can modify the parameter at the session level by executing an ALTER SESSION statement. This approach is useful when you want to create database files in a specific location for a particular session without affecting the entire instance.

The following example shows how to check the current parameter value and modify it for the session:

SHOW PARAMETER db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /rdsdbdata/db

ALTER SESSION SET db_create_file_dest = '/rdsdbdata2/db';

Session altered.

SHOW PARAMETER db_create_file_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_create_file_dest                  string      /rdsdbdata2/db

Creating and sizing tablespaces in RDS for Oracle

When you create tablespaces, the database creates the data files in the storage volume specified by the DB_CREATE_FILE_DEST initialization parameter at the time of creation. By default, if you don't specify a data file size, tablespaces are created with the default of AUTOEXTEND ON, and no maximum size. In the following example, the tablespace users1 is autoextensible.

CREATE TABLESPACE users1;

Because of these default settings, tablespaces can grow to consume all allocated storage. We recommend that you specify an appropriate maximum size on permanent and temporary tablespaces, and that you carefully monitor space usage.

The following example creates a tablespace named users2 with a starting size of 1 gigabyte. Because a data file size is specified, but AUTOEXTEND ON isn't specified, the tablespace isn't autoextensible.

CREATE TABLESPACE users2 DATAFILE SIZE 1G;

The following example creates a tablespace named users3 with a starting size of 1 gigabyte, autoextend turned on, and a maximum size of 10 gigabytes.

CREATE TABLESPACE users3 DATAFILE SIZE 1G AUTOEXTEND ON MAXSIZE 10G;

The following example creates a temporary tablespace named temp01.

CREATE TEMPORARY TABLESPACE temp01;

You can resize a bigfile tablespace by using ALTER TABLESPACE. You can specify the size in kilobytes (K), megabytes (M), gigabytes (G), or terabytes (T). The following example resizes a bigfile tablespace named users_bf to 200 MB.

ALTER TABLESPACE users_bf RESIZE 200M;

The following example adds an additional data file to a smallfile tablespace named users_sf.

ALTER TABLESPACE users_sf ADD DATAFILE SIZE 100000M AUTOEXTEND ON NEXT 250m MAXSIZE UNLIMITED;

Creating tablespaces on additional storage volumes in RDS for Oracle

To create a tablespace on an additional storage volume, modify the DB_CREATE_FILE_DEST parameter to the volume location. The following example sets the file location to /rdsdbdata2/db.

ALTER SESSION SET db_create_file_dest = '/rdsdbdata2/db';

Session altered.

In the following example, you create a tablespace on the additional volume /rdsdbdata2/db.

CREATE TABLESPACE new_tablespace DATAFILE SIZE 10G;

Tablespace created.

SELECT tablespace_name,file_id,file_name FROM dba_data_files
WHERE tablespace_name = 'NEW_TABLESPACE';

TABLESPACE_NAME              FILE_ID FILE_NAME
------------------------- ---------- --------------------------------------------------------------------------------
NEW_TABLESPACE                     7 /rdsdbdata2/db/ORCL_A/datafile/o1_mf_newtable_a123b4c5_.dbf

To create a smallfile tablespace and spread its data files across different storage volumes, add data files to the tablespace after you create it. In the following example, you create a tablespace with the data files in the default location of /rdsdbdata/db. Then you set the default destination to /rdsdbdata/db2. When you add a data file to your newly created tablespace, the database stores the file in /rdsdbdata/db2.

ALTER SESSION SET db_create_file_dest = '/rdsdbdata/db';

Session altered.

CREATE SMALLFILE TABLESPACE smalltbs DATAFILE SIZE 10G;

Tablespace created.

SELECT tablespace_name,file_id,file_name FROM dba_data_files
WHERE tablespace_name = 'SMALLTBS';

TABLESPACE_NAME              FILE_ID FILE_NAME
------------------------- ---------- --------------------------------------------------------------------------------
SMALLTBS                           8 /rdsdbdata/db/ORCL_A/datafile/o1_mf_smalltbs_n563yryk_.dbf

ALTER SESSION SET db_create_file_dest = '/rdsdbdata2/db';

Session altered.

ALTER TABLESPACE smalltbs ADD DATAFILE SIZE 10G;

Tablespace altered.

SELECT tablespace_name,file_id,file_name FROM dba_data_files
WHERE tablespace_name = 'SMALLTBS';

TABLESPACE_NAME              FILE_ID FILE_NAME
------------------------- ---------- --------------------------------------------------------------------------------
SMALLTBS                           8 /rdsdbdata/db/ORCL_A/datafile/o1_mf_smalltbs_n563yryk_.dbf
SMALLTBS                           9 /rdsdbdata2/db/ORCL_A/datafile/o1_mf_smalltbs_n564004g_.dbf

Setting the default tablespace in RDS for Oracle

To set the default tablespace, use the Amazon RDS procedure rdsadmin.rdsadmin_util.alter_default_tablespace. The alter_default_tablespace procedure has the following parameters.

The following example sets the default tablespace to users2:

EXEC rdsadmin.rdsadmin_util.alter_default_tablespace(tablespace_name => 'users2');

Setting the default temporary tablespace in RDS for Oracle

To set the default temporary tablespace, use the Amazon RDS procedure rdsadmin.rdsadmin_util.alter_default_temp_tablespace. The alter_default_temp_tablespace procedure has the following parameters.

The following example sets the default temporary tablespace to temp01.

EXEC rdsadmin.rdsadmin_util.alter_default_temp_tablespace(tablespace_name => 'temp01');

Creating a temporary tablespace on the instance store

To create a temporary tablespace on the instance store, use the Amazon RDS procedure rdsadmin.rdsadmin_util.create_inst_store_tmp_tblspace. The create_inst_store_tmp_tblspace procedure has the following parameters.

The following example creates the temporary tablespace temp01 in the instance store.

EXEC rdsadmin.rdsadmin_util.create_inst_store_tmp_tblspace(p_tablespace_name => 'temp01');

For more information, see Storing temporary data in an RDS for Oracle instance store.