Temporary tablespace management and damage 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
Frequent large operations, such as index creation and sorting, must be completed in the temporary tablespace to reduce memory overhead. Of course, you should try to avoid high query performance requirements on disks.
. 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 <br/> 2 where property_name like 'default % '; </P> <p> property_name property_value <br/> zookeeper <br/> default_temp_tablespace temp <br/> default_permanent_tablespace users <br/> default_tbs_type smallfile <br/>

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

SQL> select S. name tbsname, T. name, (T. bytes/1024/1024) bytes, status <br/> 2 from V $ tablespace S, V $ tempfile T <br/> 3 where S. TS # = T. TS #; </P> <p> tbsname name bytes status <br/> ---------- certificate ---------- ------- <br/> temp/u01/APP/Oracle/oradata/orcl/temp01.dbf 30 online </ p> <p> SQL> select tablespace_name, file_name, Bytes/1024/1024 size_mb from dba_temp_files; </P> <p> tablespace file_name size_mb <br/> ---------- hour ---------- <br/> temp/u01/APP/Oracle/oradata/orcl/temp01.dbf 30 </P> <p> SQL> select tablespace_name, logging, allocation_type <br/> 2 from dba_tablespaces where tablespace_name = 'temp '; </P> <p> tablespace logging allow.o <br/> ---------- --------- <br/> temp nologging uniform <br/>

3. Temporary table file size and used space

Select T1. "tablespace" "tablespace", <br/> T1. "Total (g)" "Total (g)", <br/> nvl (t2. "used (g) ", 0)" used (g) ", <br/> T1." Total (g) "-nvl (t2." used (g) ", 0)" free (g) "<br/> from (select tablespace_name" tablespace ", <br/> to_char (sum (Bytes/1024/1024/1024), '123')" Total (g) "<br/> from dba_temp_files <br/> group by tablespace_name <br/> Union <br/> select tablespace_name" tablespace ", <br/> to_char (sum (Bytes/1024/1024/1024), '000000') "Total (g) "<br/> from dba_data_files <br/> where tablespace_name like 'temp % '<br/> group by tablespace_name) T1, <br/> (select tablespace, round (sum (blocks) x 8/1024/1024) "used (g)" <br/> from V $ sort_usage <br/> group by tablespace) t2 <br/> where T1. "tablespace" = t2.tablespace (+); </P> <p> tablespace total (g) used (g) Free (g) <br/> ------------------------------ --------------- ---------- <br/> goex_temp 31.999 1 30.999 <br/> fix_temp 0.098 0. 098 <br/> temp 0.195 0. 195 <br/>

4. Check the SQL statement used by the current temporary table space and the occupied temporary table space.

--> SQL statement using temporary segments <br/> select sess. sid, segtype, blocks * 8/1000 "MB", SQL _text <br/> from V $ sort_usage sort, V $ session sess, V $ SQL <br/> where sort. session_addr = sess. saddr <br/> and SQL. address = sess. SQL _address <br/> order by blocks DESC; </P> <p> --> The following query can also query who is using the temporary segment <br/> Col username format A15 <br/> Col machine format A15 <br/> col program format A30 <br/> Col tablespace format A15 <br/> set linesize 160 <br/> select S. username <br/>, S. sid <br/>, S. serial # <br/>, S. SQL _address <br/>, S. machine <br/>, S. program <br/>, Su. tablespace <br/>, Su. segtype <br/>, Su. contents <br/> from V $ session S, V $ sort_usage su <br/> where S. saddr = Su. session_addr; </P> <p> select 'the '| Name | 'temp tablespaces' | tablespace_name | <br/> 'idle' | <br/> round (100-(S. tot_used_blocks/s. total_blocks) * 100, 3) | <br/> '% at' | to_char (sysdate, 'yyyymmddhh24miss') <br/> from (select D. tablespace_name, <br/> nvl (sum (used_blocks), 0) tot_used_blocks, <br/> sum (blocks) total_blocks <br/> from V $ sort_segment V, dba_temp_files d <br/> where D. tablespace_name = v. tablespace_name (+) <br/> group by D. tablespace_name) s, <br/> V $ database; <br/>

