11g_ora-01658-ora-25143-CSDN博客

--oracle11g测试环境
SQL> insert into t_onlytest values(1);
insert into t_onlytest values(1)
*
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace TBS_NEWLY

--手册上讲添加新的数据文件到表空间或以小的initial extent尝试，注意操作系统显示tbs_newly表空间对应的数据文件大小为31.9gb(window7)
ORA-01658: unable to create INITIAL extent for segment in tablespace string
Cause: Failed to find sufficient contiguous space to allocate INITIAL extent for segment being created.

Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the tablespace or retry with a smaller value for INITIAL

SQL> alter tablespace tbs_newly add datafile 'd:\tbs_newly_2.dbf' size 100m auto
extend on;

Tablespace altered.

SQL> insert into t_onlytest values(1);

1 row created.

SQL> commit;

Commit complete.

SQL> select segment_name,extent_id,bytes,blocks from user_extents where segmen
name='T_ONLYTEST';

SEGMENT_NAME
------------------------------------------------------------------------------

EXTENT_ID BYTES BLOCKS
---------- ---------- ----------
T_ONLYTEST
0 3145728 384

SQL> show parameter block_s

NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
db_block_size integer
8192
SQL> select 384*8 from dual;

384*8
----------
3072

---也与上述的dba_tablespaces的initial_extent与next_extent及min_extlen相同
SQL> select 384*8*1024 from dual;

384*8*1024
----------
3145728

－－－必须同时先指定 default nocompress方才指定storage子句，如仅指定storage子句会报错
SQL> create tablespace tbs_today datafile 'd:\tbs_today.dbf' size 10m default no
compress storage(initial 2k next 3k);

Tablespace created.

－－－即使intial指定为2k,oracle设置为64k
TABLESPACE_NAME                                              INITIAL_EXTENT
------------------------------------------------------------ --------------
NEXT_EXTENT ALLOCATION_TYPE    MIN_EXTLEN
----------- ------------------ ----------
TBS_TODAY                                                             65536
            SYSTEM                  65536

SQL> alter tablespace tbs_today default nocompress storage(initial 128k next 200
k);
alter tablespace tbs_today default nocompress storage(initial 128k next 200k)
*
ERROR at line 1:
ORA-25143: default storage clause is not compatible with allocation policy

---是否说如果extent management为autoallocate or uniform则不能再变更storage的initial and next了，改成manual试下，而如普通表空间要为dictionary，则系统表空间须先为dictionary
ORA-25143: default storage clause is not compatible with allocation policy
Cause: default storage clause was specified for a tablespace with AUTOALLOCATE or UNIFORM. policy

Action: Omit the storage clause

在extent management 为autoallocate or uniform，不能alter tablespace变更相关的storage存储参数

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

转载于:http://blog.itpub.net/9240380/viewspace-761494/