设为首页收藏本站

Botang唐波's Oracle Station

查看: 671|回复: 0

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

[复制链接]

640

主题

995

帖子

7333

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
7333
发表于 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 :
  1. alter system set dispatchers='(PROTOCOL=TCP) (dispatchers=6)';

  2. select  * from dba_users  order by 1;

  3. BEGIN
  4. DBMS_XDB.SETHTTPPORT(8888);
  5. END;

  6. SQL> @?/rdbms/admin/prvtrsch.plb
  7. select  * from dba_users  order by 1;

  8. BEGIN
  9. DBMS_SCHEDULER.SET_AGENT_REGISTRATION_PASS('mypassword');
  10. END;

  11. -----

  12. SELECT DBMS_XDB.GETHTTPPORT() FROM DUAL;

  13. --客户机器安装client中的定制---scheduleragent组件(一般填客户机主机名,端口1500)
  14. 客户机需要schagent -registerdatabase 192.168.0.90 8888 mypassword

  15. --服务器验证:
  16. DECLARE
  17. versionnum VARCHAR2(30);
  18. BEGIN
  19. versionnum := DBMS_SCHEDULER.GET_AGENT_VERSION('192.168.0.199');
  20. DBMS_OUTPUT.PUT_LINE(versionnum);
  21. END;

  22. select  * from dba_scheduler_external_dests;

  23. select  * from dba_scheduler_db_dests;

  24. begin
  25.    dbms_scheduler.create_credential('credential1','oracle','oracle');
  26. end;

  27. grant create external job to hr;

  28. grant execute on credential1 to hr;

  29. grant create job to hr;

  30. select  * from dba_scheduler_credentials;

  31. grant all on station199 to hr;
  32. ----

  33. begin
  34.    dbms_scheduler.create_credential('credential2','hr','botang123');
  35. end;
  36. select  * from dba_scheduler_credentials;
  37. ---
  38. select * from dba_scheduler_dests;
  39. ---


  40. begin
  41.    dbms_scheduler.create_database_destination(
  42.    destination_name => 'orcl199',
  43.    agent => 'station199',
  44.     tns_name => '(DESCRIPTION =
  45.     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.199)(PORT = 1521))
  46.     (CONNECT_DATA =
  47.       (SERVER = DEDICATED)
  48.       (SERVICE_NAME = orcl.example.com)
  49.     )
  50.   )
  51. ');
  52. end;

  53. select  * from dba_scheduler_db_dests;
  54. --
  55. begin
  56.    dbms_scheduler.create_database_destination(destination_name => 'utforcl1992',agent => 'station199',
  57.    tns_name => '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.199)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = utforcl.example.com)))');
  58.    end;
  59.    
  60.    grant all on utforcl1992 to hr;
  61.    
  62.    begin
  63.    dbms_scheduler.create_database_destination(destination_name => 'orcl1992',agent => 'station199',
  64.    tns_name => '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.199)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.example.com)))');
  65. end;

  66. grant all on orcl1992 to hr;
  67.    
  68.    
  69. select * from dba_scheduler_dests;


  70. ---

  71. begin
  72.    dbms_scheduler.create_credential('credential3','hr','botang567');
  73. end;
  74. ----

  75. select  * from dba_scheduler_credentials;

  76. ----
  77. grant all on station199 to hr;

  78. grant all on orcl199 to hr;


  79. grant all on utforcl199 to hr;
  80. ----

  81. grant execute on credential2 to hr;

  82. grant execute on  credential3 to hr;
  83. ----
  84. ---
  85. BEGIN
  86. sys.dbms_scheduler.create_job(
  87. job_name => 'hr.job132',
  88. job_type => 'STORED_PROCEDURE',
  89. job_action => 'hr.procremotedb_win',
  90. start_date => systimestamp at time zone 'PRC',
  91. job_class => '"DEFAULT_JOB_CLASS"',
  92. comments => 'job132',
  93. auto_drop => FALSE,
  94. enabled => FALSE,
  95. credential_name => 'sys.credential2',
  96. destination_name => 'SYS.utforcl1992');
  97. end;





  98. create or replace procedure  procremotedb_win
  99. is
  100. begin
  101.    update employees set salary=salary+700
  102.     where employee_id=100;
  103.    commit;
  104. end;


  105. begin
  106.    procremotedb_win;
  107. end;

  108. select  * from employees where employee_id=100;


  109. --
  110. begin
  111. sys.dbms_scheduler.enable( 'hr.job132' );
  112. END;
  113. ---
  114. BEGIN
  115. sys.dbms_scheduler.create_job(
  116. job_name => 'hr.job133',
  117. job_type => 'STORED_PROCEDURE',
  118. job_action => 'hr.procremotedb_win',
  119. start_date => systimestamp at time zone 'PRC',
  120. job_class => '"DEFAULT_JOB_CLASS"',
  121. comments => 'job133',
  122. auto_drop => FALSE,
  123. enabled => FALSE,
  124. credential_name => 'sys.credential2',
  125. destination_name => 'SYS.utforcl1992');
  126. end;

  127. ---

  128. begin
  129. sys.dbms_scheduler.enable( 'hr.job133' );
  130. END;


  131. ---

  132. BEGIN
  133. sys.dbms_scheduler.create_job(
  134. job_name => 'hr.job134',
  135. job_type => 'STORED_PROCEDURE',
  136. job_action => 'hr.procremotedb_win',
  137. start_date => systimestamp at time zone 'PRC',
  138. job_class => '"DEFAULT_JOB_CLASS"',
  139. comments => 'job134',
  140. auto_drop => FALSE,
  141. enabled => FALSE,
  142. credential_name => 'sys.credential3',
  143. destination_name => 'SYS.orcl1992');
  144. end;
  145. ---

  146. begin
  147. sys.dbms_scheduler.enable( 'hr.job134' );
  148. END;
  149. ---

  150. ---

  151. select  * from dba_scheduler_groups g where g.group_name='GROUP1';

  152. select  * from dba_scheduler_group_members gm where gm.group_name='GROUP1';


  153. ---

  154. BEGIN
  155.   DBMS_SCHEDULER.create_group(
  156.     group_name    => 'hr.group1',
  157.     group_type    => 'DB_DEST',member => 'LOCAL,UTFORCL1992,ORCL1992'
  158.     );
  159. END;
  160. ---
  161. select  * from dba_scheduler_groups g where g.group_name='GROUP1';

  162. select  * from dba_scheduler_group_members gm where gm.group_name='GROUP1';


  163. ---

  164. BEGIN
  165.    DBMS_SCHEDULER.CREATE_JOB (
  166.        job_name            =>  'hr.JOB14',
  167.        job_type            =>  'stored_procedure',
  168.        job_action          =>  'hr.procremotedb_win',
  169.        start_date          =>  SYSTIMESTAMP,
  170.        repeat_interval     =>  'freq=minutely; bysecond=0',
  171.        end_date            =>  SYSTIMESTAMP + 1/24,
  172.        credential_name     =>  'SYS.credential2',
  173.        destination_name    =>  'hr.group1',
  174.        enabled             =>  TRUE);
  175. END;
复制代码

stat.sql:
  1. select  t.TABLE_NAME, t.NUM_ROWS  
  2. from user_tables t
  3. where t.TABLE_NAME='T04209_UNAME';

  4. select   i.NUM_ROWS
  5.   from user_indexes i where i.TABLE_NAME='T04209_UNAME';
  6.   
  7.   select  * from User_Tab_Col_Statistics  tc where tc.table_name='T04209_UNAME';
  8.   
  9.   
  10.   begin
  11.      dbms_Stats.gather_table_stats('HR','T04209_UNAME',
  12.         method_opt=>'for columns uvalue size 254');
  13.       end;
复制代码



回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Botang唐波's Oracle Station   

GMT+8, 2018-5-26 00:21 , Processed in 0.194810 second(s), 24 queries .

快速回复 返回顶部 返回列表