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

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

### The error may exist in data/code/oms/EAR/APP-INF/lib/gg-cap-runtime-top-access-20150527.jar!/com/gg/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) ~[gg-top-corm-3.1.2.jar:na]
at gg.corm.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:104) ~[gg-top-corm-3.1.2.jar:na]
at gg.corm.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:95) ~[gg-top-corm-3.1.2.jar:na]
at gg.top.core.base.dao.CoreDAO$8.execute(CoreDAO.java:390) ~[gg-cap-runtime-top-core-20150527.jar:1.0]
at gg.top.core.base.dao.CoreDAO.executeDefault(CoreDAO.java:701) ~[gg-cap-runtime-top-core-20150527.jar:1.0]
at gg.top.core.base.dao.CoreDAO.execute(CoreDAO.java:683) ~[gg-cap-runtime-top-core-20150527.jar:1.0]
at gg.top.core.base.dao.CoreDAO.queryList(CoreDAO.java:385) ~[gg-cap-runtime-top-core-20150527.jar:1.0]

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

诊断这种问题，以前诊断过多起，通过下面的SQL我定位到了问题SQL。

select?s.sql_id,?count(1)?cc?
???from?dba_hist_active_sess_history?s
??where?s.event?=?'direct?path?write?temp'
?????or?s.event?=?'direct?path?read?temp'
??group?by?sql_id
??order?by?cc?desc

就是这条，我记得以前处理过类似的情况，于是就收集系统字典的统计信息：

select?(select?count(*)?from?v$lock)?alllock,?(select?count(*)?from?v$locked_object)deadlock?from?dual

exec?dbms_stats.gather_fixed_objects_stats();?

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

Elapsed Time (s)    Executions    Elapsed Time per Exec (s)    %Total    %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    JDBC Thin Client    SELECT t1.* FROM (SELECT t0.*,...
3,391.46    205    16.54    1.61    90.12    8.47    6uz4za48wf6j7    java.exe    select (select count(*) from v...