Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what is the Oracle Temp tablespace switching method". In the daily operation, I believe that many people have doubts about what the Oracle Temp tablespace switching method is. The editor consulted all kinds of data and sorted out a simple and easy-to-use operation method. I hope it will be helpful for you to answer the doubt of "what is the Oracle Temp tablespace switching method?" Next, please follow the editor to study!

I. TEMP tablespace function

The main purpose of temporary tablespace is to provide temporary operation space when the database performs sorting operations, managing indexes, accessing views and so on. When the operation is completed, the system will automatically clean up. When sort is needed in oracle, when the size of sort_area_size in PGA is not enough, the data will be sorted in the temporary table space, and if there is any exception, it will also be placed in the temporary table space. Normally, after completing some sorting operations using TEMP table space such as Select statement and create index, Oracle will automatically release the temporary period. Note that the release here simply marks the space as free and reusable, and the real disk space is not released. So the Temp tablespace may get bigger and bigger.

Sorting is very resource-consuming, Temp table space is full, the key is to optimize your statements, as much as possible to reduce sorting is the best policy.

It is summarized as follows:

The main role of temporary tablespaces:

Index create or rebuild

Order by or group by

Distinct operation

Union or intersect or minus

Sort-merge joins

Analyze.

II. Oracle temp tablespace switching

2.1 query TEMP tablespace usage:

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

WHERE temp_used.tablespace_name = temp_total.tablespace_name; database default tablespace:

SQL > SELECT PROPERTY_NAME, PROPERTY_VALUE

2 FROM DATABASE_PROPERTIES

3 WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE'

2.2 create a new Temp2 tablespace

Create temporary tablespace temp1 tempfile'D:\ APP\ ADMINISTRATOR\ ORADATA\ SDXJ\ TEMP2.dbf' size 20m autoextend on next 1m maxsize unlimited

Tablespace group''

Extent management local uniform size 1M

2.3 modify the database default tablespace to Temp1

SQL > alter database default temporary tablespace temp1

The database has changed.

2.4 Delete the original tablespace

Exit session and delete the original tablespace

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES

Deletion complete.

3. Some common queries in Temp table space

3.1. Change the default temporary tablespace of the system:

-- query the default temporary tablespace

Select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE'

-- modify the default temporary tablespace

Alter database default temporary tablespace temp1

-- the default temporary tablespace for all users will be switched to the new temporary tablespace:

Select username,temporary_tablespace,default_ from dba_users

-- change a user's temporary tablespace:

Alter user scott temporary tablespace temp

3.2Lookup sql statements that consume resource comparisons

/ * Formatted on 21:58:17 on 2015-7-14 (QP5 v5.163.1008.3004) * /

SELECT se.username

Se.sid

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

3.3.To view the current temporary table space usage size and the sql statements that are occupying temporary table space

Select sess.SID, segtype, blocks * 8 / 1000 "MB", sql_text

From v$sort_usage sort, v$session sess, v$sql sql

Where sort.SESSION_ADDR = sess.SADDR

And sql.ADDRESS = sess.SQL_ADDRESS

Order by blocks desc

4.4 shrink temporary tablespaces (new to 11g)

-- shrink the temp tablespace to 20m

Alter tablespace temp1 shrink space keep 20M

-- automatically reduce the temporary files in the tablespace to the smallest possible size

ALTER TABLESPACE temp1 SHRINK TEMPFILE '... / temp01.dbf'

At this point, the study on "what is the method of switching Oracle Temp tablespaces" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.