20M的dmp文件导入oracle数据库,表空间竟然2G，还有分区表没导入成功

select segment_name,sum(bytes)/1024/1024 FROM user_segments
where tablespace_name='你的表空间名字'
group by segment_name
order by 2
用DBA用户执行上面的语句查看数据对象所占的空间大小，看看是啥占的空间
估计可能是索引占用的空间

应该是dba_segments，上面的语句是查看单个用户每个数据对象的空间占用

这是报错的sql，里面好多参数不太懂，例如
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)这一串，我都没使用过，也看了相关资料，还是没理清楚。  CREATE TABLE MY_DATA
    (
      MY_DATE  CHAR(10) NOT NULL ENABLE,
      MY_ACT NO VARCHAR2(30) NOT NULL ENABLE,
      MY_A   NUMBER(18, 2) NOT NULL ENABLE,
      MY_F   CHAR(1) NOT NULL ENABLE,
      MY_R FLOAT(126),
      MY_C FLOAT(126) ,
      MY_L NUMBER(18, 2),
      MY_W FLOAT(126),
      MY_K NUMBER(18, 2)
    )
      PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 TABLESPACE DELI_DATA LOGG ING PARTITION BY RANGE
      (
        MY_DATE
      )
      (
        PARTITION P0 VALUES LESS THAN ('2010-07-01')

        PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE DELI_D ATA LOGGING NOCOMPRESS,

        PARTITION MY_S1007 VALUES LESS THAN ('2010-08-01')  TABLESPACE DELI_DATA LOGGING NOCOMPRESS,
        PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
        PARTITION MY_S1008 VALUES LESS THAN ('2010-09-01') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE DELI_DATA LOGGING NOCOMPRESS,
        PARTITION MY_S10S3 VALUES LESS THAN ('2010-10-01') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE DELI_DATA LOGGING NOCOMPRESS,
        PARTITION MY_S1011 VALUES LESS THAN ('2010-12-01') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE DELI_DATA LOGGING NOCOMPRESS,
        PARTITION MY_S1012 VALUES LESS THAN ('2011-01-01') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE DELI_DATA LOGGING NOCOMPRESS,
        PARTITION MY_S1101 VALUES LESS THAN ('2011-02-01') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE DELI_DATA LOGGING NOCOMPRESS,
        PARTITION MY_S1102 VALUES LESS THAN ('2011-03-01') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE DELI_DATA LOGGING NOCOMPRESS,
        PARTITION MY_S1104 VALUES LESS THAN ('2011-05-01') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE DEL I_DATA LOGGING NOCOMPRESS,
        PARTITION MY_S1105 VALUES LESS THAN ('2011-06 -01') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE DELI_DATA LOGGING NOCOMPRESS,
        PARTITION MY_S1106 VALUES LESS THAN ('2011-07-01') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE DELI_DATA LOGGING NO COMPRESS,
        PARTITION MY_S1107 VALUES LESS THAN ('2011-08-01') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE DELI_DATA LOGGING NOCOMPRESS,
        PARTITION MY_S1108 VALUES LESS THAN ('2011-09-01') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE DELI_DATA LOGGING NOCOMPRESS,
        PARTITION MY_S1109 VALUES LESS THAN ('2011-10-01') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE DELI_DATA LOGGING NOCOMPRESS,
        PARTITION MY_S1204 VALUES LESS THAN ('2012-05-01') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE DELI_DATA LOGGING NOCOMPRESS,
        PARTITION MY_S1205 VALUES LESS THAN ('2012-06-01') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE DELI_DATA LOGGING NOCOMPRESS,
        PARTITION MY_S1206 VALUES LESS THAN ('2012-07-01') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(IN ITIAL 3145728 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE DELI_DATA LOGGING NOCOMPRESS,
        PARTITION MY_S1207 VALUES LESS THAN ('2012-08-01') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 2 6214400 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE DELI _DATA LOGGING NOCOMPRESS,
        PARTITION MY_S1208 VALUES LESS THAN ('2012-09-01') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 4508876 8 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE DELI_DATA LOGGING NOCOMPRESS,
        PARTITION MY_S1209 VALUES LESS THAN ('2012-10-01') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 56623104 FREE LISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE DELI_DATA LOGGING NOCOMPRESS,
        PARTITION MY_S1210 VALUES LESS THAN ('2012-11-01') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 75497472 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE DELI_DATA LOGGING NOCOMPRESS,
        PARTITION MY_S1211 VALUES LESS THAN ('2012-12-01') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 100663296 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE DELI_DATA LOGGING NOCOMPRESS,
        PARTITION MY_S1212 VALUES LESS THAN ('2013-01-01') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 109051904 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE DELI_DATA LOGGING NOCOMPRESS,
        PARTITION MY_S1301 VALUES LESS THAN ('2013-02-01') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 117440512 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE DELI_DATA LOGGING NOCOMPRESS,
        PARTITION MY_S1302 VALUES LESS THAN ('2013-03-01') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 117440512 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE DELI_DATA LOGGING NOCOMPRESS,
        PARTITION MY_S1303 VALUES LESS THAN ('2013-04-01') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 134217728 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE DELI_DATA LOGGING NOCOMPRESS,
        PARTITION MY_S1304 VALUES LESS THAN ('2013-05-01') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 146800640 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE DELI_DATA LOGGING NOCOMPRESS,
        PARTITION MY_S1305 VALUES LESS THAN ('2013-06-01') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 170917888 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE DELI_DATA LOGGING NOCOMPRESS,
        PARTITION MY_S1306 VALUES LESS THAN ('2013-07-01') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 182452224 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE DELI_DATA LOGGING NOCOMPRESS,
        PARTITION MY_S1307 VALUES LESS THAN ('2013-08-01') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 201326592 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE DELI_DATA LOGGING NOCOMPRESS,
        PARTITION MY_S1308 VALUES LESS THAN ('2013-09-01') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 209715200 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE DELI_DATA LOGGING NOCOMPRESS,
        PARTITION MY_S1309 VALUES LESS THAN ('2013-10-01') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 225443840 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE DELI_DATA LOGGING NOCOMPRESS,
        PARTITION MY_S1310 VALUES LESS THAN ('2013-11-01') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 24326 9632 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE DELI_DATA LOGGING NOCOMPRESS,
        PARTITION MY_S1311 VALUES LESS THAN ('2013-12-01' ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 243269632 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE DELI_DATA LOGGING NOCOMPRESS,
        PARTITION MY_S1312 VALUES LESS THAN ('2014-01-01') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 276824064 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE DELI_DATA LOGGING NOCOMPRESS,
        PARTITION MY_S1401 VALUES LESS THAN ('2014-02-01') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 134217728 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE DELI_DATA LOGGING NOCOMPRESS,
        PARTITION MY_S1402 VALUES LESS THAN ('2014-03-01') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 1048576 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE DELI_DATA LOGGING NOCOMPRESS ,
        PARTITION MY_S1403 VALUES LESS THAN ('2014-04-01') PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 1048576 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE DELI_DATA LOGGING NOCOMPRESS
      )

还有这个错误信息：
IMP-00003: 遇到 ORACLE 错误 1659
ORA-01659: 无法分配超出 4 的 MINEXTENTS (在表空间 DELI_DATA 中)

用你的sql查询是400M，但实际占用空间是2G呢。。
只有一个用户使用这个表空间，用管理账户查看了。

我重新建了表空间，自增大小无限制的（maxsize unlimited）
等表全都导入成功后，发现MY_DATA表暂用2.7G，
MY_DATA有两个索引，一个2.7G，一个3.8G
表空间总共近10G!!  我只是导入20M的dmp呀，为什么会占用这么多空间。
另外，我也查了MY_DATA，里面只用67条数据，有38个分区。
就算一个分区占用32M，也不过1.2G吧。
    之前没怎么弄过表分区，对这块确实不懂。
以下是其中一个索引的SQL CREATE UNIQUE INDEX "TEST1"."PK_MY" ON "TEST1"."ZD_DAYDETAIL" ("MY_DATE", "MY_ACT")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 3998220288 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "DELI_DATA" ;

分区表每个分区创建时都会申请一定的空间，而其中的数据不会有那么多
另外在dmp中应该只有创建索引的设置信息，索引的空间是导入时生成索引的过程中占用的

pctused=40，表示数据块中最多能插入40%的数据，pctfree=10,表示至少要10%备用

谢谢！~ 主要问题还是创建的时候分配的空间太多了，INITIAL 3998220288这个就3.7G了。

调试易

20M的dmp文件导入oracle数据库,表空间竟然2G，还有分区表没导入成功

解决方案 »