开发者社区> 小麦苗> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

Oracle变异表解决方法--ORA-04091错误

简介: Oracle变异表解决方法--ORA-04091错误 原因:触发器(或者被语句中引用的用户自定义PL/SQL函数)视图去查询(或修改)一个被另一语句修改而触发的表。
+关注继续查看

Oracle变异表解决方法--ORA-04091错误



原因:触发器(或者被语句中引用的用户自定义PL/SQL函数)视图去查询(或修改)一个被另一语句修改而触发的表。

解决方法:重写触发器(或函数)避免读该表。



对表进行DELETE,UPDATE,INSERT操作时,所操作的表就变成了变异表,对表的行级(FOR EACH ROW)触发器中不能对该表进行DML操作。这时可以使用两个方法解决。


[@more@]

1、采用自治事务pragma autonomous_transaction解决。

下面给出一个使用自治事务解决ORA-04091错误的示例:

CREATE OR REPLACE TRIGGER SCOTT.TRG_UPDATE_EMP

  AFTER UPDATE ON SCOTT.EMP

  FOR EACH ROW

DECLARE

  V_NUM NUMBER;

BEGIN

  SELECT COUNT(1) INTO V_NUM FROM SCOTT.EMP T WHERE DEPTNO = :NEW.DEPTNO;

  IF V_NUM > 2 THEN

    RAISE_APPLICATION_ERROR(-20001, V_NUM);

  END IF;

END;

执行更新操作报错ORA-04091

SYS@lhrdb> UPDATE SCOTT.EMP SET SAL=0 ;

UPDATE scott.emp set sal=0

             *

ERROR at line 1:

ORA-04091: table SCOTT.EMP is mutating, trigger/function may not see it

ORA-06512: at "SCOTT.TRG_UPDATE_EMP", line 4

ORA-04088: error during execution of trigger 'SCOTT.TRG_UPDATE_EMP'

这里加上自治事务后可以解决该问题:

CREATE OR REPLACE TRIGGER SCOTT.TRG_UPDATE_EMP

  AFTER UPDATE ON SCOTT.EMP

  FOR EACH ROW

DECLARE

  PRAGMA AUTONOMOUS_TRANSACTION;

  V_NUM NUMBER;

BEGIN

  SELECT COUNT(1) INTO V_NUM FROM SCOTT.EMP T WHERE DEPTNO = :NEW.DEPTNO;

  IF V_NUM > 2 THEN

    RAISE_APPLICATION_ERROR(-20001, V_NUM);

  END IF;

END;

再次执行:

SYS@lhrdb> UPDATE SCOTT.EMP SET SAL=0 ;

UPDATE SCOTT.EMP SET SAL=0

             *

ERROR at line 1:

ORA-20001: 4

ORA-06512: at "SCOTT.TRG_UPDATE_EMP", line 7

ORA-04088: error during execution of trigger 'SCOTT.TRG_UPDATE_EMP'


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

再给出一个例子

 使用scott方案,创建一下表、触发器:

[sql] view plain copy
  1. SQL> create table tr_table as select * from emp;  
  2.   
  3. 表已创建。  
  4.   
  5. SQL> edit  
  6. 已写入 file afiedt.buf  
  7.   
  8.   1  create or replace trigger tr_test  
  9.   2  after update on emp  
  10.   3  for each row  
  11.   4  begin  
  12.   5     update tr_table t set t.sal = (select sal from emp where empno=t.empno and empno=:new.empno);  
  13.   6* end;  
  14. SQL> /  
  15.   
  16. 触发器已创建  
  17.   
  18. SQL> update emp set sal=3700 where empno=7788;  
  19. update emp set sal=3700 where empno=7788  
  20.        *  
  21. 第 1 行出现错误:  
  22. <span style="color:#ff0000;"</span style=>ORA-04091: 表 SCOTT.EMP 发生了变化, 触发器/函数不能读它  
  23. ORA-06512: 在 "SCOTT.TR_TEST", line 2  
  24. ORA-04088: 触发器 'SCOTT.TR_TEST' 执行过程中出错  


3.原因分析:

在Oracle中执行DML语句的时候是需要显示进行提交操作的。当我们进行插入的时候,会触发触发器执行对触发器作用表和扩展表的种种操作,但是这个时候触发器和插入语句是在同一个事务管理中的,因此在插入语句没有被提交的情况下,我们无法对触发器作用表进行其他额外的操作。如果执行其他额外的操作则会抛出如上异常信息。


