關(guān)于DataFile的大小問題
?
??? 當(dāng)發(fā)生臨時(shí)表空間不夠時(shí)，可以用以下語句來創(chuàng)建一個(gè)比較大的臨時(shí)表空間(各個(gè)文件可以創(chuàng)建到不同的磁盤以備用)
?
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;
?
?
??? 當(dāng)遇到ORA-01652: unable to extend temp segment by 128 in tablespace xxxxx問題的時(shí)候，并不代表就是temp表空間不足，因?yàn)殡m然Oracle會(huì)首先在臨時(shí)表空間中創(chuàng)建需要?jiǎng)?chuàng)建的object，但是在最后還是需要將其轉(zhuǎn)換到實(shí)際的tablespace中，而到tablespace空間不足時(shí)，創(chuàng)建的事務(wù)被打斷，SMON檢測(cè)到之后就會(huì)find the temporary extents out there and clean them up，所以這里的“temp segment”其實(shí)是指實(shí)際的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.
--當(dāng)然僅為舉例，實(shí)際中千萬不要把用戶表創(chuàng)建到SYSTEM表空間
?