课程第42次

botang

1. SQL> show parameter service
   
2. 
   
3. NAME                                 TYPE        VALUE
   
4. ------------------------------------ ----------- ------------------------------
   
5. service_names                        string      orcl.example.com
   
6. SQL> alter system set service_names='orcl.example.com','serv1';
   
7. 
   
8. System altered.
   
9. 
   
10. SQL> exit
    
11. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64                                                                             bit Production
    
12. With the Partitioning, Automatic Storage Management, OLAP, Data Mining
    
13. and Real Application Testing options
    
14. [oracle@station76 ~]$ cd /u01/app/oracle/product/
    
15. [oracle@station76 product]$ ls
    
16. 11.2.0
    
17. [oracle@station76 product]$ cd 11.2.0/
    
18. [oracle@station76 11.2.0]$ ls
    
19. dbhome_1  grid
    
20. [oracle@station76 11.2.0]$ cd dbhome_1/
    
21. [oracle@station76 dbhome_1]$ ls
    
22. apex         emcli          mgw          rdbms
    
23. assistants   EMStage        network      relnotes
    
24. bin          has            nls          root.sh
    
25. ccr          hs             oc4j         scheduler
    
26. cdata        ide            odbc         slax
    
27. cfgtoollogs  install        olap         sqldeveloper
    
28. clone        instantclient  OPatch       sqlj
    
29. config       inventory      OPatch.ori   sqlplus
    
30. crs          j2ee           opmn         srvm
    
31. csmig        javavm         oracore      station76.example.com_orcl
    
32. css          jdbc           oraInst.loc  suptools
    
33. ctx          jdev           ord          sysman
    
34. cv           jdk            oui          timingframework
    
35. dbs          jlib           owb          ucp
    
36. dc_ocm       ldap           owm          uix
    
37. deinstall    lib            perl         usm
    
38. demo         log            plsql        utl
    
39. diagnostics  md             precomp      wwg
    
40. dv           mesg           racg         xdk
    
41. [oracle@station76 dbhome_1]$ cd network/
    
42. [oracle@station76 network]$ ls
    
43. admin  doc  install  jlib  lib  log  mesg  tools  trace
    
44. [oracle@station76 network]$ cd admin/
    
45. [oracle@station76 admin]$ ls
    
46. samples  shrept.lst  sqlnet.ora  sqlnet.ora.ori  tnsnames.ora
    
47. [oracle@station76 admin]$ vim tnsnames.ora
    
48. [oracle@station76 admin]$ lsnrctl services
    
49. 
    
50. LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-AUG-2019 11:54:58
    
51. 
    
52. Copyright (c) 1991, 2013, Oracle.  All rights reserved.
    
53. 
    
54. Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
    
55. Services Summary...
    
56. Service "+ASM" has 1 instance(s).
    
57.   Instance "+ASM", status READY, has 1 handler(s) for this service...
    
58.     Handler(s):
    
59.       "DEDICATED" established:638 refused:0 state:ready
    
60.          LOCAL SERVER
    
61. Service "dbtest.example.com" has 1 instance(s).
    
62.   Instance "dbtest", status UNKNOWN, has 1 handler(s) for this service...
    
63.     Handler(s):
    
64.       "DEDICATED" established:0 refused:0
    
65.          LOCAL SERVER
    
66. Service "mydb2" has 1 instance(s).
    
67.   Instance "mydb2", status UNKNOWN, has 1 handler(s) for this service...
    
68.     Handler(s):
    
69.       "DEDICATED" established:0 refused:0
    
70.          LOCAL SERVER
    
71. Service "orcl.example.com" has 1 instance(s).
    
72.   Instance "orcl", status READY, has 1 handler(s) for this service...
    
73.     Handler(s):
    
74.       "DEDICATED" established:3 refused:0 state:ready
    
75.          LOCAL SERVER
    
76. Service "rcat.example.com" has 1 instance(s).
    
77.   Instance "rcat", status READY, has 1 handler(s) for this service...
    
78.     Handler(s):
    
79.       "DEDICATED" established:0 refused:0 state:ready
    
80.          LOCAL SERVER
    
81. Service "rcatXDB.example.com" has 1 instance(s).
    
82.   Instance "rcat", status READY, has 1 handler(s) for this service...
    
83.     Handler(s):
    
84.       "D000" established:0 refused:0 current:0 max:1022 state:ready
    
85.          DISPATCHER <machine: station76.example.com, pid: 8268>
    
