Client Use times Error: ORA-01652: Unable to extend the temp segment by 128 (in Tablespace zltoolstmp).
View temporary tablespace conditions:
Select tablespace_name,file_name,bytes/1024/1024 mb,autoextensible from dba_temp_files;
Query SQL for temporal tablespace usage:
SELECTA.tablespace_name, A.bytes/1024x768/1024x768Total , (a.bytes-NVL (B.bytes,0))/1024x768/1024x768 Free from(SELECTTablespace_name,SUM(bytes) bytes fromDba_temp_filesGROUP byTablespace_name) A, (SELECTTablespace_name,SUM(bytes_cached) bytes fromV$temp_extent_poolGROUP bytablespace_name) bWHEREA.tablespace_name=B.tablespace_name (+)
The query discovers that the temporary table space is insufficient, and that the zltoolstmp auto-extension is set to No.
The temporary table space is not set to auto-scale, and when the temporary table space is insufficient, the transaction execution will report an error that ora-01652 cannot extend the temporary segment.
The workaround is also simple: 1. Set temporary data file to expand automatically, 2, increase temporary table space. Do the following:
1. Increase the temporary file size:
sql> ALTER DATABASE tempfile ' D:\APP\ADMINISTRATOR\ORADATA\ORCL\ZLTOOLSTMP. DBF ' resize 1500m;
2. Set the temporary data file to auto-expand:
sql> ALTER DATABASE tempfile ' D:\APP\ADMINISTRATOR\ORADATA\ORCL\ZLTOOLSTMP. DBF ' autoextend on next 5m MaxSize Unlimited;
ORA-01652: Unable to extend temp segment by 128 (in Tablespace zltoolstmp)