0

I executed some heavy query on oracle. but one error is coming (unable to extend temp segment by 128 in tablespace TEMP Database driver error)

I have checked the table space but TEMP table space is not found. below is the query that i have executed. And i have already increase the USER table space. but still facing same problem.

SELECT * FROM DBA_DATA_FILES;

/home/oracle/oracle/oradata/ORCL/users01.dbf    4   USERS   32212254720 3932160 AVAILABLE   4   YES 34359721984 4194302 160 32211206144 3932032 ONLINE
/home/oracle/oracle/oradata/ORCL/undotbs01.dbf  3   UNDOTBS1    21474836480 2621440 AVAILABLE   3   YES 34359721984 4194302 640 21473787904 2621312 ONLINE
/home/oracle/oracle/oradata/ORCL/sysaux01.dbf   2   SYSAUX  1377828864  168192  AVAILABLE   2   YES 34359721984 4194302 1280    1376780288  168064  ONLINE
/home/oracle/oracle/oradata/ORCL/system01.dbf   1   SYSTEM  1073741824  131072  AVAILABLE   1   YES 34359721984 4194302 1280    1072693248  130944  SYSTEM
/home/oracle/oracle/oradata/ORCL/CSA_BILL_ITEM01.dbf    5   CSA_BILL_ITEM   32212254720 3932160 AVAILABLE   5   YES 32212254720 3932160 12800   32211206144 3932032 ONLINE
/home/oracle/oracle/oradata/ORCL/CSA_BILL_ITEM_INDEX01.dbf  6   CSA_BILL_ITEM_INDEX 2147483648  262144  AVAILABLE   6   YES 2147483648  262144  3200    2146435072  262016  ONLINE
/home/oracle/oracle/oradata/ORCL/CSA_BILL01.dbf 7   CSA_BILL    2147483648  262144  AVAILABLE   7   YES 2147483648  262144  12800   2146435072  262016  ONLINE
/home/oracle/oracle/oradata/ORCL/CSA_BILL_INDEX01.dbf   8   CSA_BILL_INDEX  536870912   65536   AVAILABLE   8   YES 1073741824  131072  3200    535822336   65408   ONLINE
/home/oracle/oracle/oradata/ORCL/META_DATA01.dbf    9   META_DATA   536870912   65536   AVAILABLE   9   YES 1073741824  131072  3200    535822336   65408   ONLINE
/home/oracle/oracle/oradata/ORCL/META_INDEX01.dbf   10  META_INDEX  268435456   32768   AVAILABLE   10  YES 536870912   65536   640 267386880   32640   ONLINE
/home/oracle/oracle/oradata/ORCL/POWERCENTER01.dbf  11  POWERCENTER 2147483648  262144  AVAILABLE   11  YES 2147483648  262144  4096    2146435072  262016  ONLINE
/home/oracle/oracle/oradata/ORCL/CSA_QOTA01.dbf 12  CSA_QOTA_TBSPACE    536870912   65536   AVAILABLE   12  YES 536870912   65536   4096    535822336   65408   ONLINE
/home/oracle/oracle/oradata/ORCL/CSA_QOTA02.dbf 13  CSA_QOTA_TBSPACE    1610612736  196608  AVAILABLE   13  NO  0   0   0   1609564160  196480  ONLINE
/home/oracle/oracle/oradata/ORCL/CSA_QOTA03.dbf 14  CSA_QOTA_TBSPACE    1073741824  131072  AVAILABLE   14  NO  0   0   0   1072693248  130944  ONLINE

Please help me on this issue.

Florin Ghita
  • 17,525
  • 6
  • 57
  • 76
sumit vedi
  • 757
  • 3
  • 11
  • 17
  • 1
    I infer that you're keeping table and index segments in different tablespaces. You may already know this, but alleged performance benefits of this are widely discredited. – David Aldridge Jan 29 '13 at 09:31
  • I hit this error when running a query using the Oracle OCI8 driver, with a query that returns a large result set, but *not* through Oracle's SQL tool, SQL Developer, which uses the thin client. More detail on tuning the temp table here: http://stackoverflow.com/questions/25350703/ora-01652-unable-to-extend-temp-segment-by-128-in-tablespace-system-how-to-ext?rq=1 – michaelok Feb 24 '15 at 18:01

1 Answers1

6

Temporary tablespaces are managed differently to normal data tablespaces. You can see the operating system files by querying dba_temp_files instead. See the documentation for how to increase the size of a temporary tablespace file, or to add a new fle.

However, you might be better off investigating why your query is using so much temporary space and seeing if you can reduce it; there may be a fundamental mistake (like an accidental cross-join) that means it is using far more temp than it should, and probably making it run for much longer than it should. Have a look at the explain plan for clues to what's going wrong. Increasing TEMP may be necessary but shouldn't be done automatically as soon as you see this error.

Another option if you have a single query that needs far more temp than normal - and it's a one-off - might be to add a new temporary tablespace, assign that to the user for the duration of the operation via alter user, revert when done, and then drop the new tablespace. Then the extra disk is only tied up for a short time. Might not be practical or sensible for your situation, but mentioned just in case.

I should say that true temporary temporary tablespaces - defined with create temporary tablespace - are managed like this, with entries in dba_temp_files. Prior to 8i they had normal datafilee, and it's possible to still have that configuration in a database that's been upgraded and not had the temporary tablespaces recreated in the new format. Clearly that isn't the case for you since yours do not appear in dba_data_files.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318