创建TABLESPACE 的ORA-03214错误

创建TABLESPACE 的ORA-03214错误

Kevin Zou

2011-8-25

在PUB论坛上看到一个问题，简单描述就是在创建TABLESPACE，遇到了ORA-03214的错误

SQL> create tablespace test datafile '/diskfdcprodb06/oradata1/oracle/timsdb/idx01/dbf/test01.dbf' size 1M autoextend on next 2m 

  2  maxsize 11M uniform;

create tablespace test datafile '/diskfdcprodb06/oradata1/oracle/timsdb/idx01/dbf/test01.dbf' size 1M autoextend on next 2m

*

ERROR at line 1:

ORA-03214: File Size specified is smaller than minimum required

查看METALINK 有这样的一段话：[ID 153869.1]

Explanation ----------- 1. In 9i, when you create a tablespace, it is by default created as a Locally     Managed Tablespace with a minimum extent size of 64K, if the local management    is system managed.    Therefore, you must allocate size datafiles to 64 Kbytes + 3 blocks for the bitmap blocks.    In solution 1, the datafile size could not be set less than 64K + (3*8k)= 88k 2. If you supply the UNIFORM. parameter, size the datafiles to 3 blocks + one extent size    minimum. It offers the means to allocate the first extent without problem.      Therefore in solution 2, the datafile cannot be set less than     10K + (3*8k) = 34K    SQL> create tablespace verysmall      2  datafile '/ora/ora901/oradata/V901/small01.dbf'      3  size 32k      4  uniform. size 10K;    create tablespace verysmall    *    ERROR at line 1:    ORA-03214: File Size specified is smaller than minimum required

在这个CREATE TABLE已经中要求UNIFORM. 来管理extent，ORACLE对于缺省的EXTENT 的大小为：

UNIFORM. specifies that the tablespace is managed with uniform. extents of SIZE bytes.The default SIZE is 1 megabyte. All extents of temporary tablespaces are of uniform. size, so this keyword is optional for a temporary tablespace. However, you must specify UNIFORM. in order to specify SIZE. You cannot specify UNIFORM. for an undo tablespace.

所以以上的例子，MIN SIZE=3*8K+1024K=1048K

再次执行修改过的语句：

SQL> create tablespace test datafile '/diskfdcprodb06/oradata1/oracle/timsdb/idx01/dbf/test01.dbf' size 1048K autoextend on next 2m 

  2     maxsize 11M uniform;

Tablespace created.

创建成功。

在新创建的TABLESPACE 创建一个对象

SQL> create table tt tablespace test as select * from all_objects ;

Table created.

检查对象存储在TABLESPACE中的位置：

SQL> select file_id, block_id  from dba_extents where segent_name ='TT' AND wner='SYS';

select file_id, block_id  from dba_extents where segment_name ='TT' AND wner='SYS';

   FILE_ID   BLOCK_ID

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

        63          4

        63        132

看到一个新的TABLESPACE中的第一个对象存储位置从第4个block 开始。

DUMP 文件的BLOCK来验证：

