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

ORACLE temporary table space summary and analysis, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain for you in detail, people with this need can come to learn, I hope you can gain something.

Temporary tablespace concept

Temporary tablespaces are used to manage database sorting operations and to store temporary objects such as temporary tables and intermediate sorting results. When SORT is needed in ORACLE, and when the size of sort_area_size in PGA is not enough, the data will be sorted in temporary tablespaces. Temporary tablespaces may be used for operations like CREATE INDEX, ANALYZE, SELECT DISTINCT, ORDER BY, GROUP BY, UNION ALL, INTERSECT, MINUS, SORT-MERGE JOINS, HASH JOIN, and so on. When the operation is completed, the system will automatically clean up the temporary objects in the temporary tablespace and automatically release the temporary segment. The release here is only marked as idle and can be reused, but the actual disk space is not really released. This is why temporary tablespaces sometimes grow.

Temporary tablespaces store the intermediate results of large-scale sorting operations (small-scale sorting operations are done directly in RAM, large-scale sorting requires disk sorting Disk Sort) and hash operations. It differs from permanent tablespaces in that it consists of temporary data files (temporary files) rather than permanent data files (datafiles). Temporary tablespaces do not store objects of permanent type, so it does not and does not need to be backed up. In addition, operations on temporary data files do not generate redo logs, but undo logs are generated.

When you create a temporary tablespace or temporary tablespace to add a temporary data file, the process is quite fast, even if the temporary data file is large. This is because ORACLE's temporary data file is a special kind of data file: sparse file (Sparse File), which only writes file header and last block information (only writes to the header and last block of the file) when a temporary tablespace file is created. Its space is deferred. This is why you create temporary tablespaces or add data files to temporary tablespaces so fast.

In addition, the temporary tablespace is NOLOGGING schema and it does not hold persistent type objects, so even if the database is corrupted, Recovery does not need to restore Temporary Tablespace.

Temporary tablespace information

View the temporary tablespace of the instance

SQL1:

SQL > SELECT PROPERTY_NAME, PROPERTY_VALUE

2 FROM DATABASE_PROPERTIES

3 WHERE PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE'

PROPERTY_NAME PROPERTY_VALUE

DEFAULT_TEMP_TABLESPACE TEMP

SQL2:

SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS

View temporary tablespace information:

SET LINESIZE 1200COL NAME FOR A60SELECT FILE# AS FILE_NUMBER, NAME AS NAME, CREATION_TIME AS CREATION_TIME, BLOCK_SIZE AS BLOCK_SIZE, BYTES/1024/1024/1024 AS "FILE_SIZE (G)", CREATE_BYTES/1024/1024/1024 AS "INIT_SIZE (G)", STATUS AS STATUS, ENABLED AS ENABLEDFROM V$TEMPFILE

The official document about V$TEMPFILE is as follows

Column

Datatype

Description

FILE#

NUMBER

Absolute file number

CREATION_CHANGE#

NUMBER

Creation System Change Number (SCN)

CREATION_TIME

DATE

Creation time

TS#

NUMBER

Tablespace number

RFILE#

NUMBER

Relative file number in the tablespace

STATUS

VARCHAR2 (7)

Status of the file (OFFLINE | ONLINE)

ENABLED

VARCHAR2 (10)

Enabled for read and/or write

BYTES

NUMBER

Size of the file in bytes (from the file header)

BLOCKS

NUMBER

Size of the file in blocks (from the file header)

CREATE_BYTES

NUMBER

Creation size of the file (in bytes)

BLOCK_SIZE

NUMBER

Block size for the file

NAME

VARCHAR2 (513)

Name of the file

SET LINESIZE 1200 COL TABLESPACE_NAME FOR A30 COL FILE_NAME FOR A60SELECT TABLESPACE_NAME AS TABLESPACE_NAME, FILE_NAME AS FILE_NAME, BLOCKS AS BLOCKS, STATUS AS STATUS, AUTOEXTENSIBLE AS AUTOEXTENSIBLE, BYTES/1024/1024/1024 AS "FILE_SIZE (G)", DECODE (MAXBYTES, 0, BYTES/1024/1024/1024 MAXBYTES/1024/1024/1024) AS "MAX_SIZE (G)", INCREMENT_BY AS "INCREMENT_BY", USER_BYTES/1024/1024/1024 AS "USEFUL_SIZE" FROM DBA_TEMP_FILES

