Oracle游标[you biao]大全

1.Oracle游标[youbiao]大全   SELECT语句[yuju]用于[yongyu]从数据[shuju]库[shujuku]中查询数据[shuju]，当在PL/SQL中使用SELECT语句[yuju]时，要与INTO子句[ziju]一起使用，查询的返回值被赋予INTO子句[ziju]中的变量[bianliang]，变量[bianliang]的声明[shengming]是在DELCARE中。SELECT            INTO语法[yufa]如下：     SELECT[DISTICT|ALL]{*|column[,column,...]}     INTO(variable[,variable,...]|record)     FROM{table|(sub-query)}[alias]     WHERE............    PL/SQL中SELECT语句[yuju]只返回一行数据[shuju]。如果超过一行数据[shuju]，那么就要使用显式[xianshi]游标[youbiao]（对游标[youbiao]的讨论我们将在后面进行），INTO子句[ziju]中要有与SELECT子句[ziju]中相同列数量的变量[bianliang]。INTO子句[ziju]中也可以是记录变量[bianliang]。 %TYPE属性[shuxing]     在PL/SQL中可以将变量[bianliang]和常量[changliang]声明[shengming]为内建或用户[yonghu]定义的数据[shuju]类型[leixing][shujuleixing]，以引用[yinyong]一个列名，同时继承[jicheng]他的数据[shuju]类型[leixing][shujuleixing]和大小。这种动态[dongtai]赋值[fuzhi]方法[fangfa]是非常有用的，比如变量[bianliang]引用[yinyong]的列的数据[shuju]类型[leixing][shujuleixing]和大小改变了，如果使用了%TYPE,那么用户[yonghu]就不必修改[xiugai]代码[daima]，否则就必须修改[xiugai]代码[daima]。 例：  v_empnoSCOTT.EMP.EMPNO%TYPE;  v_salaryEMP.SALARY%TYPE;  　不但列名可以使用%TYPE,而且变量[bianliang]、游标[youbiao]、记录，或声明[shengming]的常量[changliang]都可以使用%TYPE。这对于定义相同数据[shuju]类型[leixing][shujuleixing]的变量[bianliang]非常有用。    DELCARE    V_ANUMBER(5):=10;    V_BV_A%TYPE:=15;    V_CV_A%TYPE;    BEGIN      DBMS_OUTPUT.PUT_LINE      ('V_A='||V_A||'V_B='||V_B||'V_C='||V_C);    END        SQL>/    V_A=10V_B=15V_C=     PL/SQLproceduresuccessfullycompleted.     SQL>      其他DML语句[yuju]    其它操作数[caozuoshu]据的DML语句[yuju]是:INSERT、UPDATE、DELETE和LOCKTABLE,这些语句[yuju]在PL/SQL中的语法[yufa]与在SQL中的语法[yufa]相同。我们在前面已经讨论过DML语句[yuju]的使用这里就不再重复了。在DML语句[yuju]中可以使用任何在DECLARE部分声明[shengming]的变量[bianliang]，如果是嵌套[qiantao]块，那么要注意变量[bianliang]的作用[zuoyong]范围[fanwei]。 例：  CREATEORREPLACEPROCEDUREFIRE_EMPLOYEE(pempnoinnumber)  　AS    v_enameEMP.ENAME%TYPE;    BEGIN    　SELECTenameINTOv_ename      FROMemp      WHEREempno=p_empno;      INSERTINTOFORMER_EMP(EMPNO,ENAME)      VALUES(p_empno,v_ename);      DELETEFROMemp      WHEREempno=p_empno;      UPDATEformer_emp      SETdate_deleted=SYSDATE      WHEREempno=p_empno;          EXCEPTION       WHENNO_DATA_FOUNDTHEN       DBMS_OUTPUT.PUT_LINE('EmployeeNumberNotFound!');    END DML语句[yuju]的结果    当执行[zhihang]一条DML语句[yuju]后，DML语句[yuju]的结果保存在四个游标[youbiao]属性[shuxing]中，这些属性[shuxing]用于[yongyu]控制[kongzhi]程序流程或者了解程序的状态[zhuangtai]。当运行[yunhang]DML语句[yuju]时，PL/SQL打开一个内建游标[youbiao]并处理结果，游标[youbiao]是维护[weihu]查询结果的内存[neicun]中的一个区域[quyu]，游标[youbiao]在运行[yunhang]DML语句[yuju]时打开，完成后关闭。隐式[yinshi]游标[youbiao]只使用SQL%FOUND,SQL%NOTFOUND,SQL%ROWCOUNT三个属性[shuxing].SQL%FOUND,SQL%NOTFOUND是布尔值，SQL%ROWCOUNT是整数值。 SQL%FOUND和SQL%NOTFOUND    在执行[zhihang]任何DML语句[yuju]前SQL%FOUND和SQL%NOTFOUND的值都是NULL,在执行[zhihang]DML语句[yuju]后，SQL%FOUND的属性[shuxing]值将是： .TRUE:INSERT .TRUEELETE和UPDATE，至少有一行被DELETE或UPDATE. .TRUE:SELECTINTO至少返回一行 当SQL%FOUND为TRUE时,SQL%NOTFOUND为FALSE。 SQL%ROWCOUNT   在执行[zhihang]任何DML语句[yuju]之前，SQL%ROWCOUNT的值都是NULL,对于SELECT            INTO语句[yuju]，如果执行[zhihang]成功，SQL%ROWCOUNT的值为1,如果没有成功，SQL%ROWCOUNT的值为0，同时产生一个异常[yichang]NO_DATA_FOUND. SQL%ISOPEN  SQL%ISOPEN是一个布尔值，如果游标[youbiao]打开，则为TRUE,如果游标[youbiao]关闭，则为FALSE.对于隐式[yinshi]游标[youbiao]而言SQL%ISOPEN总是FALSE，这是因为隐式[yinshi]游标[youbiao]在DML语句[yuju]执行[zhihang]时打开，结束时就立即关闭。 事务控制[kongzhi]语句[yuju][kongzhiyuju]    事务是一个工作的逻辑单元[danyuan]可以包括一个或多个DML语句[yuju]，事物控制[kongzhi]帮助用户[yonghu]保证数据[shuju]的一致性[yizhixing]。如果事务控制[kongzhi]逻辑单元[danyuan]中的任何一个DML语句[yuju]失败，那么整个事务都将回滚，在PL/SQL中用户[yonghu]可以明确地使用COMMIT、ROLLBACK、SAVEPOINT以及SETTRANSACTION语句[yuju]。     COMMIT语句[yuju]终止事务，永久保存数据[shuju]库[shujuku]的变化，同时释放[shifang]所有LOCK,ROLLBACK终止现行事务释放[shifang]所有LOCK，但不保存数据[shuju]库[shujuku]的任何变化,SAVEPOINT用于[yongyu]设置[shezhi]中间点，当事务调用[tiaoyong]过多的数据[shuju]库[shujuku]操作时，中间点是非常有用的，SETTRANSACTION用于[yongyu]设置[shezhi]事务属性[shuxing]，比如read-write和隔离级[geliji]等。 显式[xianshi]游标[youbiao]    当查询返回结果超过一行时，就需要一个显式[xianshi]游标[youbiao]，此时用户[yonghu]不能使用selectinto语句[yuju]。PL/SQL管理隐式[yinshi]游标[youbiao]，当查询开始时隐式[yinshi]游标[youbiao]打开，查询结束时隐式[yinshi]游标[youbiao]自动关闭。显式[xianshi]游标[youbiao]在PL/SQL块的声明[shengming]部分声明[shengming]，在执行[zhihang]部分或异常[yichang]处理[yichangchuli]部分打开，取数据[shuju],关闭。 使用游标[youbiao]    这里要做一个声明[shengming]，我们所说的游标[youbiao]通常是指显式[xianshi]游标[youbiao]，因此从现在起没有特别指明的情况[qingkuang]，我们所说的游标[youbiao]都是指显式[xianshi]游标[youbiao]。要在程序中使用游标[youbiao]，必须首先声明[shengming]游标[youbiao]。 声明[shengming]游标[youbiao] 语法[yufa]：    CURSORcursor_nameISselect_statement; 在PL/SQL中游标[youbiao]名是一个未声明[shengming]变量[bianliang]，不能给游标[youbiao]名赋值[fuzhi]或用于[yongyu] 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 达式[biaodashi]中。 例：    DELCARE    CURSORC_EMPISSELECTempno,ename,salary    FROMemp    WHEREsalary>2000    ORDERBYename;    ........    BEGIN    在游标[youbiao]定义中SELECT语句[yuju]中不一定非要表可以是视图[shitu]，也可以从多个表或视图[shitu]中选择[xuanze]的列，甚至可以使用*来选择[xuanze]所有的列。     打开游标[youbiao] 使用游标[youbiao]中的值之前应该首先打开游标[youbiao]，打开游标[youbiao]初始化[chushihua]查询处理。打开游标[youbiao]的语法[yufa]是：    OPENcursor_name       cursor_name是在声明[shengming]部分定义的游标[youbiao]名。     例：     OPENC_EMP;         关闭游标[youbiao] 语法[yufa]：     CLOSEcursor_name     例：     CLOSEC_EMP; 从游标[youbiao]提取[tiqu]数据[shuju]    从游标[youbiao]得到一行数据[shuju]使用FETCH命令[mingling]。每一次提取[tiqu]数据[shuju]后，游标[youbiao]都指向[zhixiang]结果集的下一行。语法[yufa]如下：     FETCHcursor_nameINTOvariable[,variable,...]     对于SELECT定义的游标[youbiao]的每一列，FETCH变量[bianliang]列表[liebiao]都应该有一个变量[bianliang]与之相对应，变量[bianliang]的类型[leixing]也要相同。 例：   SETSERVERIUTPUTON   DECLARE     v_enameEMP.ENAME%TYPE;     v_salaryEMP.SALARY%TYPE;     CURSORc_empISSELECTename,salaryFROMemp;     BEGIN       OPENc_emp;          FETCHc_empINTOv_ename,v_salary;            DBMS_OUTPUT.PUT_LINE('SalaryofEmployee'||v_ename||'is'||v_salary);          FETCHc_empINTOv_ename,v_salary;            DBMS_OUTPUT.PUT_LINE('SalaryofEmployee'||v_ename||'is'||v_salary);          FETCHc_empINTOv_ename,v_salary;            DBMS_OUTPUT.PUT_LINE('SalaryofEmployee'||v_ename||'is'||v_salary);       CLOSEc_emp;     END         这段代码[daima]无疑是非常麻烦的，如果有多行返回结果，可以使用循环[xunhuan]并用游标[youbiao]属性[shuxing]为结束循环[xunhuan]的条件[tiaojian]，以这种方式提取[tiqu]数据[shuju]，程序的可读性和简洁性[jianjiexing]都大为提高，下面我们使用循环[xunhuan]重新写上面的程序： SETSERVERIUTPUTON DECLARE v_enameEMP.ENAME%TYPE; v_salaryEMP.SALARY%TYPE; CURSORc_empISSELECTename,salaryFROMemp; BEGIN  OPENc_emp;    LOOP      FETCHc_empINTOv_ename,v_salary;      EXITWHENc_emp%NOTFOUND;      DBMS_OUTPUT.PUT_LINE('SalaryofEmployee'||v_ename||'is'||v_salary); END 记录变量[bianliang]    定义一个记录变量[bianliang]使用TYPE命令[mingling]和%ROWTYPE，关于%ROWsTYPE的更多信息[xinxi]请参阅相关资料。    记录变量[bianliang]用于[yongyu]从游标[youbiao]中提取[tiqu]数据[shuju]行，当游标[youbiao]选择[xuanze]很多列的时候，那么使用记录比为每列声明[shengming]一个变量[bianliang]要方便得多。    当在表上使用%ROWTYPE并将从游标[youbiao]中取出的值放入记录中时，如果要选择[xuanze]表中所有列，那么在SELECT子句[ziju]中使用*比将所有列名列出来要安全得多。 例： SETSERVERIUTPUTON DECLARE R_empEMP%ROWTYPE; CURSORc_empISSELECT*FROMemp; BEGIN  OPENc_emp;   LOOP     FETCHc_empINTOr_emp;     EXITWHENc_emp%NOTFOUND;     DBMS_OUT.PUT.PUT_LINE('SalaryofEmployee'||r_emp.ename||'is'||r_emp.salary);   ENDLOOP;  CLOSEc_emp; END; %ROWTYPE也可以用游标[youbiao]名来定义，这样的话就必须要首先声明[shengming]游标[youbiao]： SETSERVERIUTPUTON DECLARE CURSORc_empISSELECTename,salaryFROMemp; R_empc_emp%ROWTYPE; BEGIN OPENc_emp;  LOOP    FETCHc_empINTOr_emp;    EXITWHENc_emp%NOTFOUND;    DBMS_OUT.PUT.PUT_LINE('SalaryofEmployee'||r_emp.ename||'is'||r_emp.salary);  ENDLOOP; CLOSEc_emp; END; 带参数[canshu]的游标[youbiao]    与存储过程[cunchuguocheng]和函数[hanshu]相似[xiangshi]，可以将参数[canshu]传递[canshuchuandi]给游标[youbiao]并在查询中使用。这对于处理在某种条件[tiaojian]下打开游标[youbiao]的情况[qingkuang]非常有用。它的语法[yufa]如下： CURSORcursor_name[(parameter[,parameter],...)]ISselect_statement; 定义参数[canshu]的语法[yufa]如下：   Parameter_name[IN]data_type[{:=|DEFAULT}value] 　与存储过程[cunchuguocheng]不同的是，游标[youbiao]只能接受传递的值，而不能返回值。参数[canshu]只定义数据[shuju]类型[leixing][shujuleixing][dingyishujuleixing]，没有大小。   另外可以给参数[canshu]设定一个缺省值，当没有参数[canshu]值传递给游标[youbiao]时，就使用缺省值。游标[youbiao]中定义的参数[canshu]只是一个占位符，在别处引用[yinyong]该参数[canshu]不一定可靠。 在打开游标[youbiao]时给参数[canshu]赋值[fuzhi]，语法[yufa]如下： OPENcursor_name[value[,value]....]; 参数[canshu]值可以是文字[wenzi]或变量[bianliang]。 例： DECALRE CURSORc_deptISSELECT*FROMdeptORDERBYdeptno; CURSORc_emp(p_deptVARACHAR2)IS  SELECTename,salary  FROMemp  WHEREdeptno=p_dept  ORDERBYename r_deptDEPT%ROWTYPE; v_enameEMP.ENAME%TYPE; v_salaryEMP.SALARY%TYPE; v_tot_salaryEMP.SALARY%TYPE; BEGIN  OPENc_dept;     LOOP        FETCHc_deptINTOr_dept;        EXITWHENc_dept%NOTFOUND;        DBMS_OUTPUT.PUT_LINE('Department:'||r_dept.deptno||'-'||r_dept.dname);        v_tot_salary:=0;        OPENc_emp(r_dept.deptno);            LOOP               FETCHc_empINTOv_ename,v_salary;               EXITWHENc_emp%NOTFOUND;               DBMS_OUTPUT.PUT_LINE('Name:'||v_ename||'salary:'||v_salary);               v_tot_salary:=v_tot_salary+v_salary;            ENDLOOP;        CLOSEc_emp;        DBMS_OUTPUT.PUT_LINE('ToltalSalaryfordept:'||v_tot_salary);     ENDLOOP;  CLOSEc_dept; END; 游标[youbiao]FOR循环[xunhuan] 在大多数时候我们在设计程序的时候都遵循下面的步骤： 1、打开游标[youbiao] 2、开始循环[xunhuan] 3、从游标[youbiao]中取值 4、检查那一行被返回 5、处理 6、关闭循环[xunhuan] 7、关闭游标[youbiao]    可以简单的把这一类代码[daima]称为游标[youbiao]用于[yongyu]循环[xunhuan]。但还有一种循环[xunhuan]与这种类型[leixing]不相同，这就是FOR循环[xunhuan]，用于[yongyu]FOR循环[xunhuan]的游标[youbiao]按照正常的声明[shengming]方式声明[shengming]，它的优点在于不需要显式[xianshi]的[xianshide]打开、关闭、取数据[shuju]，测试数据[shuju][ceshishuju]的存在、定义存放数据[shuju]的变量[bianliang]等等。游标[youbiao]FOR循环[xunhuan]的语法[yufa]如下： FORrecord_nameIN (corsor_name[(parameter[,parameter]...)] |(query_difinition) LOOP  statements ENDLOOP; 下面我们用for循环[xunhuan]重写[zhongxie]上面的例子： DECALRE CURSORc_deptISSELECTdeptno,dnameFROMdeptORDERBYdeptno; CURSORc_emp(p_deptVARACHAR2)IS SELECTename,salary FROMemp WHEREdeptno=p_dept ORDERBYename v_tot_salaryEMP.SALARY%TYPE; BEGIN   FORr_deptINc_deptLOOP     DBMS_OUTPUT.PUT_LINE('Department:'||r_dept.deptno||'-'||r_dept.dname);     v_tot_salary:=0;     FORr_empINc_emp(r_dept.deptno)LOOP    DBMS_OUTPUT.PUT_LINE('Name:'||v_ename||'salary:'||v_salary);     v_tot_salary:=v_tot_salary+v_salary;     ENDLOOP;     DBMS_OUTPUT.PUT_LINE('ToltalSalaryfordept:'||v_tot_salary);  ENDLOOP; END; 在游标[youbiao]FOR循环[xunhuan]中使用查询    在游标[youbiao]FOR循环[xunhuan]中可以定义查询，由于没有显式[xianshi]声明[shengming]所以游标[youbiao]没有名字，记录名通过游标[youbiao]查询来定义。 DECALRE v_tot_salaryEMP.SALARY%TYPE; BEGIN  FORr_deptIN(SELECTdeptno,dnameFROMdeptORDERBYdeptno)LOOP     DBMS_OUTPUT.PUT_LINE('Department:'||r_dept.deptno||'-'||r_dept.dname);     v_tot_salary:=0;     FORr_empIN(SELECTename,salary            　　　FROMemp            　　　WHEREdeptno=p_dept            　　　ORDERBYename)LOOP       DBMS_OUTPUT.PUT_LINE('Name:'||v_ename||'salary:'||v_salary);       v_tot_salary:=v_tot_salary+v_salary;     ENDLOOP;  DBMS_OUTPUT.PUT_LINE('ToltalSalaryfordept:'||v_tot_salary);  ENDLOOP; END; 游标[youbiao]中的子查询    语法[yufa]如下：     CURSORC1ISSELECT*FROMemp  WHEREdeptnoNOTIN(SELECTdeptno   FROMdept   WHEREdname!='ACCOUNTING'); 可以看出与SQL中的子查询没有什么区别。 游标[youbiao]中的更新和删除[shanchu]    在PL/SQL中依然可以使用UPDATE和DELETE语句[yuju]更新或删除[shanchu]数据[shuju]行。显式[xianshi]游标[youbiao]只有在需要获得多行数据[shuju]的情况[qingkuang]下使用。PL/SQL提供了仅仅使用游标[youbiao]就可以执行[zhihang]删除[shanchu]或更新记录的方法[fangfa]。 UPDATE或DELETE语句[yuju]中的WHERECURRENTOF子串[zichuan]专门处理要执行[zhihang]UPDATE或DELETE操作的表中取出的最近的数据[shuju]。要使用这个方法[fangfa]，在声明[shengming]游标[youbiao]时必须使用FORUPDATE子串[zichuan]，当对话使用FORUPDATE子串[zichuan]打开一个游标[youbiao]时，所有返回集中的数据[shuju]行都将处于行级（ROW-LEVEL）独占式锁定[suoding]，其他对象[duixiang]只能查询这些数据[shuju]行，不能进行UPDATE、DELETE或SELECT...FOR           UPDATE操作。 语法[yufa]：    FORUPDATE[OF[schema.]table.column[,[schema.]table.column]..    [nowait]        在多表查询中，使用OF子句[ziju]来锁定[suoding]特定的表,如果忽略了OF子句[ziju]，那么所有表中选择[xuanze]的数据[shuju]行都将被锁定[suoding]。如果这些数据[shuju]行已经被其他会话[huihua]锁定[suoding]，那么正常情况[qingkuang]下ORACLE将等待[dengdai]，直到数据[shuju]行解锁[jiesuo]。 在UPDATE和DELETE中使用WHERECURRENTOF子串[zichuan]的语法[yufa]如下： WHERE{CURRENTOFcursor_name|search_condition} 例： DELCARE CURSORc1ISSELECTempno,salary FROMemp WHEREcommISNULL FORUPDATEOFcomm; v_commNUMBER(10,2); BEGIN   FORr1INc1LOOP     IFr1.salary<500THEN       v_comm:=r1.salary*0.25;     ELSEIFr1.salary<1000THEN       v_comm:=r1.salary*0.20;     ELSEIFr1.salary<3000THEN       v_comm:=r1.salary*0.15;     ELSE         v_comm:=r1.salary*0.12;     ENDIF;   UPDATEemp;   SETcomm=v_comm   WHERECURRENTOFc1l;   ENDLOOP; END2.分析Oracle日志[rizhi]文件[wenjian] 作为OracleDBA，我们有时候需要追踪数据[shuju]误删除[shanchu]或用户[yonghu]的恶意操作情况[qingkuang]，此时我们不仅需要查出执行[zhihang]这些操作的数据[shuju]库[shujuku]账号[zhanghao]，还需要知道操作是由哪台客户[kehu]端（IP地址[dizhi]等）发出的。针对这些问 题 快递公司问题件快递公司问题件货款处理关于圆的周长面积重点题型关于解方程组的题及答案关于南海问题 [wenti]，一个最有效[youxiao]实用而又低成本的方法[fangfa]就是分析Oracle数据[shuju]库[shujuku]的日志[rizhi]文件[wenjian]。本文将就Oracle日志[rizhi]分析技术做深入探讨。一、如何分析即LogMiner解释[jieshi]从目前来看，分析Oracle日志[rizhi]的唯一方法[fangfa]就是使用Oracle公司提供的LogMiner来进行，Oracle数据[shuju]库[shujuku]的所有更改都记录在日志[rizhi]中，但是原始的日志[rizhi]信息[xinxi]我们根本无法看懂，而LogMiner就是让我们看懂日志[rizhi]信息[xinxi]的工具。从这一点上看，它和tkprof差不多，一个是用来分析日志[rizhi]信息[xinxi]，一个则是格式化[geshihua]跟踪[genzong]文件[wenjian]。通过对日志[rizhi]的分析我们可以实现下面的目的：1、查明数据[shuju]库[shujuku]的逻辑更改；2、侦察并更正用户[yonghu]的误操作；3、执行[zhihang]事后审计[shenji]；4、执行[zhihang]变化分析。不仅如此，日志[rizhi]中记录的信息[xinxi]还包括：数据[shuju]库[shujuku]的更改历史、更改类型[leixing]（INSERT、UPDATE、DELETE、DDL等）、更改对应的SCN号、以及执行[zhihang]这些操作的用户[yonghu]信息[xinxi]等，LogMiner在分析日志[rizhi]时，将重构[zhonggou]等价的SQL语句[yuju]和UNDO语句[yuju]（分别记录在V$LOGMNR_CONTENTS视图[shitu]的SQL_REDO和SQL_UNDO中）。这里需要注意的是等价语句[yuju]，而并非原始SQL语句[yuju]，例如：我们最初执行[zhihang]的是“deleteawherec1<>'cyx';”，而LogMiner重构[zhonggou]的是等价的6条DELETE语句[yuju]。所以我们应该意识[yishi]到V$LOGMNR_CONTENTS视图[shitu]中显示[xianshi]的并非是原版的现实，从数据[shuju]库[shujuku]角度来讲这是很容易理解的，它记录的是元操作，因为同样是“deleteawherec1<>'cyx';”语句[yuju]，在不同的环境中，实际删除[shanchu]的记录数可能各不相同，因此记录这样的语句[yuju]实际上并没有什么实际意义，LogMiner重构[zhonggou]的是在实际情况[qingkuang]下转化成元操作的多个单条语句[yuju]。另外由于Oracle重做[zhongzuo]日志[rizhi]中记录的并非原始的对象[duixiang]（如表以及其中的列）名称，而只是它们在Oracle数据[shuju]库[shujuku]中的内部编号（对于表来说是它们在数据[shuju]库[shujuku]中的对象[duixiang]ID，而对于表中的列来说，对应的则是该列在表中的排列[pailie]序号：COL1,COL2等），因此为了使LogMiner重构[zhonggou]出的SQL语句[yuju]易于识别[shibie]，我们需要将这些编号转化成相应的名称，这就需要用到数据[shuju]字典[shujuzidian]（也就说LogMiner本身是可以不用数据[shuju]字典[shujuzidian]的，详见下面的分析过程），LogMiner利用DBMS_LOGMNR_D.BUILD()过程来提取[tiqu]数据[shuju]字典[shujuzidian]信息[xinxi]。LogMiner包含两个PL/SQL包和几个视图[shitu]：1、dbms_logmnr_d包，这个包只包括一个用于[yongyu]提取[tiqu]数据[shuju]字典[shujuzidian]信息[xinxi]的过程，即dbms_logmnr_d.build()过程。2、dbms_logmnr包，它有三个过程：add_logfile(namevarchar2,optionsnumber)-用来添加/删除[shanchu]用于[yongyu]分析的日志[rizhi]文件[wenjian]；start_logmnr(start_scnnumber,end_scnnumber,start_timenumber,end_timenumber,dictfilenamevarchar2,optionsnumber)-用来开启日志[rizhi]分析，同时确定分析的时间/SCN窗口[chuangkou]以及确认[queren]是否使用提取[tiqu]出来的数据[shuju]字典[shujuzidian]信息[xinxi]。end_logmnr()-用来终止分析会话[huihua]，它将回收LogMiner所占用的内存[neicun]。与LogMiner相关的数据[shuju]字典[shujuzidian]。1、v$logmnr_dictionary，LogMiner可能使用的数据[shuju]字典[shujuzidian]信息[xinxi]，因logmnr可以有多个字典文件[wenjian]，该视图[shitu]用于[yongyu]显示[xianshi]这方面信息[xinxi]。2、v$logmnr_parameters，当前LogMiner所设定的参数[canshu]信息[xinxi]。3、v$logmnr_logs，当前用于[yongyu]分析的日志[rizhi]列表[liebiao]。4、v$logmnr_contents，日志[rizhi]分析结果。二、Oracle9iLogMiner的增强[zengqiang]：1、支持[zhichi]更多数据[shuju][duoshuju]/存储类型[leixing]：链接[lianjie]/迁移行、CLUSTER表操作、DIRECTPATH插入以及DDL操作。在V$LOGMNR_CONTENTS的SQL_REDO中可以看到DDL操作的原句（CREATEUSER除外，其中的密码[mima]将以加密[jiami]的形式出现，而不是原始密码[mima]）。如果TX_AUDITING初始化[chushihua]参数[canshu]设为TRUE，则所有操作的数据[shuju]库[shujuku]账号[zhanghao]将被记录。2、提取[tiqu]和使用数据[shuju]字典[shujuzidian]的选项[xuanxiang]：现在数据[shuju]字典[shujuzidian]不仅可以提取[tiqu]到一个外部文件[wenjian]中，还可以直接提取[tiqu]到重做[zhongzuo]日志[rizhi]流中，它在日志[rizhi]流中提供了操作当时的数据[shuju]字典[shujuzidian]快照[kuaizhao]，这样就可以实现离线[lixian]分析。3、允许对DML操作按事务进行分组：可以在START_LOGMNR()中设置[shezhi]COMMITTED_DATA_ONLY选项[xuanxiang]，实现对DML操作的分组，这样将按SCN的顺序返回已经提交[tijiao]的事务。4、支持[zhichi]SCHEMA的变化：在数据[shuju]库[shujuku]打开的状态[zhuangtai]下，如果使用了LogMiner的DDL_DICT_TRACKING选项[xuanxiang]，Oracle9i的LogMiner将自动对比最初的日志[rizhi]流和当前系统[xitong]的数据[shuju]字典[shujuzidian]，并返回正确的DDL语句[yuju]，并且会自动侦察并标记[biaoji]当前数据[shuju]字典[shujuzidian]和最初日志[rizhi]流之间的差别，这样即使最初日志[rizhi]流中所涉及的表已经被更改或者根本已经不存在，LogMiner同样会返回正确的DDL语句[yuju]。5、在日志[rizhi]中记录更多列信息[xinxi]的能力[nengli]：例如对于UPDATE操作不仅会记录被更新行的情况[qingkuang]，还可以捕捉更多前影信息[xinxi]。6、支持[zhichi]基于数值的查询：Oracle9iLogMiner在支持[zhichi]原有基于元数据[shuju][yuanshuju]（操作、对象[duixiang]等）查询的基础上，开始支持[zhichi]基于实际涉及到的数据[shuju]的查询。例如涉及一个工资表，现在我们可以很容易地查出员工工资由1000变成2000的原始更新语句[yuju]，而在之前我们只能选出所有的更新语句[yuju]。三、Oracle8i/9i的日志[rizhi]分析过程LogMiner只要在实例起来的情况[qingkuang]下都可以运行[yunhang]，LogMiner使用一个字典文件[wenjian]来实现Oracle内部对象[duixiang][neibuduixiang]名称的转换[zhuanhuan]，如果没有这个字典文件[wenjian]，则直接显示[xianshi]内部对象[duixiang][neibuduixiang]编号，例如我们执行[zhihang]下面的语句[yuju]：deletefrom"C"."A"where"C1"=‘gototop’andROWID='AAABg1AAFAAABQaAAH';如果没有字典文件[wenjian]，LogMiner分析出来的结果将是：deletefrom"UNKNOWN"."OBJ#6197"where"COL1"=HEXTORAW('d6a7d4ae')andROWID='AAABg1AAFAAABQaAAH';如果想要使用字典文件[wenjian]，数据[shuju]库[shujuku]至少应该出于MOUNT状态[zhuangtai]。然后执行[zhihang]dbms_logmnr_d.build过程将数据[shuju]字典[shujuzidian]信息[xinxi]提取[tiqu]到一个外部文件[wenjian]中。下面是具体分析步骤：1、确认[queren]设置[shezhi]了初始化[chushihua]参数[canshu]：UTL_FILE_DIR，并确认[queren]Oracle对改 目录 工贸企业有限空间作业目录特种设备作业人员作业种类与目录特种设备作业人员目录1类医疗器械目录高值医用耗材参考目录 拥有读写[duxie]权限[quanxian]，然后启动[qidong]实例。示例中UTL_FILE_DIR参数[canshu]如下：SQL>showparameterutlNAME                        TYPE       VALUE-----------------------------------------------------------------utl_file_dir                string     /data6/cyx/logmnr这个目录主要用于[yongyu]存放dbms_logmnr_d.build过程所产生的字典信息[xinxi]文件[wenjian]，如果不用这个，则可以不设，也就跳过下面一步。2、生成字典信息[xinxi]文件[wenjian]：execdbms_logmnr_d.build(dictionary_filename=>'dic.ora',dictionary_location=>'/data6/cyx/logmnr');其中dictionary_location指的是字典信息[xinxi]文件[wenjian]的存放位置[weizhi],它必须完全匹配UTL_FILE_DIR的值，例如：假设UTL_FILE_DIR=/data6/cyx/logmnr/，则上面这条语句[yuju]会出错，只因为UTL_FILE_DIR后面多了一个“/”，而在很多其它地方对这一“/”是不敏感的。dictionary_filename指的是放于字典信息[xinxi]文件[wenjian]的名字，可以任意取。当然我们也可以不明确写出这两个选项[xuanxiang]，即写成：execdbms_logmnr_d.build('dic.ora','/data6/cyx/logmnr');如果你第一步的参数[canshu]没有设，而直接开始这一步，Oracle会报下面的错误[cuowu]：ERRORatline1:ORA-01308:initializationparameterutl_file_dirisnotsetORA-06512:at"SYS.DBMS_LOGMNR_D",line923ORA-06512:at"SYS.DBMS_LOGMNR_D",line1938ORA-06512:atline1需要注意的是，在oracle817forWindows版中会出现以下错误[cuowu]：14:26:05SQL>executedbms_logmnr_d.build('oradict.ora','c:\oracle\admin\ora\log');BEGINdbms_logmnr_d.build('oradict.ora','c:\oracle\admin\ora\log');END;*ERRORatline1:ORA-06532:SubscriptoutsideoflimitORA-06512:at"SYS.DBMS_LOGMNR_D",line793ORA-06512:atline1解决办法：编辑"$ORACLE_HOME/rdbms/admindbmslmd.sql"文件[wenjian]，把其中的TYPEcol_desc_arrayISVARRAY(513)OFcol_description;改成：TYPEcol_desc_arrayISVARRAY(700)OFcol_description;保存文件[wenjian]，然后执行[zhihang]一遍这个脚本[jiaoben]：15:09:06SQL>@c:\oracle\ora81\rdbms\admin\dbmslmd.sqlPackagecreated.Packagebodycreated.Noerrors.Grantsucceeded.然后重新编译[bianyi]DBMS_LOGMNR_D包：15:09:51SQL>alterpackageDBMS_LOGMNR_Dcompilebody;Packagebodyaltered.之后重新执行[zhihang]dbms_logmnr_d.build即可：15:10:06SQL>executedbms_logmnr_d.build('oradict.ora','c:\oracle\admin\ora\log');PL/SQLproceduresuccessfullycompleted.3、添加需要分析的日志[rizhi]文件[wenjian]SQL>execdbms_logmnr.add_logfile(logfilename=>'/data6/cyx/rac1arch/arch_1_197.arc',options=>dbms_logmnr.new);PL/SQLproceduresuccessfullycompleted.这里的options选项[xuanxiang]有三个参数[canshu]可以用：NEW-表示创建一个新的日志[rizhi]文件[wenjian]列表[liebiao]ADDFILE-表示向这个列表[liebiao]中添加日志[rizhi]文件[wenjian]，如下面的例子REMOVEFILE-和addfile相反。SQL>execdbms_logmnr.add_logfile(logfilename=>'/data6/cyx/rac1arch/arch_2_86.arc',options=>dbms_logmnr.addfile);PL/SQLproceduresuccessfullycompleted.4、当你添加了需要分析的日志[rizhi]文件[wenjian]后，我们就可以让LogMiner开始分析了：SQL>execdbms_logmnr.start_logmnr(dictfilename=>'/data6/cyx/logmnr/dic.ora');PL/SQLproceduresuccessfullycompleted.如果你没有使用字典信息[xinxi]文件[wenjian]（此时我们只需要启动[qidong]实例就可以了），那么就不需要跟dictfilename参数[canshu]：SQL>execdbms_logmnr.start_logmnr();PL/SQLproceduresuccessfullycompleted.当然dbms_logmnr.start_logmnr()过程还有其它几个用于[yongyu]定义分析日志[rizhi]时间/SCN窗口[chuangkou]的参数[canshu]，它们分别是：STARTSCN/ENDSCN-定义分析的起始/结束SCN号，STARTTIME/ENDTIME-定义分析的起始/结束时间。例如下面的过程将只分析从'2003-09-2109:39:00'到'2003-09-2109:45:00'这段时间的日志[rizhi]：SQL>execdbms_logmnr.start_logmnr(dictfilename=>'/data6/cyx/logmnr/dic.ora',-starttime=>'2003-09-2109:39:00',endtime=>'2003-09-2109:45:00');PL/SQLproceduresuccessfullycompleted.上面过程第一行结尾的“-”表示转行，如果你在同一行，则不需要。我们可以看到有效[youxiao]日志[rizhi]的时间戳[shijianchuo]：SQL>selectdistincttimestampfromv$logmnr_contents;TIMESTAMP-------------------2003-09-2109:40:022003-09-2109:42:39这里需要注意的是，因为我之前已经设置[shezhi]NLS_DATE_FORMAT环境变量[bianliang]，所以上面的日期可以直接按这个格式写就行了，如果你没有设，则需要使用to_date函数[hanshu]来转换[zhuanhuan]一下。SQL>!env|grepNLSNLS_LANG=american_america.zhs16cgb231280NLS_DATE_FORMAT=YYYY-MM-DDHH24:MI:SSORA_NLS33=/oracle/oracle9/app/oracle/product/9.2.0/ocommon/nls/admin/data使用to_date的格式如下：execdbms_logmnr.start_logmnr(dictfilename=>'/data6/cyx/logmnr/dic.ora',-starttime=>to_date('2003-09-2109:39:00','YYYY-MM-DDHH24:MI:SS'),-endtime=>to_date('2003-09-2109:45:00','YYYY-MM-DDHH24:MI:SS'));STARTSCN和ENDSCN参数[canshu]使用方法[fangfa]类似。5、好了，在上面的过程执行[zhihang]结束之后，我们就可以通过访问与LogMiner相关的几个视图[shitu]来提取[tiqu]我们需要的信息[xinxi]了。其中在v$logmnr_logs中可以看到我们当前分析的日志[rizhi]列表[liebiao]，如果数据[shuju]库[shujuku]有两个实例（即OPS/RAC），在v$logmnr_logs中会有两个不同的THREAD_ID。而真正的分析结果是放在v$logmnr_contents中，这里面有很多信息[xinxi]，我们可以根据需要追踪我们感兴趣的信息[xinxi]。后面我将单独列出来讲常见的追踪情形。6、全部结束之后，我们可以执行[zhihang]dbms_logmnr.end_logmnr过程退出[tuichu]LogMiner分析过程，你也可以直接退出[tuichu]SQL*PLUS，它会自动终止四、如何利用LogMiner分析Oracle8的日志[rizhi]文件[wenjian]虽然说LogMiner是Oracle8i才推出来，但我们同样可以用它来分析Oracle8的日志[rizhi]文件[wenjian]，只不过稍微麻烦了一点，并且有一定的限制，下面是具体做法：我们首先复制Oracle8i的$ORACLE_HOME/rdbms/admin/dbmslmd.sql脚本[jiaoben]到Oracle8数据[shuju]库[shujuku]所在主机[zhuji]的同样目录；这个脚本[jiaoben]用于[yongyu]创建dbms_logmnr_d包（注意，Oracle9i中还将创建dbms_logmnr包），如果是8.1.5脚本[jiaoben]名字为dbmslogmnrd.sql。然后在Oracle8的数据[shuju]库[shujuku]上运行[yunhang]这个脚本[jiaoben]，之后使用dbms_logmnr_d.build过程创建字典信息[xinxi]文件[wenjian]。现在我们就可以把Oracle8的归档日志[rizhi]连同这个字典信息[xinxi]文件[wenjian]复制到Oracle8i数据[shuju]库[shujuku]所在的主机[zhuji]上，之后在Oracle8i数据[shuju]库[shujuku]中从上面分析过程的第三步开始分析Oracle8的日志[rizhi]，不过dbms_logmnr.start_logmnr()中使用的是Oracle8的字典信息[xinxi]文件[wenjian]。按照我前面所说的那样，如果不是字典文件[wenjian]，我们则可以直接将Oracle8的归档日志[rizhi]复制到Oracle8i数据[shuju]库[shujuku]所在主机[zhuji]，然后对它进行分析。其实这里涉及到了一个跨平台[pingtai]使用LogMiner的问题[wenti]，笔者做过试验，也可以在Oracle9i中来分析Oracle8i的日志[rizhi]。但这些都是有所限制的，主要表现在：1、LogMiner所使用的字典文件[wenjian]必须和所分析的日志[rizhi]文件[wenjian]是同一个数据[shuju]库[shujuku]所产生的，并且该数据[shuju]库[shujuku]的字符[zifu]集[zifuji]应和执行[zhihang]LogMiner数据[shuju]库[shujuku]的相同。这很好理解，如果不是同一个数据[shuju]库[shujuku]所产生就不存在对应关系了。2、生成日志[rizhi]的数据[shuju]库[shujuku]硬件[yingjian]平台[pingtai][yingjianpingtai]和执行[zhihang]LogMiner数据[shuju]库[shujuku]的硬件[yingjian]平台[pingtai][yingjianpingtai]要求一致，操作系统[xitong][caozuoxitong]版本可以不一致。笔者做试验时（如果读者有兴趣可以到我网站[wangzhan] http://www.ncn.cn 上下载[xiazai]试验全过程，因为太长就不放在这里了），所用的两个数据[shuju]库[shujuku]操作系统[xitong][caozuoxitong]都是tru64/UNIX，但一个是V5.1A，另一个则是V4.0F。如果操作系统[xitong][caozuoxitong]不一致则会出现下面的错误[cuowu]：ORA-01284:file/data6/cyx/logmnr/arch_1_163570.arccannotbeopenedORA-00308:cannotopenarchivedlog'/data6/cyx/logmnr/arch_1_163570.arc'ORA-27048:skgfifi:fileheaderinformationisinvalidORA-06512:at"SYS.DBMS_LOGMNR",line63ORA-06512:atline1五、分析v$logmnr_contents前面我们已经知道了LogMiner的分析结果是放在v$logmnr_contents中，这里面有很多信息[xinxi]，我们可以根据需要追踪我们感兴趣的信息[xinxi]。那么我们通常感兴趣的有哪些呢？1、追踪数据[shuju]库[shujuku]结构[jiegou]变化情况[qingkuang]，即DDL操作，如前所述，这个只有Oracle9i才支持[zhichi]：SQL>selecttimestamp,sql_redofromv$logmnr_contents2whereupper(sql_redo)like'%CREATE%';TIMESTAMP-------------------SQL_REDO-------------------------2003-09-2110:01:55createtablet(c1number);2、追踪用户[yonghu]误操作或恶意操作：例如我们现实中有这样需求[xuqiu]，有一次我们发现一位员工通过程序修改[xiugai][chengxuxiugai]了业务[yewu]数据[shuju]库[shujuku]信息[xinxi]，把部分电话的收费类型[leixing]改成免费了，现在就要求我们从数据[shuju]库[shujuku]中查出到底是谁干的这件事？怎么查？LogMiner提供了我们分析日志[rizhi]文件[wenjian]的手段，其中v$logmnr_contents的SESSION_INFO列包含了下面的信息[xinxi]：login_username=NEW_97client_info=OS_username=oracle8Machine_name=phoenix1OS_terminal=ttyp3OS_process_id=8004OS_program name=sqlplus@phoenix1 (TNSV1-V3)虽然其中信息[xinxi]已经很多了，但在我们的业务[yewu]数据[shuju]库[shujuku]中，程序是通过相同的login_username登录[denglu]数据[shuju]库[shujuku]的，这样单从上面的信息[xinxi]是很难判断的。不过我们注意到，因为公司应用[yingyong]服务[yingyongfuwu]器[yingyongfuwuqi]不是每个人都有权限[quanxian]在上面写程序的，一般恶意程序[eyichengxu]都是直接通过他自己的PC连到数据[shuju]库[shujuku]的，这就需要一个准确的定位[dingwei]。IP追踪是我们首先想到的，并且也满足我们的实际要求，因为公司内部IP地址[dizhi]分配[fenpei]是统一管理的，能追踪到IP地址[dizhi]我们就可以准确定位[dingwei]了。但从面的SESSION_INFO中我们并不能直接看到IP，不过我们还是有办法的，因为这个SESSION_INFO里面的 内容 财务内部控制制度的内容财务内部控制制度的内容人员招聘与配置的内容项目成本控制的内容消防安全演练内容 其实是日志[rizhi]从V$SESSION视图[shitu]里提取[tiqu]的，我们可以在生产数据[shuju]库[shujuku]中创建一个追踪客户[kehu]端IP地址[dizhi]的触发器[chufaqi]：createorreplacetriggeron_logon_triggerafterlogonondatabasebegin dbms_application_info.set_client_info(sys_context('userenv','ip_address'));end;/现在，我们就可以在V$SESSION视图[shitu]的CLIENT_INFO列中看到新登录[denglu]的客户[kehu]端IP地址[dizhi]了。那么上面的提出的问题[wenti]就可以迎刃而解了。假如被更新的表名为HMLX，我们就可以通过下面的SQL来找到所需信息[xinxi]：SQL>selectsession_info,sql_redofromv$logmnr_contents2whereupper(operation)='UPDATE' andupper(sql_redo)like'%HMLX%'3/SESSION_INFO-----------------------------------------SQL_REDO-----------------------------------------login_username=Cclient_info=10.16.98.26OS_username=sz-xjs-chengyxMachine_name=GDTEL\SZ-XJS-CHENGYXupdate"C"."HMLX"set"NAME"='free'where"NAME"='ncn.cn'andROWID='AAABhTAAFAAABRaAAE';3.在ORACLE里用存储过程[cunchuguocheng]定期分割[fenge]表Oracle数据[shuju]库[shujuku]里存放着各种各样的数据[shuju]，其中有一些数据[shuju]表会随着时间的推移，越来越大。如交友聊天[liaotian]的日志[rizhi]、短信收发[shoufa]的日志[rizhi]、生产系统[xitong]的日志[rizhi]、动态[dongtai]网站[wangzhan]发布系统[xitong]的日志[rizhi]等等。这样的信息[xinxi]又和时间紧密相关，有没有办法让这些日志[rizhi]表能按时间自动分割[fenge]成历史年月(如log200308,log200309)的表呢?请看看我用存储过程[cunchuguocheng]定期分割[fenge]表的方法[fangfa]吧。一、问题[wenti]的引出　　1.初学数据[shuju]库[shujuku]时只知道用delete来删除[shanchu]表里的数据[shuju]。但在Oracle数据[shuju]库[shujuku]里，大量delete记录后，并不能释放[shifang]表所占用的物理空间[kongjian]，这里面有一个高水位的概念，所以我们不能用delete来分割[fenge]表。　　2.用重命名(rename)表的方法[fangfa]　　(1)先建一个和原来日志[rizhi]表(假如是log)数据[shuju]结构[jiegou][shujujiegou]一模一样的新表(如log_new)，建约束[yueshu]、索引[suoyin]及指定字段[ziduan]的默认[moren]值;　　(2)重命名表log到log_YYYYMM;要注意的问题[wenti]是OLTP系统[xitong]可能会因为DML操作阻碍重命名执行[zhihang]成功，出现ORA-00054资源[ziyuan]正忙的错误[cuowu]提示[tishi]，需要试多次才能成功。　　(3)重命名表log_new到log。　　这样应用[yingyong]程序[yingyongchengxu]不用修改[xiugai](受影响的时间仅几秒钟)，日志[rizhi]表就被截断分割[fenge]了。　　上述步骤可以在Oracle里用存储过程[cunchuguocheng]来实现。二、用存储过程[cunchuguocheng]来分割[fenge]表　　可以看到在重命名表的方法[fangfa]中，步骤(2)是个关键。下面这个rename_table过程会在有锁阻碍的情况[qingkuang]下用递归的方式重试[zhongshi]100次。重命名原始表到目标[mubiao]表的存储过程[cunchuguocheng]rename_table:createorreplaceprocedurerename_table(source_name   in   varchar2,target_name   in    varchar2,times       inout   number) isquery_str varchar2(4000);source_name1 varchar2(64);target_name1 varchar2(64);cursorc1isselectsegment_namefromuser_segmentswheresegment_name=upper(source_name);dummyc1%rowtype;cursorc2isselectsegment_namefromuser_segmentswheresegment_name=upper(target_name);dummy2c2%rowtype;beginsource_name1:=source_name;target_name1:=target_name;openc1;fetch c1 into  dummy;--  ifc1%foundthen--  dbms_output.put_line(source_name1||'exist!');--  endif;openc2;fetch c2 into  dummy2;--  ifc2%notfoundthen--  dbms_output.put_line(target_name1||'notexist!');--  endif;ifc2%notfound andc1%foundthenquery_str:='altertable'||source_name1||'renameto'||target_name1;executeimmediatequery_str;dbms_output.put_line('renamesuccess!');endif;closec1;closec2;exceptionWHENOTHERSTHEN times:=times+1;iftimes<100then-- dbms_output.put_line('times:'||times);rename_table(source_name1,target_name1,times);elsedbms_output.put_line(SQLERRM);dbms_output.put_line('errorover100times,exit');endif;end;/截断分割[fenge]log表的存储过程[cunchuguocheng]log_history:createorreplaceprocedurelog_historyisquery_strvarchar2(32767);year_month    varchar2(8);times   number;beginselectto_char(sysdate-15,'YYYYMMDD')intoyear_monthfromdual;times:=0;query_str:='createtablelog_newpctfree10pctused80asselect*fromlogwhere1=2';executeimmediatequery_str;query_str:='altertablelog_newaddconstraintslog_'||year_month||'_pkprimarykey(id)tablespaceindxnologgingpctfree10';executeimmediatequery_str;query_str:='altertablelog_hismodifylogtimedefaultsysdate';executeimmediatequery_str; query_str:='createindexlog_'||year_month||'_logtimeonlog(logtime)tablespaceindxnologgingpctfree10';executeimmediatequery_str; rename_table('log','log'||year_month,times);query_str:='altertablelog_newrenametolog';executeimmediatequery_str;end;/　　当然您工作环境的日志[rizhi]表可能和我这个做例子的日志[rizhi]表结构[jiegou]上有所不同，约束[yueshu]条件[tiaojian][yueshutiaojian]、索引[suoyin]和默认[moren]值都不尽相同。只要稍加修改[xiugai]就可以了。三、用户[yonghu]需要有createanytable系统[xitong]权限[quanxian](不是角色里包含的权限[quanxian])　　因为在执行[zhihang]存储过程[cunchuguocheng]时，由角色赋予的权限[quanxian]会失效[shixiao],所以执行[zhihang]log_history的用户[yonghu]一定要有DBA单独赋予的createanytable系统[xitong]权限[quanxian]。　　最后在OS里定时每月一号凌晨0:00分执行[zhihang]log_history，让存储过程[cunchuguocheng]定期分割[fenge]表。　　如果要分割[fenge]的日志[rizhi]表很多，模仿log_history可以写很多类似的存储过程[cunchuguocheng]来分割[fenge]不同项目[xiangmu]里的日志[rizhi]表。然后让OS按月，按周或者不定期的执行[zhihang]这些存储过程[cunchuguocheng],管理员[guanliyuan]只要查看日志[rizhi]就可以了。四、其它注意事项　　如果应用[yingyong]程序[yingyongchengxu]有BUG，可能对在用原始日志[rizhi]表产生长期不能释放[shifang]的锁，执行[zhihang]log_history重命名会不成功。　　这时DBA可以查看数据[shuju]字典[shujuzidian]:selectobject_id,session_id,locked_modefromv$locked_object;selectt2.username,t2.sid,t2.serial#,t2.logon_timefromv$locked_objectt1,v$sessiont2wheret1.session_id=t2.sidorderbyt2.logon_time;　　如果有长期出现的一模一样的列（包括登录[denglu]时间），可能是没有释放[shifang]的锁。　　我们要在执行[zhihang]分割[fenge]日志[rizhi]表的存储过程[cunchuguocheng]前，用下面SQL语句[yuju]杀掉长期没有释放[shifang]非正常的锁：altersystemkillsession'sid,serial#';五、结束语　　用上面介绍的存储过程[cunchuguocheng]定期分割[fenge]日志[rizhi]表有很大的灵活性[linghuoxing]。历史数据[shuju][lishishuju]不仅查询方便，转移和备份[beifen]起来也都很容易。Unix和Windows平台[pingtai]的都可以使用。对服务器[fuwuqi]硬盘[yingpan]空间[kongjian]较小的中小型公司意义尤其明显。4.在Oracle中实现数据[shuju]库[shujuku]的复制在Internet上运作数据[shuju]库[shujuku]经常会有这样的需求[xuqiu]：把遍布全国各城市相似[xiangshi]的数据[shuju]库[shujuku]应用[yingyong]统一起来，一个节点[jiedian]的数据[shuju]改变不仅体现在本地，还反映到远端。复制技术给用户[yonghu]提供了一种快速访问共享[fangwengongxiang]数据[shuju]的办法。一、实现数据[shuju]库[shujuku]复制的前提[qianti]条件[tiaojian]1、数据[shuju]库[shujuku]支持[zhichi]高级复制功能您可以用system身份[shenfen]登录[denglu]数据[shuju]库[shujuku]，查看v$option视图[shitu]，如果其中Advancedreplication为TRUE，则支持[zhichi]高级复制功能；否则不支持[zhichi]。2、数据[shuju]库[shujuku]初始化[chushihua]参数[canshu]要求①、db_domain=test.com.cn指明数据[shuju]库[shujuku]的域名[yuming](默认[moren]的是WORLD)，这里可以用您公司的域名[yuming]。②、global_names=true它要求数据[shuju]库[shujuku]链接[lianjie](databaselink)和被连接[lianjie]的数据[shuju]库[shujuku]名称一致。现在全局数据[shuju]库[shujuku]名：db_name+”.”+db_domain③、有跟数据[shuju]库[shujuku]job执行[zhihang]有关的参数[canshu]job_queue_processes=1job_queue_interval=60distributed_transactions=10open_links=4 第一行定义SNP进程[jincheng]的启动[qidong]个数为n。系统[xitong]缺省值为0，正常定义范围[fanwei]为0～36，根据任务[renwu]的多少，可以配置[peizhi]不同的数值。 第二行定义系统[xitong]每隔N秒唤醒[huanxing]该进程[jincheng]一次。系统[xitong]缺省值为60秒，正常范围[fanwei]为1～3600秒。事实上，该进程[jincheng]执行[zhihang]完当前任务[renwu]后，就进入睡眠状态[zhuangtai]，睡眠一段时间后，由系统[xitong]的总控负责将其唤醒[huanxing]。 如果修改[xiugai]了以上这几个参数[canshu]，需要重新启动[qidong][zhongxinqidong]数据[shuju]库[shujuku]以使参数[canshu]生效。二、实现数据[shuju]库[shujuku]同步[tongbu]复制的步骤假设在Internet上我们有两个数据[shuju]库[shujuku]：一个叫深圳(shenzhen)，一个叫北京(beijing)。具体配置[peizhi]见下表：数据[shuju]库[shujuku]名shenzhenbeijing数据[shuju]库[shujuku]域名[yuming]test.com.cntest.com.cn数据[shuju]库[shujuku]sid号shenzhenbeijingListener端口号15211521服务器[fuwuqi]ip地址[dizhi]10.1.1.10010.1.1.2001、确认[queren]两台数据[shuju]库[shujuku]之间可以互相访问，在tnsnames.ora里设置[shezhi]数据[shuju]库[shujuku]连接[lianjie][shujukulianjie]字符[zifu]串[zifuchuan]。①、例如：深圳这边的数据[shuju]库[shujuku]连接[lianjie][shujukulianjie]字符[zifu]串[zifuchuan]是以下的格式beijing=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.1.200)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=beijing))) 运行[yunhang]$tnspingbeijing 出现以下提示[tishi]符：Attemptingtocontact(ADDRESS=(PROTOCOL=TCP)(HOST=10.1.1.200)(PORT=1521))OK（n毫秒） 表明深圳数据[shuju]库[shujuku]可以访问北京数据[shuju]库[shujuku]。②、在北京那边也同样配置[peizhi]，确认[queren]$tnspingshenzhen是通的。2、改数据[shuju]库[shujuku]全局名称，建公共的数据[shuju]库[shujuku]链接[lianjie]。①、用system身份[shenfen]登录[denglu]shenzhen数据[shuju]库[shujuku]SQL>alterdatabaserenameglobal_nametoshenzhen.test.com.cn;用system身份[shenfen]登录[denglu]beijing数据[shuju]库[shujuku]：SQL>alterdatabaserenameglobal_nametobeijing.test.com.cn;②、用system身份[shenfen]登录[denglu]shenzhen数据[shuju]库[shujuku]SQL>createpublicdatabaselinkbeijing.test.com.cnusing'beijing';测试数据[shuju][ceshishuju]库全局名称和公共的数据[shuju]库[shujuku]链接[lianjie]SQL>select*from global_name@beijing.test.com.cn ;返回结果为beijing.test.com.cn就对了。用system身份[shenfen]登录[denglu]beijing数据[shuju]库[shujuku]：SQL>createpublicdatabaselinkshenzhen.test.com.cnusing'shenzhen';测试数据[shuju][ceshishuju]库全局名称和公共的数据[shuju]库[shujuku]链接[lianjie]SQL>select*from global_name@shenzhen.test.com.cn ;返回结果为shenzhen.test.com.cn就对了。3、建立管理数据[shuju]库[shujuku]复制的用户[yonghu]repadmin，并赋权。①、用system身份[shenfen]登录[denglu]shenzhen数据[shuju]库[shujuku]SQL>createuserrepadminidentifiedbyrepadmindefaulttablespaceuserstemporarytablespacetemp;SQL>executedbms_defer_sys.register_propagator('repadmin');SQL>grantexecuteanyproceduretorepadmin;SQL>executedbms_repcat_admin.grant_admin_any_repgroup('repadmin');SQL>grantcommentanytabletorepadmin;SQL>grantlockanytabletorepadmin;②、同样用system身份[shenfen]登录[denglu]beijing数据[shuju]库[shujuku]，运行[yunhang]以上的命令[mingling]，管理数据[shuju]库[shujuku]复制的用户[yonghu]repadmin，并赋权。说明[shuoming]：repadmin用户[yonghu]名[yonghuming]和密码[mima]可以根据用户[yonghu]的需求[xuqiu]自由命名。4、在数据[shuju]库[shujuku]复制的用户[yonghu]repadmin下创建私有的数据[shuju]库[shujuku]链接[lianjie]。①、用repadmin身份[shenfen]登录[denglu]shenzhen数据[shuju]库[shujuku]SQL>createdatabaselinkbeijing.test.com.cnconnecttorepadminidentifiedbyrepadmin;测试这个私有的数据[shuju]库[shujuku]链接[lianjie]：SQL>select*from global_name@beijing.test.com.cn ;返回结果为beijing.test.com.cn就对了。②、用repadmin身份[shenfen]登录[denglu]beijing数据[shuju]库[shujuku]SQL>createdatabaselinkshenzhen.test.com.cnconnecttorepadminidentifiedbyrepadmin;测试这个私有的数据[shuju]库[shujuku]链接[lianjie]SQL>select*from global_name@shenzhen.test.com.cn ;返回结果为shenzhen.test.com.cn就对了。5、创建或选择[xuanze]实现数据[shuju]库[shujuku]复制的用户[yonghu]和对象[duixiang]，给用户[yonghu]赋权，数据[shuju]库[shujuku]对象[duixiang]必须有主关键字[guanjianzi]。假设我们用ORACLE里举例用的scott用户[yonghu]，dept表。①、用internal身份[shenfen]登录[denglu]shenzhen数据[shuju]库[shujuku]，创建scott用户[yonghu]并赋权SQL>createuserscottidentifiedbytigerdefaulttablespaceuserstemporarytablespacetemp;SQL>grantconnect,resourcetoscott;SQL>grantexecuteonsys.dbms_defertoscott;②、用scott身份[shenfen]登录[denglu]shenzhen数据[shuju]库[shujuku]，创建表deptSQL>createtabledept(deptnonumber(2)primarykey,dnamevarchar2(14),locvarchar2(13));③、如果数据[shuju]库[shujuku]对象[duixiang]没有主关键字[guanjianzi]，可以运行[yunhang]以下SQL命令[mingling]添加：SQL>altertabledeptadd(constraintdept_deptno_pkprimarykey(deptno));④、在shenzhen数据[shuju]库[shujuku]scott用户[yonghu]下创建主关键字[guanjianzi]的序列号[xuliehao]，范围[fanwei]避免和beijing的冲突[chongtu]。SQL>createsequencedept_noincrementby1startwith1maxvalue44cyclenocache;(说明[shuoming]：maxvalue44可以根据应用[yingyong]程序[yingyongchengxu]及表结构[jiegou]主关键字[guanjianzi]定义的位数需要而定)⑤、在shenzhen数据[shuju]库[shujuku]scott用户[yonghu]下插入初始化[chushihua]数据[shuju]SQL>insertintodeptvalues(dept_no.nextval,'accounting','newyork');SQL>insertintodeptvalues(dept_no.nextval,'research','dallas');SQL>commit;⑥、在beijing数据[shuju]库[shujuku]那边同样运行[yunhang]以上①，②，③⑦、在beijing数据[shuju]库[shujuku]scott用户[yonghu]下创建主关键字[guanjianzi]的序列号[xuliehao]，范围[fanwei]避免和shenzhen的冲突[chongtu]。SQL>createsequencedept_noincrementby1startwith45maxvalue99cyclenocache;⑧、在beijing数据[shuju]库[shujuku]scott用户[yonghu]下插入初始化[chushihua]数据[shuju]SQL>insertintodeptvalues(dept_no.nextval,'sales','chicago');SQL>insertintodeptvalues(dept_no.nextval,'operations','boston');SQL>commit;6、创建要复制的组scott_mg，加入数据[shuju]库[shujuku]对象[duixiang]，产生对象[duixiang]的复制支持[zhichi]①、用repadmin身份[shenfen]登录[denglu]shenzhen数据[shuju]库[shujuku]，创建主复制组scott_mgSQL>executedbms_repcat.create_master_repgroup('scott_mg');说明[shuoming]：scott_mg组名可以根据用户[yonghu]的需求[xuqiu]自由命名。②、在复制组scott_mg里加入数据[shuju]库[shujuku]对象[duixiang]SQL>executedbms_repcat.create_master_repobject(sname=>'scott',oname=>'dept',type=>'table',use_existing_object=>true,gname=>'scott_mg');参数[canshu]说明[shuoming]：sname实现数据[shuju]库[shujuku]复制的用户[yonghu]名[yonghuming]称oname实现数据[shuju]库[shujuku]复制的数据[shuju]库[shujuku]对象[duixiang]名[duixiangming]称(表名长度在27个字节内，程序包名长度在24个字节内)type实现数据[shuju]库[shujuku]复制的数据[shuju]库[shujuku]对象[duixiang]类别(支持[zhichi]的类别：表，索引[suoyin]，同义词，触发器[chufaqi]，视图[shitu]，过程，函数[hanshu]，程序包，程序包体)use_existing_objecttrue表示用主复制节点[jiedian]已经存在的数据[shuju]库[shujuku]对象[duixiang]gname主复制组名③、对数据[shuju]库[shujuku]对象[duixiang]产生复制支持[zhichi]SQL>executedbms_repcat.generate_replication_support('scott','dept','table');(说明[shuoming]：产生支持[zhichi]scott用户[yonghu]下dept表复制的数据[shuju]库[shujuku]触发器[chufaqi]和程序包)④、确认[queren]复制的组和对象[duixiang]已经加入数据[shuju]库[shujuku]的数据[shuju]字典[shujuzidian]SQL>selectgname,master,statusfromdba_repgroup; SQL>select*fromdba_repobject;7、创建主复制节点[jiedian]①、用repadmin身份[shenfen]登录[denglu]shenzhen数据[shuju]库[shujuku]，创建主复制节点[jiedian]SQL>executedbms_repcat.add_master_database (gname=>'scott_mg',master=>'beijing.test.com.cn',use_existing_objects=>true,copy_rows=>false,propagation_mode=>'asynchronous');参数[canshu]说明[shuoming]：gname主复制组名master加入主复制节点[jiedian]的另一个数据[shuju]库[shujuku]use_existing_objecttrue表示用主复制节点[jiedian]已经存在的数据[shuju]库[shujuku]对象[duixiang]copy_rowsfalse表示第一次开始复制时不用和主复制节点[jiedian]保持一致propagation_mode异步[yibu]地执行[zhihang]②、确认[queren]复制的任务[renwu]队列[duilie][renwuduilie]已经加入数据[shuju]库[shujuku]的数据[shuju]字典[shujuzidian]SQL>select*fromuser_jobs;8、使同步[tongbu]组的状态[zhuangtai]由停顿(quiesced)改为正常(normal)①、用repadmin身份[shenfen]登录[denglu]shenzhen数据[shuju]库[shujuku]，运行[yunhang]以下命令[mingling]SQL>executedbms_repcat.resume_master_activity('scott_mg',false);②、确认[queren]同步[tongbu]组的状态[zhuangtai]为正常(normal)SQL>selectgname,master,statusfromdba_repgroup;③、如果这个①命令[mingling]不能使同步[tongbu]组的状态[zhuangtai]为正常(normal)，可能有一些停顿的复制，运行[yunhang]以下命令[mingling]再试试(建议在紧急的时候才用)：SQL>executedbms_repcat.resume_master_activity('scott_mg',true);9、创建复制数据[shuju]库[shujuku]的时间表，我们假设用固定的时间表：10分钟复制一次。①、用repadmin身份[shenfen]登录[denglu]shenzhen数据[shuju]库[shujuku]，运行[yunhang]以下命令[mingling]SQL>begindbms_defer_sys.schedule_push(destination=>'beijing.test.com.cn',interval=>'sysdate+10/1440',next_date=>sysdate);end;/　 SQL>begindbms_defer_sys.schedule_purge(next_date=>sysdate,interval=>'sysdate+10/1440',delay_seconds=>0,rollback_segment=>'');end;/　 ②、用repadmin身份[shenfen]登录[denglu]beijing数据[shuju]库[shujuku]，运行[yunhang]以下命令[mingling]SQL>begindbms_defer_sys.schedule_push(destination=>'shenzhen.test.com.cn',interval=>'sysdate+10/1440',next_date=>sysdate);end;/　 SQL>begindbms_defer_sys.schedule_purge(next_date=>sysdate,interval=>'sysdate+10/1440',delay_seconds=>0,rollback_segment=>'');end;/10、添加或修改[xiugai]两边数据[shuju]库[shujuku]的记录，跟踪[genzong]复制过程如果你想立刻看到添加或修改[xiugai]后数据[shuju]库[shujuku]的记录的变化，可以在两边repadmin用户[yonghu]下找到push的job_number，然后运行[yunhang]：SQL>execdbms_job.run(job_number);三、异常[yichang]情况[qingkuang]的处理1、检查复制工作正常否，可以在repadmin用户[yonghu]下查询user_jobsSQL>selectjob,this_date,next_date,what,brokenfromuser_jobs;正常的状态[zhuangtai]有两种：任务[renwu]闲——this_date为空，next_date为当前时间[dangqianshijian]后的一个时间值任务[renwu]忙——this_date不为空，next_date为当前时间[dangqianshijian]后的一个时间值异常[yichang]状态[zhuangtai]也有两种：任务[renwu]死锁[sisuo]——next_date为当前时间[dangqianshijian]前的一个时间值任务[renwu]死锁[sisuo]——next_date为非常大的一个时间值，例如：4001-01-01这可能因为网络[wangluo]中断[zhongduan]照成的死锁[sisuo]解除死锁[sisuo]的办法：$ps–ef|greporale找到死锁[sisuo]的刷新[shuaxin]快照[kuaizhao]的进程[jincheng]号ora_snp*，用kill–9命令[mingling]删除[shanchu]此进程[jincheng]然后进入repadmin用户[yonghu]SQL>操作符[caozuofu]下，运行[yunhang]命令[mingling]：SQL>execdbms_job.run(job_number);说明[shuoming]：job_number为用selectjob,this_date,next_date,whatfromuser_jobs;命令[mingling]查出的job编号。2、增加或减少复制组的复制对象[duixiang]①、停止主数据[shuju]库[shujuku]节点[jiedian]的复制动作，使同步[tongbu]组的状态[zhuangtai]由正常(normal)改为停顿(quiesced)用repadmin身份[shenfen]登录[denglu]shenzhen数据[shuju]库[shujuku]，运行[yunhang]以下命令[mingling]SQL>executedbms_repcat.suspend_master_activity(gname=>'scott_mg');②、在复制组scott_mg里加入数据[shuju]库[shujuku]对象[duixiang]，保证数据[shuju]库[shujuku]对象[duixiang]必须有主关键字[guanjianzi]。SQL>executedbms_repcat.create_master_repobject(sname=>'scott',oname=>'emp',type=>'table',use_existing_object=>true,gname=>'scott_mg');对加入的数据[shuju]库[shujuku]对象[duixiang]产生复制支持[zhichi]SQL>executedbms_repcat.generate_replication_support('scott','emp','table');③、在复制组scott_mg里删除[shanchu]数据[shuju]库[shujuku]对象[duixiang]。SQL>executedbms_repcat.drop_master_repobject('scott','dept','table');④、重新使同步[tongbu]组的状态[zhuangtai]由停顿(quiesced)改为正常(normal)。SQL>executedbms_repcat.resume_master_activity('scott_mg',false);