Convert temporary tablespace from small file to big file.

Convert temporary tablespace from smallfile to bigfile.

1.check the current default temporary tablespace.

SQL> select property_name, property_value

    from database_properties

    where property_name like '%TEMP%';  2

PROPERTY_NAME              PROPERTY_VALUE

--------------------------------------------

DEFAULT_TEMP_TABLESPACE    TEMP

2.Create new temporary tablespace TEMP2.

CREATE BIGFILE TEMPORARY TABLESPACE TEMP2 TEMPFILE

   SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE 2G

TABLESPACE GROUP ''

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

3.Change the default temporary tablespace to TEMP2.

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2;

4.Drop the smallfile TEMP tablespace.

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

5.Create new bigfile TEMP temporary tablespace.

CREATE BIGFILE TEMPORARY TABLESPACE TEMP TEMPFILE

   SIZE 1G AUTOEXTEND ON NEXT 1G MAXSIZE 2G

TABLESPACE GROUP ''

EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

6.Change the default temporary tablespace to TEMP.

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

7.Drop the TEMP2 tablespace.

DROP TABLESPACE TEMP2 INCLUDING CONTENTS AND DATAFILES;