Shulou(Shulou.com)06/01 Report--

When Oracle is installed on the centos system, the disk space of the system itself is not very large. After running for a period of time, it is found that the temporary table space of Oracle takes up more and more disk space, so that the system is on the verge of crashing. The solution to this problem is as follows:

Step one:

Alter database tempfile'/ opt/oracle/oradata/orcl/temp01.dbf' drop

Step 2:

Alter tablespace temp add tempfile

'/ opt/oracle/oradata/orcl/temp01.dbf'

Size 2048M reuse autoextend on next 100M

Step 3:

Select d.file_name, d.file_id, d.tablespace_name, d.bytes

From dba_temp_files d

Step 4:

Alter database tempfile'/ opt/oracle/oradata/orcl/temp01.dbf' autoextend off

Just to solve small problems, don't delve into it. )

Normally, after completing some sort operations using TEMP table space, such as Select statements, create index, and so on, Oracle will automatically release temporary period a. But sometimes we encounter situations where temporary segments are not released, TEMP tablespaces are almost full, and even if we restart the database, the problem is still not solved.

When checking the disk space of the centos system, it was found that the temporary data file where the temporary tablespace was located had reached 35G and occupied 100%.

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

The following actions are done with the sys superuser action of the database

At first, I was going to shrink the data files in the temporary tablespace.

Execute:

SQL > alter database tempfile'/ opt/oracle/oradata/orcl/temp01.dbf' resize 10240M

The database reported an error, the reset space size can not meet the needs.

It seems that a new temporary tablespace needs to be established to replace the current tablespace.

1. First, check the default tablespace of the current database:

SQL > select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE'

Confirm that the current temporary tablespace is TEMP

2. View the current temporary tablespace size:

SQL > select file_name,tablespace_name,bytes/1024/1024 "MB", autoextensible from dba_temp_files

3. Create a new temporary table space:

SQL > create temporary tablespace temp02 tempfile'/ opt/oracle/oradata/orcl/temp02.dbf' size 512m

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

SQL > alter database default temporary tablespace temp02

5. Confirm the default temporary tablespace of the current database

SQL > select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE'

Confirm that temp02 is the current database default tablespace

6. Before deleting the temp temporary table space, kill the sql statements running in the temp temporary table space. Most of these sql statements are sorted statements.

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 pjungle session se,v$sql s

Where p.nameplate blockade 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 the query comes out, kill drops these sql statements:

SQL > alter system kill session '524778; (if the SID of a running sql statement is 524 series # is 778)

After confirming that there are no sql statements running in the temp temporary tablespace, you can delete the temp temporary tablespace data file

7. Delete temp temporary tablespace

SQL > drop tablespace temp including contents and datafiles

In this way, the data files of the temporary tablespace can be deleted quickly.

8. Now that the temp02 temporary table space occupies other people's disk space, it is necessary to re-establish the temporary table space in the original position and re-establish the temp temporary table space.

SQL > create temporary tablespace temp tempfile'/ opt/oracle/oradata/orcl/temp01.dbf' size 512m autoextend on maxsize 15G

Create a new automatic extension temporary table space of 512m, with a maximum extension of 15G.

Check 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 new temp temporary tablespace with the default temporary tablespace of the database

SQL > alter database default temporary tablespace temp

10. Confirm the default temporary tablespace of the current database

SQL > select * from database_properties

Where property_name='DEFAULT_TEMP_TABLESPACE'

Confirm that temp is the current database default tablespace

11. At present, the original temp temporary tablespace has been changed into 512m, and the remaining disk space has been empty, so the temp02 temporary tablespace will be useless. Delete the temp02 temporary tablespace.

SQL > drop tablespace temp02 including contents and datafiles

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.