DBA_TEMP_FILES describes all temporary files (tempfiles) in the database.

Column

Datatype

NULL

Description

FILE_NAME

VARCHAR2 (513)

Name of the database temp file

FILE_ID

NUMBER

File identifier number of the database temp file

TABLESPACE_NAME

VARCHAR2 (30)

NOT NULL

Name of the tablespace to which the file belongs

BYTES

NUMBER

Size of the file (in bytes)

BLOCKS

NUMBER

Size of the file (in Oracle blocks)

STATUS

CHAR (9)

File status:

AVAILABLE

RELATIVE_FNO

NUMBER

Tablespace-relative file number

AUTOEXTENSIBLE

VARCHAR2 (3)

Indicates whether the file is autoextensible (YES) or not (NO)

MAXBYTES

NUMBER

Maximum size of the file (in bytes)

MAXBLOCKS

NUMBER

Maximum size of the file (in Oracle blocks)

INCREMENT_BY

NUMBER

Default increment for autoextension

USER_BYTES

NUMBER

Size of the useful portion of the file (in bytes)

USER_BLOCKS

NUMBER

Size of the useful portion of the file (in Oracle blocks)

SQL > SELECT BYTES,BLOCKS, USER_BYTES, USER_BLOCKS, BLOCKS-USER_BLOCKS AS SYSTEM_USED FROM DBA_TEMP_FILES

BYTES BLOCKS USER_BYTES USER_BLOCKS SYSTEM_USED--2147483648 262144 2146435072 262016 1281073741824 131072 1072693248 130944128 209715200 25600 2086624 25472 128

Of these four columns, BYTES, BLOCKS shows how much BYTE the temporary file has and how many blocks it contains. USER_BYTES,USER_BLOCKS is the number of BYTE and blocks available. Therefore, we can know that some of the temporary files are occupied by the system, which can be understood as header information. The size of this part is 128 block, as shown in the following figure:

Manage temporary tablespaces

Create a temporary tablespace

The following is a simple example of creating a temporary table space. Many details can be found in the official documentation, which is omitted here and will not be introduced too much.

Http://docs.oracle.com/cd/B10501_01/server.920/a96540/statements_75a.htm

Http://docs.oracle.com/cd/B28359_01/server.111/b28310/tspaces002.htm#ADMIN11366

CREATE TEMPORARY TABLESPACE TMP

TEMPFILE'/ u01amp GSP oradataPlus TMP01.dbf'

SIZE 8G

AUTOEXTEND OFF

Add data files

When the temporary tablespace is too small, you need to expand the temporary tablespace (add data files, increase data files, and set files to expand automatically); sometimes you need to distribute temporary data files to different disk partitions to improve IO performance, and you also need to delete and add temporary tablespace data files.

SQL > ALTER TABLESPACE TEMP 2 ADD TEMPFILE'/ u04 MAXSIZE MAXSIZE 6G NEXT 128M 6 MAXSIZE 6G

Tablespace altered.

SQL > ALTER TABLESPACE TMPADD TEMPFILE'/ u03/eps/oradata/temp02.dbf'SIZE 64GAUTOEXTEND OFF

Tablespace altered.

Delete data file

For example, if I want to delete a file under a temporary tablespace, we have two ways to delete the data file for the temporary tablespace.

Method 1:

SQL > ALTER TABLESPACE TEMP

DROP TEMPFILE'/ u01GSPARAGSPULAR temp02.dbf'

Tablespace altered.

Note: this method of deleting temporary tablespaces deletes the corresponding physical files.

Method 2:

SQL > ALTER DATABASE TEMPFILE'/ u01GSPARAGSPHER temp02.dbf'

DROP INCLUDING DATAFILES

Database altered.

Note: when deleting temporary data files for temporary tablespaces, you do not need to specify the INCLUDING DATAFILES option to actually delete the physical files, otherwise you need to delete the physical files manually.

