注意資料檔案最大塊數限制

SQL> alter database tempfile '/oracle/app/oradata/bic/temp01.dbf' resize 33G;
alter database tempfile '/oracle/app/oradata/bic/temp01.dbf' resize 33G
*
ERROR at line 1:
ORA-01144: File size (4325376 blocks) exceeds maximum of 4194303 blocks

$oerr ora 1144
01144, 00000, "File size (%s blocks) exceeds maximum of %s blocks"
// *Cause: Specified file size is larger than maximum allowable size value.
// *Action: Specify a smaller size.

在db_block_size=8192時，最大塊數是4194303
大概是32GB
SQL> select 34359730176/(1024*1024*1024) from dual;

34359730176/(1024*1024*1024)
----------------------------
31.9999924

所以，如果想讓datafile超出32GB,可以設定更大的塊 (在建表空間時可以指定)

[@more@]

或者建立bigfile的表空間,下面是一個例子

SQL> set head off
SQL> set linesize 1000
SQL> set pagesize 0
SQL> col tablespace_name for a10
SQL> col file_name for a50
SQL> select ts.tablespace_name,ts.BLOCK_SIZE,ts.status,df.file_name,df.bytes/(1024*1024*1024) bytes,df.BLOCKS,ts.BIGFILE
2 from dba_tablespaces ts,dba_data_files df
3 where df.tablespace_name=ts.tablespace_name;
TBS_TEST 8192 ONLINE /oracle/app/oradata/bic/test.dbf 33 4325376 YES
TEST_TS 16384 ONLINE /oracle/app/oradata/bic/test_ts 40 2621440 NO

參考資料
http://hi.baidu.com/benmaoer/blog/item/39f80cf3d4d686c20b46e078.html