sqlload 报ORA-01658: unable to create INITIAL extent for segment in tablespace

陌上惠

sundog315 发表于 2012-9-7 08:37
没有足够的连续空间创建INITIAL extent

建表语句是什么样的？

CREATE TABLE ASPBI_MM.MM_I_ORDERSMSVALICODE
(
  TIME          DATE,
  MSISDN        VARCHAR2(15 BYTE),
  UA            VARCHAR2(300 BYTE),
  DEVICENAME    VARCHAR2(100 BYTE),
  VALIDATETYPE  VARCHAR2(10 BYTE),
  APN           VARCHAR2(10 BYTE),
  URL           VARCHAR2(1000 BYTE),
  GOODSID       VARCHAR2(50 BYTE),
  DOWNLOADURL   VARCHAR2(200 BYTE),
  SMSCONTENT    VARCHAR2(300 BYTE),
  RESPCODE      VARCHAR2(20 BYTE),
  RESPMSG       VARCHAR2(200 BYTE),
  SMSCODE       VARCHAR2(10 BYTE),
  USERCODE      VARCHAR2(10 BYTE)
)
TABLESPACE TBS_PDS36
PCTUSED    0
PCTFREE    0
INITRANS   1
MAXTRANS   255
NOLOGGING
PARTITION BY RANGE (TIME)
(  
  PARTITION P20120620 VALUES LESS THAN (TO_DATE(' 2012-06-21 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
    NOLOGGING
    COMPRESS
    TABLESPACE TBS_PDS36
    PCTFREE    0
    INITRANS   1
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                NEXT             1M
                MINEXTENTS       1
                MAXEXTENTS       UNLIMITED
                BUFFER_POOL      DEFAULT
               )

这是表空间的脚本：
CREATE TABLESPACE TBS_PDS32 DATAFILE
  '+DATA01/pdsdb/datafile/tbs_pds32.272.777295683' SIZE 30720M AUTOEXTEND OFF,
  '+DATA01/pdsdb/datafile/tbs_pds32.273.777295793' SIZE 30720M AUTOEXTEND OFF,
  '+DATA01/pdsdb/datafile/tbs_pds32.274.777295909' SIZE 30720M AUTOEXTEND OFF,
  '+DATA01/pdsdb/datafile/tbs_pds32.275.777296021' SIZE 30720M AUTOEXTEND OFF,
  '+DATA01/pdsdb/datafile/tbs_pds32.276.777296131' SIZE 30720M AUTOEXTEND OFF,
  '+DATA01/pdsdb/datafile/tbs_pds32.277.777296327' SIZE 30720M AUTOEXTEND OFF,
  '+DATA01/pdsdb/datafile/tbs_pds32.278.777296531' SIZE 30720M AUTOEXTEND OFF,
  '+DATA01/pdsdb/datafile/tbs_pds32.279.777296735' SIZE 30720M AUTOEXTEND OFF,
  '+DATA01/pdsdb/datafile/tbs_pds32.375.791566845' SIZE 30720M AUTOEXTEND OFF,
  '+DATA01/pdsdb/datafile/tbs_pds32.376.791569435' SIZE 25600M AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK OFF;

gszoracle

Sqlload导入表好像是全表作为一个initial extent

Yong Huang

> Sqlload导入表好像是全表作为一个initial extent

I didn't find that in documentation. Any test or article you know about that? Documentation says
"Step 3: Load the Partitions in Parallel ... You must set a small initial extent, because each loader session gets a new extent when it begins, and it does not use any existing space associated with the object."
http://docs.oracle.com/cd/B10501 ... a96520/transfor.htm

That's from Oracle 9.2 documentation. Not sure if it's still the case in 10.2.0.5. And I don't know if parallel is used in his/her case.

If his/her tablespace tends to fragment so severely, a long term solution is to use uniform extent size instead of auto allocate. But for now, I don't know how those 147657 tiny holes were formed. If there're a large number of small "islands" (tiny extents) belonging to a few big table, alter table move helps. If they're a few indexes, rebuilding indexes should help.

陌上惠

Yong Huang 发表于 2012-9-8 01:25
> Sqlload导入表好像是全表作为一个initial extent

I didn't find that in documentation. Any test or  ...

非常感谢Yong Huang 的建议，谢谢了，我就是按照你的建议做的

sundog315

很好奇是什么导致如此多的小碎片

陌上惠

sundog315 发表于 2012-9-11 14:37
很好奇是什么导致如此多的小碎片

呵呵，这个我现在还不是很清楚呢，刚接手这个库，只知道每天都有sqlload向里面导数据，并且每天导的数据不是很多，后续再观察一下吧

likingzi

尽管这是一个很久远的帖子，但仍然给到我帮助，谢谢楼主和各位大侠的精彩分享