Bo's Oracle Station

查看: 2099|回复: 0

活动第9/10次(2018-04-07星期六上下午)

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2018-4-7 10:44:22 | 显示全部楼层 |阅读模式
显式的savepoint:
  1. SQL> begin
  2.   2     for i in 1..555
  3.   3     loop
  4.   4       update t05209_z set a=i ;
  5.   5     end loop;
  6.   6     savepoint p555;
  7.   7     for i in 556..1000
  8.   8     loop
  9.   9       update t05209_z set a=i ;
  10. 10     end loop;
  11. 11  end;
  12. 12  /

  13. PL/SQL procedure successfully completed.

  14. SQL> select  * from t05209_z;

  15.          A
  16. ----------
  17.       1000

  18. SQL> rollback to p555;

  19. Rollback complete.

  20. SQL> select  * from t05209_z;

  21.          A
  22. ----------
  23.        555

复制代码

隐式的savepoint:

  1. SQL> select   * from t05209_dead;

  2.      EMPNO     SALARY           MGRNO
  3. ---------- ---------- ----------
  4.        100         1001              11
  5.        200         2001              21

  6. SQL> begin
  7.   2   for i in 1..1000
  8.   3   loop
  9.   4     update t05209_dead set SALARY=salary+i where EMPNO=100;
  10.   5   end loop;
  11.   6  end;
  12.   7  /

  13. PL/SQL procedure successfully completed.

  14. SQL> update t05209_dead set SALARY=salary+1  where EMPNO=200;
  15. update t05209_dead set SALARY=salary+1        where EMPNO=200
  16.        *
  17. ERROR at line 1:
  18. ORA-00060: deadlock detected while waiting for resource


  19. SQL> select * from t05209_dead ;

  20.      EMPNO     SALARY           MGRNO
  21. ---------- ---------- ----------
  22.        100     501501              11
  23.        200         2001              21

  24. SQL>
复制代码
  1. SQL> begin
  2.   2    for i in 1..1000
  3.   3    loop
  4.   4      update t05209_dead set MGRNO=MGRNO+i where EMPNO=200;
  5.   5    end loop;
  6.   6  end;
  7.   7  /

  8. PL/SQL procedure successfully completed.

  9. SQL> update t05209_dead set MGRNO=MGRNO+1 where EMPNO=100;

复制代码
Screenshot-PL-SQL Developer - sys@ORCL as SYSDBA - [SQL Window - select * from d.png

  1. select  f.file_name,f.autoextensible
  2. from dba_data_files f
  3. where f.tablespace_name='UNDOTBS1';
复制代码
  1. select * from dba_rollback_segs;

  2. select  t.tablespace_name, t.segment_space_management, t.retention
  3. from dba_tablespaces t ;

  4. select  f.file_name,f.autoextensible
  5. from dba_data_files f
  6. where f.tablespace_name='UNDOTBS1';

  7. alter tablespace undotbs1 retention guarantee;

  8. alter tablespace undotbs1 retention noguarantee;

  9. ----
  10. select  * from v$undostat;

  11. select * from v$rollstat;
复制代码
Screenshot.png
准备做快照太旧的实验:
  1. create undo tablespace undotbs2
  2. datafile size 256K autoextend off;

  3. select * from dba_rollback_segs;

  4. alter system set undo_tablespace=undotbs2;

  5. drop tablespace undotbs1;

  6.   select * from dba_rollback_segs;
  7.   
  8.    select  f.file_name,f.autoextensible
  9.     from dba_data_files f
  10.     where f.tablespace_name='UNDOTBS2';
  11.    
  12. select  t.tablespace_name, t.segment_space_management, t.retention
  13. from dba_tablespaces t ;

  14. alter tablespace temp add tempfile '/u01/app/oracle/oradata/orcl/temp02.dbf' size 10G;
复制代码
清理实验环境:
  1. select  * from v$transaction;

  2. select  * from v$session where taddr='00000001E45ABF60';

  3. alter system kill session '70,1555' immediate;

  4. create undo tablespace undotbs1 datafile size 40M autoextend on ;

  5. alter system set undo_tablespace=undotbs1;

  6. drop tablespace undotbs2;

  7. alter tablespace temp drop tempfile 2;
复制代码



回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-29 20:38 , Processed in 0.038681 second(s), 27 queries .

快速回复 返回顶部 返回列表