ckpt进程的trace文件中出现类似：9430ms (rw) file: kct.c line: 7800 count: 13 total: 26874ms time: 1296124
ckpt进程的trace文件中报如下错误

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /oradb/11.2.0/db
System name:	AIX
Node name:	offondb2
Release:	1
Version:	6
Machine:	00CC83664C00
Instance name: offon2
Redo thread mounted by this instance: 2
Oracle process number: 20
Unix process pid: 19660878, image: oracle@offondb2 (CKPT)
*** 2012-10-26 13:25:00.971
  1: 9430ms (rw) file: kct.c line: 7800 count: 13 total: 26874ms time: 1296124
  2: 1777ms (rw) file: krse.c line: 1800 count: 164 total: 122140ms time: 364176
  3: 1450ms (rw) file: kct.c line: 1011 count: 1 total: 1450ms time: 1594117
  4: 1230ms (rw) file: kcrf.c line: 10012 count: 135 total: 90995ms time: 2630737
  5: 1173ms (ro) file: kcrr.c line: 3525 count: 13 total: 8842ms time: 3645916
  6: 890ms (rw) file: kcrf.c line: 9959 count: 8 total: 4812ms time: 3578222
  7: 830ms (ro) file: kcf.c line: 5306 count: 1 total: 830ms time: 1594116
  8: 677ms (rw) file: kcv.c line: 11783 count: 8 total: 4499ms time: 416869
Control file enqueue hold time tracking dump at time: 2092019
*** 2012-10-26 15:25:14.789
  1: 9430ms (rw) file: kct.c line: 7800 count: 13 total: 26874ms time: 1296124
  2: 1777ms (rw) file: krse.c line: 1800 count: 165 total: 122832ms time: 364176
  3: 1450ms (rw) file: kct.c line: 1011 count: 1 total: 1450ms time: 1594117
  4: 1230ms (rw) file: kcrf.c line: 10012 count: 135 total: 90995ms time: 2630737
  5: 1173ms (ro) file: kcrr.c line: 3525 count: 13 total: 8842ms time: 3645916
  6: 890ms (rw) file: kcrf.c line: 9959 count: 8 total: 4812ms time: 3578222
  7: 830ms (ro) file: kcf.c line: 5306 count: 1 total: 830ms time: 1594116
  8: 677ms (rw) file: kcv.c line: 11783 count: 8 total: 4499ms time: 416869

原因分析并解决

New controlfile enqueue hold time tracking statistics have been added in 11.2 to
aid diagnosis of controlfile transaction related performance related issues.
To disable the trace set _controlfile_enqueue_holding_time_tracking_size to 0:
- spfile
alter system set "_controlfile_enqueue_holding_time_tracking_size"=0 scope=spfile;
- pfile
_controlfile_enqueue_holding_time_tracking_size=0
A database restart is required.

查询_controlfile_enqueue_holding_time_tracking_size

SQL> col name for a32
SQL> col value for a24
SQL> col description for a70
SQL> set linesize 150
SQL> select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
  2    from x$ksppi a,x$ksppcv b
  3   where a.inst_id = USERENV ('Instance')
  4     and b.inst_id = USERENV ('Instance')
  5     and a.indx = b.indx
  6     and upper(a.ksppinm) LIKE upper('%&param%')
  7  order by name
  8  /
Enter value for param: _controlfile_enqueue_holding_time_tracking_size
old   6:    and upper(a.ksppinm) LIKE upper('%&param%')
new   6:    and upper(a.ksppinm) LIKE upper('%_controlfile_enqueue_holding_time_tracking_size%')
</strong>
NAME                             VALUE      DESCRIPTION
-------------------------------- ---------- ------------------------------------------------
_controlfile_enqueue_holding_tim 10         control file enqueue holding time tracking size
e_tracking_size

补充MOS说明[791417.1]

New controlfile enqueue hold time tracking statistics have been added in 11.2 to aid
diagnosis of controlfile transaction related performance related issues:
Control File Enqueue AWR Statistics:
max cf enq hold time - The maximum amount of time in milliseconds a client has held the control file enqueue.
total cf enq hold time - The total amount of time in milliseconds all clients have held the control file enqueue.
total number of cf enq holders - The total number of times clients have held the control file enqueue.
Periodically, the CKPT process dumps statistics for the top N control file enqueue holders.
N defaults to 10, but can be modified with the static hidden
parameter: _controlfile_enqueue_holding_time_tracking_size.The dump looks like the following:
Preface: "Control file enqueue hold time tracking dump at time: [relative time]".
a. Time the client has held the control file enqueue.
b. Type of client's control file enqueue transaction - rw or ro.
c. File name where the client obtained control file enqueue.
d. Line number where the client obtained control file enqueue.
e. Number of times the client has held the control file enqueue since it became a member of the top N.
f. Total time the client has held the control file in all those times from [e].
g. Relative time the client obtained the control file enqueue from [a].

