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

古城微笑少年丶 2022-06-18 02:14 138阅读 0赞

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

\#\#\# 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...