课程第42次
SQL> show parameter serviceNAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string orcl.example.com
SQL> alter system set service_names='orcl.example.com','serv1';
System altered.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64 bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
$ cd /u01/app/oracle/product/
$ ls
11.2.0
$ cd 11.2.0/
$ ls
dbhome_1grid
$ cd dbhome_1/
$ ls
apex emcli mgw rdbms
assistants EMStage network relnotes
bin has nls root.sh
ccr hs oc4j scheduler
cdata ide odbc slax
cfgtoollogsinstall olap sqldeveloper
clone instantclientOPatch sqlj
config inventory OPatch.ori sqlplus
crs j2ee opmn srvm
csmig javavm oracore station76.example.com_orcl
css jdbc oraInst.locsuptools
ctx jdev ord sysman
cv jdk oui timingframework
dbs jlib owb ucp
dc_ocm ldap owm uix
deinstall lib perl usm
demo log plsql utl
diagnosticsmd precomp wwg
dv mesg racg xdk
$ cd network/
$ ls
admindocinstalljlibliblogmesgtoolstrace
$ cd admin/
$ ls
samplesshrept.lstsqlnet.orasqlnet.ora.oritnsnames.ora
$ vim tnsnames.ora
$ lsnrctl services
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 17-AUG-2019 11:54:58
Copyright (c) 1991, 2013, Oracle.All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:638 refused:0 state:ready
LOCAL SERVER
Service "dbtest.example.com" has 1 instance(s).
Instance "dbtest", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "mydb2" has 1 instance(s).
Instance "mydb2", status UNKNOWN, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0
LOCAL SERVER
Service "orcl.example.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:3 refused:0 state:ready
LOCAL SERVER
Service "rcat.example.com" has 1 instance(s).
Instance "rcat", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:0 refused:0 state:ready
LOCAL SERVER
Service "rcatXDB.example.com" has 1 instance(s).
Instance "rcat", status READY, has 1 handler(s) for this service...
Handler(s):
"D000" established:0 refused:0 current:0 max:1022 state:ready
DISPATCHER <machine: station76.example.com, pid: 8268>
(ADDRESS=(PROTOCOL=tcp)(HOST=station76.example.com)(PORT=42142))
Service "serv1.example.com" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Handler(s):
"DEDICATED" established:3 refused:0 state:ready
LOCAL SERVER
The command completed successfully
$
分服务，分模块和分行动：
declare
v1 number;
begin
dbms_application_info.set_module(module_name => 'GL',action_name => 'DEBIT_CAL');
select count(*)into v1 from t04209_uname a, t04209_uname b, t04209_uname c;
end;
select s.sid, s.username, n."NAME", se."VALUE"
from v$session s, v$sesstat se , v$statname n
where s.sid=se.sidand
se.statistic#=n.statistic#and
s.terminal='pts/0'and n."NAME"like '%redo%';
SIDUSERNAMENAMEVALUE
1202HRredo blocks read for recovery0
2202HRredo k-bytes read for recovery0
3202HRredo k-bytes read for terminal recovery0
4202HRredo entries26197
5202HRredo size7916968
6202HRredo entries for lost write detection0
7202HRredo size for lost write detection0
8202HRredo size for direct writes0
9202HRredo buffer allocation retries0
10202HRredo wastage0
11202HRredo writes0
12202HRredo blocks written0
13202HRredo write time0
14202HRredo blocks checksummed by FG (exclusive)0
15202HRredo blocks checksummed by LGWR0
16202HRredo log space requests0
17202HRredo log space wait time0
18202HRredo ordering marks0
19202HRredo subscn max counts1301
上完了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
select t.table_name, t.buffer_pool
from dba_Tables t where t.owner='HR';
alter table hr.t17018storage ( buffer_poolkeep );
selecti.index_name, i.buffer_pool, i.table_name
from dba_indexes i where i.owner='HR';
alter index hr.i17018 storage ( buffer_poolkeep );
SQL>create or replace function func1
2 return number
3as
4 v1number;
5begin
6 selectmax(a)into v1from t17018;
7 return v1;
8end;
9/
select* from v$db_object_cache doc
where doc."OWNER"='HR'and doc."NAME"='FUNC1';
begin
dbms_shared_pool.keep(name => 'HR.FUNC1');
end;默认cursor_sharing是exact,所以要去编程。
selectto_char(q."SQL_FULLTEXT"), q."SQL_ID",
s."SQL_HASH_VALUE"
from v$session s, v$sql q
where s."SQL_ID"=q."SQL_ID"
and s."TERMINAL"='pts/0';
TO_CHAR(Q."SQL_FULLTEXT")SQL_IDSQL_HASH_VALUE
1update hr.t04209_uname set uvalue=7689 where uname='a7688'02h6hbspdt4jd719098413
改成similar以后：
TO_CHAR(Q."SQL_FULLTEXT")SQL_IDSQL_HASH_VALUE
1update hr.t04209_uname set uvalue=:"SYS_B_0" where uname=:"SYS_B_1"dh73w3ss300hp808452629
自顶向下 （从源头，抓大头）
select s.sid, s.username,ev."EVENT"
from v$session s, v$session_event ev
where
ev."SID"=s."SID"
and s."TERMINAL"='pts/0';
页:
[1]