4.解决方案:

1) 我们知道,出错的原因是因为触发器和DML语句在同一事务管理中,所以方案一便是将触发器和DML语句分成两个单独的事务处理。这里可以使用Pragma autonomous_transaction; 告诉Oracle触发器是自定义事务处理。

SQL语句如下:


[sql] view plain copy
  1. create or replace trigger tr_test  
  2. after update on emp  
  3. for each row  
  4.   declare  
  5.   pragma autonomous_transaction;  
  6. begin  
  7.     update tr_table t set t.sal = (select sal from emp where empno=t.empno and empno=:new.empno);  
  8.     commit--此处需要显示提交  
  9. end;  


注:以上语句并不能实时获得更新的值。。。原因是我们在update emp表后还没来得及提交sal就触发了触发器,这个时候获取到的只能是老的sal值。

2) 在Oracle Trigger中有:new,:old两个特殊变量,当触发器为行级触发器的时候,触发器就会提供new和old两个保存临时行数据的特殊变量,我们可以从俩个特殊的变量中取出数据执行扩张表的DML操作。

SQL语句如下:


[sql] view plain copy
  1.  create or replace trigger tr_test  
  2.  after update on emp  
  3.  for each row  
  4.  begin  
  5.      update tr_table t set t.sal = :new.sal;  
  6.  end;  
  7. /  
  8.   
  9.    

5. 再次插入数据:


[sql] view plain copy
  1. SQL> update emp set sal=3800 where empno=7788;  
  2.   
  3. 已更新 1 行。  
  4.   
  5. SQL> commit;  
  6.   
  7. 提交完成。  


[sql] view plain copy
  1. SQL> select * from tr_table;  
  2.   
  3.      EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO  
  4. ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------  
  5.       7369 SMITH      CLERK           7902 17-12月-80            800                    20  
  6.       7499 ALLEN      SALESMAN        7698 20-2月 -81           1800        300         30  
  7.       7521 WARD       SALESMAN        7698 22-2月 -81           1250        500         30  
  8.       7566 JONES      MANAGER         7839 02-4月 -81           2975                    20  
  9.       7654 MARTIN     SALESMAN        7698 28-9月 -81           1250       1400         30  
  10.       7698 BLAKE      MANAGER         7839 01-5月 -81           2850                    30  
  11.       7782 CLARK      MANAGER         7839 09-6月 -81           2450                    10  
  12.       <span style="BACKGROUND-COLOR: #009900"</span style=>7788 SCOTT      ANALYST         7566 19-4月 -87           3800                    20  
  13.       7839 KING       PRESIDENT            17-11月-81           5000                    10  
  14.       7844 TURNER     SALESMAN        7698 08-9月 -81           1500          0         30  
  15.       7876 ADAMS      CLERK           7788 23-5月 -87           1100                    20  
  16.   
  17.      EMPNO ENAME      JOB              MGR HIREDATE              SAL       COMM     DEPTNO  
  18. ---------- ---------- --------- ---------- -------------- ---------- ---------- ----------  
  19.       7900 JAMES      CLERK           7698 03-12月-81            950                    30  
  20.       7902 FORD       ANALYST         7566 03-12月-81           3000                    20  
  21.       7934 MILLER     CLERK           7782 23-1月 -82           1300                    10  
  22.   
  23. 已选择14行。  






2、采用两个触发器(一个行级触发器FOR EACH ROW,一个表级触发器)和一个包来解决。


CREATE OR REPLACE PACKAGE ADATA AS
  TYPE T_AID IS TABLE OF A.AID%TYPE INDEX BY BINARY_INTEGER;
  TYPE T_ANO IS TABLE OF A.ANO%TYPE INDEX BY BINARY_INTEGER;
  V_AID        T_AID;
  V_ANO        T_ANO;
  V_NUMENTRIES BINARY_INTEGER := 0;
END ADATA;






---------------------------------------
CREATE OR REPLACE TRIGGER TR_A
  BEFORE INSERT OR UPDATE OF AID ON A
  FOR EACH ROW
BEGIN
  ADATA.V_NUMENTRIES := ADATA.V_NUMENTRIES + 1;
  ADATA.V_AID(ADATA.V_NUMENTRIES) := :NEW.AID;
  ADATA.V_ANO(ADATA.V_NUMENTRIES) := :NEW.ANO;
END TR_A;


