Pentaho Spoon transformation throws: ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

Question

Hey I'm doing a simple transformation with Pentaho Spoon, the transformation has two steps:

1. Input table: I use a SQL to extract the data from an Oracle 11g database.

2. Excel Output: I write the data from the database in an excel file.

The problem is when I try to run this 2 steps transformation, nothing is happening. I tried to visualize the first 200 entries from the first step but throws the following error:

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

Is this the reason why the transformation doesn't work, how I could fix it, having into account that I don't have admin privileges into the database to increase the TEMP tablespace size.

These are the Loggin messages:

The query used in the input table method is as follows:

SELECT
ptn.ptnID,
ptn.ptnNAME,
ptn.ptnSURNAME,
ptn.SSN,
ptn.EXTERNALID,
ptn.NTS,
ptn.GENDER,
ptn.DOB,
ptn.ADDRESS,
ptn.CITY,
ptn.STATE,
ptn.COUNTRY,
ptn.POSTALCODE,
ptn.ACTIVE,
ptn.NACIONALITY,
ptn.COUNTRYOFBIRTH,
ptn.EXITUS,
ptn.ptnFIRSTSURNAME,
ptn.ptnSECONDSURNAME,
ptn.DEATHDATE,
appl.applID,
srv.srvDESCRIPTION,
center.CENTERDESCRIPTION,
appl.applLABEL,
appl.applDATE,
appl.COMPLETEDATE,
container.COMMENTS,
sample.SAMPLEDESCRIPTION,
test.TESTDESCRIPTION,
test.UNITSCS,
rslhst.RESULTSTATUS,
rslhst.ISAUTOMATIC,
pathology.PATHOLOGYDESCRIPTION,
FROM USRGR.APPLICATION appl
LEFT OUTER JOIN USRGR.PTN ptn on ptn.ptnid = appl.ptnid
LEFT OUTER JOIN USRGR.APPLICATIONTEST appltest on appltest.ptnid = appl.ptnid
LEFT OUTER JOIN USRGR.TEST test on test.testid = appltest.testid
LEFT OUTER JOIN USRGR.RSLHST rslhst on (rslhst.applid = appltest.applid) AND (rslhst.testid = appltest.testid)
LEFT OUTER JOIN USRGR.CONTAINER container on container.containerid = rslhst.containerid
LEFT OUTER JOIN USRGR.DOC doc on doc.docid = appl.docid
LEFT OUTER JOIN USRGR.SRV srv on srv.srvid = appl.srvid
LEFT OUTER JOIN USRGR.CENTER center on center.centerid = srv.centerid
LEFT OUTER JOIN USRGR.SAMPLE sample on  sample.sampleid = container.sampleid
LEFT OUTER JOIN USRGR.CLTRISOL  cltrisol on  cltrisol.applid = rslhst.applid
LEFT OUTER JOIN USRGR.ISL isl on isl.islid = cltrisol.islid
LEFT OUTER JOIN USRGR.PATHOLOGY on pathology.pathologyid = rslhst.pathology
LEFT OUTER JOIN USRGR.COD cod on cod.codsfamilyid = rslhst.codsfamilyid AND rslhst.shortdescription1 = cod.shortdescription
LEFT OUTER JOIN USRGR.INTERVALS intervals on intervals.testid = test.testid AND intervals.methodid = rslhst.methodid
WHERE
    appl.orderdate BETWEEN '01/01/19' AND '02/01/19' AND
    rslhst.isfinal = 'Y' AND
    appl.iscomplete = 'Y' AND
    ((to_number(to_char(appl.ORDERDATE, 'YYYY')) - to_number(to_char(ptn.DOB, 'YYYY'))) * 12 * 12 >= intervals.from) AND
    ((to_number(to_char(appl.ORDERDATE, 'YYYY')) - to_number(to_char(ptn.DOB, 'YYYY'))) * 12 * 12 < intervals.to) AND
    test.testid in (
    5, 5261,
    435, 438,
    5014, 3986, 3987,
    4293, 6017, 2295, 2296, 2298,
    196, 199, 218, 219, 1626, 3861, 5275, 5276
    );

Explain Plan output:

"PLAN_TABLE_OUTPUT"

"Plan hash value: 3813514414"

