课程第55次：2016-07-23星期六上午

botang · 发表于 2016-7-23 22:10:42

课程第55次：2016-07-23星期六上午
【上完1Z0-053的第17章】Scheduler
【开始1Z0-052的第12章】AWR简介
【上完1Z0-051】：共12章（0 1 2 3 4 5 6 7 8 9 10 11）
【1Z0-052】：共17章（0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18）
【1Z0-053】：共15章（0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20）
表示已经上过的，表示还没上的。

jobremote.sql ：

alter system set dispatchers='(PROTOCOL=TCP) (dispatchers=6)';
select * from dba_users order by 1;
BEGIN
DBMS_XDB.SETHTTPPORT(8888);
END;
SQL> @?/rdbms/admin/prvtrsch.plb
select * from dba_users order by 1;
BEGIN
DBMS_SCHEDULER.SET_AGENT_REGISTRATION_PASS('mypassword');
END;
-----
SELECT DBMS_XDB.GETHTTPPORT() FROM DUAL;
--客户机器安装client中的定制---scheduleragent组件（一般填客户机主机名，端口1500）
客户机需要schagent -registerdatabase 192.168.0.90 8888 mypassword
--服务器验证：
DECLARE
versionnum VARCHAR2(30);
BEGIN
versionnum := DBMS_SCHEDULER.GET_AGENT_VERSION('192.168.0.199');
DBMS_OUTPUT.PUT_LINE(versionnum);
END;
select * from dba_scheduler_external_dests;
select * from dba_scheduler_db_dests;
begin
dbms_scheduler.create_credential('credential1','oracle','oracle');
end;
grant create external job to hr;
grant execute on credential1 to hr;
grant create job to hr;
select * from dba_scheduler_credentials;
grant all on station199 to hr;
----
begin
dbms_scheduler.create_credential('credential2','hr','botang123');
end;
select * from dba_scheduler_credentials;
---
select * from dba_scheduler_dests;
---
begin
dbms_scheduler.create_database_destination(
destination_name => 'orcl199',
agent => 'station199',
tns_name => '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.199)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl.example.com)
)
)
');
end;
select * from dba_scheduler_db_dests;
--
begin
dbms_scheduler.create_database_destination(destination_name => 'utforcl1992',agent => 'station199',
tns_name => '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.199)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = utforcl.example.com)))');
end;
grant all on utforcl1992 to hr;
begin
dbms_scheduler.create_database_destination(destination_name => 'orcl1992',agent => 'station199',
tns_name => '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.199)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.example.com)))');
end;
grant all on orcl1992 to hr;
select * from dba_scheduler_dests;
---
begin
dbms_scheduler.create_credential('credential3','hr','botang567');
end;
----
select * from dba_scheduler_credentials;
----
grant all on station199 to hr;
grant all on orcl199 to hr;
grant all on utforcl199 to hr;
----
grant execute on credential2 to hr;
grant execute on credential3 to hr;
----
---
BEGIN
sys.dbms_scheduler.create_job(
job_name => 'hr.job132',
job_type => 'STORED_PROCEDURE',
job_action => 'hr.procremotedb_win',
start_date => systimestamp at time zone 'PRC',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'job132',
auto_drop => FALSE,
enabled => FALSE,
credential_name => 'sys.credential2',
destination_name => 'SYS.utforcl1992');
end;
create or replace procedure procremotedb_win
is
begin
update employees set salary=salary+700
where employee_id=100;
commit;
end;
begin
procremotedb_win;
end;
select * from employees where employee_id=100;
--
begin
sys.dbms_scheduler.enable( 'hr.job132' );
END;
---
BEGIN
sys.dbms_scheduler.create_job(
job_name => 'hr.job133',
job_type => 'STORED_PROCEDURE',
job_action => 'hr.procremotedb_win',
start_date => systimestamp at time zone 'PRC',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'job133',
auto_drop => FALSE,
enabled => FALSE,
credential_name => 'sys.credential2',
destination_name => 'SYS.utforcl1992');
end;
---
begin
sys.dbms_scheduler.enable( 'hr.job133' );
END;
---
BEGIN
sys.dbms_scheduler.create_job(
job_name => 'hr.job134',
job_type => 'STORED_PROCEDURE',
job_action => 'hr.procremotedb_win',
start_date => systimestamp at time zone 'PRC',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'job134',
auto_drop => FALSE,
enabled => FALSE,
credential_name => 'sys.credential3',
destination_name => 'SYS.orcl1992');
end;
---
begin
sys.dbms_scheduler.enable( 'hr.job134' );
END;
---
---
select * from dba_scheduler_groups g where g.group_name='GROUP1';
select * from dba_scheduler_group_members gm where gm.group_name='GROUP1';
---
BEGIN
DBMS_SCHEDULER.create_group(
group_name => 'hr.group1',
group_type => 'DB_DEST',member => 'LOCAL,UTFORCL1992,ORCL1992'
);
END;
---
select * from dba_scheduler_groups g where g.group_name='GROUP1';
select * from dba_scheduler_group_members gm where gm.group_name='GROUP1';
---
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'hr.JOB14',
job_type => 'stored_procedure',
job_action => 'hr.procremotedb_win',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=minutely; bysecond=0',
end_date => SYSTIMESTAMP + 1/24,
credential_name => 'SYS.credential2',
destination_name => 'hr.group1',
enabled => TRUE);
END;

复制代码

stat.sql：

select t.TABLE_NAME, t.NUM_ROWS
from user_tables t
where t.TABLE_NAME='T04209_UNAME';
select i.NUM_ROWS
from user_indexes i where i.TABLE_NAME='T04209_UNAME';
select * from User_Tab_Col_Statistics tc where tc.table_name='T04209_UNAME';
begin
dbms_Stats.gather_table_stats('HR','T04209_UNAME',
method_opt=>'for columns uvalue size 254');
end;

复制代码