[20141204]11G关于使用wmsys.wm_concat的问题.txt

--今天开发提示一些sql语句在dataguard查询时报错，因为dataguard是read only打开，一些dml语句是不支持的，
--没看语句之前，以为是语句里面使用sequence，仔细检查发现没有，而发现使用了wmsys.wm_concat。
--使用wmsys.wm_concat对象实现行列转换。

--感到奇怪的是在我搭建的测试环境没有问题，能正常执行。
SCOTT@test> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- -------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SCOTT@test> select wmsys.wm_concat(dname) c60 from dept;
C60
------------------------------------------------------------
ACCOUNTING,RESEARCH,SALES,OPERATIONS,aaa,cc,aaaa

--补充如果使用sequence，出现如下错误。
SCOTT@testdg> SELECT seq1.NEXTVAL FROM dual;
SELECT seq1.NEXTVAL FROM dual
                         *
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-16000: database open for read-only access

SCOTT@testdg> select wmsys.wm_concat(dname) c60 from dept;
C60
------------------------------------------------------------
ACCOUNTING,RESEARCH,SALES,OPERATIONS,aaa,cc,aaaa

--再仔细看提示，很明显要读写临时文件。
XXXX@dg> select wmsys.wm_concat(table_name) from tabs where rownum<=3;
ERROR:
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/u01/app/oracle/oradata/dbcndg/tempfile/temp.304.862160591'
ORA-06512: at "WMSYS.WM_CONCAT_IMPL", line 31
no rows selected

$ ls -l /u01/app/oracle/oradata/dbcndg/tempfile/temp.304.862160591
ls: /u01/app/oracle/oradata/dbcndg/tempfile/temp.304.862160591: No such file or directory

--才发现临时文件没有建立。看来自己工作太不认真。
--然而自己很不理解的是这样的语句即不是排序，如何会使用临时文件呢？

SCOTT@test> desc wmsys.wm_concat
FUNCTION wmsys.wm_concat RETURNS CLOB
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
P1                             VARCHAR2                IN

--噢，发现函数的返回类型是CLOB，作为clob保存是使用临时文件的。
--可以查看eygle的链接
http://www.eygle.com/archives/2006/03/lob_and_temporary_tablespace.html

--建立临时文件后，问题解决!
--小插曲
SYS@dbcndg> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/dbcndg/tempfile/temp.304.862160591' REUSE;
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/dbcndg/tempfile/temp.304.862160591' REUSE
*
ERROR at line 1:
ORA-01537: cannot add file '/u01/app/oracle/oradata/dbcndg/tempfile/temp.304.862160591' - file already part of database

--仔细检查发现tempfile目录没有建立，实际上建立了，11G做dg会自动完成的建立。建立相应目录。
--重启dataguard。检查目录：

$ ls -l /u01/app/oracle/oradata/dbcndg/tempfile/temp.304.862160591
-rw-r----- 1 oracle oinstall 20979712 2014-12-04 12:00:08 /u01/app/oracle/oradata/dbcndg/tempfile/temp.304.862160591

--ok,问题解决。

XXXX@dbcndg> select wmsys.wm_concat(table_name) c60  from tabs where rownum<=3;
C60
------------------------------------------------------------
AMQC_SYSQ01,AMQC_SYSQ02,ATF_DRUG

XXXX@dbcndg>select s.username, s.sid, u.tablespace, u.contents, u.segtype,    
round(u.blocks*8192/1024/1024,2) MB                               
from v$session s, v$sort_usage u                                  
where s.saddr = u.session_addr                                    
and u.contents = 'TEMPORARY'                                      
order by MB DESC ;        

USERNAME      SID TABLESPACE   CONTENTS  SEGTYPE           MB
------------ ---- ------------ --------- --------- ----------
XXXXXXXX     1264 TEMP         TEMPORARY LOB_DATA           1

--如果两个用户执行类似语句：

XXXX@dbcndg> /
USERNAME                    SID TABLESPACE                      CONTENTS  SEGTYPE           MB
-------------------- ---------- ------------------------------- --------- --------- ----------
SYS                        5030 TEMP                            TEMPORARY LOB_DATA           1
XXXXXXXX                   1264 TEMP                            TEMPORARY LOB_DATA           1

--可以发现这个问题如果大量用户这样执行会大量消耗临时文件，因为临时文件的定义EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;看来应该叫开发停止使用它。
--使用别的方法代替。

--很奇怪的是在10g下，函数wmsys.wm_concat的返回是VARCHAR2。
SYS@test> @http://192.168.100.40/sqllaji/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx            10.2.0.4.0     Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

SYS@test> desc  wmsys.wm_concat
FUNCTION wmsys.wm_concat RETURNS VARCHAR2
Argument Name                  Type                    In/Out Default?
------------------------------ ----------------------- ------ --------
P1                             VARCHAR2                IN