| Profilezhanjie的共享空间PhotosBlogLists |  Tools  Help |
|
July 24 ORA-03237: Initial Extent of specified size cannot be allocated in tablespace问题是在建立LOB字段时发生的，用 dmsdeuser1用户执行
SQL> connect dmsdeuser1/pass@sid SQL> CREATE TABLE CONTENT ( 2 MSG_ID CHAR(100) NOT NULL, 3 MSG_DATA BLOB, 4 CONSTRAINT PK_CONTENT PRIMARY KEY (MSG_ID) 5 ); CREATE TABLE CONTENT ( ERROR at line 1: ORA-03237: Initial Extent of specified size cannot be allocated in tablespace (DATA01) dmsdeuser1用户默认表空间是DATA01，是UNLIMITED首先看表空间是否有空余空间
SQL> SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE, (B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE" FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME; TABLESPACE_NAME TOTAL ------------------------------------------------------------ ---------- USED FREE % USED % FREE ---------- ---------- ---------- ----------
DATA01 2147483648
1081344 2146336768 .050354004 99.9465942
SYSTEM 419430400 416677888 2686976 99.34375 .640625
UNDOTBS1 209715200 4898816 204668928 2.3359375 97.59375
虽然DATA01空间是够的，但还是做了一下扩展，SYSTEM表空间将要用尽，也扩展下。 alter database datafile 'D:\ORACLE\ORADATA\DE2\SYSTEM01.DBF' resize 600M; alter database datafile 'D:\ORACLE\ORADATA\DE2\DATA01' resize 3000M;
错误依旧
select * from dba_tablespace;
TABLESPACE_NAME BLOCK_SIZE ------------------------------------------------------------ ---------- INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE MIN_EXTLEN -------------- ----------- ----------- ----------- ------------ ---------- STATUS CONTENTS LOGGING FORCE_ ------------------ ------------------ ------------------ ------ EXTENT_MANAGEMENT ALLOCATION_TYPE PLUGGE SEGMENT_SPAC -------------------- ------------------ ------ ------------ SYSTEM 8192 65536 1 2147483645 65536 ONLINE PERMANENT LOGGING NO LOCAL SYSTEM NO MANUAL
DATA01 8192 16384 16384 1 2147483645 0 16384 ONLINE PERMANENT LOGGING NO LOCAL UNIFORM NO MANUAL
DATA02 8192 1048576 1048576 1 2147483645 0 1048576 ONLINE PERMANENT LOGGING NO LOCAL UNIFORM NO AUTO
SQL> show parameter block
NAME TYPE ------------------------------------ ---------------------- VALUE ------------------------------ db_block_size integer 8192 问题就在这里了，平常，数据库分配的最小EXTENT是两个BLOCK_SIZE，也就是16K，而DATA01表空间的INITIAL EXTENT正好是16K，不会出什么问题。但是LOB等需要至少3个BLOGKS，而RBS需要4个。所以在DATA01表空间是无法建立LOG字段的。 1 create tablespace data02 datafile 'D:\ORACLE\ORADATA\DE2\DATA02' size 2000M 2* extent management local uniform size 1M segment space management auto SQL> / Tablespace created. SQL> alter user dmsdeuser1 default tablespace data02; User altered. SQL> alter user dmsdeuser1 quota unlimited on data02; User altered.
SQL> connect dmsdeuser1/pass1@sid Connected. SQL> CREATE TABLE CONTENT ( 2 MSG_ID CHAR(100) NOT NULL, 3 MSG_DATA BLOB, 4 CONSTRAINT PK_CONTENT PRIMARY KEY (MSG_ID) 5 ) 6 / Table created. TrackbacksThe trackback URL for this entry is: http://hzjwcx.spaces.live.com/blog/cns!4A303669374E5F92!114.trak Weblogs that reference this entry
|
|
|