SQL> alter system dump datafile 63 block 1; System altered. SQL> alter system dump datafile 63 block 2; System altered. SQL> alter system dump datafile 63 block 3; System altered. SQL> alter system dump datafile 63 block 4; System altered. *** 2011-08-24 22:46:04.332 *** ACTION NAME:() 2011-08-24 22:46:04.155 *** MODULE NAME:(sqlplus@sjfdcdb9 (TNS V1-V3)) 2011-08-24 22:46:04.155 *** SERVICE NAME:(SYS$USERS) 2011-08-24 22:46:04.155 *** SESSION ID:(269.1439) 2011-08-24 22:46:04.155 Start dump data blocks tsn: 17 file#: 63 minblk 1 maxblk 1 Block 1 (file header) not dumped: use dump file header command End dump data blocks tsn: 17 file#: 63 minblk 2 maxblk 1 *** 2011-08-24 22:51:55.105 Start dump data blocks tsn: 17 file#: 63 minblk 2 maxblk 2 buffer tsn: 17 rdba: 0x0fc00002 (63/2) scn: 0x0000.3e20db7a seq: 0x03 flg: 0x04 tail: 0xdb7a1d03 frmt: 0x02 chkval: 0x2ecc type: 0x1d=KTFB Bitmapped File Space Header Hex dump of block: st=0, typ_found=1 Dump of memory from 0xB5E53E00 to 0xB5E55E00 B5E53E00 0000021D 0FC00002 3E20DB7A 04030000  [........z. >....] B5E53E10 00002ECC 0000003F 00000080 00000183  [....?...........] B5E53E20 00000009 00000100 00000580 00000002  [................] B5E53E30 00000183 00000002 00000001 00000000  [................] B5E53E40 00000000 00000000 00000000 00000000  [................] B5E53E50 00000084 00000080 00000000 00000000  [................] B5E53E60 00000000 00000000 00000000 00000000  [................]         Repeat 504 times B5E55DF0 00000000 00000000 00000000 DB7A1D03  [..............z.] File Space Header Block: Header Control: RelFno: 63, Unit: 128, Size: 387, Flag: 9 AutoExtend: YES, Increment: 256, MaxSize: 1408 Initial Area: 2, Tail: 387, First: 2, Free: 1 Deallocation scn: 0.0 Header Opcode: Save: No Pending Op End dump data blocks tsn: 17 file#: 63 minblk 2 maxblk 2 Start dump data blocks tsn: 17 file#: 63 minblk 3 maxblk 3 buffer tsn: 17 rdba: 0x0fc00003 (63/3) scn: 0x0000.3e20db7a seq: 0x01 flg: 0x04 tail: 0xdb7a1e01 frmt: 0x02 chkval: 0xde39 type: 0x1e=KTFB Bitmapped File Space Bitmap Hex dump of block: st=0, typ_found=1 Dump of memory from 0xB5E53E00 to 0xB5E55E00 B5E53E00 0000021E 0FC00003 3E20DB7A 04010000  [........z. >....] B5E53E10 0000DE39 0000003F 00000004 00000000  [9...?...........] B5E53E20 00000002 0000F7FE 00000000 00000000  [................] .。。。。 Start dump data blocks tsn: 17 file#: 63 minblk 4 maxblk 4 buffer tsn: 17 rdba: 0x0fc00004 (63/4) scn: 0x0000.3e20dbdd seq: 0x03 flg: 0x04 tail: 0xdbdd2003 frmt: 0x02 chkval: 0x1786 type: 0x20=FIRST LEVEL BITMAP BLOCK  （这里type和前3个是不一样的了） Hex dump of block: st=0, typ_found=1 Dump of memory from 0xB5E53E00 to 0xB5E55E00 B5E53E00 00000220 0FC00004 3E20DBDD 04030000  [ ......... >....] B5E53E10 00001786 00000000 00000000 00000000  [................] B5E53E20 00000000 00000000 00000000 00000000  [................]

从DUMP从来的block看到，确实前3个block为metadata 信息，存放tablespace，datafile的信息，真正的数据存放在从第四个block开始的空间。

如要看HEADER 的信息，可以通过

alter session set events 'immediate trace name FILE_HDRS level 10'; 

来查看下。

列出部分FILE HEADER的信息：

DATA FILE #63:   (name #75) /diskfdcprodb06/oradata1/oracle/timsdb/idx01/dbf/test01.dbf creation size=131 block size=8192 status=0xe head=75 tail=75 dup=1  tablespace 17, index=14 krfil=63 prev_file=0  unrecoverable scn: 0x0000.00000000 01/01/1988 00:00:00  Checkpoint cnt:2 scn: 0x0000.3e1f815e 08/24/2011 22:45:30  Stop scn: 0xffff.ffffffff 08/24/2011 22:45:30  Creation Checkpointed at scn:  0x0000.3e1f814d 08/24/2011 22:45:30  thread:1 rba:(0x4da1.2e9c6.10) 00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000   00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000   00000000 00000000 00000000 00000000 00000000 00000000  Hot Backup end marker scn: 0x0000.00000000  aux_file is NOT DEFINED  V10 STYLE. FILE HEADER:         Software vsn=153092096=0x9200000, Compatibility Vsn=134217728=0x8000000         Db ID=3333692251=0xc6b41b5b, Db Name='TIMSDB'         Activation ID=0=0x0         Control Seq=125188=0x1e904, File size=387=0x183         File Number=63, Blksiz=8192, File Type=3 DATA Tablespace #17 - TEST  rel_fn:63 Creation   at   scn: 0x0000.3e1f814d 08/24/2011 22:45:30 Backup taken at scn: 0x0000.00000000 01/01/1988 00:00:00 thread:0  reset logs count:0x29d52cdb scn: 0x0000.00000001 reset logs terminal rcv data:0x0 scn: 0x0000.00000000  prev reset logs count:0x0 scn: 0x0000.00000000 prev reset logs terminal rcv data:0x0 scn: 0x0000.00000000  recovered at 01/01/1988 00:00:00  status:0x4 root dba:0x00000000 chkpt cnt: 2 ctl cnt:1 begin-hot-backup file size: 0 Checkpointed at scn:  0x0000.3e1f815e 08/24/2011 22:45:30  thread:1 rba:(0x4da1.2e9c6.10)  enabled  threads:  01000000 00000000 00000000 00000000 00000000 00000000   00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000   00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

-THE END-

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

转载于:http://blog.itpub.net/40239/viewspace-705777/