Temporary Oracle tablespace management and impaired recovery

Temporary Oracle tablespace is an important part of Oracle databases. Although this part is not presented in the cont architecture, its importance cannot be ignored. Especially for large-sized frequent operations, such as index creation and sorting, all need to be completed in the temporary tablespace to reduce memory overhead. Of course, you should try to avoid completing these operations on the disk if you have high query performance requirements. This article mainly describes the management and damage recovery of temporary tablespace.

I. Features and precautions of temporary tablespace

1. Features
Tablespace for storing temporary data
Temporary data usually only exists during a database session. There are two forms: sorting data and global temporary table.
Temporary data is not written into the common tablespace that stores permanent objects, but stored in the temporary segment of the temporary tablespace.
Temporary tablespace does not need to be backed up. RMAN does not support backup of temporary tablespace.
Temporary data processing does not generate Redo or cancel data.
Temporary tablespace data files cannot be set to read-only or renamed
NOLOGGING is used to log data files in the tablespace during monitoring.
Main Operations for using temporary tablespace
Create index, alter index... REBUILD, order by, group by, DISTINCT, UNION, INTERSECT, MINUS, SORT-MERGER, JOIN, ANALYZE

2. Precautions for using temporary tablespace
A. each user has a default temporary tablespace. For systems with high use of temporary tablespace, we recommend that you distribute the temporary tablespace data files to different disks.
B. For large operations (large queries, large-scale classified queries, large-scale statistical analysis, etc.), a separate temporary tablespace should be specified to facilitate management.
C. assign a separate temporary tablespace to the user. It is generally used for large product databases, OLTP databases, and database warehouses.
D. You do not need to create a temporary tablespace for small products. Use the default temporary tablespace.
E. We recommend that you disable auto-scaling for temporary tablespace to avoid space pressure caused by excessive scaling.
For information on creating and managing temporary tablespace, see Oracle tablespace and data files.

Ii. Temporary tablespace Management
1. view the default temporary tablespace

SQL> select property_name, property_value from database_properties
2 where property_name like 'default % ';

PROPERTY_NAME PROPERTY_VALUE
--------------------------------------------------------------
DEFAULT_TEMP_TABLESPACE TEMP
DEFAULT_PERMANENT_TABLESPACE USERS
DEFAULT_TBS_TYPE SMALLFILE

2. view the size and position of the temporary tablespace.

SQL> select s. name tbsname, t. name, (t. bytes/1024/1024) bytes, status
2 from v $ tablespace s, v $ tempfile t
3 where s. ts # = t. ts #;

TBSNAME NAME BYTES STATUS
------------------------------------------------------------------------
TEMP/u01/app/oracle/oradata/orcl/temp01.dbf 30 ONLINE

SQL> select tablespace_name, file_name, bytes/1024/1024 Size_MB from dba_temp_files;

TABLESPACE FILE_NAME SIZE_MB
-----------------------------------------------------------------
TEMP/u01/app/oracle/oradata/orcl/temp01.dbf 30

SQL> select tablespace_name, logging, allocation_type
2 from dba_tablespaces where tablespace_name = 'temp ';

Tablespace logging allow.o
----------------------------
TEMP NOLOGGING UNIFORM

3. Temporary table file size and used space

SELECT t1. "Tablespace" "Tablespace ",
T1. "Total (G)" "Total (G )",
Nvl (t2. "Used (G)", 0) "Used (G )",
T1. "Total (G)"-nvl (t2. "Used (G)", 0) "Free (G )"
FROM (SELECT tablespace_name "Tablespace ",
To_char (SUM (bytes/1024/1024/1024), '20140901') "Total (G )"
FROM dba_temp_files
Group by tablespace_name
UNION
SELECT tablespace_name "Tablespace ",
To_char (SUM (bytes/1024/1024/1024), '20140901') "Total (G )"
FROM dba_data_files
WHERE tablespace_name LIKE 'temp %'
Group by tablespace_name) t1,
(SELECT tablespace, round (SUM (blocks) * 8/1024/1024) "Used (G )"
FROM v $ sort_usage
Group by tablespace) t2
WHERE t1. "Tablespace" = t2.tablespace (+ );


Tablespace Total (G) Used (G) Free (G)
-----------------------------------------------------------------
GOEX_TEMP 31.999 1 30.999
FIX_TEMP 0.098 0. 098
TEMP 0.195 0. 195

1 2 3 Next Page