ORACLE 表空间文件大小问题

ORACLE 表空间文件大小问题

• oracle的块 db_block_size 
  

from： http://bdcwl.blog.163.com/blog/static/765222652010112784912650/

DBA教材的第一部分就讲到了一旦create   database，db_block_size就是不可更改的。因为oracle是以块为单位存储数据的，任何一个存储元素最少占用一个块，如果你改变了db_block_size，必然导致部分块不能正常使用。
其实在unix类操作系统中，文件块和oracle块的关系非常紧密（建议相等），这样才能保证数据库的执行效率。在windows下可能就不这么讲究了。建议使用8k以上的块，我做过测试，同样的配置，8k的块比4k快大约40%，比2k快3倍以上（从hit_ratio来看）。

一般么人都是8k，通过 select value from v$parameter where name='db_block_size'  查看具体大小

 假设你的事物将对磁盘单元写入32KB的数据，DB_BLOCK_SIZE参数就设为8KB。在这个例子中，Oracle必须执行四个I/O工作单元（32KB/8KB=4）来完成这一操作。

 =======================

很不幸，你遇到了一个很难碰到的问题。很不幸，你现在一定心急火燎。能遇到这种错误的人绝不是一般人。那么你一定不是一个凡人，you are special。
呵呵，不和你开玩笑了。现在就分析这个问题的原因。
oracle支持的文件大小和他的db_block_size和db_block的数量决定的。在oracle 9i以前，oracle 的db_block的数量最大只能为2的22次方个，而我们通过oracle默认的模板建的数据库实例的db_block_size是8K。所以oracle 9i以前支持的单个文件的默认大小是32GB。虽然在oracle 11g没有这个限制，但是它默认的能力也是这么多。所以我在前面讲如果你遇到这个问题，说明你不是一般人，你在创建一个单个文件超过32GB的表空间。
那么怎么解决这个问题呢，我这里提供三个参考方案，你使用选择其中对你合适的，呵呵：
一）不要创建单个文件超过32GB的表空间
如果你创建的表空间超过32GB，请把这个表空间存储为多个数据文件，每个文件不大于32GB。这样，就可以成功的创建表空间。二）扩大db_block_size
根据oracle的算法，我们很容易想到这个解决方法。数目衡定，但是db_block_size可以更改（db_block_size的最大大小为32KB）。如果把db_block_size扩大到32KB（32位系统好像是16KB,我在Red Hat 企业版的操作系统创建32KB的block_size失败。）,那么我们的系统就可以支持单个数据文件最大128GB。
这个方案听起来好像很迷人，但是实际上并不是那么回事。因为要修改db_block_size并不是很容易的事。因为这个db_block_size在创建实例的时候就要指定。而且不能通过简单修改参数来指定db_block_size。创建更大的db_block_size的数据库实例我会在以后讲。
三）创建bigfile表空间
在oracle11g中引进了bigfile表空间，他充分利用了64位CPU的寻址能力，使oracle可以管理的数据文件总量达到8EB。单个数据文件的大小达到128TB，即使默认8K的db_block_size也达到了32TB。
创建bigfile的表空间使用的sql语句也很简单。
create bigfile tablespace...
后面的语句和普通的语句完全一样。
需要注意的是使用bigfile表空间，他只能支持一个数据文件。也就是说这个文件的最大大小就是表空间最大大小，你不可能通过增加数据文件来扩大该表空间的大小。所以oracle说，如果你的这个文件没有剩余空间的话，你还是不要使用bigfile表空间了，这个表空间你没有扩展的余地了，呵呵。
       oracle也同样告诉你了，建议你使用lvm或者别的软件管理磁盘，以使你的空间可以动态扩大。呵呵，先讲到这里，如果还有不明白的，请告诉我。

====================

如果db_block_size为8k(默认一般也是8k)，则表空间文件(单个数据文件)大小最大为32G。但是也要看是什么文件系统是什么，虽然oracle表空间单个数据文件最大可以为32G，但是如果文件系统是FAT32，那么该文件系统本身所支持的单个文件最大为4G，所以，oracle单个表空间最大也就只能为4G。其他的文件系统如unix的貌似最大才能为2G。

• oracle及操作系统对于文件大小的限制
  

from： http://blog.csdn.net/zong100/article/details/6001778

[ORACLE]：单个表空间的数据限制
当将表空间加到4GB的时候，系统提示表空间不足。
原因：

