基表导入分区表时表空间暴涨问题

leonhat

数据库环境：

服务器
单台，操作系统：suse11-64bit
内存：
             total       used       free     shared    buffers     cached
Mem:      12168760   11964648     204112          0      81228   11308232
-/+ buffers/cache:     575188   11593572
Swap:     16777208     101908   16675300

CPU：
2颗8核 2.4G至强

硬盘：
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/isw_babhfbdfji_Volume0_part2
                      7.9G  423M  7.1G   6% /
udev                   21G  260K   21G   1% /dev
/dev/mapper/isw_babhfbdfji_Volume0_part1
                      4.0G   96M  3.7G   3% /boot
/dev/mapper/vg00-lvol_home
                       20G  173M   19G   1% /home
/dev/mapper/vg00-lvol_opt
                      315G  289G   11G  97% /opt    --oracle所在
/dev/mapper/vg00-lvol_tmp
                       16G  175M   15G   2% /tmp
/dev/mapper/vg00-lvol_usr
                       50G  4.0G   43G   9% /usr
/dev/mapper/vg00-lvol_var
                       30G  343M   28G   2% /var

数据库：
单库，oracle 11.2.0.3-64bit

表空间：
Name            Type      Size (M)             Used (M)                            Used %
--------------- --------- -------------------- ----------------------------------- ----------
SYSAUX          PERMANENT       1,024.000            712.250/     1024.000           69.56%
UNDOTBS1        UNDO            2,948.000             88.250/     2948.000            2.99%
USERS           PERMANENT           5.000              1.000/        5.000           20.00%
SYSTEM          PERMANENT       1,024.000            783.938/     1024.000           76.56%
TS_CASE_DATA    PERMANENT     153,600.000          25212.625/   153600.000           16.41%  --用户表空间
TEMP            TEMPORARY       3,072.000           1740.000/     3072.000           56.64%

基表：case_infor 有1500万数据 约占10G空间
根据基表的case_id建立list分区，共3500个
新建分区表：case_infor_new

用insert into case_infor_new select * from case_infor;语句将基表数据导入到分区表

问题：
1、导入执行3分钟后报错：
ORA-01658: 无法为表空间 TS_CASE_DATA 中的段创建 INITIAL 区
查看表空间：
TS_CASE_DATA使用率为100%
2、将表drop掉，空间恢复为16%，重建时在分区后面加上以下语句：
tablespace ts_case_data pctfree 10 initrans 1 maxtrans 255 storage ( initial 1k next 100k minextents 1 maxextents unlimited )
并且将导入语句改用过程方式，过程如下：
Set serveroutput On;
Declare
v_Court Int;
Cursor v_Id Is
  Select Id From Tb_Org_Court;
Begin
For i In v_Id Loop
  v_Court := i.Id;
  Insert Into Tb_Case_Infor_New
   Select * From Tb_Case_Infor Where Court_Id = v_Court;
  Commit;
End Loop;
Commit;
Exception
When Others Then
  Rollback;
    dbms_output.put_line('err');
End;
/
在过程执行40分钟后依然报错，空间依然100%

请问：
1、为何空间会暴涨？
2、如果用其他方式导入（如在线重定义或交换方式）还会出现此现象吗？这个还没试

有需要的参数请回帖告知，谢谢。

leonhat

我将机器挂了存储，分了400G表空间

数据用APPEND方式导入，花了900秒，导入成功，总共占用了220G空间

