關于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表空間
?