" "

"-------------------------------------------------------------------------------"

"| Id  | Operation     | Name                 | Rows  | Bytes |TempSpc| Cost  | "

"-------------------------------------------------------------------------------"

"|   0 | SELECT STATEMENT  |                  |   319K|   497M|       |  1508K|"

"|*  1 |  FILTER           |                  |       |       |       |       |"

"|*  2 |   HASH JOIN OUTER |                  |   319K|   497M|   494M|  1508K|"

"|   3 |    VIEW           |                  |   319K|   490M|       |  1500K|"

"|*  4 |     FILTER        |                  |       |       |       |       |"

"|*  5 |      HASH JOIN OUTER|                |   319K|   172M|   148M|  1500K|"

"|*  6 |       HASH JOIN OUTER|               |   319K|   145M|   140M|  1491K|"

"|*  7 |        HASH JOIN OUTER|              |   319K|   137M|   116M|  1489K|"

"|*  8 |         HASH JOIN OUTER|             |   319K|   112M|   105M|  1487K|"

"|*  9 |          HASH JOIN OUTER|            |   319K|   101M|    88M|  1485K|"

"|* 10 |           HASH JOIN OUTER|           |   284K|    85M|    79M|  1483K|"

"|* 11 |            HASH JOIN OUTER|          |   284K|    76M|    74M|  1482K|"

"|* 12 |             HASH JOIN|               |   284K|    71M|   148M|  1481K|"

"|* 13 |              FILTER|                 |       |       |       |       |"

"|* 14 |               HASH JOIN OUTER|       |   669K|   141M|   121M| 89111 |"

"|  15 |                NESTED LOOPS OUTER|   |   669K|   114M|       | 87134 |"

"|* 16 |                 HASH JOIN OUTER|     |  1616 |   257K|       | 22494 |"

"|* 17 |                  HASH JOIN OUTER|    |  1616 |   211K|       | 22479 |"

"|* 18 |                   HASH JOIN OUTER|   |  1616 |   173K|       | 22474 |"

"|* 19 |       TABLE ACCESS FULL| APPLICATION |  1616 |   123K|       | 22469 |"

"|  20 |       TABLE ACCESS FULL| SERVICE     |   292 |  9344 |       |     3 |"

"|  21 |       TABLE ACCESS FULL| CENTER      |    16 |   384 |       |     3 |"

"|  22 |       TABLE ACCESS FULL| DOC         |  7154 |   202K|       |    12 |"

"|  23 |TABLE ACCESS BY INDEX ROWID| APPLICATIONTEST|414 |  6624 |    |    40 |"

"|* 24 |INDEX RANGE SCAN| RQTEST_PTNID_IDX    |   414 |       |       |     3 |"

"|  25 |TABLE ACCESS FULL       | TEST        |  4292 |   176K|       |    31 |"

"|* 26 |TABLE ACCESS FULL       | RSLHST      |   187M|  7686M|       |  1233K|"

"|  27 |TABLE ACCESS FULL       | PATHOLOGY   |    45 |   720 |       |     3 |"

"|  28 |TABLE ACCESS FULL       | COD         |  4289 |   142K|       |    14 |"

"|  29 |TABLE ACCESS FULL       | CLTRISOL    |   212K|  4144K|       |   505 |"

"|  30 |TABLE ACCESS FULL       | ISL         |  2303 | 80605 |       |     6 |"

"|  31 |TABLE ACCESS FULL       | CONTAINER   |   674 | 53920 |       |     5 |"

"|  32 |TABLE ACCESS FULL       | SAMPLE      |   354 |  9558 |       |     2 |"

"|  33 |TABLE ACCESS FULL       | PTN         |  1886K|   160M|       |  3887 |"

"|  34 |TABLE ACCESS FULL       | MARGINS     |  2232 | 49104 |       |     8 |"

"-------------------------------------------------------------------------------"

Answer 1

The transformation is performing a sort or creating a temporary data set for a join that requires TEMP space. Depending on your transformation and the explain plan of the query, it might not even matter if you limit the number of rows you are returning if the transformation must process all of the source data before applying your filter. It is very likely that the only way to get this to run is to increase the size of the TEMP tablespace. You will need to work with your DBA to confirm this and work out a solution.