Resize the file

For the following example, the temporary data file needs to be resized from 1G to 2G

SQL > ALTER DATABASE TEMPFILE

'/ u01Applink oracle RESIZE oradata GSPUnigram temp02.dbf' oradata

Files offline and online

SQL > ALTER DATABASE TEMPFILE 2'/ u01 OFFLINE

Database altered.

SQL > ALTER DATABASE TEMPFILE 2'/ u01 ONLINE

Database altered.

The default temporary tablespace cannot be offline, otherwise an error will be reported, as shown below

SQL > ALTER TABLESPACE TEMP OFFLINE

ALTER TABLESPACE TEMP OFFLINE

*

ERROR at line 1:

ORA-03217: invalid option for alter of TEMPORARY TABLESPACE

Set up automatic file extension

SQL > ALTER DATABASE TEMPFILE'/ u01GSPARAGSPHER temp03.dbf'

2 AUTOEXTEND ON

3 NEXT 100M

4 MAXSIZE UNLIMITED

Move renamed Fil

For example, I need to rename the file / u01/app/oracle/oradata/GSP/temp4.dbf to / u01/app/oracle/oradata/GSP/temp04.dbf

1: take temporary files offline for temporary tablespaces

SQL > ALTER DATABASE TEMPFILE 2'/ u01Accord oradata OFFLINE GSP temp4.dbf'

2: move or rename related temporary files

Mv / u01/app/oracle/oradata/GSP/temp4.dbf / u01qqapp.oracle.oradataAccording to GSPUniverse temp04.dbf'

3: use the script ALTER DATABASE RENAME FILE

SQL > ALTER DATABASE RENAME FILE 2'/ u01GSP TO TO 4. DBF 'oradat 3' / u01Lax

4: bring temporary files from temporary tablespaces online

SQL > ALTER DATABASE TEMPFILE'/ u01 ONLINE

Database altered.

Delete temporary tablespace

SQL > DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS

Note: the default tablespace of the current user cannot be deleted, otherwise an ORA-12906 error will be reported

SQL > DROP TABLESPACE TMP INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS

DROP TABLESPACE TMP INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS

*

ERROR at line 1:

ORA-12906: cannot drop default temporary tablespace

If you need to delete a default temporary table space, you must first create a temporary table space, then specify the newly created table space as the default table space, and then delete the original temporary table space

Temporary tablespace group

Enter tablespace groups:

The incoming tablespace group is a new feature introduced by ORACLE 10g. It is a logical concept that does not require display creation and deletion. As long as a temporary tablespace is assigned to a group, the temporary tablespace group is automatically created, and all temporary tablespaces are automatically deleted when removed from the temporary tablespace group.

A temporary tablespace group must consist of at least one temporary tablespace with no explicit maximum limit.

A temporary tablespace group contains at least one tablespace. There is no limit for a group to have a maximum number of tablespaces

If you delete all members of a temporary tablespace group, the group is also automatically deleted.

The name of the temporary tablespace cannot be the same as the name of the temporary tablespace group.

It shares the namespace of tablespaces, thus its name cannot be the same as that of any tablespace.

You can create temporary tablespaces by specifying tablespace groups, that is, implicitly.

SQL > CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE'/ u01 SIZE TABLESPACE GROUP GRP_TEMP

View temporary tablespace groups:

SQL > SELECT * FROM DBA_TABLESPACE_GROUPS

GROUP_NAME TABLESPACE_NAME---GRP_TEMP TEMP2

You can also specify a temporary tablespace group of temporary tablespaces that have been created.

SQL > ALTER TABLESPACE TEMP TABLESPACE GROUP GRP_TEMP

Tablespace altered.

SQL > select * from dba_tablespace_groups

GROUP_NAME TABLESPACE_NAME---GRP_TEMP TEMPGRP_TEMP TEMP2

Remove from the group:

SQL > ALTER TABLESPACE TEMP TABLESPACE GROUP''

When you specify a temporary table space for a database or a temporary table space for a user, you can use the name of the temporary table space group

ALTER USER DM TEMPORARY TABLESPACE GRP_TEMP