----------------------------------------
CREATE OR REPLACE TRIGGER TR_B
  AFTER INSERT OR UPDATE OF AID ON A
BEGIN
  V_MAX CONSTANT NUMBER := 5;
  V_CUR NUMBER;
  V_AID A.AID%TYPE;
  V_ANO A.ANO%TYPE;
  BEGIN
    FOR V_LOOPINDEX IN 1 .. ADATA.V_NUMENTRIES LOOP
      V_AID := ADATA.V_AID(V_LOOPINDEX);
      V_ANO := ADATA.V_ANO(V_LOOPINDEX);
      SELECT COUNT(*) INTO V_CUR FROM A WHERE AID = V_AID;
      IF V_CUR > V_MAXS THEN
        RAISE_APPLICATION_ERROR(-20000,
                                'Too many students for major ' || V_AID ||
                                'because of student ' || V_ANO);
      END IF;
    END LOOP;
  END TR_B;


变异表是指激发触发器的DML语句所操作的表


当对一个表创建行级触发器时,有下列两条限制:


1.不能读取或修改任何触发语句的变异表;
2.不能读取或修改触发表的一个约束表的PRIMARY  KEY,UNIQUE 或FOREIGN KEY关键字的列, 但可以修改其他列


例如:有这样一个需求:在更新员工所在部门或向部门插入新员工时,部门中员工人数不超过7人


如果按照下面的触发器写就会使UPDATE操作时报错


CREATE OR REPLACE TRIGGER updatetrigger
BEFORE UPDATE ON EMP
FOR EACH ROW
DECLARE
  v_num NUMBER;
BEGIN
  SELECT count(*) INTO v_num FROM emp
  WHERE deptno = :new.deptno;
  IF (v_num > 7) THEN
    RAISE_APPLICATION_ERROR(-20001,
                            '员工数多于'||v_num);  
  END IF;
END updatetrigger;


 


ORA-04091: 表 SCOTT.EMP 发生了变化, 触发器/函数不能读它
ORA-06512: 在 "SCOTT.UPDATETRIGGER", line 4
ORA-04088: 触发器 'SCOTT.UPDATETRIGGER' 执行过程中出错


如果既想更新变异表,同时又需要查询变异表,那么如何处理呢?


将行级触发器与语句级触发器结合起来,在行级触发器中获取要修改的记录的信息,存放到一个软件包的全局变量中,然后在语句级后触发器中利用软件包中全局变量信息对变异表的查询,并根据查询的结果进行业务处理


例如:


为了实现在更新员工所在部门或向部门插入新员工时,部门中员工人数不超过7人,可以在emp表上创建两个触发器,同时创建一个共享信息的包


CREATE OR REPLACE PACKAGE mutate_pkg 
AS
  v_deptno NUMBER(2); 
END;


CREATE OR REPLACE TRIGGER  rmutate_trigger
BEFORE INSERT OR UPDATE OF deptno ON EMP 
FOR EACH ROW
BEGIN
  mutate_pkg.v_deptno:=:new.deptno;  
END;


CREATE OR REPLACE TRIGGER smutate_trigger
AFTER INSERT OR UPDATE OF deptno ON EMP
DECLARE
  v_num number(3);
BEGIN
  SELECT count(*) INTO v_num FROM emp 
  WHERE deptno = mutate_pkg.v_deptno;
  IF v_num>7 THEN
    RAISE_APPLICATION_ERROR(-20003,'这部门的员工太多了 '||
                            mutate_pkg.v_deptno);
  END IF;
END;


这样操作,就不会报ORA-04091: 表SCOTT.EMP 发生了变化,触发器/函数不能读它错误了。


本篇文章来源于 Linux公社网站(www.linuxidc.com)  原文链接:http://www.linuxidc.com/Linux/2013-11/93146.htm




ORA-04091错误解决

http://asktom.oracle.com/tkyte/Mutate/index.html

Avoiding Mutating Tables

Ok, so you've just recieved the error:

ORA-04091: table XXXX is mutating, trigger/function may not see it

and you want to get around that. This short article will describe and demonstrate the various methods of getting around the mutating table error.

If you are interested in why you are getting it and in what cases you will get it, please see the Oracle Server Application Developers Guide (click here to read it right now -- this link is to technet.oracle.com.  You need a password to access this site but you can get one right away for free).

