0

Having an issue that I can't seem to find any solutions to online.

[72000][1652] ORA-01652: unable to extend temp segment by 256 in tablespace TEMP Position: 581

One of our users runs a query quarterly without any issue, then today they received he error above on the query. I've never seen it before.

So far, I've tried adding another datafile to the TEMP tablespace, it appears to have more than enough space to not even require extending the existing datafiles (90 GB).

What should I do?

EDIT: Adding the Explain Plan of the query...

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 2297118615

--------------------------------------------------------------------------------
-------------------------------------------

| Id  | Operation                | Name                   | Rows  | Bytes |TempS
pc| Cost (%CPU)| Time     | Inst   |IN-OUT|

--------------------------------------------------------------------------------
-------------------------------------------


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                        |     1 |   128 |
  | 89436   (1)| 00:00:04 |        |      |

|*  1 |  HASH JOIN               |                        |     1 |   128 |
  | 89436   (1)| 00:00:04 |        |      |

|   2 |   MERGE JOIN CARTESIAN   |                        |     1 |    84 |
  | 89200   (1)| 00:00:04 |        |      |

|*  3 |    HASH JOIN             |                        |     1 |    78 |
  | 89196   (1)| 00:00:04 |        |      |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

|   4 |     MERGE JOIN CARTESIAN |                        |     1 |    39 |
  |   810   (1)| 00:00:01 |        |      |

|   5 |      REMOTE              | TERM                   |     1 |    13 |
  |     4   (0)| 00:00:01 | SAM_S~ | R->S |

|   6 |      BUFFER SORT         |                        |  1019K|    25M|
  |   806   (1)| 00:00:01 |        |      |

|   7 |       REMOTE             | SECTION                |  1019K|    25M|

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
  |   806   (1)| 00:00:01 | SAM_S~ | R->S |

|   8 |     VIEW                 |                        |    10M|   388M|
  | 88370   (1)| 00:00:04 |        |      |

|   9 |      HASH UNIQUE         |                        |    10M|   388M|   51
9M| 88370   (1)| 00:00:04 |        |      |

|  10 |       REMOTE             | SECTIONPLACEMENT       |    10M|   388M|
  |   995   (4)| 00:00:01 | SAM_S~ | R->S |


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|  11 |    BUFFER SORT           |                        |  2709 | 16254 |
  | 89200   (1)| 00:00:04 |        |      |

|  12 |     SORT UNIQUE          |                        |  2709 | 16254 |
  |     4   (0)| 00:00:01 |        |      |

|  13 |      MAT_VIEW ACCESS FULL| MV_ASSIGNMENT_SECTIONS |  2709 | 16254 |
  |     4   (0)| 00:00:01 |        |      |

|* 14 |   TABLE ACCESS FULL      | MV_SAM_MAPPING         | 77753 |  3340K|
  |   236   (1)| 00:00:01 |        |      |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