FAT12 单文件最大支持8M
Fat16单文件最大支持2G
Fat32单文件不能大于4G
NTFS单文件最大64GB
NTFS5.0单文件最大2TB

解决方案：
增加多个数据文件，对应同一个表空间。
因为：size of a tablespace = size of each datafile * number of datafiles

 

========================================
Oracle中数据文件大小的限制

http://www.eygle.com/archives/2007/07/oracle_datafile_limit.html

Oracle数据文件的大小存在一个内部限制，这个限制是：
每个数据文件最多只能包含2^22-1个数据块。

这个限制也就直接导致了每个数据文件的最大允许大小。
在2K Block_size下，数据文件最大只能达到约8G
在32K的Block_size下，数据文件最大只能达到约16*8G的大小。

这个限制是由于Oracle的Rowid中使用22位来代表Block号，这22位最多只能代表2^22-1个数据块。

为了扩展数据文件的大小，Oracle10g中引入了大文件表空间，在大文件表空间下，Oracle使用32位来代表Block号，也就是说，在新的技术下，大文件表空间下每个文件最多可以容纳4G个Block。
那么也就是说当Block_size为2k时，数据文件可以达到8T 。
当block_size为32K时，数据文件可以达到128T。

上周在做2K block_size测试时，第一次遇到了这个限制：

SQL> alter tablespace eygle add datafile 'f:/eygle02.dbf' size 8192M;
alter tablespace eygle add datafile 'f:/eygle02.dbf' size 8192M
*
ERROR 位于第 1 行:
ORA-01144: 文件大小 (4194304 块) 超出 4194303 块的最大数

缩减一点，最后创建成功：

SQL> alter tablespace eygle add datafile 'f:/eygle02.dbf' size 8191M reuse;

表空间已更改。

已用时间: 00: 44: 42.08

计算一下，这台破烂的测试机的IO速度：
io speed = 8191 M / 00: 44: 42.08 = 8191 M / 44*60+42 = 8191M / 2682 s = 3.05M/s

够惊人的了吧。

=============================================
关于aix中大文件限制的问题

目前常见的主要有两种情况限制文件大小：

一，操作系统文件大小限制

一般文件大小限制为2g，如果要改变限制，可以有两种方法：

1， 如果文件系统为jfs，则必须重建jfs文件系统且将文件系统设置为允许大文件。之后就可以支持大于2g的文件了。

2， 如果文件系统为jfs2，则无此限制，理论上jfs2文件系统支持最大1T的文件。所以你也可以创建jfs2文件系统满足要求。