Avoiding the mutating table error is fairly easy.  We must defer processing against the mutating or constrainng table until an AFTER trigger.  We will consider two cases: 

  • Hitting the ORA-4091 in an INSERT trigger or an UPDATE trigger where you only need access to the :new values
  • Hitting the ORA-4091 in a DELETE trigger or an UPDATE trigger where you need to access the :old values

Case 1 - you only need to access the :new values

This case is the simplest.  What we will do is capture the ROWIDS of the inserted or udpated rows.  We can then use these ROWIDS in an AFTER trigger to query up the affected rows.

It always takes 3 triggers to work around the mutating table error.  They are: 

  • A before trigger to set the package state to a known, consistent state
  • An after, row level trigger to capture each rows changes
  • An after trigger to actually process the change.
As an example -- to show how to do this, we will attempt to answer the following question: 
 
I have a table containing a key/status/effective date combination.  When status 
changes, the values are propagated by trigger to a log table recording the 
status history.  When no RI constraint is in place everything works fine.

When an RI trigger enforces a parent-child relationship, the status change 
logging trigger fails because the parent table is mutating.  Propagating the 
values to the log table implicitly generates a lookup back to the parent table 
to ensure the RI constraint is satisfied.

I do not want to drop the RI constraint.  I realize that the status is 
denormalized.  I want it that way.  What is a good way to maintain the log?

Here is the implementation:

SQL> create table parent 
  2  ( theKey        int primary key, 
  3    status        varchar2(1), 
  4    effDate       date 
  5  ) 
  6  / 
Table created.

SQL> create table log_table 
  2  (       theKey  int references parent(theKey), 
  3          status  varchar2(1), 
  4          effDate date 
  5  ) 
  6  / 
Table created.

SQL> REM this package is used to maintain our state.  We will save the rowids of newly 
SQL> REM inserted / updated rows in this package.  We declare 2 arrays -- one will 
SQL> REM hold our new rows rowids (newRows).  The other is used to reset this array, 
SQL> REM it is an 'empty' array

SQL> create or replace package state_pkg 
  2  as 
  3          type ridArray is table of rowid index by binary_integer; 
  4 
  4          newRows ridArray; 
  5          empty   ridArray; 
  6  end; 
  7  / 
Package created.

SQL> REM We must set the state of the above package to some known, consistent state 
SQL> REM before we being processing the row triggers.  This trigger is mandatory, 
SQL> REM we *cannot* rely on the AFTER trigger to reset the package state.  This 
SQL> REM is because during a multi-row insert or update, the ROW trigger may fire 
SQL> REM but the AFTER tirgger does not have to fire -- if the second row in an update 
SQL> REM fails due to some constraint error -- the row trigger will have fired 2 times 
SQL> REM but the AFTER trigger (which we relied on to reset the package) will never fire. 
SQL> REM That would leave 2 erroneous rowids in the newRows array for the next insert/update 
SQL> REM to see.   Therefore, before the insert / update takes place, we 'reset'

SQL> create or replace trigger parent_bi 
  2  before insert or update on parent 
  3  begin 
  4          state_pkg.newRows := state_pkg.empty; 
  5  end; 
  6  / 
Trigger created.

SQL> REM This trigger simply captures the rowid of the affected row and 
SQL> REM saves it in the newRows array.

SQL> create or replace trigger parent_aifer 
  2  after insert or update of status on parent for each row 
  3  begin 
  4          state_pkg.newRows( state_pkg.newRows.count+1 ) := :new.rowid; 
  5  end; 
  6  / 
Trigger created.

SQL> REM this trigger processes the new rows.  We simply loop over the newRows 
SQL> REM array processing each newly inserted/modified row in turn.

SQL> create or replace trigger parent_ai 
  2  after insert or update of status on parent 
  3  begin 
  4          for i in 1 .. state_pkg.newRows.count loop 
  5                  insert into log_table 
  6                  select theKey, status, effDate 
  7                    from parent where rowid = state_pkg.newRows(i); 
  8          end loop; 
  9  end; 
 10  / 
Trigger created.

SQL> REM this demonstrates that we can process single and multi-row inserts/updates 
SQL> REM without failure (and can do it correctly)

SQL> insert into parent values ( 1, 'A', sysdate-5 ); 
1 row created.

SQL> insert into parent values ( 2, 'B', sysdate-4 ); 
1 row created.

SQL> insert into parent values ( 3, 'C', sysdate-3 ); 
1 row created.

SQL> insert into parent select theKey+6, status, effDate+1 from parent; 
3 rows created.

