1：查看旧临时表空间信息 

set pagesize 999

set linesize 160

col PROPERTY_NAME for a26

col PROPERTY_VALUE for a18

SELECT PROPERTY_NAME, PROPERTY_VALUE  FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

 

set pagesize 999

set linesize 160

col USERNAME for a30

col TEMPORARY_TABLESPACE for a18

SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS;

 

 

2：创建中转的临时表空间 

CREATE TEMPORARY TABLESPACE TEMP02  TEMPFILE '/data/orcl/temp_01.dbf' SIZE 8G AUTOEXTEND ON maxsize 30G;

 

 

3：切换临时表空间。

 

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP02;

 

 

4：重新指定用户临时表空间为新建的临时表空间 

 

select 'alter user '|| username ||' TEMPORARY TABLESPACE TEMP02 ;' from dba_users;

 

alter user GLOBAL_READ TEMPORARY TABLESPACE TEMP02 ;

alter user CLSPREAD TEMPORARY TABLESPACE TEMP02 ;

alter user APSREAD TEMPORARY TABLESPACE TEMP02 ;

alter user SASCS26USER TEMPORARY TABLESPACE TEMP02 ;

alter user LICAISASUSER TEMPORARY TABLESPACE TEMP02 ;

alter user NEWCSUSER TEMPORARY TABLESPACE TEMP02 ;

alter user WEBP2PUSER TEMPORARY TABLESPACE TEMP02 ;

alter user DOCUSER TEMPORARY TABLESPACE TEMP02 ;

alter user CSUSER TEMPORARY TABLESPACE TEMP02 ;

alter user SALARYUSER TEMPORARY TABLESPACE TEMP02 ;

alter user WEBUSER TEMPORARY TABLESPACE TEMP02 ;

alter user CLSPUSER TEMPORARY TABLESPACE TEMP02 ;

alter user FCUSER TEMPORARY TABLESPACE TEMP02 ;

alter user APSUSER TEMPORARY TABLESPACE TEMP02 ;

alter user SCOTT TEMPORARY TABLESPACE TEMP02 ;

alter user TEMPUSER TEMPORARY TABLESPACE TEMP02 ;

alter user TSMSYS TEMPORARY TABLESPACE TEMP02 ;

alter user MDDATA TEMPORARY TABLESPACE TEMP02 ;

alter user DIP TEMPORARY TABLESPACE TEMP02 ;

alter user DBSNMP TEMPORARY TABLESPACE TEMP02 ;

alter user SYSMAN TEMPORARY TABLESPACE TEMP02 ;

alter user MDSYS TEMPORARY TABLESPACE TEMP02 ;

alter user ORDSYS TEMPORARY TABLESPACE TEMP02 ;

alter user EXFSYS TEMPORARY TABLESPACE TEMP02 ;

alter user DMSYS TEMPORARY TABLESPACE TEMP02 ;

alter user WMSYS TEMPORARY TABLESPACE TEMP02 ;

alter user CTXSYS TEMPORARY TABLESPACE TEMP02 ;

alter user ANONYMOUS TEMPORARY TABLESPACE TEMP02 ;

alter user XDB TEMPORARY TABLESPACE TEMP02 ;

alter user ORDPLUGINS TEMPORARY TABLESPACE TEMP02 ;

alter user SI_INFORMTN_SCHEMA TEMPORARY TABLESPACE TEMP02 ;

alter user OLAPSYS TEMPORARY TABLESPACE TEMP02 ;

alter user MGMT_VIEW TEMPORARY TABLESPACE TEMP02 ;

alter user SYS TEMPORARY TABLESPACE TEMP02 ;

alter user SYSTEM TEMPORARY TABLESPACE TEMP02 ;

alter user OUTLN TEMPORARY TABLESPACE TEMP02 ;

 

 

5：删除旧的临时表空间数据文件 

 

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;