3， 如果以上2个都不行，则可能是操作系统对单一用户的文件大小有限制，必须修改/etc/secrucity/limits 文件中指定用户的 fsize参数为-1(表示无限制），修改完后，必须重新登陆shell生效。可使用ulimit –a命令查看修改后的值。

4， 为什么在一个目录下无法再创建目录，因为一个目录下最多只能创建32767个文件（含目录），该参数是由系统内核决定的，在系统安装以后该参数不能更改。

（/usr/include/sys/limits.h文件里的 #define SHRT_MAX 参数      (32767)）

    This is due to the fact that ".." in each subdirectory is a hard-link back to
the parent directory and the link count is implemented with a signed 16
bit quantity. Hence the error, EMLINK. Regular files are ok since they
do not require this link. You could work around this limitation by using
symbolic <links> to directories

/usr/include/sys/limits.h as the LINK_MAX value set to 32767

二，tar文件大小限制

tar文件最大只能为8g，原因是：tar命令介绍，因为tar使用USTAR格式，（美国磁带归档程序），而ustar头格式允许文件大到8GB，所以tar支持归档最大8GB文件。

The ustar header format allows for file sizes to be as large as 8 GB. Therefore, the tar command is enabled to archive files of up to 8 GB in size.

解决办法有以下三种：

1， pax

    pax使用方法：

备份：pax -wvf /dev/rmt0 <directory or filename>

查看备份文件列表：pax -vf /dev/rmt0

解备份：pax -rvf /dev/rmt0 <directory or filename> -----可以随意解哪个都行，空为全部

复制：pax -rw <old directory> <new directory>

向磁带后追加：pax -avf /dev/rmt0 <directory or filename>

处理大文件（超过8GB）：pax -x pax -wvf /dev/rmt0 <directory or filename>

不方便之处：分卷、不存在的目录不能恢复、存在的目录自动覆盖。

  2， backup

backup使用方法：

find <directory1 or filename1> <directory2 or filename2> ... -print | backup -iqvf /dev/rmt0

注：qv不是关键参数，默认是/dev/fd0所以f一般为主要参数，i为文件而非文件系统。

restore -Tqf /dev/rmt0 查看备份列表

restore -xdqvf /dev/rmt0 <directory1 or filename1> <directory2 or filename2> ...

注：d表示如果为目录将目录下的文件全部RESTORE，x为主要解备参数。

3， exp 的filesize参数

如果是数据库的备份文件，可以使用exp 的filesize参数。将大文件分片存取。

=============================================
看了一篇关于oracle表空间的数据文件大小上限的文章，里面介绍oracle的数据文件大小除了和数据块大小有关之外，还和操作系统的限制有关，下面是根据块大小的限制：
            数据块的大小         物理文件的最大值 M
        ===============================================                
                2048                         8191 M
                4096                        16383 M
                8192                        32767 M
               16384                        65535 M
在裸设备上有没有其他限制呢？还是说裸设备的限制是基于操作系统的。

转fenng版主的一篇文章，bft也受限于操作系统的文件系统，不过在一定程度上扩展了单个表空间、单个文件的大小，不过只能建一个文件。
作者：fenng
日期：27-nov-2004　
出处：http://www.dbanotes.net
版本：0.1

简单介绍
oracle 10g 的存储能力有了显著的增强。这表现在很多方面，下面介绍 10g 新增的表空间类型：大文件 (bigfile) 表空间。

大文件表空间从某种角度来说提高了 oracle 在 vldb 上的管理能力。 只有自动段空间管理的 lmt (locally managed tablespaces ) 支持 bigfile 表空间。 大文件表空间只能包含一个文件，但是文件可以达到 4g 个数据块大小。(以下用 bft 指代bigfile tablespace。)

bft 可以和以下存储技术结合使用：
自动存储管理(asm) lvm omf
理论上的 bft 可以达到下面所列的值：
数据块大小(单位：k)bft 最大值(单位：t)2k8t4k16t8k32t16k64t32k128t
在实际环境中，这还受到操作系统的文件系统的限制。
bft基本操作
10g 数据库在创建的时候，会指定默认的表空间类型。如果不特殊指定的话，默认为 smallfile 类型的表空间。

sql> select * 2 from database_properties 3 where property_name = default_tbs_type;property_name property_value description-------------------- --------------- ----------------------------------------default_tbs_type smallfile default tablespace type

这种情况下，如果我们创建表空间的时候不指定类型，那么默认创建的都是 smallfile 类型的表空间。可以通过 alter database 命令来修改数据库默认的表空间类型：

sql> alter database set default bigfile tablespace;database altered.sql> select * 2 from database_properties 3 where property_name = default_tbs_type;property_name property_value description-------------------- --------------- ----------------------------------------default_tbs_type bigfile default tablespace typesql>

sql> alter database set default smallfile tablespace;

创建 bigfile 类型的表空间，只需指定额外的一个参数 bigfile 即可，其他和原有创建表空间语法类似：

create bigfile tablespace bftbs datafile /u01/app/oracle/oradata/demo/bftbs01.dbf size 5m;

dba_tablespaces (user_tablespaces)与 v$tablespace 这两个视图可以查看 bigfile 表空间的相关信息。先看看 dba_tablespaces 在 10g 中有了什么变化：

sql> desc dba_tablespaces name null? type ----------------------------------------- -------- ------------------------ tablespace_name not null varchar2(30) block_size not null number initial_extent number next_extent number min_extents not null number max_extents number pct_increase number min_extlen number status varchar2(9) contents varchar2(9) logging varchar2(9) force_logging varchar2(3) extent_management varchar2(10) allocation_type varchar2(9) plugged_in varchar2(3) segment_space_management varchar2(6) def_tab_compression varchar2(8) retention varchar2(11) bigfile varchar2(3)sql>

和 9i 相比， dba_tablespaces 视图多了两列：retention 和 bigfile。其中 bigfile 列说明该表空间是否为 bft：

sql> select tablespace_name, bigfile 2 from dba_tablespaces;tablespace_name big------------------------------ ---system noundotbs nosysaux notemp nousers noexample notest nobftbs yes8 rows selected.
v$tablespace 视图相对 9i 也增加了新的列：
sql> desc v$tablespace name null? type ----------------------------------------- -------- ------------------------ ts# number name varchar2(30) included_in_database_backup varchar2(3) bigfile varchar2(3) flashback_on varchar2(3)

其中 flashback_on 和 bigfile 列都是新增的。
bft 属性
bft有一些特有的属性。

1.每个表空间只能包含一个数据文件。如果试图添加新的文件，则会报告 ora-32771 错误：

sql> alter tablespace bftbs 2 add datafile /u01/app/oracle/oradata/demo/bftbs02.dbf size 5m;alter tablespace bftbs*error at line 1 ora-32771: cannot add file to bigfile tablespace

2.只有自动段空间管理的 lmt (locally managed tablespaces ) 支持 bft

sql> create bigfile tablespace bftbs02 2 datafile /u01/app/oracle/oradata/demo/bftbs02.dbf size 5m 3 extent management dictionary;create bigfile tablespace bftbs02*error at line 1 ora-12913: cannot create dictionary managed tablespacesql> create bigfile tablespace bftbs02 2 datafile /u01/app/oracle/oradata/demo/bftbs02.dbf size 5m 3 segment space management manual;create bigfile tablespace bftbs02*error at line 1 ora-32772: bigfile is invalid option for this type of tablespace

3.相对文件号(relative_fno)为1024 ( 4096 on os/390)

因为bft只有一个数据文件，所以其相对文件号也是固定的:1024

sql> select tablespace_name, file_id, relative_fno 2 from dba_data_files;tablespace_name file_id relative_fno------------------------------ ---------- ------------users 4 4sysaux 3 3undotbs 2 2system 1 1example 5 5test 6 6bftbs 7 10247 rows selected.sql>

4.rowid的变化

在 bft 上存储的表的 rowid 与 smallfile 表空间上的 rowid 结构有些不同的。要正确得到 rowid 信息，dbms_rowid 包增加了一个新的参数　ts_type_in　来解决这个问题。参考这个范例：

sql> select dbms_rowid.rowid_block_number (rowid, bigfile) 2 from foo;dbms_rowid.rowid_block_number(rowid,bigfile)---------------------------------------------- 24sql>
你可以创建多大的表空间?
我们在前面提及，bft 还受到操作系统的文件系统的限制。下面我们以 linux 操作系统为例：

sql> show parameters db_block_sizename type value------------------------------------ ----------- ------------------------------db_block_size integer 8192sql>
也就是说，理论上我们可以创建最大 32t (4g*8k) 的表空间。我们可以做到么?
sql> create bigfile tablespace bftbs02 2 datafile /u01/app/oracle/oradata/demo/bftbs02.dbf size 20t reuse;create bigfile tablespace bftbs02*error at line 1 ora-01119: error in creating database file /u01/app/oracle/oradata/demo/bftbs02.dbfora-27059: could not reduce file sizelinux error: 27: file too largeadditional information: 2sql>

注意我们得到的操作系统信息(黑色部分)：file too large 。这说明超出了操作系统允许值。我所用的环境是fedora core linux, 内核的版本是2.6.9，文件系统是 ext3 。2.4以后的版本的内核都是支持 lfs (large file support)的。
文件系统(块)文件大小限制文件系统大小限制ext2/3 (2k)256g8text2/3 (4k)2t16text2/3 (8k)64t32treiserfs 3.6 1e16t
我们检查一下os文件系统块大小：

[root@fc3 ~]# tune2fs -l /dev/hda7 | grep blockblock count: 2621440block size: 4096blocks per group: 32768[root@fc3 ~]#

也即，我们可以在操作系统上创建不大于 2t 的文件。虽然我们没有那么大的存储空间，不妨也测试一下：

sql> create bigfile tablespace bftbs02 2 datafile /u01/app/oracle/oradata/demo/bftbs02.dbf size 1800g;

在另外一个终端中，观察该目录的变化情况：

[root@fc3 demo]# ls -ltrtotal 1159048-rw------- 1 oracle oracle 5251072 nov 28 20:05 bftbs01.dbf-rw------- 1 oracle oracle 1932735291392 nov 28 20:49 bftbs02.dbf[root@fc3 demo]#

哇，我们真的能观察到oracle在创建“超大”文件呢，接近1.8t 的文件 :-) 要过一会儿，oracle 才会报告错误(毕竟1800g 的大文件):

