ORA-01652 cannot extend the temp segment through 256 (in table space temp)

Source: Internet
Author: User

"It's an old system." Oracle9i a table with more than 100 million data in a database, and when the table was recently queried for a long time, the Web page was blank, so consider optimizing the table. The optimization plan is as follows:

1. Create a temporary table

Create  Global  temporary  table  tmptable on  commit  preserve  rows  as  Select  * from  water;

2. Delete water table data

Delete from water;
Commit

3. Temporary table data assigned to water table
INSERT INTO water select * from tmptable;

The first step is an error: ORA-01652 cannot extend the temp segment by 256 (in table space temp). This error tells us that there is not enough temporary table space.

The primary purpose of a temporary tablespace is to sort operations in a database [such as creating indexes, orders by and group BY, DISTINCT, union/intersect/minus/, sort-merge and join, analyze commands], managing indexes [ such as the creation of indexes, IMP for data import], access to views, and so on to provide a temporary computing space, when the operation is completed, the system will automatically clean up.
When the temporary table space is low, the operation speed is unusually slow, and the temporary table space grows rapidly to the maximum space (the limit of the extension), and generally does not clean up automatically.

If the temporary table space is not set to auto expand, then the transaction execution will report ora-01652 that cannot extend the temporary segment if the temporary table space is not sufficient: 1, set up the temporary data file automatic expansion, or 2, increase the temporary table space.

Temporary table space Related actions:

To query the default temp table space:

Sql> SELECT * from database_properties where property_name= ' default_temp_tablespace ';

Query temp table space status:

Sql> Select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from Dba_temp_file

To extend a temporary table space:

Method One, increase the temporary file size:

sql> ALTER DATABASE Tempfile '/U01/APP/ORACLE/ORADATA/ORCL/TEMP01.DBF ' resize100m;

Method Two, set the temporary data file to automatic expansion:

sql> ALTER DATABASE Tempfile '/U01/APP/ORACLE/ORADATA/ORCL/TEMP01.DBF ' autoextend on next 5m MaxSize Unlimited;

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.