存储过程执行报错:
ORA-1652: unable to extend temp segment by 64 in tablespace TBS_REPORT
检查表空间使用率,发现表空间空闲很多啊
TABLESPACE_NAME MEGS_TOTAL MEGS_ALLOC MEGS_FREE MEGS_USED PCT_FREE PCT_USED
------------------------------ ---------- ---------- ---------- ---------- ---------- ----------
TEMP 0 319450.84 315923.84 3527 98.9 1.1
TBS_STAT 106470 106470 74532 31938 70 30
TBS_REPORT 466838 466838 76310 390528 16.35 83.65
执行了几遍,存储过程还是报错,观察了一下,在第一步创建临时表的时候报错退出了,将语句拿出来单独执行,观察情况:
SQL> CREATE TABLE REPORT.REPORT_D_GROUP_DEV_TEMP1 tablespace tbs_report AS
2 SELECT /*+PARALLEL(A,8),(B,8)*/A.*, B.CLUSTER_FLAG CALLING_TYPE
3 FROM (SELECT DISTINCT T.USER_NO,
4 T.CUST_NO,
5 T.AREA_NO,
6 T.CITY_NO,
7 T.NET_FLAG,
8 T.IS_NEW_INNET,
9 T.IS_INNET,
10 T.IS_OUTNET
11 FROM MID.MID_D_USER_FLAG T
12 WHERE T.ACCT_DAY = '20110531'
13 AND T.OPEN_MODE <> '1'
14 AND T.NET_FLAG IS NOT NULL) A,
15 (SELECT USER_NO, CALLING_TYPE, CLUSTER_FLAG
16 FROM MID.MID_D_USER_CLUST T
17 WHERE T.ACCT_DAY = '20110531') B
18 WHERE A.USER_NO = B.USER_NO(+);
FROM MID.MID_D_USER_FLAG T
*
ERROR at line 11:
ORA-01652: unable to extend temp segment by 64 in tablespace TBS_REPORT
换一个表空间执行,表创建成功:
SQL> CREATE TABLE REPORT.REPORT_D_GROUP_DEV_TEMP1 tablespace tbs_stat AS
2 SELECT /*+PARALLEL(A,8),(B,8)*/A.*, B.CLUSTER_FLAG CALLING_TYPE
3 FROM (SELECT DISTINCT T.USER_NO,
4 T.CUST_NO,
5 T.AREA_NO,
6 T.CITY_NO,
7 T.NET_FLAG,
8 T.IS_NEW_INNET,
9 T.IS_INNET,
10 T.IS_OUTNET
11 FROM MID.MID_D_USER_FLAG T
12 WHERE T.ACCT_DAY = '20110531'
13 AND T.OPEN_MODE <> '1'
14 AND T.NET_FLAG IS NOT NULL) A,
15 (SELECT USER_NO, CALLING_TYPE, CLUSTER_FLAG
16 FROM MID.MID_D_USER_CLUST T
17 WHERE T.ACCT_DAY = '20110531') B
18 WHERE A.USER_NO = B.USER_NO(+);
Table created.
这个表空间是uniform size的,不存在碎片问题:
SQL> select tablespace_name,extent_management,allocation_type
from dba_tablespaces where tablespace_name='TBS_REPORT';
TABLESPACE_NAME EXTENT_MANAGEMENT ALLOCATION_TYPE
------------------------------ ----------------- ---------------
TBS_REPORT LOCAL UNIFORM
临时表空间300G,足够大,在语句执行的过程中,观察v$tempseg_usage,发现使用率超过5%。