数据库版本

SQL> select * from v$version;
BANNER
-------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

执行DBMS_STATS报错

SQL> exec dbms_stats.GATHER_TABLE_STATS('CHF','T_XIFENFEI');
begin sys.dbms_stats.GATHER_TABLE_STATS('CHF','T_XIFENFEI');; end;
ORA-04063: package body "SYS.DBMS_STATS" 有错误
ORA-06508: PL/SQL: 无法找到正在调用 : "SYS.DBMS_STATS" 的程序单元
ORA-06512: 在 line 2

重建DBMS_STATS包

SQL> drop package DBMS_STATS;
Package dropped.
SQL> @?/rdbms/admin/dbmsstat.sql
Package created.
No errors.
Synonym created.
Grant succeeded.
create role gather_system_statistics
            *
ERROR at line 1:
ORA-01921: role name 'GATHER_SYSTEM_STATISTICS' conflicts with another user or
role name
Grant succeeded.
Grant succeeded.
Library created.
SQL> @?/rdbms/admin/prvtstas.plb
Package created.
No errors.
SQL> @?/rdbms/admin/prvtstai.plb
Package body created.
No errors.
SQL> @?/rdbms/admin/prvtstat.plb
Package body created.
No errors.

重新执行DBMS_STATS

SQL> exec dbms_stats.GATHER_TABLE_STATS('CHF','T_XIFENFEI');
PL/SQL procedure successfully completed.

补充说明
1.建议数据库在restricted模式下执行重建DBMS_STATS相关脚本
2.对于11g以前版本,具体参考1310365.1

SQL> @?/rdbms/admin/dbmsstat.sql
SQL> @?/rdbms/admin/prvtstas.plb
SQL> @?/rdbms/admin/prvtstat.plb

以前对Transportable Tablespaces(TTS)一直理解不深,今天无意中看到TTS可以实现数据库升级,今天测试了实现使用TTS 迁移9.2.0.4的一个表空间到11.2.0.3,平台均为Linux 32位
源端版本

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production

创建测试环境

SQL> create tablespace tts_xff
  2  datafile '/u01/oracle/oradata/xifenfei/tts_xifenfei01.dbf' size 10m autoextend on next 10m,
  3  '/u01/oracle/oradata/xifenfei/tts_xifenfei02.dbf' size 10m autoextend on next 10m
  4  ;
Tablespace created.
SQL> create user tts_xff identified by xifenfei;
User created.
SQL> grant dba to tts_xff;
Grant succeeded.
SQL> conn tts_xff/xifenfei
Connected.
SQL> create table t1 tablespace tts_xff
  2  as
  3  select * from dba_objects;
Table created.
SQL> create table t2 tablespace tts_xff
  2  as
  3  select * from dba_objects;
Table created.
SQL> create table t_xifenfei  tablespace tts_xff
  2  as
  3  select * from dba_objects;
Table created.
SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T1                             TABLE
T2                             TABLE
T_XIFENFEI                     TABLE
SQL> select count(*) from t1;
  COUNT(*)
----------
     30805
SQL> conn / as sysdba
Connected.
SQL> alter tablespace tts_xff read only;
Tablespace altered.

导出并传输测试表空间

[oracle@xifenfei ~]$ exp userid=\'/ as sysdba\' tablespaces=tts_xff file=/tmp/tts_xff.dmp transport_tablespace=y
Export: Release 9.2.0.4.0 - Production on Sun Oct 7 04:53:25 2012
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported
About to export transportable tablespace metadata...
For tablespace TTS_XFF ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                             T1
. . exporting table                             T2
. . exporting table                     T_XIFENFEI
. exporting referential integrity constraints
. exporting triggers
. end transportable tablespace metadata export
Export terminated successfully without warnings.
[oracle@xifenfei ~]$ scp /tmp/tts_xff.dmp 192.168.1.10:/tmp/
oracle@192.168.1.10's password:
tts_xff.dmp                                                       100%   16KB  16.0KB/s   00:00
[oracle@xifenfei ~]$ scp /u01/oracle/oradata/xifenfei/tts_xifenfei* 192.168.1.10:/u01/oracle/oradata/ora11g/
oracle@192.168.1.10's password:
tts_xifenfei01.dbf                                                100%   10MB   3.3MB/s   00:03
tts_xifenfei02.dbf                                                100%   10MB   5.0MB/s   00:02

