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 Go to next message
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 #659416 is a reply to message #659413] Mon, 16 January 2017 04:37 Go to previous messageGo to next message
Roachcoach
Messages: 1571
Registered: May 2010
Location: UK
Senior Member
You'll need to post the execution plans tbh.
Re: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP2 [message #659417 is a reply to message #659413] Mon, 16 January 2017 04:44 Go to previous messageGo to next message
John Watson
Messages: 7618
Registered: January 2010
Location: Global Village
Senior Member
Outer joins will often have an awful effect on execution plans, because they force the join order. Generate plans for the statements, and you ,may see this.
Do you need the outer join? Consideriing that you have the filter on b.active?
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 Go to previous messageGo to next message
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 #659421 is a reply to message #659420] Mon, 16 January 2017 06:29 Go to previous messageGo to next message
Roachcoach
Messages: 1571
Registered: May 2010
Location: UK
Senior Member
Are those row counts accurate?
Re: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP2 [message #659422 is a reply to message #659421] Mon, 16 January 2017 06:34 Go to previous messageGo to next message
arfan_alam
Messages: 3
Registered: May 2015
Junior Member
SQL>SELECT COUNT(*) FROM BANK
  2  /

 COUNT(*)
---------
        8

SQL>

Sorry That was typo.
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 Go to previous messageGo to next message
John Watson
Messages: 7618
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 Go to previous messageGo to next message
cookiemonster
Messages: 13282
Registered: September 2008
Location: Rainy Manchester
Senior Member
John Watson wrote on Mon, 16 January 2017 14:08
We'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.
Re: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP2 [message #659425 is a reply to message #659424] Mon, 16 January 2017 08:59 Go to previous messageGo to next message
Bill B
Messages: 1801
Registered: December 2004
Senior Member
also an outer join returns all rows and a normal join would only join the specific 8 rows. Try the following and paste what you get.

select bankcode,count(*)
from SALARY
group by bankcode;

And then compare them to what is in the BANK table
Re: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP2 [message #659426 is a reply to message #659425] Mon, 16 January 2017 09:14 Go to previous message
Roachcoach
Messages: 1571
Registered: May 2010
Location: UK
Senior Member
Whilst all the above is valuable I'm very suspicious that those rowcounts are going to blow 10gb of temp. Possible but it'd take some doing.
Previous Topic: AUDSID temporary not found
Next Topic: Help with REGEXP_SUBSTR
Goto Forum:
  


Current Time: Thu Oct 18 04:30:40 CDT 2018