create bigfile tablespace bftbs02*error at line 1 ora-19502: write error on file "/u01/app/oracle/oradata/demo/bftbs02.dbf",blockno 898048 (blocksize=8192)ora-27072: file i/o erroradditional information: 898047

可见，在本例中，由于操作系统文件系统的限制，我们只可以创建2t以下的 bft。
你需要bft么?
应用 bft 的话，优点缺点都存在。根据 oracle 官方的文档，db_files 和 maxdatafiles 这两个参数的值给 sga 带来的压力会减轻(原来的压力就很大么?)。数据库中最大数据文件数是有限的 (64k files)，bft 的出现的确对海量数据库有一定的积极意义。从一定程度上来说，bft 简化了管理，但是在恢复的时候可能是一场灾难。

在笔者看来，至少我们现在在大多数情况下不需要用 bft 。"你要把鸡蛋都放到一个篮子里么? "

参考信息

oracle database administrators guide 10g release 1 (10.1) part number b10739-01 ( note 62294.1 )
large file support in linux - http://www.suse.de/~aj/linux_lfs.html
metalink [note:262472.1] 10g: bigfile type tablespaces versus smallfile type

=============================================

大文件表空间
Oracle允许你创建大文件表空间。这允许Oracle数据库包含由一个大文件而不是多个小文件组成的表空间。这使得Oracle可以利用64位系统的优势来创建和管理超大文件。这样的结果是Oracle数据库可以扩大至8EB（1EB = 1,073,741,824 (1024**3)GB）。