目标库版本

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

创建用户

SQL> create user tts_11g identified by xifenfei;
User created.
SQL> grant dba to tts_11g;
Grant succeeded.

导入表空间

[oracle@xifenfei ~]$ imp userid=\'/ as sysdba\' tablespaces=tts_xff file=/tmp/tts_xff.dmp
> transport_tablespace=y datafiles=/u01/oracle/oradata/ora11g/tts_xifenfei01.dbf,
> /u01/oracle/oradata/ora11g/tts_xifenfei02.dbf fromuser=tts_xff touser=tts_11g
Import: Release 11.2.0.3.0 - Production on Sat Sep 29 04:18:04 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V09.02.00 via conventional path
About to import transportable tablespace(s) metadata...
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing TTS_XFF's objects into TTS_11G
. . importing table                           "T1"
. . importing table                           "T2"
. . importing table                   "T_XIFENFEI"
Import terminated successfully without warnings.

测试数据

SQL> alter tablespace tts_xff read write;
Tablespace altered.
SQL> conn tts_11g/xifenfei
Connected.
SQL> select * from tab;
TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T1                             TABLE
T2                             TABLE
T_XIFENFEI                     TABLE
SQL> select count(*) from t1;
  COUNT(*)
----------
     30805
SQL> delete from t1;
30805 rows deleted.
SQL> commit;
Commit complete.

至此测试完成，证明使用tts可以实现跨版本迁移数据

补充说明
1.10g及其以上版本可以实现不同平台的tts迁移，可能需要使用rman convert转换
2.迁移前需要使用 EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK检测依赖性

很多时候，我们在数据库中设置了event,如何确认设置的event生效或者如何确认你的库中设置了什么event.下面的文章测试了在11g中比较方便的方法
数据库版本

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

设置测试event

SQL> alter session set events '10510 trace name context forever,level 1';
Session altered.
SQL> alter session set events
  2  '10046 trace name context forever,level 4';
Session altered.
SQL> alter system set events '60025 trace name context forever';
System altered.
SQL> alter system set events '10513 trace name context forever,level 2';
System altered.

测试spfile参数中是否有event

SQL> create pfile='/tmp/pfile' from spfile;
File created.
solaris*orcl-/home/oracle$ grep -i event /tmp/pfile
--无记录
SQL> show parameter event;
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------
event                                string
xml_db_events                        string      enable

证明设置event不会在spfile中记录

查询会话event

--dbms_system实现
SQL> set serveroutput on size 1000000
SQL> declare
  2  event_level number;
  3  begin
  4  for i in 1..100000 loop
  5  sys.dbms_system.read_ev(i,event_level);
  6  if (event_level > 0) then
  7  dbms_output.put_line('Event '||to_char(i)||' set at level '||
  8  to_char(event_level));
  9  end if;
 10  end loop;
 11  end;
 12  /
Event 10510 set at level 1
Event 10513 set at level 2
Event 60025 set at level 1
PL/SQL procedure successfully completed.
--oradebug实现
SQL> oradebug SETMYPID
Statement processed.
SQL> oradebug eventdump session
10510 trace name context forever,level 1
10513 trace name context forever,level 2
60025 trace name context forever
sql_trace level=4

测试证明使用dbms_system可以捕获到event,oradebug可以捕获到本身会话,还可以通过setospid/setorapid来跟踪其他会话的event设置情况.event 10046对应的本质是sql_trace所以使用dbms_system不能捕获到10046

临时表空间被使用现状
接到客户反馈,他们的数据库使用了长连接,临时表空间使用率一直不下降,一个会话占用了几百M甚至几个G的临时表空间不释放,随着时间的积累,会话占用的临时表空间还在继续增加,最终的现象是100G的数据文件,160G的临时表空间还在继续报临时表空间不足.查询v$sort_usage发现其SEGTYPE全部为LOB_DATA而CONTENTS为TEMPORARY,而且BLOCKS都很大,通过上面的信息大概分析,怀疑是因为数据库查询或者操作LOB类型时候使用了TEMPORARY,但是没有释放导致

相关版本信息

OS:AIX 6.1(64)
DB:10.2.0.5

测试案例证明

