分类目录归档：数据库

修改clob字段并插入新表

发表于 2011 年 12 月 14 日由惜分飞

最近开发有个需求，需要替换一张表的clob字段中的某些字符串，然后插入的一张新表中，我查询了一些资料，利用function结合匿名块模拟实现
0、数据库版本

SQL> select * from v$version;

BANNER
-----------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

1、创建替换clob中字符串函数

create or replace function replaceClob_new (
srcClob IN CLOB,
replaceStr IN VARCHAR2,
replaceWith IN VARCHAR2
--newClob OUT CLOB
) RETURN CLOB
IS

vBuffer    VARCHAR2 (32767);
l_amount   BINARY_INTEGER := 32767;
l_pos      PLS_INTEGER := 1;
l_clob_len PLS_INTEGER;
newClob  CLOB;

BEGIN
   newClob  := EMPTY_CLOB;
  -- initalize the new clob
 dbms_lob.createtemporary(newClob,TRUE);
 l_clob_len := dbms_lob.getlength(srcClob);
 WHILE l_pos < l_clob_len
  LOOP
    dbms_lob.read(srcClob, l_amount, l_pos, vBuffer);
    IF vBuffer IS NOT NULL THEN
      -- replace the text
      vBuffer := replace(vBuffer, replaceStr, replaceWith);
      -- write it to the new clob
      dbms_lob.writeappend(newClob, LENGTH(vBuffer), vBuffer);
    END IF;
    l_pos := l_pos + l_amount;
  END LOOP;
  RETURN newclob;
  
 EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END;

2、创建原表，并测试该函数

SQL> CREATE TABLE t_clob(ID NUMBER,clob_xff CLOB);
 
Table created

--手工通过plsql dev插入一些数据（大于4000）

SQL> select dbms_lob.getlength(clob_xff) from t_clob;
 
DBMS_LOB.GETLENGTH(CLOB_XFF)
----------------------------
                        4856

SQL> select dbms_lob.getlength(replaceClob_new(clob_xff,'function','1')) from t_clob;
 
DBMS_LOB.GETLENGTH(REPLACECLOB
------------------------------
                          4814

SQL> set long 50
SQL> select clob_xff from t_clob where id=1;

CLOB_XFF
--------------------------------------------------
create or replace function replaceClob_new (
srcCl

SQL> select replaceClob_new(clob_xff,'function','1')
  2  from t_clob where id=1;

REPLACECLOB_NEW(CLOB_XFF,'FUNCTION','1')
--------------------------------------------------
create or replace 1 replaceClob_new (
srcClob IN C

3、编写匿名块实现插入功能

DECLARE
  A_CLOB CLOB;
  R_CLOB CLOB;
  VSTRT  NUMBER(4);
BEGIN
--否则会在新表的clob字段头部会有空格
  VSTRT := 1;
  SELECT REPLACECLOB_NEW(CLOB_XFF, 'function', 'xifenfei')
    INTO R_CLOB
    FROM T_CLOB
   WHERE ID = 1;
  INSERT INTO T_CLOB_NEW
    (ID, CLOB_XFF)
  VALUES
    (1, EMPTY_CLOB())
  RETURNING CLOB_XFF INTO A_CLOB;
  DBMS_LOB.WRITE(A_CLOB, DBMS_LOB.GETLENGTH(R_CLOB), VSTRT, R_CLOB);
  COMMIT;
END;

4、测试匿名块功能

SQL> set long 50
SQL> select clob_xff from t_clob where id=1;

CLOB_XFF
--------------------------------------------------
create or replace function replaceClob_new (
srcCl

SQL> select clob_xff from t_clob_new where id=1;

CLOB_XFF
--------------------------------------------------
create or replace xifenfei replaceClob_new (
srcCl

参考:http://space.itpub.net/111631/viewspace-605827

发表在 Oracle 开发 | 评论关闭

kkjcre1p: unable to spawn jobq slave process, error 1089

发表于 2011 年 12 月 12 日由惜分飞

闲着无聊，看历史的alert文件，发现kkjcre1p: unable to spawn jobq slave process, error 1089警告

Thu Jul  8 21:23:39 2010
Starting background process CJQ0
CJQ0 started with pid=18, OS id=9019
Thu Jul  8 21:23:39 2010
Shutting down instance: further logons disabled
Thu Jul  8 21:23:42 2010
kkjcre1p: unable to spawn jobq slave process, error 1089
………………
Thu Jul  8 21:23:57 2010
kkjcre1p: unable to spawn jobq slave process, error 1089
Thu Jul  8 21:23:59 2010
Stopping background process QMNC
Thu Jul  8 21:23:59 2010
Stopping background process CJQ0
Thu Jul  8 21:24:01 2010
Stopping background process MMNL
Thu Jul  8 21:24:02 2010
Stopping background process MMON
Thu Jul  8 21:24:03 2010
Shutting down instance (immediate)
License high water mark = 1
Waiting for dispatcher 'D000' to shutdown
All dispatchers and shared servers shutdown
Thu Jul  8 21:24:05 2010
ALTER DATABASE CLOSE NORMAL
Thu Jul  8 21:24:05 2010
SMON: disabling tx recovery
SMON: disabling cache recovery
Thu Jul  8 21:24:05 2010
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Thread 1 closed at log sequence 2
Successful close of redo thread 1
Thu Jul  8 21:24:05 2010
Completed: ALTER DATABASE CLOSE NORMAL
Thu Jul  8 21:24:05 2010
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archival disabled due to shutdown: 1089
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active

警告原因
If a job is about to be spawned when shutdown of database is in progress, you will see these errors in the alert log file and this is perfectly valid.

解决方法
1、这个警告可以安全的忽略
There is no harm at all because of this warning being logged to the alert.log The Error can be safely ignored as the job coordinator process tried to spawn a job slave when the Shutdown was in progress.

2、设置_JOB_QUEUE_INTERVAL更大值，减少出现该警告概率
One workaround that we can suggest is to set an underscore parameter
_JOB_QUEUE_INTERVAL=120 or greater value
The default value is 60 but when we change to 120 there are less chances of getting the above warnings in the alert log file.
Kkjcre1p: Unable To Spawn Jobq Slave Process, Error 1089 [ID 344275.1]

发表在 Oracle | 评论关闭

17813235971

QQ 咨询

分类目录归档：数据库

修改clob字段并插入新表

模拟跨resetlogs恢复

kkjcre1p: unable to spawn jobq slave process, error 1089

文章分类

最近发表

分类目录归档：数据库

修改clob字段并插入新表

模拟跨resetlogs恢复

kkjcre1p: unable to spawn jobq slave process, error 1089

标签云

文章分类

最近发表