临下班时,同学发来一个截图,意思是说dba_data_files里的bytes值大于maxbytes值。截图如下:
下面动手还原一下
本次测试的平台为11.2.0.3
--创建一个测试表空间
SQL> create tablespace zx1 datafile '/opt/bboss/tst4/oracle/arch/zx1.dbf' size 10M;
Tablespace created.
--查看数据文件的属性
SQL> col file_name for a100
SQL> set linesize 200
SQL> set num 20
SQL> select file_name,tablespace_name,bytes,maxbytes,autoextensible from dba_data_files where tablespace_name='ZX1';
FILE_NAME TABLESPACE_NAME BYTES MAXBYTES AUT
---------------------------------------------------------------------------------------------------- ------------------------------ ---------- ---------- ---
/opt/bboss/tst4/oracle/arch/zx1.dbf ZX1 10485760 0 NO
--看到默认数据文件的自动增长为NO,bytes也只有创建时指定的10M
--把数据文件转为自动增长
SQL> alter database datafile '/opt/bboss/tst4/oracle/arch/zx1.dbf' autoextend on;
Database altered.
SQL> select file_name,tablespace_name,bytes,maxbytes,autoextensible from dba_data_files where tablespace_name='ZX1';
FILE_NAME TABLESPACE_NAME BYTES MAXBYTES AUT
---------------------------------------------------------------------------------------------------- ------------------------------ -------------------- -------------------- ---
/opt/bboss/tst4/oracle/arch/zx1.dbf ZX1 10485760 34359721984 YES
--看到打开自动增长后MAXBYTES默认为32G,也就是数据文件扩展到32G后就不再扩展
--测试是自动扩展
--创建测试用户及表
SQL> create user zhaoxu identified by zhaoxu;
User created.
SQL> grant connect , resource to zhaoxu;
Grant succeeded.
SQL> create table zhaoxu.test1 tablespace zx1 as select * from dba_objects;
Table created.
--循环插入数据
SQL> begin
2 for i in 1..100
3 loop
4 insert /* append */ into zhaoxu.test1 select * from zhaoxu.test1;
5 commit;
6 end loop;
7 end;
8 /
begin
*
ERROR at line 1:
ORA-01653: unable to extend table ZHAOXU.TEST1 by 8192 in tablespace ZX1
ORA-06512: at line 4
--执行一段时间后报错,表空间无法扩展
--查看数据文件大小
SQL> select file_name,tablespace_name,bytes,maxbytes,autoextensible from dba_data_files where tablespace_name='ZX1';
FILE_NAME TABLESPACE_NAME BYTES MAXBYTES AUT
---------------------------------------------------------------------------------------------------- ------------------------------ -------------------- -------------------- ---
/opt/bboss/tst4/oracle/arch/zx1.dbf ZX1 34293743616 34359721984 YES
--看操作系统上的数据文件大小
SQL> !du -sk /opt/bboss/tst4/oracle/arch/zx1.dbf
33490016 /opt/bboss/tst4/oracle/arch/zx1.dbf
--可以看到数据文件增长没有超过MAXBYTES指定的32G
--此时resize数据文件
SQL> alter database datafile '/opt/bboss/tst4/oracle/arch/zx1.dbf' resize 33554424k; --由于操作系统限制最大只能33554424k,不过足可以说明问题
Database altered.
SQL> select file_name,tablespace_name,bytes,maxbytes,bytes-maxbytes,autoextensible from dba_data_files where tablespace_name='ZX1';
FILE_NAME TABLESPACE_NAME BYTES MAXBYTES BYTES-MAXBYTES AUT
-------------------------------------------------------------------------------------------