现场报问题，现场用户操作系统功能报错，分析日志知道临时表空间不够用。
### 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-
selects.sql_id,count(1)cc
fromdba_hist_active_sess_historys
wheres.event='directpathwritetemp'
ors.event='directpathreadtemp'
groupbysql_id
orderbyccdesc
fromdba_hist_active_sess_historys
wheres.event='directpathwritetemp'
ors.event='directpathreadtemp'
groupbysql_id
orderbyccdesc
就是这条，我记得以前处理过类似的情况，于是就收集系统字典的统计信息：
select(selectcount(*)fromv$lock)alllock,(selectcount(*)fromv$locked_object)deadlockfromdual
execdbms_stats.gather_fixed_objects_stats();
| 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 | 3,391.46 | 205 | 16.54 | 1.61 | 90.12 | 8.47 | 6uz4za48wf6j7 | java.exe | select (select count(*) from v... |
