概述：

这篇文章主要给大家介绍表空间maxsize，通过实际操作，大家会对maxsize有一个充分详细的了解，需要的朋友可以收藏加关注。

1、创建表空间chen,自动增长，最大增长到10M。

create tablespace chendatafile '/u01/app/oracle/oradata/orcl/chen01.dbf' size 5M autoextend on next 1M maxsize 10M;

2、增加数据文件操作

alter tablespace chen add datafile '/u01/app/oracle/oradata/orcl/chen02.dbf' size 10M;alter tablespace chen add datafile '/u01/app/oracle/oradata/orcl/chen03.dbf' size 10M;alter tablespace chen add datafile '/u01/app/oracle/oradata/orcl/chen03.dbf' size 10M;alter tablespace chen add datafile '/u01/app/oracle/oradata/orcl/chen04.dbf' size 10M autoextend on next 1M maxsize 15M;select file_name,tablespace_name,bytes/1024/1024||'M' f,maxbytes/1024/1024||'M' m from dba_data_files;………………………………………/u01/app/oracle/oradata/orcl/chen01.dbf CHEN 5M 10M/u01/app/oracle/oradata/orcl/chen02.dbf CHEN 10M 0M/u01/app/oracle/oradata/orcl/chen03.dbf CHEN 10M 0M/u01/app/oracle/oradata/orcl/chen04.dbf CHEN 10M 15M

3、删除数据文件操作

 alter tablespace chen drop datafile '/u01/app/oracle/oradata/orcl/chen04.dbf'; alter tablespace chen drop datafile '/u01/app/oracle/oradata/orcl/chen03.dbf'; alter tablespace chen drop datafile '/u01/app/oracle/oradata/orcl/chen02.dbf';select file_name,tablespace_name,bytes/1024/1024||'M' f,maxbytes/1024/1024||'M' m from dba_data_files;FILE_NAME                                                 TABLESPACE F M--------------------------------------------- ---------- ---------- ----------/u01/app/oracle/oradata/orcl/chen01.dbf CHEN 5M 10Malter tablespace chen drop datafile '/u01/app/oracle/oradata/orcl/chen01.dbf';alter tablespace chen drop datafile '/u01/app/oracle/oradata/orcl/chen01.dbf'ERROR at line 1:ORA-03261: the tablespace CHEN has only one file关注点一：删除失败，说明一个表空间至少保留一个数据文件。alter database datafile '/u01/app/oracle/oradata/orcl/chen01.dbf' resize 1M;alter database datafile '/u01/app/oracle/oradata/orcl/chen01.dbf' resize 1MERROR at line 1:ORA-03214: File Size specified is smaller than minimum required关注点二：重新创建数据文件失败，原因是数据文件的大小过小，容纳不下所有的数据。alter database datafile '/u01/app/oracle/oradata/orcl/chen01.dbf' resize 2M;select file_name,tablespace_name,bytes/1024/1024||'M' f,maxbytes/1024/1024||'M' m from dba_data_files;FILE_NAME TABLESPACE F M--------------------------------------------- ---------- ---------- ----------/u01/app/oracle/oradata/orcl/chen01.dbf CHEN 2M 10M

4、创建大表，插入大量数据

此处省略建表，插入数据过程，直到数据插入报以下错误insert into t1 select * from t1ERROR at line 1:ORA-01653: unable to extend table CHEN.T1 by 128 in tablespace CHEN关注点三：插入数据失败，原因是数据已经超过maxsize值,说明maxsize对自动增长上限有限制；select file_name,tablespace_name,bytes/1024/1024||'M' f,maxbytes/1024/1024||'M' m from dba_data_files;FILE_NAME TABLESPACE F M--------------------------------------------- ---------- ---------- ----------/u01/app/oracle/oradata/orcl/chen01.dbf CHEN 10M 10M

5、手动增大maxsize的值

