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

发布时间：2020-12-12 16:33:43 所属栏目：百科来源：网络整理

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

100,251.96432,331.4447.5199.370.0483aqtgsb349ymJDBC Thin ClientSELECT t1.* FROM (SELECT t0.*,...7,591.859997.603.6097.440.00fnbvjwzzstuuqJDBC Thin Clientselect '["'||BAY_NAME||'","'|...4,802.936800.492.2899.810.00agxa7pcppy9wh3,391.4620516.541.6190.128.476uz4za48wf6j7java.exeselect (select count(*) from v...

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

现场报问题，现场用户操作系统功能报错，分析日志知道临时表空间不够用。 ### 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- 诊断这种问题，以前诊断过多起，通过下面的SQL我定位到了问题SQL。 selects.sql_id,count(1)cc 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(); 收集之后还是有问题，后面我看数据库报告，发现这条SQL是java.exe调用的，晕倒，是人为调用的。通过v$session定位到第三方运维厂家监控程序导致。
Elapsed Time (s)	Executions	Elapsed Time per Exec (s)	%Total	%CPU	%IO	SQL Id	SQL Module	SQL Text

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

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

诊断这种问题，以前诊断过多起，通过下面的SQL我定位到了问题SQL。 selects.sql_id,count(1)cc
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();
收集之后还是有问题，后面我看数据库报告，发现这条SQL是java.exe调用的，晕倒，是人为调用的。通过v$session定位到第三方运维厂家监控程序导致。

Elapsed Time (s)

Executions

Elapsed Time per Exec (s)

%Total

%CPU

%IO

SQL Id

SQL Module

SQL Text