创建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-