? 现场报问题,现场用户操作系统功能报错,分析日志知道临时表空间不够用。
### 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...