-------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("MSMAP"."SECTIONID"="SEC"."SECTIONID" AND "MSMAP"."SECTIONID"="SEC
TIONID")


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   3 - access("SPLA"."TERMID"="TER"."TERMID" AND "SEC"."SECTIONID"="SPLA"."SECTI
ONID" AND

              "SEC"."TRIALID"="SPLA"."TRIALID")
  14 - filter("MSMAP"."SECTIONID" IS NOT NULL)

Remote SQL Information (identified by operation id):
----------------------------------------------------

   5 - SELECT "TERMID" FROM "SIS_DATA"."TERM" "TER" (accessing 'SAM_SIS_DATA' )


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
   7 - SELECT "SECTIONID","TRIALID" FROM "SIS_DATA"."SECTION" "SEC" (accessing '
SAM_SIS_DATA' )


  10 - SELECT "SECTIONID","TERMID","TRIALID" FROM "SIS_DATA"."SECTIONPLACEMENT"
"SECTIONPLACEMENT" (accessing

        'SAM_SIS_DATA' )


40 rows selected.
10
  • Find out what's using temp, it may be something else than the query getting the error. Jul 29, 2021 at 4:51
  • Get a full explain plan of the query. That should tell you the projected use of the TEMP tablespace and help point you in the right direction: oracle.com/technetwork/database/bi-datawarehousing/…
    – pmdba
    Jul 29, 2021 at 10:23
  • @pmdba I got the explain plan, but I'm not sure how to make sense of it. I edited my question to include the explained plan. Could you take a look?
    – Phillip
    Jul 29, 2021 at 13:28
  • The plan shows minimal TEMP usage (~519M), but I see a couple of other things: (1) I see several remote database access points, so your query could be affected by issues in the remote database as well as the local one. Did you receive any other error messages? (2) I see several ACCESS FULL operations, which implies a potential lack of suitable indexes. (3) I see multiple MERGE JOIN CARTESIAN operations. Not necessarily a problem unless your table statistics are out of date; you might want to consider rebuilding your table stats in both the local and remote DBs.
    – pmdba
    Jul 29, 2021 at 14:48
  • Generally if you fill up your temporary tablespace you need to add more space to that tablespace or tune the SQL statements that are using a lot of temporary space at that time. You may be able to adjust the timing of when things run so that SQL statements that use a lot of temp do not run at the same time. You may be able to create a new temporary tablespace and assign it to the user that is running the problem query to isolate that user from SQL statements from other users that use a lot of temp. Jul 29, 2021 at 15:13

2 Answers 2

0

We use this query to find out who is using too much TEMP.

select /*+ RULE */
  s.sid,
  s.serial#,
  p.spid "OS SID",
  s.sql_hash_value "HASH VALUE",
  s.username "ORA USER",
  s.status,
  s.osuser "OS USER",
  s.machine,
  s.terminal,
  s.type,
  s.program,
  s.logon_time,
  s.last_call_et,
  su.contents, 
  ROUND(((su.blocks*param.value)/1024/1024),2) "MB",
  tablespace,
  s.sql_id
FROM 
  v$session s, v$sort_usage su, v$process p, v$parameter param
WHERE 
  param.name  = 'db_block_size' and
  su.session_addr = s.saddr and
  p.addr = s.paddr and
  ((su.blocks*param.value)/1024/1024) > 100
ORDER BY 
  su.blocks DESC
3
  • It tells me "no rows selected".
    – Phillip
    Jul 29, 2021 at 13:40
  • Adjust the >100 to >10 or >1 and try again
    – sandman
    Jul 29, 2021 at 14:28
  • 1
    You'll need to run this query at the same time you are experiencing the error. v$session and v$process do not contain historical data - only real-time monitoring information. You may need to run this search multiple times during your main query execution to confirm what is going on.
    – pmdba
    Jul 29, 2021 at 14:33
0

Please provide the SQL along with the base tables last stats analyzed.

select owner,table_name,'GOLBAL' partition_name, num_rows, blocks, avg_row_len, last_analyzed from dba_tables where (owner,table_name) in
(select OBJECT_OWNER,OBJECT_NAME from v$sql_plan  where sql_id='&sql_id' and (OBJECT_TYPE='TABLE' or OPERATION='UPDATE'))
union
select table_owner,table_name,Partition_name, num_rows, blocks, avg_row_len, last_analyzed from dba_tab_partitions where (table_owner,table_name) in
(select OBJECT_OWNER,OBJECT_NAME from v$sql_plan  where sql_id='&sql_id' and (OBJECT_TYPE='TABLE' or OPERATION='UPDATE'));
select owner, index_name,'GLOBAL' partitionn_name, clustering_factor, distinct_keys, last_analyzed from dba_indexes where (owner,table_name) in
(select distinct OBJECT_OWNER,OBJECT_NAME from v$sql_plan  where sql_id='&sql_id' and (OBJECT_TYPE='TABLE' or OPERATION='UPDATE'))
union
select owner, i.index_name,partition_name, clustering_factor, distinct_keys, last_analyzed from dba_ind_partitions i,dba_part_indexes p
where p.table_name in (select distinct OBJECT_NAME from v$sql_plan  where sql_id='&sql_id' and (OBJECT_TYPE='TABLE' or OPERATION='UPDATE'))
and i.index_name=p.index_name and i.index_owner=p.owner and owner in (select distinct OBJECT_OWNER from v$sql_plan  where sql_id='&sql_id' and (OBJECT_TYPE='TABLE' or OPERATION='UPDATE'));
select owner, column_name , num_distinct, num_nulls, last_analyzed from dba_tab_cols where (owner,table_name) in
(select OBJECT_OWNER,OBJECT_NAME from v$sql_plan  where sql_id='&sql_id' and (OBJECT_TYPE='TABLE' or OPERATION='UPDATE'));

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.