<?xml version="1.0" encoding="utf-8" ?><rss version="2.0"><channel><title><![CDATA[wrh_csdn的博客]]></title><description><![CDATA[]]></description><link>https://blog.youkuaiyun.com/wrh_csdn</link><language>zh-cn</language><generator>https://blog.youkuaiyun.com/</generator><copyright><![CDATA[Copyright &copy; wrh_csdn]]></copyright><item><title><![CDATA[标题：ORACLE LONG类型转为VARCHAR2]]></title><link>https://blog.youkuaiyun.com/wrh_csdn/article/details/132731687</link><guid>https://blog.youkuaiyun.com/wrh_csdn/article/details/132731687</guid><author>wrh_csdn</author><pubDate>Thu, 07 Sep 2023 10:04:00 +0800</pubDate><description><![CDATA[-超过3593 会报character string buffer too small 错误。]]></description><category></category></item><item><title><![CDATA[oracle 拆分类型为多行(regexp_substr) 与 多个行汇总合并(listagg(codecname,‘,‘)within group(order by endortype))到一个字段]]></title><link>https://blog.youkuaiyun.com/wrh_csdn/article/details/126467053</link><guid>https://blog.youkuaiyun.com/wrh_csdn/article/details/126467053</guid><author>wrh_csdn</author><pubDate>Mon, 22 Aug 2022 15:42:19 +0800</pubDate><description><![CDATA[oracle 拆分类型为多行(regexp_substr) 与 多个行汇总合并(listagg(codecname,',')within group(order by endortype))到一个字段]]></description><category></category></item><item><title><![CDATA[2021-10-19]]></title><link>https://blog.youkuaiyun.com/wrh_csdn/article/details/120850212</link><guid>https://blog.youkuaiyun.com/wrh_csdn/article/details/120850212</guid><author>wrh_csdn</author><pubDate>Tue, 19 Oct 2021 17:08:50 +0800</pubDate><description><![CDATA[ORACLE-MD5

定义：
create or replace function F_GET_MD5(input_string VARCHAR2) return VARCHAR2 IS
raw_input RAW(128) := UTL_RAW.CAST_TO_RAW(input_string);
decrypted_raw RAW(2048);
error_in_input_buffer_length EXCEPTION;
BEGIN
s...]]></description><category></category></item><item><title><![CDATA[ORA-01840: 输入值对于日期格式不够长]]></title><link>https://blog.youkuaiyun.com/wrh_csdn/article/details/118992112</link><guid>https://blog.youkuaiyun.com/wrh_csdn/article/details/118992112</guid><author>wrh_csdn</author><pubDate>Thu, 22 Jul 2021 09:58:42 +0800</pubDate><description><![CDATA[解决办法：




]]></description><category></category></item><item><title><![CDATA[连续年份判断]]></title><link>https://blog.youkuaiyun.com/wrh_csdn/article/details/106192515</link><guid>https://blog.youkuaiyun.com/wrh_csdn/article/details/106192515</guid><author>wrh_csdn</author><pubDate>Mon, 18 May 2020 14:37:13 +0800</pubDate><description><![CDATA[连续年份判断
–存在不连续投保年份的被保险人
create table tmp_wrh_no_insuredcode as
select distinct tt.insuredcode from (
select mmmm.,
(case
when mmmm.aa is null then
1 --起始第一条记录标识
else
mmmm.startdate_yyyy - mmmm.aa
end) flag --后一条记录年份减前一条记录年份
from (select mmm.,
lag(mmm.startd]]></description><category></category></item><item><title><![CDATA[关于物化视图增量刷新报ORA-12018 问题的解决方案]]></title><link>https://blog.youkuaiyun.com/wrh_csdn/article/details/100740177</link><guid>https://blog.youkuaiyun.com/wrh_csdn/article/details/100740177</guid><author>wrh_csdn</author><pubDate>Wed, 11 Sep 2019 16:44:59 +0800</pubDate><description><![CDATA[由于表之前采用的是全量刷新方式进行刷新，但是因为表的数据量越来越大，全量刷新的时候偶尔会出现失败的情况，因为同一个时点刷新的任务比较多，回滚段被占满了之后会出现报错，所以急需要解决这个问题。

发现源表在源系统里是有主键的，所以决定采用增量刷新来解决刷新失败的问题。

1.让DBA帮忙创建 MV LOG

create materialized view log on HX_KPXT.FPGL_F...]]></description><category></category></item><item><title><![CDATA[oracle分析函数系列之sum(col1) over(partition by col2 order by col3):实现分组汇总或递增汇总]]></title><link>https://blog.youkuaiyun.com/wrh_csdn/article/details/100138043</link><guid>https://blog.youkuaiyun.com/wrh_csdn/article/details/100138043</guid><author>wrh_csdn</author><pubDate>Thu, 29 Aug 2019 14:54:25 +0800</pubDate><description><![CDATA[oracle分析函数系列之sum(col1) over(partition by col2 order by col3):实现分组汇总或递增汇总

rfb0204421 分享于 2012-06-18

