oracle中临时段的使用回收规则

之前的由于出现问题：与temp表空间相关的操作数据库都会hang。特此对oracle中临时表空间做一个深入了解，结果发现，内有乾坤。oracle中的临时段有2种：
如果TEMPORARY TABLESPACE的类型是TEMPORARY，TEMPORARY TABLESPACE里的使用过的空间是不会被释放的，除非shutdown。
如果是PERMANENT，SMON会在process不再使用临时段之后去做清理。
mos解释如下：

perment表空间里的临时段

用于临时操作的临时段不仅仅存在于临时表空间中，在普通的表空间里也可能存在临时段，比如我们通过CTAS创建一张表，在CTAS命令没有结束前，新的表的数据是放在临时段中的，这些临时段在CTAS完成的时候会转换为PERMENT段。如果这些临时操作由于某些原因异常中止了，那么这些临时段就没有创建完成，也没有最终转为PERMENT段，这种情况下，我们就可以观察到在PERMENT表空间中也会存在临时段。这些临时段会被SMON自动清理掉。不过也会因为某些情况或者BUG，SMON长时间没有清理掉这些临时段，这种情况在早期的ORACLE数据库版本中会出现，Oracle 10g以后出现的较少。如果要手工清理，可以使用drop_segments诊断事件。

EVENT: 10500 “turn on traces for SMON”

下面我们测试下SMON清理自动清理的机制
要想跟踪SMON清理过程，必须设置一个事件

[oracle@oracle11g trace]$ oerr ora 10500
10500, 00000, "turn on traces for SMON"
// *Document: NO
// *Cause:
// *Action:
//    Level:  <=5  trace instance recovery
//            > 5  trace posting of SMON


Error:  ORA 10500
Text:   turn on traces for SMON
-------------------------------------------------------------------------------
Cause:
Action:
        Level:  <=5  trace instance recovery
        > 5  trace posting of SMON

To set event 10500:

For the instance:
a.  Shutdown database
b.  Edit the initialisation parameter file and add:
    event="10500 trace name context forever, level <value>"
c.  restart the database

For the SMON session:
Post the SMON process using oradbx (Oracle 7) or oradebug (Oracle 8).
For oradebug from server manager issue:

oradebug setospid <OS PID>
oradebug event 10500 trace name context forever, level <value>

For further information about oradebug Note 29786.1
                              oradbx   Note 28863.1

<value> is 'Level' as per above

测试event 10500：
默认情况下，路径下没有smon的trace文件

[oracle@oracle11g trace]$ pwd
/u01/app/oracle/diag/rdbms/zhuo/zhuo/trace
[oracle@oracle11g trace]$ ls -ltr *smon*
ls: cannot access *smon*: No such file or directory

打开trace跟踪

SQL> alter system set events '10500 trace name context forever,level 10';

System altered.
[oracle@oracle11g trace]$ ls -ltr *smon*
ls: cannot access *smon*: No such file or directory
[oracle@oracle11g trace]$ 

还是没有文件产生。

执行建表语句

SQL> create table smon as select * from dba_objects;

Table created.
[oracle@oracle11g trace]$ ls -ltr *smon*
-rw-r-----. 1 oracle oinstall   87 Jan 14 22:54 zhuo_smon_1471.trm
-rw-r-----. 1 oracle oinstall 1509 Jan 14 22:54 zhuo_smon_1471.trc

trace产生。

部分片段：

[oracle@oracle11g trace]$ tail -200f zhuo_smon_1471.trc 
Trace file /u01/app/oracle/diag/rdbms/zhuo/zhuo/trace/zhuo_smon_1471.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name:    Linux
Node name:      oracle11g
Release:        2.6.32-642.el6.x86_64
Version:        #1 SMP Wed Apr 13 00:51:26 EDT 2016
Machine:        x86_64
VM name:        VMWare Version: 6
Instance name: zhuo
Redo thread mounted by this instance: 1
Oracle process number: 13
Unix process pid: 1471, image: oracle@oracle11g (SMON)


*** 2021-01-14 22:54:06.160
*** SESSION ID:(13.1) 2021-01-14 22:54:06.160
*** CLIENT ID:() 2021-01-14 22:54:06.160
*** SERVICE NAME:(SYS$BACKGROUND) 2021-01-14 22:54:06.160
*** MODULE NAME:() 2021-01-14 22:54:06.160
*** ACTION NAME:() 2021-01-14 22:54:06.160
 
SMON: process sort segment requests begin

*** 2021-01-14 22:54:06.160
SMON: process sort segment requests end

*** 2021-01-14 22:54:06.160
SMON: Posted, but not for trans recovery, so skip it.
SMON: obj$ cleanup begin
SMON: obj$ cleanup end. more:0
SMON: cleanup the cursor transient types begin
SMON: cleanup the cursor transient types end
SMON: launch SMCO begin
SMON: launch SMCO end
SMON: poll shared mount broadcast channel begin
SMON: poll shared mount broadcast channel end
SMON: poll flashback broadcast channel begin
SMON: poll flashback broadcast channel end
SMON: poll segment info broadcast channel begin
SMON: poll segment info broadcast channel end

在创建一条索引

SQL> create index idx_smon on smon(object_id);

Index created.

会有如下trace产生：

*** 2021-01-14 22:56:34.632
SMON: system monitor process posted msgflag:0x1000 (-/-/-/-/-/-/-)

*** 2021-01-14 22:56:34.634
SMON: process sort segment requests begin

*** 2021-01-14 22:56:34.634
SMON: process sort segment requests end

*** 2021-01-14 22:56:34.634
SMON: parallel transaction recovery begin

*** 2021-01-14 22:56:34.635
SMON: parallel transaction recovery end
SMON: offline rollback segment begin
SMON: offline rollback segment end
SMON: launch SMCO begin
SMON: launch SMCO end
SMON: poll shared mount broadcast channel begin
SMON: poll shared mount broadcast channel end
SMON: poll flashback broadcast channel begin
SMON: poll flashback broadcast channel end
SMON: poll segment info broadcast channel begin
SMON: poll segment info broadcast channel end

说明smon的跟踪被打开，只有有事务或者段操作，才会产生smon trace。

关闭：

SQL>  alter system set events '10500 trace name context off';

System altered.

SQL> create index idx_na on smon(object_name);

Index created.

即使创建索引，也不再产生trace，trace内容不再更新。

smon是如何清理永久表空间上的temporary segment

设置10500事件以跟踪smon进程

session 1：
SQL> alter system set events '10500 trace name context forever,level 10';

System altered.
执行create table命令，这将产生一定量的Temporary Extents（在建表的过程中会产生temporary，创建完后，永远也查不到这种类型数据，所以建的表一定要大，时间长，我们才能捕捉到）
SQL> conn zhuo/zhuo
SQL> create table myTestTable6 as
  2  select rownum as id,
  3  to_char(sysdate + rownum/24/3600, 'yyyy-mm-dd hh24:mi:ss') as inc_datetime,
  4  trunc(dbms_random.value(0, 100)) as random_id,
  5  dbms_random.string('x', 20) random_string
  6  from dual
  7  connect by level <= 50000000;

在另一个会话中执行对DBA_EXTENTS视图的查询，可以发现产生了多少临时区间

session 2：
SQL>  SELECT COUNT(*) FROM DBA_EXTENTS WHERE SEGMENT_TYPE='TEMPORARY';

  COUNT(*)
----------
         0

SQL> /

  COUNT(*)