在Oracle管理文件的情况下，大文件表空间使得数据文件对用户来说完全透明。换句话说，你可以在表空间上执行操作，而不是在底层的数据文件上操作。大文件表空间使得表空间成为磁盘空间管理、备份、恢复等等的主要单位。大文件表空间还简化了Oracle管理文件和自动存储管理的文件管理，因为它降低了增加新文件和处理多个文件的必要性。

系统默认是创建一个小文件表空间，这也是Oracle表空间的传统格式。SYSTEM和SYSAUX表空间总是创建为系统默认类型。

大文件表空间只支持使用自动段空间管理的本地管理表空间的情况。不过也有两个例外：本地管理的undo表空间和临时表空间即使在段空间手动管理的情况下也可以是用大文件表空间。

一个Oracle数据库可以同时包含大文件和小文件表空间。表空间的不同类型对于执行没有明确指定数据文件的SQL语句来说是没有多大区别。

你可以创建一个临时表空间组，这样一个用户就可以从多个表空间获得临时空间了。表空间组也可以指定为数据库的默认临时表空间。这对于大文件表空间很有用，这里你可能需要大量的临时表空间来进行排序。

大文件表空间的优点

* 大文件表空间可以有效的提高一个Oracle数据库的存储容量。小文件表空间能够包含1024个数据文件，但是大文件表空间可以只包含一个1024倍小文件表空间大小的文件。小文件表空间和大文件表空间总容量是相同的。虽然如此，由于存在一个数据库数据文件数不能超过64K的限制，一个包含大文件表空间的数据库可以1024倍小文件表空间的数据库，所以大文件表空间提升了整个数据库容量达3个数量级。换句话说，当文件表空间使用最大块大小（32K）时，Oracle数据库的最大尺寸是8EB。

* 超大规模的数据库中使用大文件表空间可以简化数据文件的管理，因为它降低了需要数据文件的数目。你也可以调整参数来减少数据文件信息需要的SGA空间和控制文件的大小。

* 通过数据文件透明来简化了数据库管理。

使用大文件表空间应该注意

* 大文件表空间倾向于在自动存储管理或者其他的支持自动扩展的逻辑卷和条带化的逻辑卷管理或者RAID的情况下使用。

* 避免在不支持条带化的系统上创建大文件数据库。因为它不支持并发执行和并发RMAN备份。

* 避免在下列情况下使用大文件表空间：磁盘组中没有足够的空闲空间，只能通过在另外一个磁盘组中增加数据文件的方式扩展表空间。

* 不推荐在不支持大文件尺寸的平台上使用大文件数据库，因为这会限制表空间容量。参考你的操作系统规格文档来获得支持的最大的文件大小的信息。

* 在大文件表空间上而不是在传统表空间上保存数据，可以提高数据库打开、检查点和DBWR进程的性能。虽然如此，提高数据文件大小可能会增加创建一个新数据文件或者恢复一个被破坏的数据文件所需的时间。

 

=============================================

ORA-01653: Unable to extend table %s.%s by %s in tablespace %s

The tablespace the table is residing in is unable to extend.

