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
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 SIZE1G
;
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 SIZE1G
AUTOEXTEND ON MAXSIZE10G
;
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
RESIZE200M
;
The following example adds an additional data file to a smallfile tablespace named
users_sf
.
ALTER TABLESPACE
users_sf
ADD DATAFILE SIZE100000M
AUTOEXTEND ON NEXT250m
MAXSIZEUNLIMITED
;
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 |
---|---|---|---|---|
|
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 |
---|---|---|---|---|
|
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
');