Troubleshoot temporary tablespace errors

error message: [HY000] (1652) [oracle][odbc][ora]ora-01652: unable to extend temp segment by (in table Space temp)

cause Analysis:There is not enough Oracle temp table space, and transaction execution will generally report an error that ora-01652 cannot extend the temporary segment. Because Oracle always allocates contiguous space as much as possible, this behavior occurs when there is not enough space to allocate or the distribution is discontinuous.

Recall the role of temporary table space:

The primary purpose of a temporal tablespace is to sort operations on a database [ such as index creation,ORDER by and Group by,distinct,union/intersect/minus/,sort-merge and join,analyze command ], manage indexes [ such as CREATE INDEX,IMP for data import ], Provides temporary computing space when accessing operations such as views, and the system cleans up automatically when the operation is completed.

When the temporary table space is low, it behaves as if the operation speed is unusually slow, and the temporal table space rapidly increases to the maximum space (the extended limit), and is generally not automatically cleaned up.

Workaround: We know that because ORACLE takes a tablespace as a logical structure - cell, and the physical structure of the tablespace is a data file, the data file is created physically on disk, and all the objects of the tablespace exist on disk, and the data file must be added to increase the space for the table space. First look at the available space for the specified tablespace, using view SYS. Dba_free_space, each record in the view represents the fragment size of the free space. Of course, you can also extend table space.

1. Increase the temporal tablespace (or add temporary tablespace files).

2. Set temporary data File auto-expansion

Steps:

1. Query temporary tablespace status :
Sql> col file_name for A20;

Sql> select tablespace_name,file_name,bytes/1024/1024file_size,autoextensible from Dba_temp_files;
650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M01/8B/58/wKioL1hKUB_QPhpeAAA__53Y8ew670.jpg "title=" 111. JPG "alt=" wkiol1hkub_qphpeaaa__53y8ew670.jpg "/>

2. extending temporary table space
sql> ALTER DATABASE tempfile '/U01/APP/ORACLE/ORADATA/CP7PV1DB/TEMP01.DBF ' resize 8192m;

Or you can add temporary tablespace files

Alter tablespace temp add tempfile '/u01/app/oracle/oradata/cp7pv1db/temp02.dbf ' size 8192m;

Note: temporary tablespace files if you have 32G to reach the maximum file size, you can only add files.

3. Set automatic expansion

sql> ALTER DATABASE Tempfile '/U01/APP/ORACLE/ORADATA/CP7PV1DB/TEMP01.DBF ' autoextend on next 10m maxsizeunlimited;

4 . Error when extending table space

ERROR Atline 1:

Ora-00376:file 201 cannot is read at this time

Ora-01110:data file 201: '/U01/APP/ORACLE/ORADATA/CP7PV1DB/TEMP01.DBF '

The reason is that the temporary tablespace does not know what the cause offline , modified to online after the modification succeeds.

sql> ALTER DATABASE Tempfile '/U01/APP/ORACLE/ORADATA/CP7PV1DB/TEMP01.DBF ' online;

Database altered.

5. Delete temporary tablespace (supplemental)

Sql>drop tablespace temp01 including contents and datafiles;

sql> ALTER DATABASE tempfile '/u01/app/oracle/oradata/cp7pv1db/temp01.dbf ' dropincluding datafiles;

Database altered.

Note: When you delete a temporary data file for a temporary tablespace, you do not need to specify The including datafiles option will also actually delete the physical files, otherwise you will need to manually delete the physical files. You cannot directly delete the default tablespace for the current user, or you will report a ORA-12906 error. If you need to delete a default temporary tablespace, you must first create a temporary tablespace, then specify the newly created tablespace as the default tablespace, and then delete the original temporary table space.

6. Change the system default temp table space

-- Query the default temporary table space

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

-- Modify the default temp table space

sql> alterdatabase default temporary tablespace temp02;

Databasealtered.

we can query whether to switch to TEMP02:

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

Property_name Property_value DESCRIPTION

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

Default_temp_tablespace TEMP02 Name of the default temporary tablespace

7 . Viewing the utilization of temporary tablespace

Sql>select Temp_used.tablespace_name,

total-used as "free",

Total as "total",

Round (NVL (total-used, 0) * 100/total, 3) "Free percent"

From (SELECT tablespace_name,sum (bytes_used)/1024/1024 used

From Gv_$temp_space_header

GROUP by Tablespace_name) temp_used,

(SELECT tablespace_name, SUM (bytes)/1024/1024 Total

From Dba_temp_files

GROUP by Tablespace_name) temp_total

Wheretemp_used.tablespace_name = Temp_total.tablespace_name;

Tablespace_name Free percent

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

TEMP 6876 8192 83.936

8. Find SQL Statements that consume more resources

Select Se.username,
Se.sid,
Su.extents,
Su.blocks * To_number (RTrim (p.value)) Asspace,
Tablespace,
Segtype,
Sql_text
From V$sort_usage Su, v$parameter p, v$session se, v$sql s
where p.name = ' db_block_size '
and su.session_addr = Se.saddr
and S.hash_value = Su.sqlhash
and s.address = Su.sqladdr
Order by Se.username, Se.sid;

This article is from the "Hollows Jie Sun" blog, be sure to keep this source http://xjsunjie.blog.51cto.com/999372/1881133

Troubleshoot temporary tablespace errors