建立測試表空間:
create tablespace tmaxs datafile 'D:\APP\LIMING\ORADATA\ORCL11S\tmaxs01.DBF' size 1M autoextend on maxsize 1M;
檢視錶空間資訊:
SQL> select d.file_name,d.tablespace_name,d.maxbytes,d.autoextensible from dba_data_files d where d.tablespace_name='TMAXS';
FILE_NAME TABLESPACE_NAME MAXBYTES AUTOEXTENSIBLE
-------------------------------------------------------------------------------- ------------------------------ ---------- --------------
D:\APP\LIMING\ORADATA\ORCL11S\TMAXS01.DBF TMAXS 1048576 YES
在表空間中建立表:
create table tmaxs(id char(100),name char(1000)) tablespace tmaxs;
插入資料:
begin
for i in 1..1000 loop
insert into tmaxs values(i,i||'test');
end loop;
commit;
end;
迴圈幾次後:
SQL> begin
2 for i in 1..1000 loop
3 insert into tmaxs values(i,i||'test');
4 end loop;
5 commit;
6 end;
7 /
begin
for i in 1..1000 loop
insert into tmaxs values(i,i||'test');
end loop;
commit;
end;
ORA-01653: 表 LM.TMAXS 無法通過 8 (在表空間 TMAXS 中) 擴充套件
ORA-06512: 在 line 4
檢查:
SQL> select d.file_name,d.tablespace_name,d.maxbytes,d.autoextensible,d.bytes from dba_data_files d where d.tablespace_name='TMAXS';
FILE_NAME TABLESPACE_NAME MAXBYTES AUTOEXTENSIBLE BYTES
-------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ----------
D:\APP\LIMING\ORADATA\ORCL11S\TMAXS01.DBF TMAXS 1048576 YES 1048576
那麼:
設定maxsize的自動擴充套件資料檔案在達到maxsize後不會繼續擴充套件。