The role of temp tablespace and the role of temp tablespace

Source: Internet
Author: User

The role of temp tablespace and the role of temp tablespace

The role of temp tablespace:

Temp tablespace is mainly used for sorting operations [such as creating indexes, order by and group by, distinct, union/intersect/minus/, sort-merge, join, analyze command], index [such as creating indexes, importing data using IMP, and accessing views, after the computation is completed, the system automatically cleans it up.

When the temporary tablespace is insufficient, the operation speed is abnormally slow, and the temporary tablespace rapidly increases to the maximum space (the limit of expansion), and is generally not automatically cleared.

If the temporary tablespace is not set to auto-scale, when the temporary tablespace is not enough, the current affairs execution will report an error where the ora-01652 cannot extend the temporary segment, and the solution is also simple: 1. Set automatic expansion of temporary data files, or 2. Increase the temporary tablespace.

Temporary tablespace operations:

Query the default temporary tablespace:

SQL> select * from database_properties where property_name = 'default _ TEMP_TABLESPACE ';

PROPERTY_NAME

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

PROPERTY_VALUE

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

DESCRIPTION

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

DEFAULT_TEMP_TABLESPACE

TEMP

Name of default temporary tablespace

Query the temporary tablespace status:

SQL> select tablespace_name, file_name, bytes/1024/1024 file_size, autoextensible from dba_temp_files;

TABLESPACE_NAME

----------

FILE_NAME

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

FILE_SIZE AUT

-----

TEMP

/U01/app/oracle/oradata/orcl/temp01.dbf

100 YES

Extended temporary tablespace:

Method 1: Increase the temporary file size:

SQL> alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' resize100m;

Database altered.

Method 2: Set the temporary data file to automatic extension:

SQL> alter database tempfile '/u01/app/oracle/oradata/orcl/temp01.dbf' autoextend on next 5 m maxsize unlimited;

Related Article

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.