ORA-01652，temp表空间不足的相关问题

[img]http://dl2.iteye.com/upload/attachment/0098/2086/71c04a2c-4f71-3ba8-b920-95bfd5ee36b5.jpg[/img]
原因分析

1，登录数据库，查看主机日志，报错内容为ORA-01652，temp表空间不足

ORA-01652: unable to extend temp segment by 128 in tablespace TEMP01

2，让业务部门重新执行相关脚本，发现占用temp表空间的具体语句如下，目前temp表空间96GB，大约1个小时会被该sql使用满，sql异常退出

Sql具体如下

INSERT INTO www.WWW_BILL_DTL_TEMP_0101(ACCT_ID,SERV_ID,FEE,BRAND,
PHONE_ID,USER_TYPE) SELECT ACCT_ID, SERV_ID, sum(FEE) FEE,BRAND
,PHONE_ID,USER_TYPE FROM ( SELECT ACCT_ID, SERV_ID, SUM(FEE) F
EE,BRAND,PHONE_ID,USER_TYPE FROM (select a.acct_id,e.serv_id,s
um(b.unpay_fee) FEE,a.brand,a.phone_id,a.user_type from www.ACC_B
ILL_010120121010 A , www.WWW_BILL_DTL_010120121010 B , www.OWE_MO
NITOR_QUEUE_ACTION E where a.bill_id=b.bill_id and A.ACCT_ID=E.
ACCT_ID and a.brand in (:"SYS_B_00",:"SYS_B_01",:"SYS_B_02",:"SY
S_B_03",:"SYS_B_04",:"SYS_B_05") and b.fee_item_id>:"SYS_B_06"
group by a.acct_id,e.serv_id,a.brand,a.phone_id,a.user_type ) g
roup by ACCT_ID, SERV_ID,BRAND,PHONE_ID,USER_TYPE UNION ALL SE
LECT ACCT_ID, SERV_ID, -:"SYS_B_07"*SUM(FEE) FEE,BRAND,PHONE_ID,
USER_TYPE FROM (select a.acct_id,e.serv_id,sum(b.unpay_fee) FE
E,a.brand,a.phone_id,a.user_type from www.WWW_BILL_010120121005 A
, www.WWW_BILL_DTL_010120121005 B , www.WWW_MONITOR_QUEUE_ACTION
E where a.bill_id=b.bill_id and A.ACCT_ID=E.ACCT_ID and a.bran
d in (:"SYS_B_08",:"SYS_B_09",:"SYS_B_10",:"SYS_B_11",:"SYS_B_12
",:"SYS_B_13") and b.fee_item_id>:"SYS_B_14" group by a.acct_id
,e.serv_id,a.brand,a.phone_id,a.user_type ) group by ACCT_ID, S
ERV_ID,BRAND,PHONE_ID,USER_TYPE ) GROUP BY ACCT_ID, SERV_ID,BRAN
D,PHONE_ID,USER_TYPE
执行计划如下

Plan hash value: 3236377944

--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------------
| 0 | INSERT STATEMENT | | | | 19281 (100)| |
| 1 | HASH GROUP BY | | 2 | 184 | 19281 (2)| 00:03:52 |
| 2 | VIEW | | 2 | 184 | 19280 (2)| 00:03:52 |
| 3 | UNION-ALL | | | | | |
| 4 | SORT GROUP BY | | 1 | 92 | 19271 (2)| 00:03:52 |
| 5 | VIEW | | 1 | 92 | 19271 (2)| 00:03:52 |
| 6 | SORT GROUP BY | | 1 | 144 | 19271 (2)| 00:03:52 |
|* 7 | HASH JOIN | | 1 | 144 | 19270 (2)| 00:03:52 |
| 8 | MERGE JOIN CARTESIAN | | 1 | 65 | 8717 (2)| 00:01:45 |
| 9 | TABLE ACCESS FULL | WWW_MONITOR_QUEUE_ACTION | 1 | 26 | 2 (0)| 00:00:01 |
| 10 | BUFFER SORT | | 257K| 9810K| 8715 (2)| 00:01:45 |
|* 11 | TABLE ACCESS FULL | WWW_BILL_DTL_010120121010 | 257K| 9810K| 8715 (2)| 00:01:45 |
|* 12 | TABLE ACCESS FULL | WWW_BILL_010120121010 | 16755 | 1292K| 10552 (1)| 00:02:07 |
| 13 | SORT GROUP BY | | 1 | 53 | 9 (12)| 00:00:01 |
| 14 | VIEW | | 1 | 53 | 9 (12)| 00:00:01 |
| 15 | SORT GROUP BY | | 1 | 79 | 9 (12)| 00:00:01 |
| 16 | TABLE ACCESS BY INDEX ROWID | WWW_BILL_DTL_010120121005 | 1 | 18 | 3 (0)| 00:00:01 |
| 17 | NESTED LOOPS | | 1 | 79 | 8 (0)| 00:00:01 |
| 18 | NESTED LOOPS | | 1 | 61 | 5 (0)| 00:00:01 |
| 19 | TABLE ACCESS FULL | WWW_MONITOR_QUEUE_ACTION | 1 | 26 | 2 (0)| 00:00:01 |
|* 20 | TABLE ACCESS BY INDEX ROWID| WWW_BILL_010120121005 | 1 | 35 | 3 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | ITDX_ACCT_ID_10120121005 | 1 | | 2 (0)| 00:00:01 |
|* 22 | INDEX RANGE SCAN | TPK_BILL_DTL_ID_10120121005 | 1 | | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------------