--执行查询脚本
$ more check.sql
connect / as sysdba
select * from v$tempseg_usage where username not in ('HDDS_CLPS_DTA','FOGLIGHT');
--测试脚本1
$ more test1.sh
sqlplus /nolog <<EOF
connect / as sysdba
drop user xifenfei cascade;
create user xifenfei identified by tc
default tablespace users temporary tablespace temp quota unlimited on users;
grant connect,resource,alter session to xifenfei;
revoke unlimited tablespace from xifenfei;
connect xifenfei/tc
select to_nclob('a') from dual;
!sqlplus /nolog @check
commit;
!sqlplus /nolog @check
EOF
--测试脚本2
$ more test2.sh
sqlplus /nolog << EOF2
connect xifenfei/tc
alter session set events '60025 trace name context forever';
select to_nclob('a') from dual;
!sqlplus /nolog @check
commit;
!sqlplus /nolog @check
EOF2

测试结果

$ ./test1.sh
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Oct 12 10:04:39 2012
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
SQL> Connected.
SQL> drop user xifenfei cascade
          *
ERROR at line 1:
ORA-01918: user 'XIFENFEI' does not exist
Grant succeeded.
SQL>
Revoke succeeded.
SQL> SQL> SQL> Connected.
SQL>
TO_NCLOB('A')
--------------------------------------------------------------------------------
a
SQL> SQL>
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Oct 12 10:04:39 2012
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
Connected.
USERNAME                       USER                           SESSION_ADDR
------------------------------ ------------------------------ ----------------
SESSION_NUM SQLADDR             SQLHASH SQL_ID
----------- ---------------- ---------- -------------
TABLESPACE                      CONTENTS  SEGTYPE     SEGFILE#    SEGBLK#
------------------------------- --------- --------- ---------- ----------
   EXTENTS     BLOCKS   SEGRFNO#
---------- ---------- ----------
xifenfei                           xifenfei                           07000002F96ECB30
      10152 07000002AE1C36E0 1362191183 9z69tsx8m2sug
TEMP                            TEMPORARY LOB_DATA         201       3465
         1        128          1
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SQL>
Commit complete.
SQL> SQL>
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Oct 12 10:04:39 2012
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
Connected.
USERNAME                       USER                           SESSION_ADDR
------------------------------ ------------------------------ ----------------
SESSION_NUM SQLADDR             SQLHASH SQL_ID
----------- ---------------- ---------- -------------
TABLESPACE                      CONTENTS  SEGTYPE     SEGFILE#    SEGBLK#
------------------------------- --------- --------- ---------- ----------
   EXTENTS     BLOCKS   SEGRFNO#
---------- ---------- ----------
xifenfei                           xifenfei                           07000002F96ECB30
      10152 07000002AE1C36E0 1362191183 9z69tsx8m2sug
TEMP                            TEMPORARY LOB_DATA         201       3465
         1        128          1
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
--测试脚本2
$ ./test2.sh
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Oct 12 10:03:56 2012
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
SQL> Connected.
SQL>
Session altered.
SQL>
TO_NCLOB('A')
--------------------------------------------------------------------------------
a
SQL> SQL>
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Oct 12 10:03:56 2012
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
Connected.
no rows selected
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SQL>
Commit complete.
SQL> SQL>
SQL*Plus: Release 10.2.0.5.0 - Production on Fri Oct 12 10:03:56 2012
Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
Connected.
no rows selected
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

1.Without event 60025 set (before and after commit):都出现v$tempseg_usage中存在对应记录,而且提交后不能释放Temp LOB space
2.With event 60025 set (before and after commit):都未现v$tempseg_usage中存在对应记录,证明提交后释放Temp LOB space

解决方案
通过上面的试验证明我们可以通过设置event 60025来解决该版本的会话提交后Temp LOB space不能被回收的问题.
我们可以通过在session级别使用”alter session set events ‘60025 trace name context forever’;”来实现。如果想实现全库级别的，但是因为event 60025不能通过system设置生效,所以我们可以通过logon触发器来实现该功能

create or replace trigger sys.login_db after logon on database
begin
execute immediate 'alter session set events ''60025 trace name context forever''';
end;
/

注意这个是ORCLE bug(Bug 5723140 – Temp LOB space not released after commit [ID 5723140.8]),从10.2.0.4开始虽然已经修复了该bug,但是默认情况下:为了更加高效的利用temp,在session未断开前,不自动释放temp 空间,可以通过设置event 60025来强制会话在commit之后就立即释放temp space