Oracle 问题处理

2021-11-2

监听程序找不到********可用处理程序

原因：会话达到最大值

治标：重新启动数据库实例

治本：会话为INACTIVE的会话在不响应一段时间之后需要释放资源，使用存储过程和定时器来做较好

可以参考的例子：ORACLE数据库连接数满的分析及优化 - zhuntidaoren - 博客园 (cnblogs.com)

2021-11-15

同义词

在不同用户之间更方便的访问表

可以参考的例子：ORACLE同义词总结 - 潇湘隐者 - 博客园 (cnblogs.com)

查找多个字段重复的数据：这个麻烦

1：数据查重

SELECT T.*,T.ROWID FROM PRODFACT T WHERE
(T.VIN,T.FACTTYPE) IN (SELECT S.VIN,S.FACTTYPE FROM PRODFACT S
                        WHERE S.FACTTYPE IN ('30','31','32')
                        GROUP BY S.VIN,S.FACTTYPE
                        HAVING COUNT(1) > 1) ORDER BY NO

-------------------------------------------------------------------------------------------

select t.tugno,t.tugrowno,t.recno from weekaskmrp t

where (t.tugno,t.tugrowno) in
(select s.tugno,s.tugrowno  from soleaskorder s
where s.askno in ('YH202201040003','YH202201200029') and s.seqplanno is  null)  
group by t.tugno,t.tugrowno ,t.recno
having count(1) > 1

2：创建临时表，存放重复的数据，要有唯一递增的字段。会是完全重复的数据，两行

insert into weektugonc(planno,compno,tugno,tugrowno,rn,cc) select planno,compno,tugno,tugrowno,rn,f_GetNo('AskNo') cc
  from (select t1.planno,t1.compno,t1.tugno,t1.tugrowno,
               count(1) over(partition by t1.planno,t1.compno,t1.tugno,t1.tugrowno) rn
          from weekaskmrp t1  where t1.year = '2022'    ) t1
 where t1.rn > 1

3：依据唯一递增的字段，将重复的数据进行删除，只保留一行

delete from weektugonc t
 where t.cc not in
       (select max(cc) from weektugonc t1 group by t1.planno,t1.compno,t1.tugno,t1.tugrowno);

4：原数据中查找不重复的数据

select t.*,t.rowid from weekaskmrp t
where t.year = '2022'  and  t.recno  in ('2')
and (t.planno,t.compno,t.tugno,t.tugrowno) not in (
select t1.planno,t1.compno,t1.tugno,t1.tugrowno from weektugonc t1
)

5：数据分组后再行转列

--分组后再套一层再行转列
select to_char(wm_concat(vnm)) from 
(select vnm from sss where clxh = 'DFD4250GL6N1'  group by vnm) t