关于DataFile的大小问题
当发生临时表空间不够时，可以用以下语句来创建一个比较大的临时表空间(各个文件可以创建到不同的磁盘以备用)
SQL>create temporary tablespace TMPACCT2 tempfile '/u02/oradata/acct/tmpacct2-1.dbf' size 10M autoextend on next 10M maxsize 2000M;
SQL> alter tablespace TMPACCT2 add tempfile '/u02/oradata/acct/tmpacct2-2.dbf' size 10M autoextend on next 10M maxsize 2000M;
SQL> alter tablespace TMPACCT2 add tempfile '/u02/oradata/acct/tmpacct2-3.dbf' size 10M autoextend on next 10M maxsize 2000M;
SQL> alter tablespace TMPACCT2 add tempfile '/u02/oradata/acct/tmpacct2-4.dbf' size 10M autoextend on next 10M maxsize 2000M;
SQL> alter tablespace TMPACCT2 add tempfile '/u02/oradata/acct/tmpacct2-5.dbf' size 10M autoextend on next 10M maxsize 2000M;
SQL> alter user TCICDR TEMPORARY TABLESPACE TMPACCT2;
当遇到ORA-01652: unable to extend temp segment by 128 in tablespace xxxxx问题的时候，并不代表就是temp表空间不足，因为虽然Oracle会首先在临时表空间中创建需要创建的object，但是在最后还是需要将其转换到实际的tablespace中，而到tablespace空间不足时，创建的事务被打断，SMON检测到之后就会find the temporary extents out there and clean them up，所以这里的“temp segment”其实是指实际的tablespace空间不足。可以用以下方法来增加tablespace的空间：
SQL> alter database datafile '&f' autoextend off;
old 1: alter database datafile '&f' autoextend off
new 1: alter database datafile '/home/ora10gr2/oracle/product/10.2.0/oradata/ora10gr2/system01.dbf' autoextend off
Database altered.
SQL> create table ttt tablespace system as select * from all_objects;
create table ttt tablespace system as select * from all_objects
*
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace SYSTEM
SQL> alter database datafile '&f' autoextend on next 10m maxsize 31g;
old 1: alter database datafile '&f' autoextend on next 10m maxsize 31g
new 1: alter database datafile '/home/ora10gr2/oracle/product/10.2.0/oradata/ora10gr2/system01.dbf' autoextend on next 10m maxsize 31g
Database altered.
SQL> create table ttt tablespace system as select * from all_objects;
Table created.
--当然仅为举例，实际中千万不要把用户表创建到SYSTEM表空间