Create Temporary Tablespace in Oracle: Quick Guide

1 year ago
Sophia Anderson
2 minutes

You can create a temporary tablespace in Oracle database by following these steps:

  1. Firstly, connect to the Oracle database and log in as the SYS user.
  2. To create a new temporary tablespace, you can use the following SQL statement:
CREATE TEMPORARY TABLESPACE temp_ts
TEMPFILE 'temp_ts.dbf' SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

temp_ts is the name of the newly created temporary tablespace, temp_ts.dbf is the filename of the data file for the temporary tablespace, SIZE specifies the initial size of the tablespace, AUTOEXTEND ON means the tablespace file will automatically grow, EXTENT MANAGEMENT LOCAL indicates local management is used, and UNIFORM SIZE means the extents of the tablespace will be evenly distributed.

  1. To set the user-specified temporary tablespace as the default temporary tablespace, you can use the following SQL statement:
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_ts;

This way, users will default to using the temp_ts temporary tablespace when creating temporary tables.

  1. Finally, to confirm if the temporary tablespace has been successfully created, you can use the following SQL statement to query all tablespace information in the database:
SELECT tablespace_name, status, contents, extent_management, allocation_type
FROM dba_tablespaces;

By following the aforementioned steps, you can successfully create a temporary tablespace and set it as the default temporary tablespace.

bannerAds