create temporary tablespace TEMP2 TEMPFILE '/mnt/MX_ORA_6/oradata/PROD/temp2.dbf'

SIZE 512M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED;

alter database default temporary tablespace temp2;

drop tablespace temp including contents and datafiles;

create temporary tablespace TEMP TEMPFILE '/mnt/MX_ORA_1/home/oracle/oradata/PROD/temp01.dbf'

SIZE 1024M REUSE AUTOEXTEND ON NEXT 128m MAXSIZE 8192M;

ALTER TABLESPACE temp ADD TEMPFILE '/mnt/MX_ORA_6/oradata/PROD/temp02.dbf'

SIZE 1024M REUSE AUTOEXTEND ON NEXT 128M MAXSIZE UNLIMITED;

alter database default temporary tablespace temp;

drop tablespace temp2 including contents and datafiles;

alter user matrix temporary tablespace temp;

--verification--

select temporary_tablespace from dba_users where username = 'MATRIX';

select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

select file_name,tablespace_name,AUTOEXTENSIBLE from dba_temp_files;

ITPub博客

Shrink the temp tablespace