查找修改前用户的属性
SQL> select username,temporary_tablespace from dba_users;
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEW TEMP
SYS TEMP
SYSTEM TEMP
DBSNMP TEMP
SYSMAN TEMP
RMAN TEMP
OUTLN TEMP
MDSYS TEMP
ORDSYS TEMP
CTXSYS TEMP
ANONYMOUS TEMP
查找临时表空间的位置
SQL> select name from v$tempfile;
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/SDRS02/datafile/o1_mf_temp_424otkpt_.tmp
SQL> create temporary tablespace tmp tempfile '/u01/app/oracle/oradata/SDRS02/datafile/TEMP01.DBF' size 10240m;
Tablespace created.
对临时表空间进行转换
SQL> alter database default temporary tablespace tmp;
Database altered.
删除旧的表空间及其数据文件
SQL> drop tablespace temp including contents;
Tablespace dropped.
对比显示修改后的结果
SQL> select username,temporary_tablespace from dba_users;
USERNAME TEMPORARY_TABLESPACE
------------------------------ ------------------------------
MGMT_VIEW TMP
SYS TMP
SYSTEM TMP
DBSNMP TMP
SYSMAN TMP
RMAN TMP
OUTLN TMP
MDSYS TMP
ORDSYS TMP
CTXSYS TMP
ANONYMOUS TMP
添加数据文件
SQL> alter tablespace tmp add tempfile '/u01/app/oracle/oradata/SDRS03/datafile/TEMP02.DBF' size 5120m;
Tablespace altered.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/216109/viewspace-1003920/ ，如需转载，请注明出处，否则将追究法律责任。
