Recursive CTE throws temp tablespace is empty error
That error does not mean the temp tablespace got full, it means it is empty. Empty as no tempfile in it.

CREATE TABLE EMP (
    EMPID NUMBER,
    NAME VARCHAR2(20),
    MGRID NUMBER,
    DEPT VARCHAR2(5)
);

INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(1, 'GPM', NULL, 'IT');
INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(2, 'SPM', 1, 'IT');
INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(3, 'PM', 2, 'IT');
INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(4, 'Dev1', 3, 'IT');
INSERT INTO EMP (EMPID, NAME, MGRID, DEPT) VALUES(5, 'Dev2', 4, 'IT');


Query:

SQL&gt; WITH tmp (rnum,dept,id) AS
    (SELECT rownum rnum, dept, to_char(empid) AS id FROM (SELECT dept, empid FROM emp WHERE dept='IT' ORDER BY empid)),
cte (rnum,dept,id) AS
    (SELECT rnum, dept, id FROM tmp WHERE rnum=1
        UNION ALL
    SELECT cte.rnum+1, tmp.dept, cte.id||'/'||tmp.id FROM cte JOIN tmp on tmp.rnum=cte.rnum+1)
SELECT * FROM cte cte1
  8  WHERE rnum=(SELECT max(rnum) FROM cte cte2 WHERE cte1.dept=cte2.dept);

      RNUM DEPT  ID
---------- ----- --------------------
         5 IT    1/2/3/4/5


Now if I drop the tempfile:

SQL&gt; select file_id from dba_temp_files;

   FILE_ID
----------
         1

SQL&gt; alter database tempfile 1 drop;

Database altered.

SQL&gt; select file_id from dba_temp_files;

no rows selected

SQL&gt; WITH tmp (rnum,dept,id) AS
    (SELECT rownum rnum, dept, to_char(empid) AS id FROM (SELECT dept, empid FROM emp WHERE dept='IT' ORDER BY empid)),
cte (rnum,dept,id) AS
    (SELECT rnum, dept, id FROM tmp WHERE rnum=1
        UNION ALL
    SELECT cte.rnum+1, tmp.dept, cte.id||'/'||tmp.id FROM cte JOIN tmp on tmp.rnum=cte.rnum+1)
SELECT * FROM cte cte1
  8  WHERE rnum=(SELECT max(rnum) FROM cte cte2 WHERE cte1.dept=cte2.dept);
WITH tmp (rnum,dept,id) AS
*
ERROR at line 1:
ORA-25153: Temporary Tablespace is Empty


Check DBA_TEMP_FILES and fix your temporary tablespace.