3. Modify the default temporary tablespace
Alter database default temporary tablespace tablespace_name;
4. When temporary tablespace is used excessively, the session can be killed if the temporary tablespace is allowed.
Alter system kill session 'sid, serial #';

Iii. Temporary tablespace faults
As mentioned above, temporary tablespace does not need to be backed up. If you back up temporary tablespace, you will receive an error message.

SQL> alter tablespace temp begin backup; <br/> alter tablespace temp begin backup <br/> * <br/> error at line 1: <br/> ORA-03217: invalid option for alter of temporary tablespace </P> <p> SQL> alter temporary tablespace temp begin backup; <br/> alter temporary tablespace temp begin backup <br/> * <br/> error at line 1: <br/> ORA-00940: Invalid alter command <br/>

Error message about temporary tablespace error (alert_orcl.log in the alarm log)
Errors in file/u01/APP/Oracle/admin/orcl/bdump/orcl_dbw0_2230.trc:
The ORA-01186: file 3 failed verification tests
ORA-01157: cannot identify/lock data file 3-see dbwr trace file
ORA-01110: data file 3: '/u01/APP/Oracle/oradata/orcl/temp01.dbf'
 
Iv. restore the damaged temporary tablespace
1. Delete the temporary tablespace data file in session 1

SQL> show user; <br/> User is "sys" <br/> SQL> Ho RM/u01/APP/Oracle/oradata/orcl/temp01.dbf </P> <p> SQL> alter System checkpoint; --> execute the Checkpoint Process </P> <p> system altered. <br/>

2. Execute sorting query in session 2, prompting that the temporary data file is incorrect.

SQL> show user; <br/> User is "Scott" <br/> SQL> set autotrace traceonly; <br/> SQL> select owner, object_name from big_table order by ID, owner, object_name; <br/> select owner, object_name from big_table order by ID, owner, object_name <br/> * <br/> error at line 1: <br/> ORA-01565: Error in identifying File '/u01/APP/Oracle/oradata/orcl/temp01.dbf' <br/> ORA-27037: unable to obtain File status <br/> Linux error: 2: no such file or directory <br/> additional information: 3 <br/>

3. Add data files for temporary tablespace in session 1

SQL> alter tablespace temp add tempfile '/u01/APP/Oracle/oradata/orcl/temp02.dbf' <br/> 2 size 30 m autoextend on next 1 m maxsize 2G; </P> <p> tablespace altered. <br/>

4. Continue sorting in session 2. This operation is successful.

SQL> select owner, object_name from big_table order by ID, owner, object_name; </P> <p> 1000000 rows selected. <br/>

5. query the temporary data file status and file existence

SQL> set linesize 160 --> View the status of the temporary data file in session 1. The size is 0, but it is still in the online status <br/> SQL> select S. name tbsname, T. name, (T. bytes/1024/1024) bytes, T. status <br/> 2 from V $ tablespace S, V $ tempfile T <br/> 3 where S. TS # = T. TS #; </P> <p> tbsname name bytes status <br/> -------------------- ---------- ------- <br/> temp/u01/APP/Oracle/oradata/orcl/temp01.dbf 0 online <br /> temp/u01/APP/Oracle/oradata/orcl/temp02.dbf 30 online </P> <p> SQL> Ho ls/u01/APP/Oracle/oradata/orcl/temp01.dbf <br/> ls: /u01/APP/Oracle/oradata/orcl/temp01.dbf: no such file or directory <br/>

6. Clear non-existing temporary file information recorded in the control file

SQL> alter tablespace temp drop tempfile '/u01/APP/Oracle/oradata/orcl/temp01.dbf'; </P> <p> tablespace altered. <br/>

7. tablespace-level recovery operations (the main steps are given below, which are not demonstrated)
A. Create a new temporary tablespace.
B. Use alter database to switch the user to the new temporary tablespace, that is, set the new default temporary tablespace.
C. Delete the damaged temporary tablespace.