Using tempfiles - Amazon Relational Database Service (original) (raw)

Adding a tempfile to the instance store on a read replica

When you create a temporary tablespace on a primary DB instance, the read replica doesn't create tempfiles. Assume that an empty temporary tablespace exists on your read replica for either of the following reasons:

• You dropped a tempfile from the tablespace on your read replica. For more information, see Dropping tempfiles on a read replica.
• You created a new temporary tablespace on the primary DB instance. In this case, RDS for Oracle synchronizes the metadata to the read replica.

You can add a tempfile to the empty temporary tablespace, and store the tempfile in the instance store. To create a tempfile in the instance store, use the Amazon RDS procedurerdsadmin.rdsadmin_util.add_inst_store_tempfile. You can use this procedure only on a read replica. The procedure has the following parameters.

In the following example, the empty temporary tablespace temp01 exists on your read replica. Run the following command to create a tempfile for this tablespace, and store it in the instance store.

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

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

Dropping tempfiles on a read replica

You can't drop an existing temporary tablespace on a read replica. You can change the tempfile storage on a read replica from Amazon EBS to the instance store, or from the instance store to Amazon EBS. To achieve these goals, do the following:

1. Drop the current tempfiles in the temporary tablespace on the read replica.
2. Create new tempfiles on different storage.

To drop the tempfiles, use the Amazon RDS procedure rdsadmin.rdsadmin_util. drop_replica_tempfiles. You can use this procedure only on read replicas. The drop_replica_tempfiles procedure has the following parameters.

Assume that a temporary tablespace named temp01 resides in the instance store on your read replica. Drop all tempfiles in this tablespace by running the following command.

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

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