有個應用聯絡我說他們在進行業務資料匯入的時候提示表空間不足，於是他們問我該表空間是自動擴充套件的，為什麼還會提示空間不足呢？

對於這個問題我自己也有2個疑問，
第一個是自動擴充套件為什麼還會提示空間不足呢？
第二個是autoextend on 預設的next值是多少？

我做了以下的試驗，USERS表空間下有1個資料檔案，在對其開啟了自動擴充套件以後，我們檢視它的DDL語句發現NEXT值為8192，也就是說一次會擴充套件1個塊8K的大小.
對於上面提到的問題，我還注意到maxsize值為10M，也就是說該檔案雖說開啟了自動擴充套件，但最大隻能使用至10M，這也就是為什麼會提示空間不足的真正原因，被maxsize值限制了，這個值預設是unlimited，這個10M可能是在建立表空間時指定的，所以第一個問題解答了。

SYS@em12c>set long 99999999
SYS@em12c>select dbms_metadata.get_ddl('TABLESPACE','&tbsname') from dual;
Enter value for tbsname: USERS
old 1: select dbms_metadata.get_ddl('TABLESPACE','&tbsname') from dual
new 1: select dbms_metadata.get_ddl('TABLESPACE','USERS') from dual

DBMS_METADATA.GET_DDL('TABLESPACE','USERS')
--------------------------------------------------------------------------------
CREATE TABLESPACE "USERS" DATAFILE
'/oracle/oradata/em12c/users01.dbf' SIZE 5242880
AUTOEXTEND ON NEXT 8192 MAXSIZE 10485760
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO

第二個問題，autoextend on 預設的next值是多少？上面5M的資料檔案next值為8k，我們再看看其它資料檔案的情況呢？
我測試環境下這個大小2G的資料檔案next值為20M,另一個大小32G的資料檔案next值為50M，這個值根據資料檔案大小自動調整的嗎？

DBMS_METADATA.GET_DDL('TABLESPACE','MGMT_ECM_DEPOT_TS')
--------------------------------------------------------------------------------
CREATE TABLESPACE "MGMT_ECM_DEPOT_TS" DATAFILE
'/oracle/oradata/em12c/mgmt_ecm_depot1.dbf' SIZE 20971520
AUTOEXTEND ON NEXT 20971520 MAXSIZE 32767M
...

DBMS_METADATA.GET_DDL('TABLESPACE','MGMT_TABLESPACE')
--------------------------------------------------------------------------------
CREATE TABLESPACE "MGMT_TABLESPACE" DATAFILE
'/oracle/oradata/em12c/mgmt.dbf' SIZE 209715200
AUTOEXTEND ON NEXT 52428800 MAXSIZE 32767M
...

我將這個資料檔案用資料填充至2G大小，看看next是否變化,在不變插入資料時，可以看到很多等待事件是由於init file造成的，
說明在資料檔案擴充套件很頻繁時，過小的next size是很影響效能的。所以當我們看到很多等待在Data file init write事件的時候，
也說明autoextend on next值設定過小了。
*** 2016-10-12 12:14:13.221
WAIT #140465064598264: nam='Data file init write' ela= 4595 count=1 intr=256 timeout=4294967295 obj#=-1 tim=1476245653221043
WAIT #140465064598264: nam='Data file init write' ela= 4936 count=1 intr=256 timeout=4294967295 obj#=-1 tim=1476245653226491
WAIT #140465064598264: nam='Data file init write' ela= 7302 count=1 intr=256 timeout=4294967295 obj#=-1 tim=1476245653234144
WAIT #140465064598264: nam='Data file init write' ela= 3459 count=1 intr=256 timeout=4294967295 obj#=-1 tim=1476245653238035
WAIT #140465064598264: nam='Data file init write' ela= 4133 count=1 intr=256 timeout=4294967295 obj#=-1 tim=1476245653515536
WAIT #140465064598264: nam='Data file init write' ela= 4447 count=1 intr=256 timeout=4294967295 obj#=-1 tim=1476245653520532
WAIT #140465064598264: nam='Data file init write' ela= 3786 count=1 intr=256 timeout=4294967295 obj#=-1 tim=1476245653524725
WAIT #140465064598264: nam='Data file init write' ela= 3239 count=1 intr=256 timeout=4294967295 obj#=-1 tim=1476245653528313
WAIT #140465064598264: nam='Data file init write' ela= 8088 count=1 intr=256 timeout=4294967295 obj#=-1 tim=1476245653536716

--這裡可以看到資料檔案已經自動擴充套件到4G大小了，但是next值一直是8k,沒有變化。
Name Type Managment Files Size(M) Used(M) Free(M) used(%)
------------------------------ --------- ---------- ---------- ---------- ---------- ---------- ----------
USERS PERMANENT LOCAL 1 3617.1 3585 32.1 99.11

SYS@em12c>select dbms_metadata.get_ddl('TABLESPACE','&tbsname') from dual;
Enter value for tbsname: USERS
old 1: select dbms_metadata.get_ddl('TABLESPACE','&tbsname') from dual
new 1: select dbms_metadata.get_ddl('TABLESPACE','USERS') from dual

DBMS_METADATA.GET_DDL('TABLESPACE','USERS')
--------------------------------------------------------------------------------
CREATE TABLESPACE "USERS" DATAFILE
'/oracle/oradata/em12c/users01.dbf' SIZE 5242880
AUTOEXTEND ON NEXT 8192 MAXSIZE 15360M
...

後續當資料檔案擴充套件至8G大小時next值一直是8k,沒有變化。
我又在ASM下和Exadata平臺下進行了一個簡單的測試，發現在ASM平臺下autoextend on next預設值是10M，而在我的Exadata X4的ASM平臺下發現autoextend on next預設值是100M，看來這是oracle針對不同平臺作了最佳化。

所以我認為第二個問題的答案是不同的平臺，autoextend on next預設值是不同的，ASM平臺下已經做了最佳化。

關於資料檔案autoextend on的一點記錄

相關文章