There is not enough space left either due to the datafiles being full, autoextend which is not set at datafile level or due to a disk which's full.

You'll have to check the size of the datafiles attached to the tablespace and check whether they can autoextend or not..
select file_name, bytes, autoextensible, maxbytesfrom dba_data_fileswhere tablespace_name='TABLESPACE_NAME'
Either add more datafiles to the tablespace, set the autoextensible flag or enlarge the datafile(s).

To add more space to a file issue following command:
alter database datafile 'C:/ORACLE/ORADATA/TOOLS01.DBF' resize 1000m;

To turn on the autoextend feature on a datafile use following command:

alter database datafile 'C:/ORACLE/ORADATA/TOOLS01.DBF' autoextend on next 100m maxsize 2000m;

To add a new datafile to the tablespace use following command:
alter tablespace TOOLS add datafile 'C:/ORACLE/ORADATA/TOOLS02.DBF' size 100m autoextend on next 100m maxsize 2000m;

=============================================
ORA-01653
Unable to extend table XXX by 8192 in tablespace XXX

--check tablespace usage
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC;

--check autoextensible
SELECT T.TABLESPACE_NAME,D.FILE_NAME,
D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS
FROM DBA_TABLESPACES T,DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME,FILE_NAME;

发现MAXBYTES为34359721984，而其中一个数据文件已经接近于这个值。
原来我们的ORACLE db_block_size = 8192
我们的AIX中已经设置了fsize=-1 (/etc/secrucity/limits)
It is not an OS limitation.
It is an Oracle limitation.
Using 8k block size the maximum file size is 32G.

(2^22-1)*8192=34359730176
(2^22-1)*8192/1024/1024/1024= 31.99999237G

SQL> alter database datafile '/u02/oradata/PROD/app_data.dbf' resize 40960M;
alter database datafile '/u02/oradata/PROD/app_data.dbf' resize 40960M
*
ERROR at line 1:
ORA-01144: File size (5242880 blocks) exceeds maximum of 4194303 blocks

 

action:

alter tablespace TABLE_SPACE add datafile '/u02/oradata/PROD/app_data1.dbf' size 500M;

alter datafile '/u02/oradata/PROD/app_data1.dbf' autoextend on next 10240K maxsize unlimited;

--------------------------------------------------------------------------------------

• 如何配置不同尺寸的db block size 

from： http://blog.itpub.net/9240380/viewspace-756446/

create tablespace与db block size

http://www.dba-oracle.com/oracle_tips_blocksizes-.htm

-支持2k到16k的data block size,不支持32k;在一些os平台不支持一些特定的db block size
SQL> create tablespace tbs_non_standard datafile 'c:\tbs_non_standard.dbf' size 100m blocksize 32k;
 
create tablespace tbs_non_standard datafile 'c:\tbs_non_standard.dbf' size 100m blocksize 32k
 
ORA-00382: 32768 not a valid block size, valid range [2048..16384]
 
SQL> create tablespace tbs_non_standard datafile 'c:\tbs_non_standard.dbf' size 100m blocksize 16k;
 
create tablespace tbs_non_standard datafile 'c:\tbs_non_standard.dbf' size 100m blocksize 16k
 
ORA-29339: tablespace block size 16384 does not match configured block sizes

SQL> alter system set sga_target=3g scope=spfile;
 
System altered
 
SQL> select * from v$sga_dynamic_free_memory;
 
CURRENT_SIZE
------------
           0
 
SQL> show parameter sga_target
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_target                           big integer 0
 
SQL> alter system set db_16k_cache_size=100m;
 
alter system set db_16k_cache_size=100m
 
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00384: Insufficient memory to grow cache
 
SQL> show parameter cache_size
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_size             big integer 0
db_16k_cache_size                    big integer 0
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0
db_4k_cache_size                     big integer 0
db_8k_cache_size                     big integer 0
db_cache_size                        big integer 1552M
db_flash_cache_size                  big integer 0
db_keep_cache_size                   big integer 0
db_recycle_cache_size                big integer 0
 
SQL>

SQL> alter system set sga_max_size=3g scope=spfile;
 
System altered

--重启库

SQL> show parameter cache_size
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
client_result_cache_size             big integer 0
db_16k_cache_size                    big integer 0
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0
db_4k_cache_size                     big integer 0
db_8k_cache_size                     big integer 0
db_cache_size                        big integer 1552M
db_flash_cache_size                  big integer 0
db_keep_cache_size                   big integer 0
db_recycle_cache_size                big integer 0
 
