Linux system Oracle TEMP01.DBF constantly getting bigger should be solved

Oracle installed on the CentOS system, the system disk space itself is not very large, after running a period of time to find that Oracle's temporary tablespace occupies a larger disk, so that the system is on the edge of the crash, the method to solve the problem is as follows:

The first step:
ALTER DATABASE tempfile '/opt/oracle/oradata/orcl/temp01.dbf ' drop;
Step Two:
Alter Tablespace temp Add tempfile
'/opt/oracle/oradata/orcl/temp01.dbf'
Size 2048M reuse autoextend on next 100M;
Step Three:
Select D.file_name, d.file_id, D.tablespace_name, d.bytes
from Dba_temp_files D;
Fourth Step:
ALTER DATABASE Tempfile '/opt/oracle/oradata/orcl/temp01.dbf' autoextend off;
(just to solve a small problem, don't delve into it.) ）

Normally, Oracle automatically releases temporary segment A after completing a sort operation using the temp table space, such as the SELECT statement, create INDEX, and so on. But some of us will encounter the temporary section has not been released, the temp table space is almost full of conditions, even we restarted the database still does not solve the problem

When checking the disk space of the CentOS system, it is found that the temporary tablespace is located at 35G, which is already occupying 100%.

Because it is an official database server, you cannot restart the database at random.

The following operations are done with the SYS super user of the database

I was just beginning to shrink the data file for the temp table space again.

Perform:

sql> ALTER DATABASE Tempfile '/opt/oracle/oradata/orcl/temp01.dbf' Resize 10240M;

Database error, re-set the size of the space can not meet the needs.

It appears that you need to re-establish a new temporary tablespace to replace the current table space.

1. First, look at the current database default table space:

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

Verify that the current temp table space is temp

2. View the size of the current temp table space:

Sql>select file_name,tablespace_name,bytes/1024/1024 "MB", autoextensible from Dba_temp_files;

3. Create a new temporary tablespace:

sql> Create temporary tablespace temp02 tempfile '/opt/oracle/oradata/orcl/temp02.dbf' Size 512M;

4. Replace the newly created temporary tablespace with the default temporary table space for the database

sql> ALTER DATABASE default temporary tablespace temp02;

5. Confirm the default temporary table space for the current database

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

Confirm that TEMP02 is the current database default table space

6. Kill the SQL statement running in temp tablespace before deleting the temp tablespace, so that the SQL statement is more of a sorted statement

Sql>select Se.username,se.sid,se.serial#,su.extents,su.blocks*to_number (RTrim (P.value)) as Space,

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;

After querying, kill these SQL statements:

Sql>alter system kill session ' 524,778 '; (If the SID of a running SQL statement is 524,serial# 778)

After you confirm that there are no SQL statements running in the temp table space, you can delete the temp temporary tablespace data file.

7. Delete temp temporary table space

sql> Drop Tablespace temp including contents and datafiles;

This will quickly delete the data file for the temporary tablespace.

8, now temp02 temporary table space occupies the other people's disk space, need to re-establish the temporary table space in the original location, re-establish temp table space

sql> Create temporary tablespace temp tempfile '/opt/oracle/oradata/orcl/temp01.dbf ' size 512M autoextend on maxsize 15 G

Create a new 512M auto-extended temporary tablespace with a maximum expansion of 15G.

To see if the new temp temporary tablespace is correct:

Sql>select file_name,tablespace_name,bytes/1024/1024,maxbytes/1024/1024,autoextensible from Dba_temp_files;

9. Replace the newly created temp tablespace with the default temporary table space for the database

sql> ALTER DATABASE default temporary tablespace temp;

10. Confirm the default temporary table space for the current database

Sql>select * from Database_properties

where property_name= ' default_temp_tablespace ';

Confirm that temp is the current database default table space

11, currently the original temp table space into 512M, the remaining disk space is empty, temp02 temporary table space is no use, delete temp02 temporary table space
Sql> drop tablespace temp02 including contents and datafiles;

Linux system Oracle TEMP01.DBF constantly getting bigger should be solved