Set temporary tablespace Group

Because the temporary tablespace is large, I want to set the temporary tablespace as a temporary tablespace group. After deleting the old tablespace, I found that the physical file was not deleted and cannot be deleted manually, at the same time, the data file and temporary tablespace are not found in dba_temp_files and dba_tablespaces. After restarting the database, you can delete the data file and record it here.
The operations I perform in the following steps:
----- Query the temporary tablespace and the default temporary tablespace.

SELECT TABLESPACE_NAME FROM DBA_TABLESPACES;SELECT DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE FROM DBA_USERSWHERE USERNAME=‘P_USER‘;

 

---- Create a temporary tablespace group.

CREATE TEMPORARY TABLESPACE TEMP1_1 TEMPFILE ‘E:\ORADATA\PDB\TEMP1_1.DBF‘ SIZE 2000MTABLESPACE GROUP TMP_GRP1;CREATE TEMPORARY TABLESPACE TEMP1_2 TEMPFILE ‘E:\ORADATA\PDB\TEMP1_2.DBF‘ SIZE 2000MTABLESPACE GROUP TMP_GRP1;CREATE TEMPORARY TABLESPACE TEMP1_3 TEMPFILE ‘E:\ORADATA\PDB\TEMP1_3.DBF‘ SIZE 2000MTABLESPACE GROUP TMP_GRP1;

 

---- Query the temporary tablespace groups.

SELECT * FROM DBA_TABLESPACE_GROUPS;

---- Designate the user to a temporary tablespace

ALTER USER P_USER TEMPORARY TABLESPACE TMP_GRP1;

 

---- Switch all users to the specified temporary tablespace

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TMP_GRP1;

 

---- Query the user's default temporary tablespace again

SELECT DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE FROM DBA_USERSWHERE USERNAME=‘P_USER‘;

----- View temporary tablespace files

Select tablespace_name, file_name, Bytes/1024/1024 file_size, autoextensible from dba_temp_files; select status, enabled, name, Bytes/1024/1024 file_size from V _ $ tempfile; -- View by sys

 

------ Delete a data file in the temporary tablespace:

ALTER DATABASE TEMPFILE ‘E:\ORADATA\PDB\PXTMP01.DBF‘ OFFLINE;ALTER DATABASE TEMPFILE ‘E:\ORADATA\PDB\PXTMP01.DBF‘ DROP;ALTER DATABASE TEMPFILE ‘E:\ORADATA\PDB\PXTMP01.DBF‘ OFFLINE;ALTER DATABASE TEMPFILE ‘E:\ORADATA\PDB\PXTMP02.DBF‘ DROP;

------ Delete temporary tablespace (delete permanently ):

DROP TABLESPACE QX_TEMP INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

Sometimes the temporary tablespace data files cannot be deleted in windows, and the database must be restarted.

Set temporary tablespace Group