ITPub博客

首页 > Linux操作系统 > Linux操作系统 > unlimited size datafile is no concept of an 'UNLIMITED' file size

unlimited size datafile is no concept of an 'UNLIMITED' file size

原创 Linux操作系统 作者:zhanglei_itput 时间:2009-04-23 13:04:55 0 删除 编辑

今天有一个同事问我,为什么数据文件设置成自动扩展,表空间仍然会满?之前看到过数据文件max的概念,很久不用忘记了,查了一下资料,8k 单位的db_block_size 的unlimited的定义是有上限值的,详细信息如下:

1. 确认数据文件是不是自动扩展,并且max值是否为无限
    SQL> conn com/com@bos123
    已连接。
    SQL> select a.tablespace_name, a.file_name, a.autoextensible, a.bytes, a.blocks, a.maxbytes, a.maxblocks
           2  from dba_data_files a
           3  where a.tablespace_name = 'DATA';
TABLESPACE_NAME      FILE_NAME                              AUT      BYTES        BLOCKS     MAXBYTES      MAXBLOCKS
--------        -------------------------------------------------------     ----     -----------------   ------------  ------------------   ---------               
DATA         /u01/app/oracle/oradata/bos1/DATA.dbf        YES 34359672832 4194296 34359721984 4194302
DATA         /u01/app/oracle/oradata/bos1/DATA01.dbf   YES 34359672832 4194296 34359721984 4194302
DATA         /u01/app/oracle/oradata/bos1/DATA02 .dbf  YES 34359672832 4194296 34359721984 4194302
DATA         /u01/app/oracle/oradata/bos1/DATA03.dbf   YES 34359672832 4194296 34359721984 4194302
    
结果:dba_data_files里面的数据文件的max值为:34359721984byte = 32767M = 32G       

2. 手工修改data.dbf数据文件的最大值为无限
    SQL> ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/bos123/DATA.dbf' AUTOEXTEND ON MAXSIZE
               UNLIMITED;(同时用em工具也修改为unlimited)
    数据库已更改。
    SQL> select a.tablespace_name, a.file_name, a.autoextensible, a.bytes, a.blocks, a.maxbytes, a.maxblocks
           2  from dba_data_files a
           3  where a.tablespace_name = 'DATA';
TABLESPACE_NAME      FILE_NAME                              AUT      BYTES        BLOCKS     MAXBYTES      MAXBLOCKS
--------        -------------------------------------------------------     ----     -----------------   ------------  ------------------   ---------               
DATA         /u01/app/oracle/oradata/bos1/DATA.dbf        YES 34359672832 4194296 34359721984 4194302
DATA         /u01/app/oracle/oradata/bos1/DATA01.dbf   YES 34359672832 4194296 34359721984 4194302
DATA         /u01/app/oracle/oradata/bos1/DATA02 .dbf  YES 34359672832 4194296 34359721984 4194302
DATA         /u01/app/oracle/oradata/bos1/DATA03.dbf   YES 34359672832 4194296 34359721984 4194302
  
结果:已经把数据文件的最大值设置成unlimited了,为什么MAXBYTES还是32G呢?

3.查找metalink,确定datafile中unlimited的真正定义(Note 468096.1)
    For confirming whether the maxsize is set to UNLIMITED we have to check for the maximum possible size as there is no concept of an 'UNLIMITED' file size. Oracle limits the number of blocks in a datafile to approximately 4 million blocks (unless BIGFILE tablespace is used, which is a different topic). Hence, the limit, in fact, depends on DB_BLOCK_SIZE . If the database/tablespace has a 2K block size, the max size for a file is approximately 8GB. With a 4k block, maxfile size is 16GB, 8k block, 32GB, etc. This limit in the number of blocks in a file applies to all platforms. In short, we have to query 'maxbytes ' column and see whether it is the maximum possible value or manually limited to a smaller value.

Subject: How To Check For Autoextensible Datafiles Set To Maxsize Unlimited
  Doc ID: 468096.1 Type: HOWTO
  Modified Date : 25-JUN-2008 Status: MODERATED

 

                                                                 

 

datafile.JPG

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9252210/viewspace-591543/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-02-10

  • 博文量
    400
  • 访问量
    1117473