过了个年,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