SQL> show parameter sga
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 3G
sga_target                           big integer 3G

SQL> alter system set db_16k_cache_size=100m;
 
System altered

SQL> create tablespace tbs_non_standard datafile 'c:\tbs_non_standard.dbf' size 100m blocksize 16k;
 
Tablespace created

SQL> select tablespace_name,block_size from dba_tablespaces;
 
TABLESPACE_NAME                BLOCK_SIZE
------------------------------ ----------
SYSTEM                               8192
SYSAUX                               8192
UNDOTBS1                             8192
TEMP                                 8192
USERS                                8192
SELF_LEARN                           8192
T_TBS1                               8192
T_TBS2                               8192
TBS_HANG                             8192
TBS_AUTO                             8192
TBS_NON_STANDARD                    16384
 
11 rows selected

SQL> create table t_8k(a int) tablespace tbs_auto;
 
Table created

SQL> begin
  2  for i in 1..1000000 loop
  3  insert into t_8k values(i);
  4  if mod(i,10000)=0 then
  5   commit;
  6  end if;
  7  end loop;
  8  end;
  9  /
 
PL/SQL procedure successfully completed

---如下连续运行3次的结果，逻辑读为1577次
SQL> select count(a) from t_8k;

Execution Plan
----------------------------------------------------------
Plan hash value: 576579961

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    13 |   293   (5)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |    13 |            |          |
|   2 |   TABLE ACCESS FULL| T_8K |   870K|    10M|   293   (5)| 00:00:04 |
---------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1577  consistent gets
          0  physical reads
          0  redo size
        526  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

--创建16 db block size逻辑表大大减少；
SQL> create table t_16k(a int) tablespace tbs_non_standard;

Table created.

22:42:30 SQL> begin
           2  for i in 1..1000000 loop
           3  insert into t_16k values(i);
           4  if mod(i,10000)=0 then
           5   commit;
           6  end if;
           7  end loop;
           8  end;
           9  /
          
 
PL/SQL procedure successfully completed
 
Executed in 120.589 seconds
 
 
SQL> select count(a) from t_16k;

Execution Plan
----------------------------------------------------------
Plan hash value: 3599734656

----------------------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |    13 |   157   (9)| 00:00:02 |
|   1 |  SORT AGGREGATE    |       |     1 |    13 |            |          |
|   2 |   TABLE ACCESS FULL| T_16K |   910K|    11M|   157   (9)| 00:00:02 |
----------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        775  consistent gets
          0  physical reads
          0  redo size
        526  bytes sent via SQL*Net to client
        523  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
 
 
 
--在dml方面二者又有何区别呢

22:47:21 SQL> truncate table t_8k;
 
Table truncated
 
Executed in 1.046 seconds
 
22:50:38 SQL> truncate table t_16k;
 
Table truncated
 
Executed in 0.047 seconds      

--如下测试16k比8k用时少一些；
22:51:52 SQL> begin                      
           2  for i in 1..1000000 loop   
           3  insert into t_8k values(i);
           4  if mod(i,10000)=0 then     
           5   commit;                   
           6  end if;                    
           7  end loop;                  
           8  end;                       
           9  /                          
                                         
                                         
PL/SQL procedure successfully completed  
                                         
Executed in 112.617 seconds              
                                         
22:53:45 SQL>                            
22:54:06 SQL>                            
22:54:06 SQL>                            
22:54:06 SQL> ed                         
22:54:24 SQL>                            
22:54:26 SQL> begin                      
           2  for i in 1..1000000 loop   
           3  insert into t_16k values(i);
           4  if mod(i,10000)=0 then     
           5   commit;                   
           6  end if;                    
           7  end loop;                  
           8  end;                       
           9  /                          
                                         
PL/SQL procedure successfully completed  
                                         
Executed in 108.936 seconds               

--为了公平及更接近真实情况，清空缓冲池及共享池
22:59:08 SQL> alter system flush buffer_cache;
                                             
System altered                               
                                             
Executed in 36.317 seconds                   
                                             
22:59:56 SQL> alter system flush shared_pool;
                                             
System altered                               
                                             
Executed in 0.094 seconds                     

23:01:33 SQL> truncate table t_8k;
                                  
Table truncated                   
                                  
Executed in 3.104 seconds         
                                  
23:02:27 SQL> truncate table t_16k;
                                  
