OCA-UNIT8-DATA

OCA 第八章主要实验

一： MERGE的用法
SQL> conn hr/hr
Connected.
SQL> drop table test1a;
Table dropped.
SQL> create table test1a(a number,b varchar2(10));
Table created.
SQL> drop table test1b
2 ;
Table dropped.
SQL> create table test1b(a number,b varchar2(10));
Table created.
SQL> insert into test1a values(1,'z1');
1 row created.
SQL> insert into test1a values(2,'w2');
1 row created.
SQL> insert into test1a values(4,'ma4');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test1a;
A B
---------- ----------
1 z1
2 w2
4 ma4
SQL> insert into test1b values(1,'w1');
1 row created.
SQL> insert into test1b values(2,'w2');
1 row created.
SQL> insert into test1b values(3,'li3');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test1b;
A B
---------- ----------
1 w1
2 w2
3 li3
SQL> merge into test1a a
2 using (select * from test1b) b
3 on (a.a = b.a)
4 when matched then update set a.b=b.b
5 when not matched then insert
6 (a.a,a.b)
7 values(b.a,b.b);
3 rows merged.
先不提交！
开另一个终端（是另一个终端，也就是另一个TERMINAL），HR登入
SQL> conn hr/hr
Connected.
SQL> select * from test1a;
A B
---------- ----------
1 z1
2 w2
4 ma4
SQL> select * from test1b;
A B
---------- ----------
1 w1
2 w2
3 li3
再原先的终端，执行：
SQL> commit;
Commit complete.
再在另一个终端查询：
SQL> select * from test1a;
A B
---------- ----------
1 w1
2 w2
4 ma4
3 li3
SQL> select * from test1b;
A B
---------- ----------
1 w1
2 w2
3 li3（看到没，TEST1A表已经更改了）
二： COMMIT 与 ROLLBACK
本实验仅验证COMMIT与ROLLBACK本身
（1） 不
COMMIT 后 CREATE TABLE(某个DDL语句)
SQL> show user
USER is "HR"
SQL> drop table test;
Table dropped.
SQL> create table test(a number);
Table created.
SQL> insert into test values(1);
1 row created.
先开另一个终端
SQL> conn hr/hr
Connected.
SQL> select * from test;
no rows selected
再回到原先终端
SQL> create table test2(a number);
create table test2(a number)
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
（哪怕本建成功，只要是发出了这样的命令）
在另一个终端看：
SQL> select * from test;
A
----------
1（之前的DDL语句帮你隐式提交了）
（2） 不
COMMIT 之后重新连接一个SESSION
SQL> show user
USER is "HR"
SQL> drop table test;
Table dropped.
SQL> create table test(a number);
Table created.
SQL> insert into test values(1);
1 row created.
在另一个终端：
SQL> select * from test;
no rows selected
SQL> show user
USER is "HR"
在原先终端的INSERT语句下
SQL> conn hr/hr
Connected.
在另一个终端：
SQL> select * from test;
A
----------
1（CONN 重新连一个SESSION会把之前不COMMIT的SESSION隐式提交）
（3） 不
COMMIT 之后显示退出EXIT
SQL> conn hr/hr
Connected.
SQL> drop table test;
Table dropped.
SQL> create table test(a number);
Table created.
SQL> insert into test values(1);
1 row created.
在另一个终端：
SQL> select * from test;
no rows selected
在原先的终端，执行
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
在之前新连的终端上敲
SQL> select * from test;
A
----------
1（它帮你隐式提交了）
（4）
COMMIT与ROLLBACK速度比较
SQL> conn / as sysdba
Connected.
SQL> drop table testsource1;
Table dropped.
SQL> drop table testsource2;
Table dropped.
SQL> create table testsource1 as select * from dba_source;
Table created.
SQL> create table testsource2 as select * from dba_source;
Table created.
SQL> select count(*) from dba_source;
COUNT(*)
----------
292541
SQL> set timing on
SQL> insert into testsource1 select * from testsource1;
292541 rows created.
Elapsed: 00:00:12.91
SQL> commit;
Commit complete.
Elapsed: 00:00:00.00（本实验主要关注COMMIT的速度）
SQL> insert into testsource2 select * from testsource2;
292541 rows created.
Elapsed: 00:00:09.00
SQL> rollback;
Rollback complete.
Elapsed: 00:00:00.43（表越大这种速度的差距越明显，这里只是示个意，因为COMMIT只记一条日志，而ROLLBACK是有几行就回退几行，行越多自然就越慢）
三： 函数 FUNCTION
SQL> create or replace function compute_tax (salary number)
2 return number
3 as
4 begin
5 if salary < 5000 then
6 return salary*.15;
7 else
8 return salary*.33;
9 end if;
10 end;
11 /
Function created.
SQL> select salary,compute_tax(salary) from employees;
SALARY COMPUTE_TAX(SALARY)
---------- -------------------
2600 390
2600 390
4400 660
13000 4290
6000 1980
6500 2145
10000 3300
12000 3960
8300 2739
24000 7920
17000 5610
SALARY COMPUTE_TAX(SALARY)
---------- -------------------
17000 5610
9000 2970
6000 1980
4800 720
4800 720
4200 630
12000 3960
9000 2970
8200 2706
7700 2541
7800 2574
SALARY COMPUTE_TAX(SALARY)
---------- -------------------
6900 2277
11000 3630
3100 465
2900 435
2800 420
2600 390
2500 375
8000 2640
8200 2706
7900 2607
6500 2145
四： 过程 PROCEDURE
SQL> show user
USER is "HR"
SQL> create table test_employees as select * from employees;
Table created.
SQL> create or replace procedure give_raise_to_all
2 as
3 begin
4 update hr.test_employees set salary = salary *1.05;
5 end;
6 /
Procedure created.
SQL> select salary,salary*1.05 from test_employees;
SALARY SALARY*1.05
---------- -----------
2600 2730
2600 2730
4400 4620
13000 13650
6000 6300
6500 6825
10000 10500
12000 12600
8300 8715
24000 25200
17000 17850
SALARY SALARY*1.05
---------- -----------
17000 17850
9000 9450
6000 6300
4800 5040
4800 5040
4200 4410
12000 12600
9000 9450
8200 8610
7700 8085
7800 8190
SQL> exec give_raise_to_all;
PL/SQL procedure successfully completed.
SQL> select salary from test_employees;
SALARY
----------
2730
2730
4620
13650
6300
6825
10500
12600
8715
25200
17850
SALARY
----------
17850
9450
6300
5040
5040
4410
12600
9450
8610
8085
8190(数据在运行完存储过程后得到了更改)
五： 程序包 PACKAGE
程序包是函数与过程的组合。通过将一些函数和过程组成一个程序包，性能和可维护性会有所提高。每个程序包应由两个独立编译的数据库对象组成：
SQL> show user
USER is "HR"
SQL> create or replace package money
2 as
3 procedure give_raise_to_all;
4 function compute_tax(salary in number) return number;
5 end;
6 /
Package created.
SQL> exec money.give_raise_to_all;
BEGIN money.give_raise_to_all; END;
*
ERROR at line 1:
ORA-04067: not executed, package body "HR.MONEY" does not exist
ORA-06508: PL/SQL: could not find program unit being called: "HR.MONEY"
ORA-06512: at line 1（明白？因为你只建了包头说明，没有把包体创建，创建是会成功，但是不能使用）
现在创建包体
create or replace package body money
as
procedure give_raise_to_all
as
begin
update hr.test_employees set salary = salary*1.05;
end;
function compute_tax(salary in number) return number
as
begin
if salary<5000 then
return salary*0.15;
else
return salary*0.33;
end if;
end;
end;
/
SQL> select salary,money.compute_tax(salary) from test_employees;
SALARY MONEY.COMPUTE_TAX(SALARY)
---------- -------------------------
2730 409.5
2730 409.5
4620 693
13650 4504.5
6300 2079
6825 2252.25
10500 3465
12600 4158
8715 2875.95
25200 8316
17850 5890.5
SALARY MONEY.COMPUTE_TAX(SALARY)
---------- -------------------------
17850 5890.5
9450 3118.5
6300 2079
5040 1663.2
SQL> select salary,salary*1.05 from test_employees;
SALARY SALARY*1.05
---------- -----------
2730 2866.5
2730 2866.5
4620 4851
13650 14332.5
6300 6615
6825 7166.25
10500 11025
12600 13230
8715 9150.75
25200 26460
17850 18742.5
SQL> execute money.give_raise_to_all;
PL/SQL procedure successfully completed.
SQL> select salary,salary*1.05 from test_employees;
SALARY SALARY*1.05
---------- -----------
2866.5 3009.825
2866.5 3009.825
4851 5093.55
14332.5 15049.125
6615 6945.75
7166.25 7524.5625
11025 11576.25
13230 13891.5
9150.75 9608.2875
26460 27783
18742.5 19679.625（看懂了吧？）
六： 内置程序包 DBMS_STATS
（1）ORACLE 10G推荐用DBMS_STATS来收集优化器统计信息
SQL> conn hr/hr
Connected.
SQL> create table testemp as select * from employees;
Table created.
SQL> select count(*) from testemp;
COUNT(*)
----------
107
SQL> conn / as sysdba
Connected.
SQL> select num_rows from dba_tables t where t.table_name='TESTEMP' and t.OWNER='HR';
NUM_ROWS （为什么，前面不是查出有107行了吗？）
----------
SQL> conn / as sysdba
Connected.
SQL> exec dbms_stats.gather_table_stats('hr','testemp');
PL/SQL procedure successfully completed.
SQL> select num_rows from dba_tables t where t.table_name='TESTEMP' and t.OWNER='HR';
NUM_ROWS
----------
107（有了）
（2） 看看统计信息的重要性 特别是它对于SQL执行计划的影响
SQL> conn / as sysdba
Connected.
SQL> create table t as select object_id,object_name from dba_objects where 1=2;
Table created.
SQL> create index ind_t on t(object_id);
Index created.
SQL> insert into t select object_id,object_name from dba_objects;
50410 rows created.
SQL> commit;
Commit complete.
SQL> select num_rows,avg_row_len,blocks,last_analyzed from dba_tables t where t.table_name='T';
NUM_ROWS AVG_ROW_LEN BLOCKS LAST_ANALYZE
---------- ----------- ---------- ------------
SQL> select blevel,leaf_blocks,distinct_keys,last_analyzed from dba_indexes i where i.table_name='T';
BLEVEL LEAF_BLOCKS DISTINCT_KEYS LAST_ANALYZE
---------- ----------- ------------- ------------
请注意，因为10G有动态采样，避免因动态采样对我们的实验造成的影响（动态采样是很好的一个功能，我说避免只是因为就这个实验而已）
我们只看执行计划
SQL> set autotrace trace explain
SQL> set timing on
SQL> select /*+ dynamic_sampling(t 0) */ * from t where object_id>30;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 4013845416
--------------------------------------------------------------------------------
-----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-----
| 0 | SELECT STATEMENT | | 4 | 316 | 1 (0)| 00:00
:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 4 | 316 | 1 (0)| 00:00
:01 |
|* 2 | INDEX RANGE SCAN | IND_T | 1 | | 2 (0)| 00:00
:01 |
--------------------------------------------------------------------------------
-----
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">30)
它走的是索引，真的走索引最好吗？
SQL> conn / as sysdba
Connected.
SQL> select count(*) from t;
COUNT(*)
----------
50410
SQL> select count(*) from t where object_id > 30;
COUNT(*)
----------
50381（总行数是50410，OBJECT_ID>30的行数是50381，你现在还认为它走索引最好吗）
什么叫优化器？OPTIMIZER。简而言之，就是ORACLE的调优大脑，你创建一张100万行的表，你知道，但是ORACLE不知道，也就是
说不要把你知道的跟ORACLE知不知道等同起来。所以我们才要收集优化器统计信息，让ORACLE知道）
SQL> show user
USER is "SYS"
SQL> exec dbms_stats.gather_table_stats('SYS','T');
PL/SQL procedure successfully completed.
SQL> select num_rows,avg_row_len,blocks,last_analyzed from dba_tables t where t.table_name='T';
NUM_ROWS AVG_ROW_LEN BLOCKS LAST_ANALYZE
---------- ----------- ---------- ------------
50410 28 237 29-DEC-10
SQL> select blevel,leaf_blocks,distinct_keys,last_analyzed from dba_indexes i where i.table_name='T';
BLEVEL LEAF_BLOCKS DISTINCT_KEYS LAST_ANALYZE
---------- ----------- ------------- ------------
1 180 50410 29-DEC-10
SQL> select /*+ dynamic_sampling(t 0) */ * from t where object_id>30;
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 50384 | 1377K| 56 (4)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 50384 | 1377K| 56 (4)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID">30)（很明显，走全表的COST越小，但请注意，不是说COST越小越好，没有绝对的，但至少在本例，他走全表的速度更快，COST更小）
七： 触发器 TRIGGER （基于值的审计）
SQL> conn / as sysdba
Connected.
SQL> create table hr_log(a varchar2(200)) tablespace users;
Table created.
SQL> desc hr_log;
Name Null? Type
----------------------------------------- -------- ----------------------------
A VARCHAR2(200)
SQL> select * from hr_log;
no rows selected
SQL> create or replace trigger trg_hrlog after logon on database
2 when (USER='HR')
3 begin
4 insert into hr_log values('Logon '||user||' at '||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')||sys_context('userenv','ip_address')||sys_context('userenv','os_user'));
5 commit;
6 end;
7 /
Trigger created.
SQL> select * from hr_log;
no rows selected
SQL> conn hr/hr
Connected.
SQL> conn / as sysdba
Connected.
SQL> select * from hr_log;
A
--------------------------------------------------------------------------------
Logon HR at 2010-12-29 16:27:58oracle
八： 锁定 LOCK
（1） 普通LOCK
SQL> conn hr/hr
Connected.
SQL> create table test(a number);
Table created.
SQL> insert into test values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
A
----------
1
SQL> update test set a=2 where a=1;
1 row updated.
在另一个终端
SQL> conn hr/hr
Connected.
SQL> update test set a=3 where a=1;（挂住了）
select s.sid,s.blocking_session from v$session s,v$lock l
where s.sid=l.sid and s.username='HR';
SID BLOCKING_SESSION
159 148
148
159 148
148
148 就是第一个HR的SID 它挡住了第二个HR 也就是159
在第一个终端
SQL> show user
USER is "HR"
SQL> commit;
Commit complete.
在第二个终端看
SQL> update test set a=3 where a=1;
0 rows updated.（没更新成功正常，因为A已经被第一个终端的HR改成了2，明白？）
（2） NOWAIT LOCK
在你要更新数据之前，通常要先获得对这个表的锁定
之前会挂住是因为，他会等待（WAIT）
这次我们来做个实验，是不等待的（NOWAIT），也就是获得不了锁的话直接报错。
SQL> show user
USER is "HR"
SQL> select * from test;
A
----------
2
SQL> update test set a=3 where a=2;
1 row updated.
不提交。
在另一个终端
SQL> show user
USER is "HR"
SQL> select * from test where a=2 for update nowait;
select * from test where a=2 for update nowait
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
在第一个终端
SQL> show user
USER is "HR"
SQL> commit;
Commit complete.（把它提交）
再在第二个终端
然后把之前的那个NOWAIT语句改一下（因为你上面COMMIT完后A变成3了，我们现在要做实验看效果）
SQL> select * from test where a=3 for update nowait;
A
----------
3（它只是将它锁定了并未更改）
SQL> select * from test;
A
----------
3
在另一个终端
SQL> update test set a=4 where a=3;（挂住了）
那就取消掉（CANCEL）
SQL> drop table test;
drop table test
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
（这个时候你想删表也是删不掉的）
如果这个时候第二个节点卡住，我要杀掉卡住第二个节点的会话
用SYS登入
select sid, serial#, username
from v$session where sid in
(select blocking_session from v$session)
查出SID=148 SERIAL#=10
alter system kill session '148,10';
这个时候，我们发现
第二个节点
SQL> update test set a=4 where a=3;
1 row updated.（可以执行了，不会卡住了）
第一个端点
SQL> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-00028: your session has been killed（被杀掉了）
九： 死锁
SQL> conn hr/hr
Connected.
SQL> drop table test;
Table dropped.
SQL> create table test(a number);
Table created.
SQL> insert into test values(1);
1 row created.
SQL> insert into test values(2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test;
A
----------
1
2
SQL> update test set a=2 where a=1;
1 row updated.
在另一个终端（也就是第二个）上
SQL> show user
USER is "HR"
SQL> update test set a=4 where a=2;
1 row updated.
第一个终端上
SQL> update test set a=5 where a=2;（挂住了）
在第二个终端
SQL> update test set a=6 where a=1;（也挂住了）
但是回到第一个终端，会看到
SQL> update test set a=5 where a=2;
update test set a=5 where a=2
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
（探测到了死锁）
在第一个终端上看到
SQL> select * from test;
A
----------
2
2
第一个2是由第一个UPDATE语句修改的，本SESSION可以看到，但是其他SESSION看不到
从本SESSION看出他已修改成功
但是第二个值却没有修改成功，由此看出，在生产环境碰到了死锁，要重新录入最后一条记录，否则你是没有UPDATE进去的，因为ORACLE回探测到它，并把它回滚了。