oracle 无法在表空间 TEMP 数据库驱动程序错误中将临时段扩展 128
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14578364/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
unable to extend temp segment by 128 in tablespace TEMP Database driver error
提问by sumit vedi
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)
我在 oracle 上执行了一些繁重的查询。但是出现了一个错误（无法在表空间 TEMP 数据库驱动程序错误中将临时段扩展 128）
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.
我已检查表空间，但未找到 TEMP 表空间。下面是我执行的查询。而且我已经增加了 USER 表空间。但仍然面临同样的问题。
SELECT * FROM DBA_DATA_FILES;
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.
请帮助我解决这个问题。
回答by Alex Poole
Temporary tablespaces are managed differently to normal data tablespaces. You can see the operating system files by querying dba_temp_filesinstead. See the documentationfor how to increase the size of a temporary tablespace file, or to add a new fle.
临时表空间的管理方式与普通数据表空间不同。您可以通过查询dba_temp_files来查看操作系统文件。请参阅有关如何增加临时表空间文件的大小或添加新文件的文档。
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 TEMPmay be necessary but shouldn't be done automatically as soon as you see this error.
但是，您最好调查一下为什么您的查询使用了如此多的临时空间并查看是否可以减少它；可能存在一个根本性错误（例如意外的交叉连接），这意味着它使用的温度远远超过应有的温度，并且可能使其运行时间比应有的长得多。查看解释计划以获取问题所在的线索。增加TEMP可能是必要的，但不应在您看到此错误后立即自动完成。
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 userfor 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.
如果您有一个需要比正常情况多得多的临时查询的另一种选择 - 这是一次性的 - 可能是添加一个新的临时表空间，在操作期间将其分配给用户via alter user，完成后恢复，以及然后删除新表空间。那么额外的磁盘只会被占用很短的时间。对于您的情况可能不切实际或不明智，但为了以防万一而提及。
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.
我应该说真正的临时临时表空间 - 定义为create temporary tablespace- 是这样管理的，条目在dba_temp_files. 在 8i 之前，他们拥有正常的数据文件，并且可能在已升级的数据库中仍然具有该配置，并且没有以新格式重新创建临时表空间。显然，您的情况并非如此，因为您的未出现在dba_data_files.
