课程第14次（2017-08-07星期一)

botang

上完1Z0-052第6章网络
1Z0-052共19章（上完6章），1Z0-053共21章（上完2章）和1Z0-063多租户部分共9章（上完0章）
总共上完全部49章中的8章

共享服务器：

1. select  * from v$circuit;
   
2. 
   
3. select  * from v$session s where s.saddr='00000001EC922828';
   
4. 
   
5. select * from v_$dispatcher  s where s.PADDR='00000001EC73FDE0';
   
6. 
   
7. alter system kill session '76,4839' immediate;

复制代码

公共数据库链和普通数据库链：

1. [oracle@station90 admin]$ sqlplus /nolog
   
2. 
   
3. SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 7 20:25:29 2017
   
4. 
   
5. Copyright (c) 1982, 2009, Oracle.  All rights reserved.
   
6. 
   
7. SQL> conn hr/hr@utforcl199
   
8. ERROR:
   
9. ORA-28002: 7 天之后口令将过期
   
10. 
    
11. 
    
12. Connected.
    
13. SQL> select  salary from employees where employee_id=100;
    
14. 
    
15.     SALARY
    
16. ----------
    
17.      99999
    
18. 
    
19. SQL> exit
    
20. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    
21. With the Partitioning, OLAP, Data Mining and Real Application Testing options
    
22. [oracle@station90 admin]$ sqlplus /nolog
    
23. 
    
24. SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 7 20:25:55 2017
    
25. 
    
26. Copyright (c) 1982, 2009, Oracle.  All rights reserved.
    
27. 
    
28. SQL> conn hr/oracle_4U
    
29. ERROR:
    
30. ORA-15000: command disallowed by current instance type
    
31. 
    
32. 
    
33. SQL> exit
    
34. [oracle@station90 admin]$ . oraenv
    
35. ORACLE_SID = [+ASM] ? orcl
    
36. The Oracle base for ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1 is /u01/app/oracle
    
37. [oracle@station90 admin]$ sqlplus /nolog
    
38. 
    
39. SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 7 20:26:13 2017
    
40. 
    
41. Copyright (c) 1982, 2009, Oracle.  All rights reserved.
    
42. 
    
43. SQL> conn hr/oracle_4U
    
44. Connected.
    
45. SQL> select  salary from employees where employee_id=100;
    
46. 
    
47.     SALARY
    
48. ----------
    
49.      24000
    
50. 
    
51. SQL> create database link dblink1
    
52.   2   connect to hr identified by hr
    
53.   3   using 'utforcl199';
    
54. 
    
55. Database link created.
    
56. 
    
57. SQL> select  salary from employees@dblink1 where employee_id=100;
    
58. 
    
59. 
    
60.     SALARY
    
61. ----------
    
62.      99999
    
63. 
    
64. SQL> SQL> select  salary from employees where employee_id=100;
    
65. 
    
66.     SALARY
    
67. ----------
    
68.      24000
    
69. 
    
70. SQL> update employees@dblink1 set salary=88888 where employee_id=100;
    
71. 
    
72. 1 row updated.
    
73. 
    
74. SQL> commit;
    
75. 
    
76. Commit complete.
    
77. 
    
78. SQL> conn / as sysdba
    
79. Connected.
    
80. SQL> alter user sh identified by oracle_4U account unlock;
    
81. 
    
82. User altered.
    
83. 
    
84. SQL> select  salary from employees@dblink1 where employee_id=100;
    
85. select        salary from employees@dblink1 where employee_id=100
    
86.                               *
    
87. ERROR at line 1:
    
88. ORA-02019: connection description for remote database not found
    
89. 
    
90. 
    
91. SQL> select  salary from hr.employees@dblink1 where employee_id=100;
    
92. select        salary from hr.employees@dblink1 where employee_id=100
    
93.                                  *
    
94. ERROR at line 1:
    
95. ORA-02019: connection description for remote database not found
    
96. 
    
97. 
    
98. SQL> conn hr/oracle_4U
    
99. Connected.
    
100. SQL> grant execute on dblink1 to sh;
     
101. grant execute on dblink1 to sh
     
102.                  *
     
103. ERROR at line 1:
     
104. ORA-04042: procedure, function, package, or package body does not exist
     
105. 
     
106. 
     
107. SQL> grant select  on dblink1 to sh;
     
108. grant select  on dblink1 to sh
     
109.                  *
     
110. ERROR at line 1:
     
111. ORA-00942: table or view does not exist
     
112. 
     
113. 
     
114. SQL> create public  database link dblink1
     
115.   2  connect to hr identified by hr
     
116.   3  using 'utforcl199';
     
117. create public  database link dblink1
     
118.                              *
     
119. ERROR at line 1:
     
120. ORA-01031: insufficient privileges
     
121. 
     
122. 
     
123. SQL> /
     
124. 
     
125. Database link created.
     
126. 
     
127. SQL> conm sh/oracle_4U
     
128. SP2-0734: unknown command beginning "conm sh/or..." - rest of line ignored.
     
129. SQL> conn sh/oracle_4U
     
130. Connected.
     
131. SQL> select  salary from employees@dblink1 where employee_id=100;
     
132. 
     
133.     SALARY
     
134. ----------
     
135.      88888
     
136. 
     
137. SQL> update employees@dblink1  set salary=77777 where employee_id=100;
     
138. 
     
139. 1 row updated.
     
140. 
     
141. SQL> commit;
     
142. 
     
143. Commit complete.
     
144. 
     
145. SQL>
     

复制代码

锁竞争：

1. select  * from v$session where sid in (
   
2. select  s.BLOCKING_SESSION
   
3. from v_$session s where s.BLOCKING_SESSION is not null);
   
4. 
   
5. select  * from v_$lock l
   
6. where l.SID in ( 130,131)
   
7.   and type <> 'AE';
   

复制代码