[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 rownumERROR:
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 rownumC60
------------------------------------------------------------
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