Oracle临时表空间不够用一次经验主义导致贻误战机

现场报问题，现场用户操作系统功能报错，分析日志知道临时表空间不够用。

### The error may exist in data/code/oms/EAR/APP-INF/lib/comtop-cap-runtime-top-access-20150527.jar!/com/comtop/top/sys/accesscontrol/service/dbconfig/UserFuncsql.xml

### The error may involve gg.top.sys.accesscontrol.service.model.queryAllChildMenu-Inline
### The error occurred while setTing parameters
### sql: WITH T_FUNC AS (SELECT F.* FROM top_PER_FUNC F WHERE F.STATUS = 1 AND F.FUNC_NODE_TYPE = 2 AND ((F.PERMISSION_TYPE = 2 AND EXISTS (SELECT 1 FROM v_subject_permission P WHERE P.subject_id = ? AND P.SUBjeCT_CLASSIFY_CODE = 'USER' AND P.resource_id = F.FUNC_ID AND P.resourcE_TYPE_CODE = 'FUNC')) OR F.PERMISSION_TYPE = 1) START WITH F.PARENT_FUNC_ID = ? CONNECT BY PRIOR F.FUNC_ID = F.PARENT_FUNC_ID) SELECT T1.*,decode(B.menu_id,NulL,2,1) isFollow FROM (SELECT TF.* FROM T_FUNC TF UNION SELECT F1.* FROM top_PER_FUNC F1 WHERE F1.STATUS = 1 AND F1.FUNC_NODE_TYPE = 1 AND F1.PARENT_FUNC_ID = ? AND EXISTS (SELECT 1 FROM T_FUNC F2 WHERE F1.FUNC_ID = F2.PARENT_FUNC_ID )) T1 left JOIN top_PER_CUSTOM_MENU B ON T1.func_ID = B.menu_id AND B.user_iD = ? ORDER BY T1.soRT_NO ASC
### Cause: java.sql.sqlException: ORA-01652: 无法通过 128 (在表空间 TEMP 中) 扩展 temp 段
at gg.corm.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:23) ~[comtop-top-corm-3.1.2.jar:na]
at gg.corm.session.defaults.DefaultsqlSession.SELEctList(DefaultsqlSession.java:104) ~[comtop-top-corm-3.1.2.jar:na]
at gg.corm.session.defaults.DefaultsqlSession.SELEctList(DefaultsqlSession.java:95) ~[comtop-top-corm-3.1.2.jar:na]
at gg.top.core.base.dao.CoreDAO$8.execute(CoreDAO.java:390) ~[comtop-cap-runtime-top-core-20150527.jar:1.0]
at gg.top.core.base.dao.CoreDAO.executeDefault(CoreDAO.java:701) ~[comtop-cap-runtime-top-core-20150527.jar:1.0]
at gg.top.core.base.dao.CoreDAO.execute(CoreDAO.java:683) ~[comtop-cap-runtime-top-core-20150527.jar:1.0]
at gg.top.core.base.dao.CoreDAO.queryList(CoreDAO.java:385) ~[comtop-cap-runtime-top-core-20150527.jar:1.0]

at gg.top.sys.accesscontrol.service.appservice.UserFuncAppservice.queryAllChildMenu(UserFuncAppservice.java:249) ~[comtop-cap-runtime-top-access-

SELEct(SELEctcount(*)fromv$lock)alllock,(SELEctcount(*)fromv$locked_object)deadlockfromdual

收集之后还是有问题，后面我看数据库报告，发现这条sql是java.exe调用的，晕倒，是人为调用的。通过v$session定位到第三方运维厂家监控程序导致。

Elapsed Time (s)	EXECUTIONS	Elapsed Time per Exec (s)	%@R_105_10586@l	%cpu	%IO	sql ID	sql Module	sql Text
100,251.96	43	2,331.44	47.51	99.37	0.04	83aqtgsb349ym	JDBC Thin ClIEnt	SELECT t1.* FROM (SELECT t0.*,...
7,591.85	999	7.60	3.60	97.44	0.00	fnbvjwzzstuuq	JDBC Thin ClIEnt	SELEct '["'||BAY_name||'","'|...
4,802.93	6	800.49	2.28	99.81	0.00	agxa7pcppy9wh	BACkground:rgb(255,...
3,391.46	205	16.54	1.61	90.12	8.47	6uz4za48wf6j7	java.exe	SELEct (SELEct count(*) from v...