How to create a temporary tablespace in Oracle?

In Oracle, you can create temporary tablespace using the following syntax:

CREATE TEMPORARY TABLESPACE temp_tablespace
TEMPFILE '/path/to/tempfile.dbf' SIZE 100M
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

Key points in the above statement are as follows:

1. The name of the temporary tablespace is temp_tablespace and can be customized as needed.
2. The path and name of the data file for the temporary table space, /path/to/tempfile.dbf, can be customized as needed.
3. The initial size of the temporary tablespace is set to 100MB with the SIZE 100M option, and can be adjusted as needed.
4. Enabling AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED will automatically increase the temporary tablespace, with each expansion being 10MB and a maximum size set to unlimited.
5. LOCAL UNIFORM SIZE 1M specifies a local managed, evenly distributed extension method with each extension size set to 1MB, which can be adjusted as needed.

When creating a temporary tablespace, other parameters can also be set according to actual requirements, such as:

1. You can set a default temporary tablespace called temp_tablespace.
2. You can add temporary tablespace to a temporary tablespace group named group_name.

It’s important to note that creating temporary tablespaces requires the appropriate permissions, such as CREATE TABLESPACE and CREATE ANY DIRECTORY permissions.