ORA-01688資料表空間無法擴充

錯誤報表:

SQL 錯誤: ORA-01688: 表 JINRILOG.USEROPERATELOG 分區 P1 無法通過 8192 (在資料表空間 JINRILOG 中) 擴充

01688. 00000 - "unable to extend table %s.%s partition %s by %s in tablespace %s"

*Cause: Failed to allocate an extent for table segment in tablespace.

*Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more

files to the tablespace indicated.

-----解決過程：

第一步：確認是否自動擴充開啟

SELECT file_id, file_name, tablespace_name, autoextensible, increment_by

FROM dba_data_files

WHERE tablespace_name = 'JINRILOG'

order by file_id desc;

第二步：嘗試resize檔案大小

SQL> alter database datafile 'S:\APP\ADMINISTRATOR\ORADATA\ORCL\JINRILOG01.DBF' resize 100000M;

alter database datafile 'S:\APP\ADMINISTRATOR\ORADATA\ORCL\JINRILOG01.DBF' resize 100000M

*

第 1 行出現錯誤:

ORA-01144: 檔案大小 (12800000 塊) 超出 4194303 塊的最大數

select value from v$parameter where name='db_block_size';

/****

FIX 1:

Specify a smaller file size.

The maximum file size is

db_block_size * 4194303

For example, if db_block_size is 2kB then maximum file size is limited

to 2k*4194303, 8Gb-2k.

FIX 2:

Change db_block_size. This can only be done by recreating of

database.

 

db_block_size Maximum data file size

------------- ----------------------

2kb 8Gb-2kb

4kb 16Gb-4kb

8kb 32Gb-8kb

16kb 64Gb-16kb

32kb 128Gb-32kb

可見db_block_size對 maximum data file size還是起決定作用的

由上可知,由於db_block_size設定為8kb的原因導致系統檔案只能32G.

****/

第二步：增加資料檔案

alter tablespace jinrilog add datafile

'S:\APP\ADMINISTRATOR\ORADATA\ORCL\JINRILOG02.DBF'

size 30g;

alter tablespace jinrilog add datafile

'S:\APP\ADMINISTRATOR\ORADATA\ORCL\JINRILOG03.DBF'

size 30g;