SQL> select * from log_table;

    THEKEY S EFFDATE 
---------- - --------- 
         1 A 04-AUG-99 
         2 B 05-AUG-99 
         3 C 06-AUG-99 
         7 A 05-AUG-99 
         8 B 06-AUG-99 
         9 C 07-AUG-99

6 rows selected.

SQL> update parent set status = chr( ascii(status)+1 ), effDate = sysdate; 
6 rows updated.

SQL> select * from log_table;

    THEKEY S EFFDATE 
---------- - --------- 
         1 A 04-AUG-99 
         2 B 05-AUG-99 
         3 C 06-AUG-99 
         7 A 05-AUG-99 
         8 B 06-AUG-99 
         9 C 07-AUG-99 
         1 B 09-AUG-99 
         2 C 09-AUG-99 
         3 D 09-AUG-99 
         7 B 09-AUG-99 
         8 C 09-AUG-99 
         9 D 09-AUG-99

12 rows selected. 
 

Case 2 - you need to access the :old values

This one is a little more involved but the concept is the same.  We'll save the actual OLD values in an array (as opposed to just the rowids of the new rows).  Using tables of records this is fairly straightforward.  Lets say we wanted to implement a flag delete of data -- that is, instead of actually deleting the record, you would like to set a date field to SYSDATE and keep the record in the table (but hide it from queries).  We need to 'undo' the delete.

In Oracle8.0 and up, we could use "INSTEAD OF" triggers on a view to do this, but in 7.3 the implementation would look like this: 

SQL> REM this is the table we will be flag deleting from. 
SQL> REM No one will ever access this table directly, rather, 
SQL> REM they will perform all insert/update/delete/selects against 
SQL> REM a view on this table..

SQL> create table delete_demo ( a            int, 
  2                             b            date, 
  3                             c            varchar2(10), 
  4                             hidden_date  date default to_date( '01-01-0001', 'DD-MM-YYYY' ), 
  5                             primary key(a,hidden_date) ) 
  6  / 
Table created.

SQL> REM this is our view.  All DML will take place on the view, the table 
SQL> REM will not be touched.

SQL> create or replace view delete_demo_view as 
  2  select a, b, c from delete_demo where hidden_date = to_date( '01-01-0001', 'DD-MM-YYYY' ) 
  3  / 
View created.

SQL> grant all on delete_demo_view to public 
  2  / 
Grant succeeded.

SQL> REM here is the state package again.  This time the array is of 
SQL> REM TABLE%ROWTYPE -- not just a rowid

SQL> create or replace package delete_demo_pkg 
  2  as 
  3      type array is table of delete_demo%rowtype index by binary_integer; 
  4 
  4      oldvals    array; 
  5      empty    array; 
  6  end; 
  7  / 
Package created.

SQL> REM the reset trigger...

SQL> create or replace trigger delete_demo_bd 
  2  before delete on delete_demo 
  3  begin 
  4      delete_demo_pkg.oldvals := delete_demo_pkg.empty; 
  5  end; 
  6  / 
Trigger created.

SQL> REM Here, instead of capturing the rowid, we must capture the before image 
SQL> REM of the row. 
SQL> REM We cannot really undo the delete here, we are just capturing the deleted 
SQL> REM data

SQL> create or replace trigger delete_demo_bdfer 
  2  before delete on delete_demo 
  3  for each row 
  4  declare 
  5      i    number default delete_demo_pkg.oldvals.count+1; 
  6  begin 
  7      delete_demo_pkg.oldvals(i).a := :old.a; 
  8      delete_demo_pkg.oldvals(i).b := :old.b; 
  9      delete_demo_pkg.oldvals(i).c := :old.c; 
 10  end; 
 11  / 
Trigger created.

SQL> REM Now, we can put the deleted data back into the table.  We put SYSDATE 
SQL> REM in as the hidden_date field -- that shows us when the record was deleted.

SQL> create or replace trigger delete_demo_ad 
  2  after delete on delete_demo 
  3  begin 
  4      for i in 1 .. delete_demo_pkg.oldvals.count loop 
  5          insert into delete_demo ( a, b, c, hidden_date ) 
  6          values 
  7          ( delete_demo_pkg.oldvals(i).a, delete_demo_pkg.oldvals(i).b, 
  8            delete_demo_pkg.oldvals(i).c, sysdate ); 
  9      end loop; 
 10  end; 
 11  / 
Trigger created.