86.          (ADDRESS=(PROTOCOL=tcp)(HOST=station76.example.com)(PORT=42142))
    
87. Service "serv1.example.com" has 1 instance(s).
    
88.   Instance "orcl", status READY, has 1 handler(s) for this service...
    
89.     Handler(s):
    
90.       "DEDICATED" established:3 refused:0 state:ready
    
91.          LOCAL SERVER
    
92. The command completed successfully
    
93. [oracle@station76 admin]$
    

复制代码

分服务，分模块和分行动：

1. declare
   
2. v1 number;
   
3. begin
   
4.   dbms_application_info.set_module(module_name => 'GL',action_name => 'DEBIT_CAL');
   
5.   select count(*)  into v1 from t04209_uname a, t04209_uname b, t04209_uname c;
   
6. end;

复制代码

1. 
   
2. select s.sid, s.username, n."NAME", se."VALUE"
   
3. from v$session s, v$sesstat se , v$statname n
   
4. where s.sid=se.sid  and
   
5.        se.statistic#=n.statistic#  and
   
6.        s.terminal='pts/0'  and n."NAME"  like '%redo%';

复制代码

	SID	USERNAME	NAME	VALUE
1	202	HR	redo blocks read for recovery	0
2	202	HR	redo k-bytes read for recovery	0
3	202	HR	redo k-bytes read for terminal recovery	0
4	202	HR	redo entries	26197
5	202	HR	redo size	7916968
6	202	HR	redo entries for lost write detection	0
7	202	HR	redo size for lost write detection	0
8	202	HR	redo size for direct writes	0
9	202	HR	redo buffer allocation retries	0
10	202	HR	redo wastage	0
11	202	HR	redo writes	0
12	202	HR	redo blocks written	0
13	202	HR	redo write time	0
14	202	HR	redo blocks checksummed by FG (exclusive)	0
15	202	HR	redo blocks checksummed by LGWR	0
16	202	HR	redo log space requests	0
17	202	HR	redo log space wait time	0
18	202	HR	redo ordering marks	0
19	202	HR	redo subscn max counts	1301

上完了1Z0-052：0、1、2、3、4、5、6、9、10、12、13、14、15、16、18；上完了1Z0-053：0、1、2、3、4、5、6、7、8、9、10、11、12、13、14、15、19、20

1. select t.table_name, t.buffer_pool
   
2.   from dba_Tables t where t.owner='HR';
   
3. 
   
4. alter table hr.t17018  storage ( buffer_pool  keep );
   
5. 
   
6. 
   
7. select  i.index_name, i.buffer_pool, i.table_name
   
8. from dba_indexes i where i.owner='HR';
   
9. 
   
10. alter index hr.i17018 storage ( buffer_pool  keep );

复制代码

1. SQL>  create or replace function func1
   
2.   2   return number
   
3.   3  as
   
4.   4    v1  number;
   
5.   5  begin
   
6.   6   select  max(a)  into v1  from t17018;
   
7.   7   return v1;
   
8.   8  end;
   
9.   9  /
   

复制代码

1. select  * from v$db_object_cache doc
   
2. where doc."OWNER"='HR'  and doc."NAME"='FUNC1';
   
3. 
   
4. 
   
5. begin
   
6.     dbms_shared_pool.keep(name => 'HR.FUNC1');
   
7. end;

复制代码

默认cursor_sharing是exact,所以要去编程。

1. select  to_char(q."SQL_FULLTEXT"), q."SQL_ID",
   
2.         s."SQL_HASH_VALUE"
   
3.   from v$session s, v$sql q
   
4.   where s."SQL_ID"=q."SQL_ID"
   
5.   and s."TERMINAL"='pts/0';
   
6.   

复制代码

	TO_CHAR(Q."SQL_FULLTEXT")	SQL_ID	SQL_HASH_VALUE
1	update hr.t04209_uname set uvalue=7689 where uname='a7688'	02h6hbspdt4jd	719098413

改成similar以后：

	TO_CHAR(Q."SQL_FULLTEXT")	SQL_ID	SQL_HASH_VALUE
1	update hr.t04209_uname set uvalue=:"SYS_B_0" where uname=:"SYS_B_1"	dh73w3ss300hp	808452629

自顶向下 （从源头，抓大头）

1. select s.sid, s.username,ev."EVENT"
   
2. from v$session s, v$session_event ev
   
3. where
   
4.        ev."SID"=s."SID"
   
5.        and s."TERMINAL"='pts/0';
   
6.       

复制代码