Maclean Liu(刘相兵

嗯 这么一看很清晰， 问题要么在 partition上 要么在LOb上！

leonhat

1. 
   
2.   CREATE TABLE "ZHIXING"."TB_CASE_INFOR"
   
3.    (        "ID" NUMBER(10,0) NOT NULL ENABLE,
   
4.         "COURT_ID" NUMBER(6,0) DEFAULT 0 NOT NULL ENABLE,
   
5.         "CASE_CODE" VARCHAR2(40) NOT NULL ENABLE,
   
6.         "ROOT_ID" VARCHAR2(40),
   
7.         "DEPART_ID" NUMBER(6,0),
   
8.         "CASE_KIND_ID" NUMBER(6,0),
   
9.         "CASE_SRC_ID" NUMBER(6,0),
   
10.         "PARENT_COURT" VARCHAR2(80),
    
11.         "PARENT_CASE_CODE" VARCHAR2(40),
    
12.         "REG_DATE" DATE,
    
13.         "EXEC_INCEPT_DATE" DATE,
    
14.         "CAUSE_ID" NUMBER(9,0),
    
15.         "GIST_ID" NUMBER(6,0),
    
16.         "GIST_UNIT" VARCHAR2(80),
    
17.         "GIST_CID" VARCHAR2(200),
    
18.         "GIST_CONTENT" CLOB,
    
19.         "GIST_DATE" DATE,
    
20.         "GIST_TERM" VARCHAR2(80),
    
21.         "FOREIGN_FACTOR_ID" NUMBER(6,0),
    
22.         "KIND_CAUSE_ID" NUMBER(10,0),
    
23.         "APPLY_MONEY" NUMBER(16,2) DEFAULT 0.00,
    
24.         "APPLY_RES" CLOB,
    
25.         "APPLY_ACTION" CLOB,
    
26.         "APPLY_RIGHTS" CLOB,
    
27.         "WILL_EXPENSE" NUMBER(16,2) DEFAULT 0.00 NOT NULL ENABLE,
    
28.         "CASE_STATE_ID" NUMBER(6,0),
    
29.         "DEFER_BEGIN_DATE" DATE,
    
30.         "DEFER_END_DATE" DATE,
    
31.         "DEFER_DAYS" NUMBER(6,0) DEFAULT 0 NOT NULL ENABLE,
    
32.         "MATURITY_DATE" DATE NOT NULL ENABLE,
    
33.         "DEFER_CAUSE_ID" NUMBER(6,0),
    
34.         "AUTO_EXEC_MONEY" NUMBER(16,2) DEFAULT 0.00 NOT NULL ENABLE,
    
35.         "EXECED_MONEY" NUMBER(16,2) DEFAULT 0.00 NOT NULL ENABLE,
    
36.         "EXECED_RES" CLOB,
    
37.         "EXECED_ACTION" CLOB,
    
38.         "CREATER_ID" NUMBER(6,0) NOT NULL ENABLE,
    
39.         "CREATE_TIME" DATE,
    
40.         "STATE_END_DATE" DATE,
    
41.         "STATE_UPDATE_TIME" DATE,
    
42.         "LAST_UPDATE_TIME" DATE,
    
43.         "REMARK" VARCHAR2(240),
    
44.         "IS_DELETED" NUMBER(1,0) DEFAULT 0,
    
45.         "EXECED_RIGHTS" CLOB,
    
46.         "IS_REPORT" NUMBER(1,0) DEFAULT 1,
    
47.          CONSTRAINT "PK_CASE_INFOR" PRIMARY KEY ("ID")
    
48.   USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
    
49.   STORAGE(INITIAL 67108864 NEXT 67108864 MINEXTENTS 1 MAXEXTENTS 2147483645
    
50.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
    
51.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
    
52.   TABLESPACE "TS_INDEX_CASE"  ENABLE,
    
53.          CONSTRAINT "FK_CASE_SRC_REF_PARA" FOREIGN KEY ("CASE_SRC_ID")
    
54.           REFERENCES "ZHIXING"."TB_PARA_CODE" ("ID") ENABLE,
    
55.          CONSTRAINT "FK_CASE_STATUE_REF_PARA" FOREIGN KEY ("CASE_STATE_ID")
    
56.           REFERENCES "ZHIXING"."TB_PARA_CODE" ("ID") ENABLE,
    
57.          CONSTRAINT "FK_CASEINFO_REF_KINDCAUSE" FOREIGN KEY ("KIND_CAUSE_ID")
    
58.           REFERENCES "ZHIXING"."TB_PARA_CASE_KIND_CAUSE" ("ID") ENABLE,
    
59.          CONSTRAINT "FK_CASE_CREATOR_REF_UNDERTAKER" FOREIGN KEY ("CREATER_ID")
    
60.           REFERENCES "ZHIXING"."TB_ORG_UNDERTAKER" ("ID") ENABLE,
    
61.          CONSTRAINT "FK_CASE_DEFER_REF_CAUSE" FOREIGN KEY ("DEFER_CAUSE_ID")
    
62.           REFERENCES "ZHIXING"."TB_PARA_CAUSE" ("ID") ENABLE,
    
63.          CONSTRAINT "FK_CASE_FOREIGN_REF_PARA" FOREIGN KEY ("FOREIGN_FACTOR_ID")
    
64.           REFERENCES "ZHIXING"."TB_PARA_CODE" ("ID") ENABLE,
    
65.          CONSTRAINT "FK_CASE_GIST_REF_PARA" FOREIGN KEY ("GIST_ID")
    
66.           REFERENCES "ZHIXING"."TB_PARA_CODE" ("ID") ENABLE,
    
67.          CONSTRAINT "FK_CASE_KIND_REF_CASEKIND" FOREIGN KEY ("CASE_KIND_ID")
    
68.           REFERENCES "ZHIXING"."TB_PARA_CASE_KIND" ("ID") ENABLE,
    
69.          CONSTRAINT "FK_CASE_KIND_REF_PARA" FOREIGN KEY ("CASE_KIND_ID")
    
70.           REFERENCES "ZHIXING"."TB_PARA_CODE" ("ID") DISABLE,
    
71.          CONSTRAINT "FK_CASE_REF_CAUSE" FOREIGN KEY ("CAUSE_ID")
    
72.           REFERENCES "ZHIXING"."TB_PARA_CASE_CAUSE" ("ID") ENABLE,
    
73.          CONSTRAINT "FK_CASE_REF_COURT" FOREIGN KEY ("COURT_ID")
    
74.           REFERENCES "ZHIXING"."TB_ORG_COURT" ("ID") ENABLE,
    
75.          CONSTRAINT "FK_CASE_REF_DEPART" FOREIGN KEY ("DEPART_ID")
    
76.           REFERENCES "ZHIXING"."TB_ORG_DEPART" ("ID") ENABLE
    
77.    ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
    
78.   STORAGE(INITIAL 536870912 NEXT 536870912 MINEXTENTS 2
    
79.   PCTINCREASE 0
    
80.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
    
81.   TABLESPACE "TS_CASE_DATA"
    
82. LOB ("GIST_CONTENT") STORE AS BASICFILE (
    
83.   ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
    
84.   NOCACHE LOGGING
    
85.   STORAGE(
    
86.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
    
87. LOB ("APPLY_RES") STORE AS BASICFILE (
    
88.   ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
    
89.   NOCACHE LOGGING
    
90.   STORAGE(
    
91.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
    
92. LOB ("APPLY_ACTION") STORE AS BASICFILE (
    
93.   ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
    
94.   NOCACHE LOGGING
    
95.   STORAGE(
    
96.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
    
97. LOB ("APPLY_RIGHTS") STORE AS BASICFILE (
    
98.   ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
    
99.   NOCACHE LOGGING
    
100.   STORAGE(
     
101.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
     
102. LOB ("EXECED_RES") STORE AS BASICFILE (
     
103.   ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
     
104.   NOCACHE LOGGING
     
105.   STORAGE(
     
106.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
     
107. LOB ("EXECED_ACTION") STORE AS BASICFILE (
     
108.   ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
     
109.   NOCACHE LOGGING
     
110.   STORAGE(
     
111.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
     
112. LOB ("EXECED_RIGHTS") STORE AS BASICFILE (
     
113.   ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
     
114.   NOCACHE LOGGING
     
115.   STORAGE(
     
116.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
     
117.   PARTITION BY RANGE ("REG_DATE") INTERVAL (NUMTOYMINTERVAL(12,'MONTH'))
     
118. (PARTITION "P2006"  VALUES LESS THAN (TO_DATE(' 2007-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) SEGMENT CREATION IMMEDIATE
     
119.   PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
     
120. NOCOMPRESS LOGGING
     
121.   STORAGE(INITIAL 1073741824 NEXT 536870912 MINEXTENTS 1 MAXEXTENTS 2147483645
     
122.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
     
123.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
     
124.   TABLESPACE "TS_CASE_DATA"
     
125. LOB ("GIST_CONTENT") STORE AS BASICFILE (
     
126.   TABLESPACE "TS_CASE_DATA" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
     
127.   NOCACHE LOGGING
     
128.   STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
     
129.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
     
130.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
     
131. LOB ("APPLY_RES") STORE AS BASICFILE (
     
132.   TABLESPACE "TS_CASE_DATA" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
     
133.   NOCACHE LOGGING
     
134.   STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
     
135.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
     
136.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
     
137. LOB ("APPLY_ACTION") STORE AS BASICFILE (
     
138.   TABLESPACE "TS_CASE_DATA" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
     
139.   NOCACHE LOGGING
     
140.   STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
     
141.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
     
142.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
     
143. LOB ("APPLY_RIGHTS") STORE AS BASICFILE (
     
144.   TABLESPACE "TS_CASE_DATA" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
     
145.   NOCACHE LOGGING
     
146.   STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
     
147.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
     
148.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
     
149. LOB ("EXECED_RES") STORE AS BASICFILE (
     
150.   TABLESPACE "TS_CASE_DATA" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
     
151.   NOCACHE LOGGING
     
152.   STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
     
153.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
     
154.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
     
155. LOB ("EXECED_ACTION") STORE AS BASICFILE (
     
156.   TABLESPACE "TS_CASE_DATA" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
     
157.   NOCACHE LOGGING
     
158.   STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
     
159.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
     
160.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT))
     
161. LOB ("EXECED_RIGHTS") STORE AS BASICFILE (
     
162.   TABLESPACE "TS_CASE_DATA" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
     
163.   NOCACHE LOGGING
     
164.   STORAGE(INITIAL 8388608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
     
165.   PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
     
166.   BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) )  ENABLE ROW MOVEMENT

复制代码

由于case_infor_new没建上，所以没有

Maclean Liu(刘相兵

和分区数目有关系  与INSERT无关， 这个表 就在这个表空间建不下！

leonhat

Maclean Liu(刘相兵 发表于 2013-1-30 16:05
action plan 2:

alter system set deferred_segment_creation=false;

按照上面的操作
建表时出现：
ORA-01658: 无法为表空间 TS_CASE_DATA 中的段创建 INITIAL 区

表空间：
TS_CASE_DATA    PERMANENT     153,600.000         152830.500/   153600.000           99.50%

Maclean Liu(刘相兵

action plan 2:

alter system set deferred_segment_creation=false;

之后drop 你的case_infor_new  表 并 重新执行DDL!!!!!!!!!!!!!!

Maclean Liu(刘相兵

把  case_infor 和 case_infor_new的DDL定义用  dbms_metadata.get_ddl 找出来并 贴出来

请用 code模式 贴！！！！！！！！！！！！！！！！！

leonhat

用APPEND试了还是不行

insert /*+ APPEND */ into tb_case_infor_new select * from tb_case_infor

ORA-01658: 无法为表空间 TS_CASE_DATA 中的段创建 INITIAL 区

TS_CASE_DATA    PERMANENT     153,600.000         153564.188/   153600.000           99.98%

leonhat

重现后查询结果：

SQL>

TSNAME               NFRAGS     MXFRAG     TOTSIZ     AVASIZ PCT
---------------- ---------- ---------- ---------- ---------- ---
SYSAUX                   16        304       1024    307.625 69.
                                                             958
                                                             496
                                                             093
                                                              75

UNDOTBS1                 23       1113       2948    2585.75 12.
                                                             287
                                                             991
                                                             858
                                                             887
                                                               4

TS_INDEX_STAT             2       2640       5120       3791 25.
                                                             957
                                                             031
                                                              25

TS_INDEX_EXEC             3       2244       5120  3395.8125 33.
                                                             675
                                                             537
                                                             109
                                                             375

USERS                     1          4          5          4  20
TS_EXEC_SIFA              2  2641.6875      10240  4944.6875 51.
                                                             712
                                                             036
                                                             132
                                                             812
                                                               5

TS_INDEX_CASE             4       3627      20480      11472 43.
                                                             984
                                                             375

SYSTEM                    2        238       1024   238.0625 76.
                                                             751
                                                             708
                                                             984
                                                             375

TS_EXEC_OTHER             3       2303      10240   4595.875 55.
                                                             118
                                                             408
                                                             203
                                                             125

TS_EXEC_FORCE             2  3470.9375      10240  5773.9375 43.
                                                             613
                                                             891
                                                             601
                                                             562
                                                               5

TS_STATICS                3       3968      10240      10239 0.0
                                                             097
                                                             656
                                                              25

TS_ZHIXING_DATA           3       3676      10240  5979.9375 41.
                                                             602
                                                             172
                                                             851
                                                             562
                                                               5

TS_CASE_DATA              7         25     153600    36.3125 99.
                                                             976
                                                             359
                                                             049
                                                             479
                                                               2

TS_EXEC_LOG               7       3968      20480  12733.625 37.
                                                             824
                                                             096
                                                             679
                                                             687
                                                               5

TS_INDEX_LOG              3       3968      10240       7654 25.
                                                             253
                                                             906
                                                              25

TS_PARA                   2       1533       2048   1533.375 25.
                                                             128
                                                             173
                                                             828
                                                             125


16 rows selected

  COUNT(*) SUM(BYTES)
---------- ----------
         0

SQL>

leonhat

1、没试过
insert /*+ APPEND */ into case_infor_new select * from case_infor;

2、正在重现问题。。

Maclean Liu(刘相兵

1. insert /*+ APPEND */ into case_infor_new select * from case_infor;

试过吗？

2. 暴涨， 你确定是哪一个segment占用的？

重现该问题 并查以下信息

1. 
   
2. 
   
3. ttitle -
   
4.    center  'Database Freespace Summary'  skip 2
   
5. 
   
6. comp sum of nfrags totsiz avasiz on report
   
7. break on report
   
8. 
   
9. set pages 999
   
10. col tsname  format     a16 justify c heading 'Tablespace'
    
11. col nfrags  format 999,990 justify c heading 'Free|Frags'
    
12. col mxfrag  format 999,999 justify c heading 'Largest|Frag (MB)'
    
13. col totsiz  format 999,999 justify c heading 'Total|(MB)'
    
14. col avasiz  format 999,999 justify c heading 'Available|(MB)'
    
15. col pctusd  format     990 justify c heading 'Pct|Used'
    
16. 
    
17. select total.TABLESPACE_NAME tsname,
    
18.        D nfrags,
    
19.        C/1024/1024 mxfrag,
    
20.        A/1024/1024 totsiz,
    
21.        B/1024/1024 avasiz,
    
22.        (1-nvl(B,0)/A)*100 pctusd
    
23. from
    
24.     (select sum(bytes) A,
    
25.             tablespace_name
    
26.             from dba_data_files
    
27.             group by tablespace_name) TOTAL,
    
28.     (select sum(bytes) B,
    
29.             max(bytes) C,
    
30.             count(bytes) D,
    
31.             tablespace_name
    
32.             from dba_free_space
    
33.             group by tablespace_name) FREE
    
34. where
    
35.       total.TABLESPACE_NAME=free.TABLESPACE_NAME(+)
    
36. /
    
37. 
    
38. SELECT COUNT(*),sum(bytes) FROM DBA_EXTENTS WHERE SEGMENT_TYPE='TEMPORARY';

复制代码