alter tablespace chen add datafile '/u01/app/oracle/oradata/orcl/chen02.dbf' size 5M autoextend on next 1M maxsize 10M;循环插入数据，直到报一下错误insert into t1 select * from t1*ERROR at line 1:ORA-01653: unable to extend table CHEN.T1 by 128 in tablespace CHENselect file_name,tablespace_name,bytes/1024/1024||'M' f,maxbytes/1024/1024||'M' m from dba_data_files;FILE_NAME TABLESPACE F M--------------------------------------------- ---------- ---------- ----------/u01/app/oracle/oradata/orcl/chen01.dbf CHEN 10M 10M/u01/app/oracle/oradata/orcl/chen02.dbf CHEN 10M 10Malter tablespace chen drop datafile '/u01/app/oracle/oradata/orcl/chen02.dbf';alter tablespace chen drop datafile '/u01/app/oracle/oradata/orcl/chen02.dbf'*ERROR at line 1:ORA-03262: the file is non-empty关注点四：删除数据文件chen02.dbf失败，原因是数据文件chen02.dbf里有数据，非空，不能删除

6、清空表

truncate table t1;select file_name,tablespace_name,bytes/1024/1024||'M' f,maxbytes/1024/1024||'M' m from dba_data_files;FILE_NAME TABLESPACE F M--------------------------------------------- ---------- ---------- ----------/u01/app/oracle/oradata/orcl/chen01.dbf CHEN 10M 10M/u01/app/oracle/oradata/orcl/chen02.dbf CHEN 10M 10M关注点五：清空大表后，数据文件的大小并没有回缩，仍然是10M。alter tablespace chen drop datafile '/u01/app/oracle/oradata/orcl/chen02.dbf';删除数据文件chen02dbf成功，原因是数据文件chen02.dbf为空

7、手动修改数据文件大小

alter database datafile '/u01/app/oracle/oradata/orcl/chen01.dbf' resize 200M;select file_name,tablespace_name,bytes/1024/1024||'M' f,maxbytes/1024/1024||'M' m from dba_data_files;FILE_NAME TABLESPACE F M--------------------------------------------- ---------- ---------- ----------/u01/app/oracle/oradata/orcl/chen01.dbf CHEN 200M 10M关注点六：重建数据文件chen01的大小为200M，200M>10M，说明maxsize只对自动扩展的空间有限制，对手动增加的空间没有限制。

8、手动修改maxsize的值

drop tablespace chen including contents and datafiles;create tablespace chen datafile '/u01/app/oracle/oradata/orcl/chen01.dbf' size 5M autoextend on next 1M maxsize 6M;select file_name,tablespace_name,bytes/1024/1024||'M' f,maxbytes/1024/1024||'M' m from dba_data_files where tablespace_name='CHEN';FILE_NAME TABLESPACE F M--------------------------------------------- ---------- ---------- ----------/u01/app/oracle/oradata/orcl/chen01.dbf CHEN 5M 6Malter database datafile '/u01/app/oracle/oradata/orcl/chen01.dbf' autoextend on maxsize 15M;select file_name,tablespace_name,bytes/1024/1024||'M' f,maxbytes/1024/1024||'M' m from dba_data_files where tablespace_name='CHEN';FILE_NAME TABLESPACE F M--------------------------------------------- ---------- ---------- ----------/u01/app/oracle/oradata/orcl/chen01.dbf CHEN 5M 15M

9、将maxsize设置为unlimited

alter database datafile '/u01/app/oracle/oradata/orcl/chen01.dbf' autoextend on maxsize unlimited;当maxsize设置为unlimited时，其大小为32Gselect file_name,tablespace_name,bytes/1024/1024||'M' f,maxbytes/1024/1024||'M' m from dba_data_files where tablespace_name='CHEN';FILE_NAME TABLESPACE F M--------------------------------------------- ---------- ---------- ----------/u01/app/oracle/oradata/orcl/chen01.dbf CHEN 5M 32767.9843 75M关注点七：对于8k的db block size，最大大小是32G。

10、结束

到此关于Oracle tablespace maxsize详解的文章就介绍到这里，更多DBA相关技术文档请双击加关注，希望大家以后多多支持。