2019阿里云全部产品优惠券(新购或升级都可以使用，强烈推荐)领取地址：https://promotion.aliyun.com/ntms/yunparter/invite.html



语法:...]]></description><category></category></item><item><title><![CDATA[oracle查询A表中主键都被哪些表引用了]]></title><link>https://blog.youkuaiyun.com/wrh_csdn/article/details/88742449</link><guid>https://blog.youkuaiyun.com/wrh_csdn/article/details/88742449</guid><author>wrh_csdn</author><pubDate>Fri, 22 Mar 2019 15:28:57 +0800</pubDate><description><![CDATA[oracle查询A表中主键都被哪些表引用了


			1

			2

			3

			4

			5
			
			
			selectr.TABLE_NAME

			fromUSER_CONSTRAINTS p, USER_CONSTRAINTS r

			wherep.TABLE_NAME ='IAM_AUDIT_FINDING'

			andr.CONSTRAINT_TY...]]></description><category></category></item><item><title><![CDATA[物化视图刷新-落地表-调度过程]]></title><link>https://blog.youkuaiyun.com/wrh_csdn/article/details/88407760</link><guid>https://blog.youkuaiyun.com/wrh_csdn/article/details/88407760</guid><author>wrh_csdn</author><pubDate>Mon, 11 Mar 2019 20:25:12 +0800</pubDate><description><![CDATA[--1.create materialized view log on YWUSER.BIAUTOPRICEXOM with primary key; --在dblink远程表创建log

--1.在ods用户下创建表，删表空间，主键，添加lastmodifydate 默认系统时间，注释
create table BIAUTOPRICEXOM
(
 autopricedemandno V...]]></description><category></category></item><item><title><![CDATA[oracle数据库定时任务dbms_job的用法详解]]></title><link>https://blog.youkuaiyun.com/wrh_csdn/article/details/88383109</link><guid>https://blog.youkuaiyun.com/wrh_csdn/article/details/88383109</guid><author>wrh_csdn</author><pubDate>Sun, 10 Mar 2019 20:14:42 +0800</pubDate><description><![CDATA[一、dbms_job涉及到的知识点

1、创建job：

?


			1

			2

			3

			4

			5

			6
			
			
			variable jobno number;

			dbms_job.submit(:jobno, —-job号 

			'your_procedure;',—-执行的存储过程, ';'不能省略 

			next_date, —-下...]]></description><category></category></item><item><title><![CDATA[查看Oracle中存储过程长时间被卡住的原因]]></title><link>https://blog.youkuaiyun.com/wrh_csdn/article/details/88353476</link><guid>https://blog.youkuaiyun.com/wrh_csdn/article/details/88353476</guid><author>wrh_csdn</author><pubDate>Fri, 08 Mar 2019 16:14:36 +0800</pubDate><description><![CDATA[查看Oracle中存储过程长时间被卡住的原因

1：查V$DB_OBJECT_CACHE

SELECT * FROM V$DB_OBJECT_CACHE WHERE name='CUX_OE_ORDER_RPT_PKG' AND LOCKS!='0';

注意：CUX_OE_ORDER_RPT_PKG 为存储过程的名称。

发现locks＝2

2：按对象查出sid的值