Switch temporary tablespaces

1: view old temporary tablespace information

SELECT * FROM V$TEMPFILE

SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS

2: create temporary tablespaces for transit

3: add the corresponding data file

4: switch temporary tablespaces.

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TMP

5: delete old temporary tablespace data files

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES

6: if necessary, reassign the user temporary tablespace to the newly created temporary tablespace

ALTER USER ODS TEMPORARY TABLESPACE TMP

ALTER USER EDS TEMPORARY TABLESPACE TMP

ALTER USER ETL TEMPORARY TABLESPACE TMP

ALTER USER DM TEMPORARY TABLESPACE TMP

Shrink temporary tablespace

The temporary period used by sorting and other operations will be marked as idle after use, indicating that it can be reused, the space occupied will not be released immediately, and sometimes the temporary table space will become very large. At this point, you can free up the unused space by shrinking the temporary table space. Shrinking temporary table spaces is a new feature of ORACLE 11g.

SQL > ALTER TABLESPACE TEMP SHRINK SPACE KEEP 8G

SQL > ALTER TABLESPACE TEMP SHRINK TEMPFILE'/ u01GSPARAGSPHER temp02.dbf'

Monitor temporary tablespace

View temporary tablespace usage:

SELECT TU.TABLESPACE_NAME AS "TABLESPACE_NAME", TT.TOTAL-TU.USED AS "FREE (G)", TT.TOTAL AS "TOTAL (G)", ROUND (NVL (TU.USED, 0) / TT.TOTAL * 100,3) AS "USED (%)", ROUND (NVL (TT.TOTAL-TU.USED, 0) * 100 / TT.TOTAL, 3) AS "FREE (%)" FROM (SELECT TABLESPACE_NAME SUM (BYTES_USED) / 1024 / 1024 / 1024 USED FROM GV_$TEMP_SPACE_HEADER GROUP BY TABLESPACE_NAME) TU, (SELECT TABLESPACE_NAME, SUM (BYTES) / 1024 / 1024 / 1024 AS TOTAL FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME) TTWHERE TU.TABLESPACE_NAME = TT.TABLESPACE_NAME COL TEMP_FILE FOR A60 SELECT ROUND ((F.BYTES_FREE + F.BYTES_USED) / 1024 F.BYTES_USED 1024 P.BYTES_USED 2) AS "TOTAL (GB)", ROUND (F.BYTES_FREE + F.BYTES_USED)-NVL (P.BYTES_USED, 0)) / 1024 P.BYTES_USED 2) AS "FREE (GB)", D.FILE_NAME AS "TEMP_FILE", ROUND (NVL (P.BYTES_USED, 0) / 1024 Universe 1024) AS "USED (GB)" ROUND ((F.BYTES_USED + F.BYTES_FREE) / 1024 F.BYTES_FREE 1024 P.BYTES_USED 1024, 2) AS "TOTAL (GB)", ROUND (F.BYTES_USED + F.BYTES_FREE)-NVL (P.BYTES_USED, 0)) / 1024 AS "FREE (GB)", ROUND (NVL (P.BYTES_USED, 0) / 1024 AS "USED (GB)" FROM SYS.V_$TEMP_SPACE_HEADER F DBA_TEMP_FILES D, SYS.V_$TEMP_EXTENT_POOL PWHERE F.TABLESPACE_NAME (+) = D.TABLESPACE_NAME AND F.FILE_ID (+) = D.FILE_ID AND P.FILE_ID (+) = D.FILE_ID

View the usage of temporary files corresponding to temporary tablespaces

SELECT TABLESPACE_NAME AS TABLESPACE_NAME, BYTES_USED/1024/1024/1024 AS TABLESAPCE_USED, BYTES_FREE/1024/1024/1024 AS TABLESAPCE_FREEFROM V$TEMP_SPACE_HEADERORDER BY 1 DESC

Find SQL statements that consume a lot of temporary tablespace resources

SELECT se.username, se.sid, su.extents, su.blocks * to_number (rtrim (p.value)) as Space, tablespace, segtype, sql_textFROM 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.sqladdrORDER BY se.username, se.sid Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.

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.