SQL> REM Now, to show it at work... 
SQL> insert into delete_demo_view values ( 1, sysdate, 'Hello' ); 
1 row created.

SQL> insert into delete_demo_view values ( 2, sysdate, 'Goodbye' ); 
1 row created.

SQL> select * from delete_demo_view;

         A B         C 
---------- --------- ---------- 
         1 09-AUG-99 Hello 
         2 09-AUG-99 Goodbye

SQL> delete from delete_demo_view; 
2 rows deleted.

SQL> select * from delete_demo_view; 
no rows selected

SQL> select * from delete_demo;

         A B         C          HIDDEN_DA 
---------- --------- ---------- --------- 
         1 09-AUG-99 Hello      09-AUG-99 
         2 09-AUG-99 Goodbye    09-AUG-99 
  
  
  

All information and materials provided here are provided "as-is"; Oracle disclaims all express and implied warranties, including, the implied warranties of merchantability or fitness for a particular use. Oracle shall not be liable for any damages, including, direct, indirect, incidental, special or consequential damages for loss of profits, revenue, data or data use, incurred by you or any third party in connection with the use of this information or these materials.






About Me

...............................................................................................................................

● 本文转载自网络http://blog.itpub.net/143904/viewspace-862876/

● 本文在itpub(http://blog.itpub.net/26736162)、博客园(http://www.cnblogs.com/lhrbest)和个人微信公众号(xiaomaimiaolhr)上有同步更新

● 本文itpub地址:http://blog.itpub.net/26736162/abstract/1/

● 本文博客园地址:http://www.cnblogs.com/lhrbest

● 本文pdf版及小麦苗云盘地址:http://blog.itpub.net/26736162/viewspace-1624453/

● 数据库笔试面试题库及解答:http://blog.itpub.net/26736162/viewspace-2134706/

● QQ群:230161599     微信群:私聊

● 联系我请加QQ好友(646634621),注明添加缘由

● 于 2017-05-09 09:00 ~ 2017-05-30 22:00 在魔都完成

● 文章内容来源于小麦苗的学习笔记,部分整理自网络,若有侵权或不当之处还请谅解

● 版权所有,欢迎分享本文,转载请保留出处

...............................................................................................................................

拿起手机使用微信客户端扫描下边的左边图片来关注小麦苗的微信公众号:xiaomaimiaolhr,扫描右边的二维码加入小麦苗的QQ群,学习最实用的数据库技术。

img_e3029f287d989cd04bd75432ecc1c172.png
DBA笔试面试讲解
欢迎与我联系

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
oracle将指定的表中所有字段变为大写
最近在写一个项目,用到的是oracle数据库,但是在执行oracle语句的时候总是提示找不到字段,只有将字段名称用双引号引起来才可以解决问题,最后发现是由于数据库字段名称是小写造成的,但是一个一个的手动替换太过于麻烦,要是将所有的表都替换风险又太大,因为自己涉及到的只有新增的七八张表,所以用了一个指定表名称来替换的方法。
20 0
一个开发需求的解决方案 &amp; Oracle临时表介绍
一、开发需求 最近有一个开发需求,大致需要先使用主表,或主表和几张子表关联查询出ID(主键)及一些主表字段,然后再用这些ID查找最多10张表中对应的记录,主表记录数大约2000万,每张子表的记录数均为百万以上,最多可能会有5000万,主表一条数据可能对应子表多条数据。
763 0
【Oracle】并行等待之PX Deq Credit: send blkd
本系列文章将会介绍在并行操作过程中 slave 进程和 QC 进程经常遇到的等待事件! PX Deq Credit: send blkd等待事件: Waiting Process: QC 想发送信息的进程没有credit bit。
608 0
【体系结构】有关Oracle SCN知识点的整理
                                                                                                                            ...
678 0
oracle查看所有表及字段
oracle表设计 http://blog.csdn.net/lanpy88/article/details/7580820 Oracle查看所有表和字段 获取表: select table_name from user_tables; //当前用户的表        select...
2015 0
+关注
小麦苗
小麦苗,专注于数据库,Oracle OCM,PostgreSQL PGCM,PostgreSQL ACE,中国PG分会官方认证讲师,PGfans签约作者,PGfans年度MVP;微信公众号: DB宝,个人网站:www.xmmup.com
889
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
低代码开发师(初级)实战教程
立即下载
阿里巴巴DevOps 最佳实践手册
立即下载
冬季实战营第三期:MySQL数据库进阶实战
立即下载