select /*+...]]></description><category></category></item><item><title><![CDATA[Oracle中查看正在运行的SQL进程]]></title><link>https://blog.youkuaiyun.com/wrh_csdn/article/details/88338637</link><guid>https://blog.youkuaiyun.com/wrh_csdn/article/details/88338637</guid><author>wrh_csdn</author><pubDate>Fri, 08 Mar 2019 10:03:19 +0800</pubDate><description><![CDATA[Oracle show full processlist



复制代码代码如下:


set linesize 400;
set pagesize 400;
set long 4000;
col SQL_FULLTEXT format a100;
col machine format a25;
col username format a15;
SELECT a.username,a.machi...]]></description><category></category></item><item><title><![CDATA[ORA-01652:无法通过512（在表空间REPORT_TMP中）扩展 temp 段]]></title><link>https://blog.youkuaiyun.com/wrh_csdn/article/details/88338197</link><guid>https://blog.youkuaiyun.com/wrh_csdn/article/details/88338197</guid><author>wrh_csdn</author><pubDate>Fri, 08 Mar 2019 09:56:07 +0800</pubDate><description><![CDATA[ORA-01652:无法通过128（在表空间ZLTOOLSTMP中）扩展 temp 段



客户端使用时报错：ORA-01652:无法通过128（在表空间ZLTOOLSTMP中）扩展 temp 段。

查看临时表空间情况：

select tablespace_name,file_name,bytes/1024/1024 MB,autoextensible fromdba_temp_files...]]></description><category></category></item><item><title><![CDATA[ORA-00600: 内部错误代码]]></title><link>https://blog.youkuaiyun.com/wrh_csdn/article/details/88252393</link><guid>https://blog.youkuaiyun.com/wrh_csdn/article/details/88252393</guid><author>wrh_csdn</author><pubDate>Wed, 06 Mar 2019 19:54:07 +0800</pubDate><description><![CDATA[　　运行时报ORA-00600: 内部错误代码, 参数:[qcsgpvc3],[],[],[],[],[],[],[],[],[],[],[] ，上网搜了但大多数都不行。无心插柳柳成荫，就在无奈想要换个SQL方法时

看到了一篇看起来挺有用的文章，上面说 “问题应该是Oracle在编译包体时，没有区分表的列和过程的参数的区别，从而引发了这个错误。而Oracle给出的建议也是，

避免过程或...]]></description><category></category></item><item><title><![CDATA[查看是否有锁表和根据sid查看对应连接正在运行的sql]]></title><link>https://blog.youkuaiyun.com/wrh_csdn/article/details/88194324</link><guid>https://blog.youkuaiyun.com/wrh_csdn/article/details/88194324</guid><author>wrh_csdn</author><pubDate>Tue, 05 Mar 2019 18:12:50 +0800</pubDate><description><![CDATA[---查看是否有锁表

Select
   l.session_id sid,
   s.serial#,
   l.locked_mode 锁模式,
   l.oracle_username 登录用户,
   l.os_user_name 登录机器用户名,
   s.machine 机器名,
   s.terminal 终端用户名,
 ...]]></description><category></category></item><item><title><![CDATA[oracle 表空间大小和使用情况]]></title><link>https://blog.youkuaiyun.com/wrh_csdn/article/details/88187318</link><guid>https://blog.youkuaiyun.com/wrh_csdn/article/details/88187318</guid><author>wrh_csdn</author><pubDate>Tue, 05 Mar 2019 16:19:51 +0800</pubDate><description><![CDATA[SELECT a.tablespace_name &quot;表空间名&quot;,
   total &quot;表空间大小&quot;,
   free &quot;表空间剩余大小&quot;,
   (total - free) &quot;表空间使用大小&quot;,
   total / (1024 * 1024 * 1024) &quot;表空间大小(G)&quot;,
   free / (1024 * 1024 * 1024) &quot;表空间剩余...]]></description><category></category></item><item><title><![CDATA[Oracle Job的使用(定时执行)]]></title><link>https://blog.youkuaiyun.com/wrh_csdn/article/details/87930487</link><guid>https://blog.youkuaiyun.com/wrh_csdn/article/details/87930487</guid><author>wrh_csdn</author><pubDate>Tue, 26 Feb 2019 10:40:07 +0800</pubDate><description><![CDATA[Oracle Job的使用(定时执行)

oracle中的job能为你做的就是在你规定的时间格式里执行存储过程，定时执行一个任务 。下面是一个小案例，定时每15分钟向一张表插入一条数据

一

        1.创建一张测试表




-- Create table
create table A8
(
  a1 VARCHAR2(500)
)
tablespace DSP_DATA
  pctf...]]></description><category></category></item><item><title><![CDATA[创建物化视图 报错 ora-12060:预建表的形式与定义查询不匹配]]></title><link>https://blog.youkuaiyun.com/wrh_csdn/article/details/87369458</link><guid>https://blog.youkuaiyun.com/wrh_csdn/article/details/87369458</guid><author>wrh_csdn</author><pubDate>Fri, 15 Feb 2019 16:37:08 +0800</pubDate><description><![CDATA[如果以PREBUILD方式建立物化视图，除非指定了WITH REDUCED PRECISION语句(在on prebuilt table后面加上with reduced precision就可以了)，否则列的精度必须满足SELECT表达式的精度；

--1.在ods用户下创建表，删表空间，主键，添加lastmodifydate 默认系统时间，注释
create table FPGL_FPXX
(
...]]></description><category></category></item><item><title><![CDATA[mysql 出现 "1067 - Invalid default value for 'UPDATE_TIME' " 错误提示的解决办法]]></title><link>https://blog.youkuaiyun.com/wrh_csdn/article/details/82223228</link><guid>https://blog.youkuaiyun.com/wrh_csdn/article/details/82223228</guid><author>wrh_csdn</author><pubDate>Thu, 30 Aug 2018 16:19:55 +0800</pubDate><description><![CDATA[问题：

由于 tmp_wrh_1 的字段UPDATE_TIME 的字段类型是 timestamp ，默认值是：'0000-00-00 00:00:00'

即：`UPDATE_TIME` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '更新时间';

在对这个表进行操作的时候，

如：alter table tmp_wrh_1 ...]]></description><category></category></item><item><title><![CDATA[查看scheduler_job的执行情况]]></title><link>https://blog.youkuaiyun.com/wrh_csdn/article/details/81974630</link><guid>https://blog.youkuaiyun.com/wrh_csdn/article/details/81974630</guid><author>wrh_csdn</author><pubDate>Thu, 23 Aug 2018 09:27:22 +0800</pubDate><description><![CDATA[ 

select * from user_scheduler_job_run_details t where t.LOG_DATE&amp;gt;=date'2018-08-20' and t.STATUS&amp;lt;&amp;gt;'SUCCEEDED';

执行结果：

]]></description><category></category></item></channel></rss>