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

This article introduces the relevant knowledge of "oracle temporary tablespace add, delete, change and query command". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Addition, deletion, modification and query of oracle temporary tablespace

1. View temporary tablespaces (dba_temp_files view) (v_$tempfile view)

Select tablespace_name,file_name,bytes/1024/1024 file_size,autoextensible from dba_temp_files

Select status,enabled, name, bytes/1024/1024 file_size from vastly updated tempfileview by users of Murray Mursys

2. Reduce the size of temporary table space

Alter database tempfile'D:\ ORACLE\ PRODUCT\ 10.2.0\ ORADATA\ TELEMT\ TEMP01.DBF' resize 100m

3. Expand temporary tablespaces:

Method 1. Increase the temporary file size:

SQL > alter database tempfile'/ u01 resize

Method 2. Set the temporary data file to expand automatically:

SQL > alter database tempfile'/ u01 autoextend on next autoextend on next 5m maxsize unlimited

Method 3. Add data files to the temporary tablespace:

SQL > alter tablespace temp add tempfile'/ u01 size

4. Create a temporary table space:

SQL > create temporary tablespace temp1 tempfile'/ u01 size

5. Change the system's default temporary tablespace:

-- 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

6. Delete temporary tablespaces

Delete a data file for the temporary tablespace:

SQL > alter database tempfile'/ u01 drop

Delete temporary tablespaces (completely deleted):

SQL > drop tablespace temp1 including contents and datafiles cascade constraints

7. View the use of temporary tablespaces (GV_$TEMP_SPACE_HEADER views can only be queried under sys users)

The GV_$TEMP_SPACE_HEADER view records the size of temporary tablespaces used and unused

The bytes field of the dba_temp_files view records the total size of the temporary tablespace

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

Create a new temporary table space to view the space situation, the new temporary table space TEMP1 from v$temp_space_header and dba_temp_free_space

The statistical free space is the same, but why is the current Default TEMP different from the two views? the free space of TEMP in v$temp_space_header is 0.

But the count in dba_temp_free_space is 134217728.

SQL > create temporary tablespace temp1 tempfile'e:\ oracle\ oradata\ ORCL\ DATAFILE\ temp01.dbf' size 200m

The tablespace has been created.

SQL > select tablespace_name,bytes_used,BLOCKS_USED,bytes_free from v$temp_space_header

TABLESPACE_NAME BYTES_USED BLOCKS_USED BYTES_FREE

-

TEMP 137355264 16767 0

TEMP1 1048576 128 208666624

SQL > select * from dba_temp_free_space

TABLESPACE_NAME TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE SHARED INST_ID

--

TEMP 137355264 137355264 134217728 SHARED

TEMP1 209715200 1048576 208666624 SHARED

8. Find sql statements that consume resource comparisons

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

9. 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

10. Introduction of temporary table space group

1) create a temporary tablespace group:

Create temporary tablespace tempts1 tempfile'/ home/oracle/temp1_02.dbf' size 2m tablespace group group1

Create temporary tablespace tempts2 tempfile'/ home/oracle/temp2_02.dbf' size 2m tablespace group group2

2) query temporary table space group: dba_tablespace_groups view

Select * from dba_tablespace_groups

GROUP_NAME TABLESPACE_NAME

GROUP1 TEMPTS1

GROUP2 TEMPTS2

3) move tablespaces from one temporary tablespace group to another:

Alter tablespace tempts1 tablespace group GROUP2

Select * from dba_tablespace_groups

GROUP_NAME TABLESPACE_NAME

GROUP2 TEMPTS1

GROUP2 TEMPTS2

4) assign temporary tablespace groups to users

Alter user scott temporary tablespace GROUP2

5) set temporary tablespaces at the database level

Alter database default temporary tablespace GROUP2

6) Delete temporary tablespace groups (delete all temporary tablespaces that make up temporary tablespace groups)

Drop tablespace tempts1 including contents and datafiles

Select * from dba_tablespace_groups

GROUP_NAME TABLESPACE_NAME

GROUP2 TEMPTS2

Drop tablespace tempts2 including contents and datafiles

Select * from dba_tablespace_groups

GROUP_NAME TABLESPACE_NAME

11. Shrink temporary tablespaces (added to 11g)

-- shrink the temp tablespace to 20m

Alter tablespace temp shrink space keep 20M

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

ALTER TABLESPACE temp SHRINK TEMPFILE'/ u02qoracleqqdataUniple lmtemp02.dbf'

Temporary tablespace function

Oracle temporary table space is mainly used to query and store some buffer data. The main reason for temporary tablespace consumption is the need to sort the intermediate results of the query.

Restarting the database frees up temporary tablespaces, and if you cannot restart the instance and keep the problem sql statements executed, the temp tablespaces will grow all the time. Until the hard drive runs out of space.

Some people on the Internet speculate that oracle uses a greedy algorithm in the allocation of disk space. If the last disk space consumption reaches 1GB, then the temporary table space is 1GB.

That is, the current temporary tablespace file size is the largest in the history of using temporary tablespaces.

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.

Transferred from: http://www.blogjava.net/japper/archive/2012/06/28/381721.html

Here is an example of dealing with temporary tablespaces

Https://blog.csdn.net/u013050593/article/details/77850929/

When the OPS staff queried 100 million-level data sorting, the database reported an error, indicating that ora-01652 cannot expand the temp segment through 128 (in the tablespace temp). The troubleshooting process is as follows:

1. Query tablespace usage:

Select * from (

Select a.tablespace_name

To_char (a.bytesAccorde 1024Accord 1024 total_bytes)

To_char (b.bytesUniverse 1024 + 1024 + 1024) free_bytes

To_char (a.bytes/1024/1024-b.bytesAccord 1024 Universe 99999.999') use_bytes

To_char ((1-b.bytes/a.bytes) * 100 use 99.99') | |% 'use

From (select tablespace_name

Sum (bytes) bytes

From dba_data_files

Group by tablespace_name) a

(select tablespace_name

Sum (bytes) bytes

From dba_free_space

Group by tablespace_name) b

Where a.tablespace_name = b.tablespace_name

Union all

Select c.tablespace_name

To_char (c. BytesUniverse 1024 + 1024-1024) total_bytes

To_char ((c.bytes-d.bytes_used) / 1024Universe 99999.9999') free_bytes

To_char (d.bytes unused use_bytes 1024 pound 1024 lead 99999.999')

To_char (d.bytesusused.bytes.bytes1999.99') | |'% 'use

From

(select tablespace_name,sum (bytes) bytes

From dba_temp_files group by tablespace_name) c

(select tablespace_name,sum (bytes_cached) bytes_used

From v$temp_extent_pool group by tablespace_name) d

Where c.tablespace_name = d.tablespace_name

)

Order by tablespace_name

Found that the tablespace usage is 100%.

2. Use 11g tablespaces to shrink tablespaces and reduce usage. Sql statement: ALTER TABLESPACE TEMP SHRINK SPACE

3. Check that the size of the temp tablespace becomes 1.99m, and the utilization rate is 0%.

4. Add a temporary data file and set the size. The sql statement is as follows: alter tablespace temp add tempfile'/ oracle/oradata/dbaxj/temp02.dbf' size 10240m autoextend on next 1024m maxsize 30g

5. Check the tablespace usage again

Reduced to 33%, problem solved.

This is the end of the order for adding, deleting and changing oracle temporary tablespaces. Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.