Using tablespaces

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

Topics

• Creating and sizing tablespaces
• Setting the default tablespace
• Setting the default temporary tablespace
• Creating a temporary tablespace on the instance store

Creating and sizing tablespaces

Amazon RDS only supports Oracle Managed Files (OMF) for data files, log files, and control files. When you create data files and log files, you can't specify the physical file names.

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;

Setting the default tablespace

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.

Parameter name	Data type	Default	Required	Description
tablespace_name	varchar	—	Yes	The name of the default tablespace.

The following example sets the default tablespace to users2:

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

Setting the default temporary tablespace

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.

Parameter name	Data type	Default	Required	Description
tablespace_name	varchar	—	Yes	The name of the default temporary tablespace.

The following example sets the default temporary tablespace to temp01.

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