temp表空间的缩小、计算、移动方法

缩小方法：
-- Create new temporary tablespace with smaller size.
SQL> create temporary tablespace TEMP1 tempfile 'c:\temp01.dbf' size 100M extent management local uniform size 128K;
-- Set new tablespace as default temporary tablespace for all users in database.
SQL> alter user temporary tablespace TEMP1;
If you have a large number of users, the following SQL can be used to generate the code to change them all.
SQL> select 'alter user 'username' temporary tablespace TEMP1;' from dba_users;
-- Drop the old tablespace.SQL> drop tablespace temp including contents.

计算方法：无，无法估计temp表空间的合适大小
移动方法：
To move tempfiles of temporary tablespace:
first: alter database tempfile 'c:\..\temp1.dbf' offline.
second: use the Operating system command to copy or move the tempfile;
third: update the control file using the:
alter database rename file 'c:\...\temp1.dbf' to 'c:\...\temp2.dbf';
But:Renaming Locally-Managed TEMPFILES is not permited by oracle with the error :ORA-01516

solution1:
a.create a new temporary tablespace:
create temporary tablespace temp2 tempfile 'c:\...\temp3.dbf' size 10m extent management local uniform size 1m;
b.set the new temporary tablespace to the default temporary tablespace alter database default temporary tablespace temp2;
c.drop and recreate the old temporary tablespace:
drop tablespace temp1;
create temporary tablespace temp1 tempfile 'c:\...\temp1.dbf' size 10m reuse extent management local uniform size 1m;
This temporary tablespace temp1 will prepared for the temp2's backup.

solution2:
a.add the default temporary tablespace "temp1" a new tempfile:
alter tablespace temp1 add tempfile 'c:\..\temp2.dbf' size 10m;
b.drop the default temporary tablespace temp1's old tempfile:
alter database tempfile 'c:\..\temp1.dbf' drop including datafiles;