How to Create Temporary Tablespace in Oracle

1 year ago
Noah Thompson
1 minute

In Oracle database, you can create a temporary tablespace by following these steps.

  1. First, log in to the sys user or a user with sysdba privileges in the Oracle database.
  2. Create a new temporary tablespace using the following SQL statement.
CREATE TEMPORARY TABLESPACE temp_tbs
TEMPFILE '/path/to/tempfile.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

In this, temp_tbs is the name of the temporary tablespace, /path/to/tempfile.dbf is the path of the temporary file, SIZE specifies the initial size, AUTOEXTEND ON indicates automatic extension, NEXT specifies the size of the next extension, MAXSIZE specifies the maximum size, EXTENT MANAGEMENT LOCAL indicates using local management, and UNIFORM SIZE specifies a uniform extension size.

  1. After creation, you can designate the previously default temporary tablespace as the newly created temporary tablespace.
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp_tbs;

By following the above steps, you can create a new temporary tablespace in Oracle database and set it as the default temporary tablespace.

bannerAds