LINUX system, consult everybody masters, ORACLE 11 g database temp file 32 gb, but can be normal use, how should handle?

CodePudding user response:

Check the temp table space is futile, analysis and processing,
Temporary table space utilization rate:
The SELECT a.t ablespace_name,
Trunc (a. YTES/1024/1024/1024) totalG,
Trunc (a. ytes - NVL (b.b ytes, 0))/1024/1024/1024) FREEG,
Trunc (NVL (b.b ytes, 0)/1024/1024/1024) usedG,
Trunc (NVL (b.b ytes, 0)/a. ytes * 100) as usedpercent
The FROM (SELECT tablespace_name, SUM (bytes) bytes
The FROM dba_temp_files
GROUP BY tablespace_name). A,
(SELECT tablespace_name, SUM (T.B YTES_USED) bytes
The FROM gv $temp_extent_pool T
GROUP BY tablespace_name) b
WHERE a.t ablespace_name=b.t ablespace_name (+);

Session - the temporary table space level detailed usage (first 10 to take up the most) :
Select * from (
The SELECT se INST_ID,
Se. The username, -- -- the database user name
Se. SID, -- -- SID
Se. Serial#, -- -- serial#
Se status,
Se. Machine, -- -- session source host
Se. The program, a program - connect session using
Se TADDR,
P. pid - process id
Su. SQL_ID, -- -- SQL_ID
Su. In TABLESPACE, -- -- space occupied by the table
Su. Segtype, -- -- take up table space type
Su. CONTENTS,
Su. BLOCKS -- -- the amount of BLOCKS that can reflect the footprint size
The FROM gv $session se, gv $sort_usage su, gv $process p
WHERE se. Saddr=su. Session_addr
And se. INST_ID=su. INST_ID
And se. INST_ID=p.I NST_ID
And se. PADDR=p.A DDR
The order by su. BLOCKS desc
) where rownum<=10;

Find out to take up temporary tablespace real SQL:
The select ktssosqlid from x $ktsso, v $session where ktssoses=v $session. Saddr
And ktssosno=v $session. Serial#
And v $session. Sid=2111; (2111 according to find out the actual sid)

CodePudding user response:

1. The temp file there is no wrong to 32 gb, temp don't use will release, but the size will not change) 1) query SQL to see the actual usage of temp, 2) whether the alert log to a large number of temp is not enough, influence business
2. 32 gb temp file, if it is not enough, can increase the temp file inside the temp tablespace

CodePudding user response:

1, extensive use of using a temporary table, 2, big data sorting,

The above two factors, can create TEMP surging,

CodePudding user response:

Open the automatic extension? 32 g is 8 k data block under the data file, the size of the largest speculation temporary table space should have surrendered, for the system, the 32 gb of temporary table space may not be able to cope with peak and need for temporary table space to add more temporary files to prevent database when using it as an error to exit the space problem,

CodePudding user response:

Add temp file

CodePudding user response:

Disk space is enough, without having to deal with!

CodePudding user response:

Thank you for your attention!