ORA-03114 通信信道问题解决
问题描述：今天业务人员反映执行一个调度SQL语句时报ORA-03114的问题 通过一天的排查已解决 整理如下：
1 排查数据库后台表空间使用情况：
SELECT D.TABLESPACE_NAME,
SPACE || ‘M’ “SUM_SPACE(M)”,
BLOCKS “SUM_BLOCKS”,
SPACE - NVL (FREE_SPACE, 0) || ‘M’ “USED_SPACE(M)”,
ROUND ( (1 - NVL (FREE_SPACE, 0) / SPACE) * 100, 2) ||
‘%’
“USED_RATE(%)”,
FREE_SPACE || ‘M’ “FREE_SPACE(M)”
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2)
FREE_SPACE
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
UNION ALL
–如果有临时表空间
SELECT D.TABLESPACE_NAME,
SPACE || ‘M’ “SUM_SPACE(M)”,
BLOCKS SUM_BLOCKS,
USED_SPACE || ‘M’ “USED_SPACE(M)”,
ROUND (NVL (USED_SPACE, 0) / SPACE * 100, 2) || ‘%’
“USED_RATE(%)”,
NVL (FREE_SPACE, 0) || ‘M’ “FREE_SPACE(M)”
FROM ( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES) / (1024 * 1024), 2) SPACE,
SUM (BLOCKS) BLOCKS
FROM DBA_TEMP_FILES
GROUP BY TABLESPACE_NAME) D,
( SELECT TABLESPACE_NAME,
ROUND (SUM (BYTES_USED) / (1024 * 1024), 2)
USED_SPACE,
ROUND (SUM (BYTES_FREE) / (1024 * 1024), 2)
FREE_SPACE
FROM V$TEMP_SPACE_HEADER
GROUP BY TABLESPACE_NAME) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
ORDER BY 1;
2、清理数据库归档问题：
rman target /
crosscheck backup;
delete obsolete;
delete expired backup;
crosscheck archivelog all;
delete expired archivelog all;
此步会有提示，输入 YES 回车
退出rman 删除完成，重新连接数据库
删除完毕后查看结果：
sqlplus /nolog
SQL> connect /as sysdba
SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;
清理完毕
3、排查undo表空间和undo_retention参数 小改大
SYS@vsensjf > alter system set undo_retention=10800;
System altered.
SYS@vsensjf > show parameter undo
NAME TYPE VALUE
undo_management string AUTO
undo_retention integer 10800
undo_tablespace string UNDOTBS1
4、修改一个隐藏参数
https://www.cnblogs.com/mellowsmile/p/5900890.html
alter system set “_complex_view_merging”=false scope=both;
再次执行 已成功。