源码-Oracle数据库管理-第十八章-事务和锁-Part 2_1（使用Oracle锁）_oracle 在读取或执行并读取之前没有完成定义-CSDN博客

本文链接：https://blog.csdn.net/hpdlzu80100/article/details/55046968
过了个年，Oracle的学习有所放松。
今天，就让我采用“断点恢复”技术，回到正轨吧
概念：什么是Oracle锁？
锁是Oracle提供的用于并发访问情况下的数据保护机制，用于防止当多个用户访问一个数据对象时造成的对数据的破坏。
这部分调试花了我不少时间，记录如下。
--2017/02/13
--18.2 使用Oracle锁
--18.2.1 什么是锁

scott_pd@ORCL> --18.2.2 使用锁
scott_pd@ORCL> UPDATE emp SET sal=5000 where empno=4093;
更新了sal字段

已更新 1 行。

scott_pd@ORCL> select (select username from v$session where sid = v$lock.sid) username,
  2  sid,
  3  id1,
  4  id2,
  5  lmode,
  6  request,
  7  block,
  8  v$lock.type
  9  from v$lock
 10  where sid = (select sid from v$mystat where rownum = 1);
select (select username from v$session where sid = v$lock.sid) username,
                             *
第 1 行出现错误:
ORA-00942: 表或视图不存在


scott_pd@ORCL> conn system as sysdba
已连接。

GLOBAL_NAME
----------------------------------------------------------------------------------------------------
sys_pd@ORCL

sys_pd@ORCL> edifile
SP2-0042: 未知命令 "edifile" - 其余行忽略。
sys_pd@ORCL> editfile
SP2-0042: 未知命令 "editfile" - 其余行忽略。
sys_pd@ORCL> edi
已写入 file afiedt.buf

  1  select lower(user)||'_pd'|| '@' || substr( global_name, 1, decode( dot, 0, length(global_name), dot-1) ) global_name
  2*   from (select global_name, instr(global_name,'.') dot from global_name )
sys_pd@ORCL> edit
已写入 file afiedt.buf

  1  --查询锁定信息
  2  select (select username from v$session where sid = v$lock.sid) username,
  3         sid,
  4         id1,
  5         id2,
  6         lmode,
  7         request,
  8         block,
  9         v$lock.type
 10    from v$lock
 11*  where sid = (select sid from v$mystat where rownum = 1);
sys_pd@ORCL> /
--查询锁定信息
*
第 1 行出现错误:
ORA-24374: 在读取或执行并读取之前没有完成定义


sys_pd@ORCL> edit
已写入 file afiedt.buf

  1  select (select username from v$session where sid = v$lock.sid) username,
  2         sid,
  3         id1,
  4         id2,
  5         lmode,
  6         request,
  7         block,
  8         v$lock.type
  9    from v$lock
 10*  where sid = (select sid from v$mystat where rownum = 1);
sys_pd@ORCL> /
 where sid = (select sid from v$mystat where rownum = 1);
                                                        *
第 10 行出现错误:
ORA-00911: 无效字符


sys_pd@ORCL> edit
已写入 file afiedt.buf

  1  select (select username from v$session where sid = v$lock.sid) username,
  2         sid,
  3         id1,
  4         id2,
  5         lmode,
  6         request,
  7         block,
  8         v$lock.type
  9    from v$lock
 10*  where sid = (select sid from v$mystat where rownum = 1)
sys_pd@ORCL> /

USERNAME                                                            SID        ID1        ID2
------------------------------------------------------------ ---------- ---------- ----------
     LMODE    REQUEST      BLOCK TYPE
---------- ---------- ---------- ----
SYS                                                                 198        100          0
         4          0          0 AE


sys_pd@ORCL> conn scott
已连接。

GLOBAL_NAME
----------------------------------------------------------------------------------------------------
scott_pd@ORCL

scott_pd@ORCL> edit
已写入 file afiedt.buf

  1  select (select username from v$session where sid = v$lock.sid) username,
  2         sid,
  3         id1,
  4         id2,
  5         lmode,
  6         request,
  7         block,
  8         v$lock.type
  9    from v$lock
 10*  where sid = (select sid from v$mystat where rownum = 1)
scott_pd@ORCL> /
select (select username from v$session where sid = v$lock.sid) username,
                             *
第 1 行出现错误:
ORA-00942: 表或视图不存在


scott_pd@ORCL> select empno, sal from emp where empno=7369;

     EMPNO        SAL
---------- ----------
      7369    10035.2

scott_pd@ORCL> edit
已写入 file afiedt.buf

  1  select (select username from v$session where sid = v$lock.sid) username,
  2         sid,
  3         id1,
  4         id2,
  5         lmode,
  6         request,
  7         block,
  8         v$lock.type
  9    from v$lock
 10*  where sid = (select sid from v$mystat where rownum = 1)
scott_pd@ORCL> /
select (select username from v$session where sid = v$lock.sid) username,
                             *
