ORA-1652 temporary table space full of solutions that cause new session data to not be in storage

DB version: Oracle database 10g Enterprise Edition release 10.2.0.5.0-64bi

Operating system platform: HP-UX

Alarm log: More Alert_mdsoss.log

2. Positioning problems

Error Symptom:

Fri Aug 13:37:39 EAT 2012

Ora-1652:unable to extend temp segment through 128 in Tablespace mdstemp shows that temporary segments cannot be extended, indicating that temporary table space is already being used and there is not enough space.

Description: From the official interpretation of the Metalink, no more free to distinguish between the temporary paragraph, you can add data files to the table space to solve the problem, on the surface, we have more in-depth understanding of what causes the temporary paragraph has no room for it, We all know that the temporary segment is record sorting and data migration, now the deep problem is not enough space, after a while to execute SQL may not be the error. Is that the SQL statement is not optimized. Because when the SQL in batch DML operation, will suddenly occupy a large number of temporary space sorting, will report the temporary paragraph is not enough, new data can not be stored at this time! After a while the space release can be stored again, to solve this problem requires SQL optimization.

The below is from Metalink:

error:ora-1652

Text:unable to extend temp segment from%s in tablespace%s

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

Cause:failed to allocate a extent for temp segment in tablespace.

Action:use ALTER tablespace ADD datafile statement to add one or more

Files to the tablespace indicated or create the object in another

Tablespace.

SELECT * from gnwebbrw12081720; There is data at this time, indicating that the space has been released

Colfile_name for A35

Selectfile_name,file_id,bytes/1024/1024,status,autoextensible tablespace_name from DBA_TEMP_FILES;

file_name file_id bytes/1024/1024 STATUS TAB

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

/ORADATA/MDSOSS/TEMP01.DBF 1 24671 AVAILABLE YES

/ORADATA/MDSOSS/MDSTMP.DBF 2 20000 AVAILABLE no mdstemp is not automatic expansion, if there is no such problem, but we do not recommend the use of data files to automatically expand function, not easy to monitor. Look at 24G + 20G space is no problem, generally is not good enough to write SQL to cause unnecessary sorting.

3. The solution

(1) Restart the instance, 7*24 the instance Smon process can release the sort segment, but our library is not down

(2) Add data file, my space is very tense, can not

(3) configuration reasonable Sort_area size has been configured, is PGA 4G sort_area_size 208M

(4) SQL optimization best solution

(5) Summary which operations will cause temporary table space to skyrocket?

What is the operation in using temp

-index creation or rebuilding.

-Order BY or GROUP by

-DISTINCT operation.

-UNION & INTERSECT & Minus

-Sort-merge joins.

-Analyze operation

-some anomalies will cause the temp to skyrocket.

When dealing with the above operation, the DBA needs to pay more attention to the temp usage? Let's see who uses these temporary segments now.

(5) Use of temporary table space

Select Tablespace_name,current_users,total_blocks,used_blocks,free_blocks from V$sort_segment;

Tablespace_name current_users total_blocks used_blocks free_blocks

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

TEMP 1 3157760 128 3157632

Mdstemp 24 2559872 2337152 222720 has been used for 92%

(6) Who is using these sort segments

Select Username,session_addr,sqladdr,sqlhash from V$sort_usage;

USERNAME session_addr sqladdr Sqlhash

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

Mdsoss C0000008483ecfb8 c0000008512150b8 3342809064

SABOCOUSR C00000084b405e50 c00000033f867510 141205382

Mdsoss c00000084740e988 c0000008508ab1c0 409467952

Mdsoss c0000008483de390 c00000033b8914f0 2951877480

Mdsoss c00000084a404460 c0000003404007a0 2584373469

Mdsoss c0000008483f5088 C00000033fa63e18 2245874020

Mdsoss c0000008483ffc48 c00000084d5b5f98 3000467390

Mdsoss c0000008483f5088 C00000033fa63e18 2245874020

Mdsoss C000000852404a60 c00000084dd6f598 1491833069

Mdsoss c0000008483eba40 c00000084de28990 1530468420