Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01652: unable to extend temp segment by 128 in tablespace TEMP2 (Enterprise Edition 10.2.0.4.0 - 64bit, Windows Server 2008)
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP2 [message #659413] |
Mon, 16 January 2017 04:09 |
|
arfan_alam
Messages: 3 Registered: May 2015
|
Junior Member |
|
|
SELECT ALL
S.EMPID,
S.EMPCOD,
S.SAL_DATE,
S.SALARY,
S.EOBI,
S.OTHER_DEDUCT,
S.P_FUND,
0 TRANSPORT,
S.SOCIAL,
S.DAY_WRK,
S.LOAN,
S.TAX,
S.LUNCH,
B.BANKCODE,
B.BANKNAME ,
SI.BANKACCOUNT#,
S.I_O_U,
S.SBU_CODE,
E.EMPNAM,
SBU.SBU_DESC,
S.BANKSALARY,
S.FIX_ALLOW,
(NVL(S.SALARY,0) - NVL(S.BANKSALARY,0)) MILLSALARY,
J.DEPCOD,
J.DESCOD,
J.MACCOD,
M.MACNAM,
DP.DEPNAM,
DS.DESNAM,
0 MOBILE_DED,
0 LUNCH_dED,
( S.SALARY / ( SUBSTR( LAST_DAY(S.SAL_dATE) ,1,2) ) * DAY_WRK ) EARNED_AMOUNT
FROM
EMP1 E,
SALARYF S,
JOB_INFO J,
DESIGNATION DS,
DEPARTMENT DP,
MACHINE M,
SALARY SI ,
SBU ,
BANK B
WHERE
E.EMPID = S.EMPID
AND S.SAL_DATE >= '01-JAN-10'
AND S.SAL_DATE ='31-DEC-2016'
AND S.VERIFY_DATE IS NOT NULL
AND S.DAY_WRK >0
AND SBU.SBU_CODE = S.SBU_CODE
AND J.EMPID = E.EMPID
AND J.SDATE = (SELECT MAX(SDATE) FROM JOB_INFO WHERE EMPID = E.EMPID AND SDATE <= S.SAL_DATE)
AND M.MACCOD = J.MACCOD
AND DP.DEPCOD = J.DEPCOD
AND DS.DESCOD = J.DESCOD
AND (E.EMPID = SI.EMPID)
AND SI.SDATE = (SELECT MAX(SDATE) FROM SALARY WHERE EMPID = E.EMPID AND SDATE <= S.SAL_DATE)
-- 1
--AND SI.BANKCODE = B.BANKCODE
-- 2
AND SI.BANKCODE = B.BANKCODE(+)
AND E.EMPTYP != 'R'
AND S.SAL_dATE = :MONTH_YEAR
AND NVL(B.ACTIVE,'N') = 'Y'
1 - Without using outer join [ SI.BANKCODE = B.BANKCODE(+) ]
Execution time is 1.46 Sec
2 - But when i Use outer join then it ends up (More than 10 mintus) with error [Bank table have 3 active records only]
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP2
01652. 00000 - "unable to extend temp segment by %s in tablespace %s"
*Cause: Failed to allocate an extent of the required number of blocks for
a temporary segment in the tablespace indicated.
*Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
files to the tablespace indicated.
I have increased table space from 4999 MB to 10420 MB considering amount of data.
Please Advice me
Thanks
|
|
|
|
|
Re: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP2 [message #659420 is a reply to message #659413] |
Mon, 16 January 2017 06:14 |
|
arfan_alam
Messages: 3 Registered: May 2015
|
Junior Member |
|
|
Sir i no need of b.active, now 7 rows remains in table bank
Execution Plan is
SQL>ED
Wrote file afiedt.buf
1 EXPLAIN PLAN FOR
2 SELECT ALL S.EMPID,
3 S.EMPCOD,
4 S.SAL_DATE,
5 S.SALARY,
6 S.EOBI,
7 S.OTHER_DEDUCT,
8 S.P_FUND,
9 0 TRANSPORT,
10 S.SOCIAL,
11 S.DAY_WRK,
12 S.LOAN,
13 S.TAX,
14 S.LUNCH,
15 B.BANKCODE,
16 B.BANKNAME ,
17 SI.BANKACCOUNT#,
18 S.I_O_U,
19 S.SBU_CODE,
20 E.EMPNAM,
21 SBU.SBU_DESC,
22 S.BANKSALARY,
23 S.FIX_ALLOW,
24 (NVL(S.SALARY,0) - NVL(S.BANKSALARY,0)) MILLSALARY,
25 J.DEPCOD,
26 J.DESCOD,
27 J.MACCOD,
28 M.MACNAM,
29 DP.DEPNAM,
30 DS.DESNAM,
31 0 MOBILE_DED,
32 0 LUNCH_dED,
33 ( S.SALARY / ( SUBSTR( LAST_DAY(S.SAL_dATE) ,1,2) ) * DAY_WRK ) EARNED_AMOUNT
34 FROM EMP1 E,
35 SALARYF S,
36 JOB_INFO J,
37 DESIGNATION DS,
38 DEPARTMENT DP,
39 MACHINE M,
40 SALARY SI ,
41 SBU ,
42 BANK B
43 WHERE E.EMPID = S.EMPID
44 --AND S.SBU_CODE IN (33,22,14,15,11,99)
45 AND S.SAL_DATE >= '01-JAN-10'
46 --AND S.SAL_DATE =:MONTH_YEAR
47 AND S.VERIFY_DATE IS NOT NULL
48 AND S.DAY_WRK >0
49 AND SBU.SBU_CODE = S.SBU_CODE
50 AND J.EMPID = E.EMPID
51 AND J.SDATE = (SELECT MAX(SDATE) FROM JOB_INFO WHERE EMPID = E.EMPID AND SDATE <= S.SAL_DATE)
52 AND M.MACCOD = J.MACCOD
53 AND DP.DEPCOD = J.DEPCOD
54 AND DS.DESCOD = J.DESCOD
55 AND (E.EMPID = SI.EMPID)
56 AND SI.SDATE = (SELECT MAX(SDATE) FROM SALARY WHERE EMPID = E.EMPID AND SDATE <= S.SAL_DATE)
57 -- 1
58 --AND SI.BANKCODE = B.BANKCODE
59 -- 2
60 AND SI.BANKCODE = B.BANKCODE(+)
61 AND E.EMPTYP != 'R'
62 AND S.SAL_dATE = '31-DEC-2016'
63* --AND NVL(B.ACTIVE,'N') = 'Y'
64 /
Explained.
SQL>SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
Plan hash value: 579222647
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 322 | 15181 (1)| 00:03:03 |
|* 1 | FILTER | | | | | |
|* 2 | FILTER | | | | | |
|* 3 | HASH JOIN | | 9151 | 2877K| 2088 (2)| 00:00:26 |
|* 4 | HASH JOIN | | 3704 | 1110K| 2018 (2)| 00:00:25 |
| 5 | INDEX FAST FULL SCAN | DESNAM_GRAT_LEVEL_UQ | 678 | 14238 | 3 (0)| 00:00:01 |
|* 6 | HASH JOIN | | 3606 | 1007K| 2014 (2)| 00:00:25 |
| 7 | TABLE ACCESS FULL | MACHINE | 184 | 3312 | 3 (0)| 00:00:01 |
|* 8 | HASH JOIN | | 3606 | 943K| 2011 (2)| 00:00:25 |
| 9 | TABLE ACCESS FULL | DEPARTMENT | 124 | 1984 | 3 (0)| 00:00:01 |
|* 10 | HASH JOIN | | 3606 | 887K| 2007 (2)| 00:00:25 |
|* 11 | HASH JOIN RIGHT OUTER | | 1589 | 349K| 1770 (2)| 00:00:22 |
| 12 | TABLE ACCESS FULL | BANK | 8 | 176 | 3 (0)| 00:00:01 |
|* 13 | HASH JOIN | | 1589 | 315K| 1766 (2)| 00:00:22 |
| 14 | NESTED LOOPS | | 872 | 156K| 1625 (2)| 00:00:20 |
| 15 | NESTED LOOPS | | 849 | 149K| 1625 (2)| 00:00:20 |
| 16 | NESTED LOOPS | | 849 | 145K| 1625 (2)| 00:00:20 |
| 17 | NESTED LOOPS | | 849 | 142K| 1625 (2)| 00:00:20 |
| 18 | NESTED LOOPS OUTER | | 849 | 139K| 1625 (2)| 00:00:20 |
|* 19 | HASH JOIN | | 849 | 136K| 1625 (2)| 00:00:20 |
| 20 | NESTED LOOPS | | 466 | 68968 | 1484 (2)| 00:00:18 |
| 21 | NESTED LOOPS | | 466 | 67570 | 1484 (2)| 00:00:18 |
|* 22 | HASH JOIN | | 466 | 66172 | 1484 (2)| 00:00:18 |
| 23 | NESTED LOOPS | | 205 | 22345 | 1246 (2)| 00:00:15 |
|* 24 | HASH JOIN | | 205 | 16400 | 1040 (2)| 00:00:13 |
| 25 | TABLE ACCESS FULL | SBU | 16 | 208 | 3 (0)| 00:00:01 |
|* 26 | TABLE ACCESS FULL | SALARYF | 205 | 13735 | 1037 (2)| 00:00:13 |
|* 27 | TABLE ACCESS BY INDEX ROWID| EMP_MST | 1 | 29 | 1 (0)| 00:00:01 |
|* 28 | INDEX UNIQUE SCAN | SYS_C008671 | 1 | | 0 (0)| 00:00:01 |
| 29 | TABLE ACCESS FULL | JOB_INFO | 81351 | 2621K| 237 (2)| 00:00:03 |
|* 30 | INDEX UNIQUE SCAN | SYS_C009007 | 1 | 3 | 0 (0)| 00:00:01 |
|* 31 | INDEX UNIQUE SCAN | SYS_C009007 | 1 | 3 | 0 (0)| 00:00:01 |
| 32 | TABLE ACCESS FULL | SALARY | 65318 | 1084K| 140 (2)| 00:00:02 |
|* 33 | INDEX UNIQUE SCAN | SYS_C008533 | 1 | 3 | 0 (0)| 00:00:01 |
|* 34 | INDEX UNIQUE SCAN | SYS_C008601 | 1 | 4 | 0 (0)| 00:00:01 |
|* 35 | INDEX UNIQUE SCAN | DISTRICT_PK | 1 | 4 | 0 (0)| 00:00:01 |
|* 36 | INDEX UNIQUE SCAN | SYS_C008842 | 1 | 4 | 0 (0)| 00:00:01 |
|* 37 | INDEX UNIQUE SCAN | PK_DES | 1 | 4 | 0 (0)| 00:00:01 |
| 38 | TABLE ACCESS FULL | SALARY | 65318 | 1211K| 140 (2)| 00:00:02 |
| 39 | TABLE ACCESS FULL | JOB_INFO | 81351 | 2144K| 237 (2)| 00:00:03 |
| 40 | INDEX FAST FULL SCAN | PK_COMP_JOB_STATUS1 | 88561 | 1297K| 69 (2)| 00:00:01 |
| 41 | SORT AGGREGATE | | 1 | 15 | | |
| 42 | FIRST ROW | | 1 | 15 | 2 (0)| 00:00:01 |
|* 43 | INDEX RANGE SCAN (MIN/MAX) | PK_COMP_JOB_INFO | 1 | 15 | 2 (0)| 00:00:01 |
| 44 | SORT AGGREGATE | | 1 | 15 | | |
| 45 | FIRST ROW | | 1 | 15 | 2 (0)| 00:00:01 |
|* 46 | INDEX RANGE SCAN (MIN/MAX) | SAL_INFO | 1 | 15 | 2 (0)| 00:00:01 |
| 47 | SORT AGGREGATE | | 1 | 15 | | |
| 48 | FIRST ROW | | 3 | 45 | 2 (0)| 00:00:01 |
|* 49 | INDEX RANGE SCAN (MIN/MAX) | PK_COMP_JOB_STATUS1 | 3 | 45 | 2 (0)| 00:00:01 |
| 50 | SORT AGGREGATE | | 1 | 15 | | |
| 51 | FIRST ROW | | 2 | 30 | 2 (0)| 00:00:01 |
|* 52 | INDEX RANGE SCAN (MIN/MAX) | PK_COMP_JOB_INFO | 2 | 30 | 2 (0)| 00:00:01 |
| 53 | SORT AGGREGATE | | 1 | 15 | | |
| 54 | FIRST ROW | | 2 | 30 | 2 (0)| 00:00:01 |
|* 55 | INDEX RANGE SCAN (MIN/MAX) | SAL_INFO | 2 | 30 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("J"."SDATE"= (SELECT MAX("SDATE") FROM "JOB_INFO" "JOB_INFO" WHERE "SDATE"<=:B1 AND "EMPID"=:B2)
AND "SI"."SDATE"= (SELECT MAX("SDATE") FROM "SALARY" "SALARY" WHERE "SDATE"<=:B3 AND "EMPID"=:B4) AND
"JS"."SDATE"= (SELECT MAX("J"."SDATE") FROM TTIME."JOB_STATUS" "J" WHERE "J"."EMPID"=:B5) AND "JI"."SDATE"=
(SELECT MAX("J"."SDATE") FROM TTIME."JOB_INFO" "J" WHERE "J"."EMPID"=:B6) AND "S"."SDATE"= (SELECT
MAX("A"."SDATE") FROM TTIME."SALARY" "A" WHERE "A"."EMPID"=:B7))
2 - filter('01-JAN-10'<=TO_DATE(' 2016-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
3 - access("JS"."EMPID"="E"."EMPID")
4 - access("DS"."DESCOD"="J"."DESCOD")
6 - access("M"."MACCOD"="J"."MACCOD")
8 - access("DP"."DEPCOD"="J"."DEPCOD")
10 - access("J"."EMPID"="E"."EMPID")
11 - access("SI"."BANKCODE"="B"."BANKCODE"(+))
13 - access("E"."EMPID"="SI"."EMPID")
19 - access("S"."EMPID"="E"."EMPID")
22 - access("JI"."EMPID"="E"."EMPID")
24 - access("SBU"."SBU_CODE"="S"."SBU_CODE")
26 - filter("S"."SAL_DATE"=TO_DATE(' 2016-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "S"."VERIFY_DATE"
IS NOT NULL AND "S"."SAL_DATE">='01-JAN-10' AND "S"."DAY_WRK">0)
27 - filter("E"."EMPTYP"<>'R')
28 - access("E"."EMPID"="S"."EMPID")
30 - access("JI"."SBU"="SBU"."SBU_CODE")
31 - access("JI"."FSBU"="SBU2"."SBU_CODE")
33 - access("S"."BANKCODE"="B"."BANKCODE"(+))
34 - access("JI"."DEPCOD"="DEP"."DEPCOD")
35 - access("E"."DISCOD"="DIS"."DISCOD")
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------------
36 - access("JI"."MACCOD"="MAC"."MACCOD")
37 - access("JI"."DESCOD"="DES"."DESCOD")
43 - access("EMPID"=:B1 AND "SDATE"<=:B2)
46 - access("EMPID"=:B1 AND "SDATE"<=:B2)
49 - access("J"."EMPID"=:B1)
52 - access("J"."EMPID"=:B1)
|
|
|
|
|
Re: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP2 [message #659423 is a reply to message #659420] |
Mon, 16 January 2017 08:08 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
I've just noticed these predicates:
AND S.SAL_DATE >= '01-JAN-10'
...
AND S.SAL_dATE = '31-DEC-2016'
that is silly. Which do you want? We've already established that you do not need both an outer join to BANK and a filter on BANK. Again, which do you want?
Given those mistakes, there may well be others. Perhaps the query needs to be written again. From the beginning. Better use ANSI join syntax, it is less prone to error.
|
|
|
Re: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP2 [message #659424 is a reply to message #659423] |
Mon, 16 January 2017 08:19 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
John Watson wrote on Mon, 16 January 2017 14:08We've already established that you do not need both an outer join to BANK and a filter on BANK.
Depends on whether that Y is hard-coded in the original query.
If it is then all rows that don't join to bank will thrown out. If that Y can sometimes be an N then you can get rows that don't have a match in bank.
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 16:55:56 CDT 2024
|