第 1 行出现错误:
ORA-00942: 表或视图不存在


scott_pd@ORCL> drop table emp;

表已删除。

scott_pd@ORCL> select * from emp;
select * from emp
              *
第 1 行出现错误:
ORA-00942: 表或视图不存在


scott_pd@ORCL> rollback
  2  ;

回退已完成。

scott_pd@ORCL> select count(1) from emp;
select count(1) from emp
                     *
第 1 行出现错误:
ORA-00942: 表或视图不存在


scott_pd@ORCL> select * from emp;
select * from emp
              *
第 1 行出现错误:
ORA-00942: 表或视图不存在


scott_pd@ORCL> spool out


scott_pd@ORCL> select * from emp;

     EMPNO ENAME                JOB                       MGR HIREDATE             SAL2       COMM
---------- -------------------- ------------------ ---------- -------------- ---------- ----------
    DEPTNO
----------
      7509 pd                   cont                     2166 17-12月-07        5632000      18000
        40

      5093 pdh                  cont                     2166 03-8月 -15         640000       1000
        40

      5021 hpd                  cont                     2166 01-2月 -15        1280000       2000
        40


scott_pd@ORCL> update emp set sal=8888 whwere empno=5093;
update emp set sal=8888 whwere empno=5093
                        *
第 1 行出现错误:
ORA-00933: SQL 命令未正确结束


scott_pd@ORCL> update emp set sal=8888 where empno=5093;
update emp set sal=8888 where empno=5093
               *
第 1 行出现错误:
ORA-00904: "SAL": 标识符无效


scott_pd@ORCL> update emp set sal2=8888 where empno=5093;

已更新 1 行。

scott_pd@ORCL> edit
已写入 file afiedt.buf

  1  select (select username from v$session where sid = v$lock.sid) username,
  2         sid,
  3         id1,
  4         id2,
  5         lmode,
  6         request,
  7         block,
  8         v$lock.type
  9    from v$lock
 10*  where sid = (select sid from v$mystat where rownum = 1)
scott_pd@ORCL> /
select (select username from v$session where sid = v$lock.sid) username,
                             *
第 1 行出现错误:
ORA-00942: 表或视图不存在


scott_pd@ORCL> conn system
ERROR:
ORA-01017: invalid username/password; logon denied


警告: 您不再连接到 ORACLE。
scott_pd@ORCL> conn system
已连接。

GLOBAL_NAME
----------------------------------------------------------------------------------------------------
system_pd@ORCL

system_pd@ORCL> edit
已写入 file afiedt.buf

  1  select (select username from v$session where sid = v$lock.sid) username,
  2         sid,
  3         id1,
  4         id2,
  5         lmode,
  6         request,
  7         block,
  8         v$lock.type
  9    from v$lock
 10*  where sid = (select sid from v$mystat where rownum = 1)
system_pd@ORCL> /

USERNAME                                                            SID        ID1        ID2
------------------------------------------------------------ ---------- ---------- ----------
     LMODE    REQUEST      BLOCK TYPE
---------- ---------- ---------- ----
SYSTEM                                                              134        100          0
         4          0          0 AE


system_pd@ORCL> update scott.emp set sal2=8899 where empno=5093;

已更新 1 行。

system_pd@ORCL> edit
已写入 file afiedt.buf

  1  select (select username from v$session where sid = v$lock.sid) username,
  2         sid,
  3         id1,
  4         id2,
  5         lmode,
  6         request,
  7         block,
  8         v$lock.type
  9    from v$lock
 10*  where sid = (select sid from v$mystat where rownum = 1)
system_pd@ORCL> /

USERNAME                                                            SID        ID1        ID2
------------------------------------------------------------ ---------- ---------- ----------
     LMODE    REQUEST      BLOCK TYPE
---------- ---------- ---------- ----
SYSTEM                                                              134        100          0
         4          0          0 AE

SYSTEM                                                              134      80709          0
         3          0          0 TM

SYSTEM                                                              134     655370       2177
         6          0          0 TX


system_pd@ORCL> commit;

提交完成。

system_pd@ORCL> select * from emp;
select * from emp
              *
第 1 行出现错误:
ORA-00942: 表或视图不存在


system_pd@ORCL> select * from scott.emp;

     EMPNO ENAME                JOB                       MGR HIREDATE             SAL2       COMM
---------- -------------------- ------------------ ---------- -------------- ---------- ----------
    DEPTNO
----------
      7509 pd                   cont                     2166 17-12月-07        5632000      18000
        40

      5093 pdh                  cont                     2166 03-8月 -15           8899       1000
        40

      5021 hpd                  cont                     2166 01-2月 -15        1280000       2000
        40


system_pd@ORCL> update emp set sal2=404 where empno=5093;
update emp set sal2=404 where empno=5093
       *
第 1 行出现错误:
ORA-00942: 表或视图不存在


system_pd@ORCL> update scott.emp set sal2=404 where empno=5093;

已更新 1 行。

system_pd@ORCL> spool out