Table truncated                   
                                  
Executed in 0.125 seconds        

--确实大block size比小block size稍显优势；
23:02:55 SQL> begin                           
           2  for i in 1..1000000 loop       
           3  insert into t_8k values(i);    
           4  if mod(i,10000)=0 then         
           5   commit;                       
           6  end if;                        
           7  end loop;                      
           8  end;                           
           9  /                              
                                             
                                             
PL/SQL procedure successfully completed      
                                             
Executed in 119.981 seconds                  
                                             
23:04:56 SQL> alter system flush buffer_cache;
                                             
System altered                               
                                             
Executed in 34.242 seconds                   
                                             
23:08:19 SQL> alter system flush shared_pool;
                                             
System altered                               
                                             
Executed in 0.031 seconds                    
                                             
23:08:31 SQL>                                
23:08:31 SQL>                                
23:08:53 SQL> begin                          
           2  for i in 1..1000000 loop       
           3  insert into t_16k values(i);   
           4  if mod(i,10000)=0 then         
           5   commit;                       
           6  end if;                        
           7  end loop;                      
           8  end;                           
           9  /                              
                                             
                                             
PL/SQL procedure successfully completed      
                                             
Executed in 103.663 seconds       

大数据块在删除数据方面比小数据块尺寸要花费更多的时间；原因？非常重演中学
23:12:03 SQL> alter system flush buffer_cache;          
                                                                                           
System altered                               
                                             
Executed in 32.76 seconds                    
                                             
23:12:38 SQL> alter system flush shared_pool;
                                             
System altered                               
                                             
Executed in 0.016 seconds                    
                                             
23:12:44 SQL>                                
23:12:44 SQL> delete from t_8k;              
                                             
1000000 rows deleted                         
                                             
Executed in 31.809 seconds                   
                                             
23:13:28 SQL> commit;                        
                                             
Commit complete                              
                                             
Executed in 0.016 seconds                    
                                             
23:13:40 SQL> alter system flush shared_pool;
                                             
System altered                               
                                             
Executed in 0.015 seconds                    
                                             
23:13:48 SQL> alter system flush buffer_cache;
                                             
System altered                               
                                             
Executed in 64.944 seconds                   
                                             
23:14:56 SQL> delete from t_16k;             
                                             
1000000 rows deleted                         
                                             
Executed in 43.618 seconds                   
                                             
23:15:55 SQL>                   

 

--重新插入数据分别到2个表中,大块表比小块表用时稍多一点点；
23:20:55 SQL> begin                                       
           2  for i in 1..1000000 loop       
           3  insert into t_8k values(i);    
           4  if mod(i,10000)=0 then         
           5   commit;                       
           6  end if;                        
           7  end loop;                      
           8  end;                           
           9  /                              
                                             
                                             
PL/SQL procedure successfully completed      
                                             
Executed in 97.002 seconds                   
                                             
23:22:32 SQL> ed                             
23:22:50 SQL>                                
23:22:50 SQL>                                
23:22:50 SQL>                                
23:22:51 SQL> begin                          
           2  for i in 1..1000000 loop       
           3  insert into t_16k values(i);   
           4  if mod(i,10000)=0 then         
           5   commit;                       
           6  end if;                        
           7  end loop;                      
           8  end;                           
           9  /                              
                                             
PL/SQL procedure successfully completed      
                                             
Executed in 97.937 seconds                   
                                             
23:24:33 SQL> alter system flush buffer_cache;
                                             
System altered                               
                                             
Executed in 84.256 seconds                   
                                             
23:26:35 SQL> alter system flush shared_pool;
                                             
System altered                               
                                             
Executed in 0.015 seconds                    
                                             
23:26:49 SQL> update t_8k set a=1;           
                                             
1000000 rows updated                         
                                             
Executed in 43.867 seconds                   
                                             
23:27:46 SQL> commit;                        
                                             
Commit complete                              
                                             
Executed in 0.015 seconds                    
                                             
23:28:17 SQL> alter system flush buffer_cache;
                                             
System altered                               
                                             
Executed in 88.156 seconds                   
                                             
23:29:49 SQL> alter system flush shared_pool;
                                             
System altered                               
                                             
Executed in 0.016 seconds                    
                                             
23:29:56 SQL> update t_16k set a=1;          
                                             
1000000 rows updated                         
                                             
Executed in 46.099 seconds                   
                                             
23:30:54 SQL>