课程第10次

botang

1. select  * from v$circuit;
   
2. 
   
3. select  * from v$session  where saddr in ( select saddr from v$circuit);
   
4. 
   
5. 
   
6. select  * from dba_users  order by 1;
   
7. 
   
8. SELECT DBMS_XDB.GETHTTPPORT() FROM DUAL;
   
9. 
   
10. 
    
11. DECLARE
    
12.        versionnum VARCHAR2(30);
    
13. BEGIN
    
14.        versionnum := DBMS_SCHEDULER.GET_AGENT_VERSION('192.168.0.79');
    
15.        DBMS_OUTPUT.PUT_LINE(versionnum);
    
16. END;
    
17. 
    
18. 
    
19. select  * from dba_tab_privs t where t.grantee='HR';
    
20. 
    
21. select  * from dba_sys_privs  s where s.grantee='HR';
    
22. 
    
23. 
    
24. select  * from dba_scheduler_external_dests;
    
25. 
    
26. 
    
27. grant all on station79 to hr;
    
28. 
    
29. select  * from dba_tab_privs t where t.grantee='HR';

复制代码

-----------------------------job12

1. select  * from dba_scheduler_dests;
   
2. 
   
3. select  * from dba_scheduler_file_watchers;
   
4. 
   
5. 
   
6. begin
   
7.    dbms_scheduler.create_file_watcher(
   
8.    file_watcher_name => 'filewatcher2',
   
9.    directory_path => '/home/oracle/yourfile',
   
10.    file_name =>  'botang*.txt',
    
11.    credential_name => 'credential1',
    
12.    destination => 'STATION79');
    
13. end;
    
14. 
    
15. 
    
16. grant execute on filewatcher2 to hr;
    
17. 
    
18. 
    
19. select  * from hr.tfilewatcher1;
    
20. 
    
21. create table hr.tfilewatcher2( a  varchar2(200)  ) ;
    
22. 
    
23. create or replace procedure hr.procfilewatcher2( p_1  SYS.SCHEDULER_FILEWATCHER_RESULT)
    
24. is
    
25. begin
    
26.   insert into  hr.tfilewatcher2 values ( to_char( p_1.file_timestamp,'YYYY-MM-DD:HH24:MI:SS') ||'      '
    
27.                                                       ||p_1.directory_path||'        '
    
28.                                                       ||p_1.actual_file_name||'       '
    
29.                                                       ||p_1.file_size  ) ;
    
30.   commit;
    
31. end;
    
32. 
    
33. 
    
34. begin
    
35.     dbms_scheduler.create_program(
    
36.      program_name => 'HR.program12',
    
37.      program_type => 'STORED_PROCEDURE',
    
38.      program_action => 'HR.procfilewatcher2',
    
39.      number_of_arguments => 1,
    
40.      enabled => false);
    
41. end;
    
42. 
    
43. 
    
44. 
    
45. begin
    
46.    dbms_scheduler.define_metadata_argument(
    
47.    program_name => 'HR.program12',
    
48.    metadata_attribute => 'event_message',argument_position => 1 ) ;
    
49. end;
    
50. 
    
51. 
    
52. 
    
53. begin
    
54.   dbms_scheduler.enable('HR.program12');
    
55. end;
    
56. 
    
57. 
    
58. BEGIN
    
59.   DBMS_SCHEDULER.CREATE_JOB(
    
60.    jOB_NAME=> 'hr.job12',
    
61.    PROGRAM_NAME=> 'hr.program12',
    
62.    EVENT_CONDITION => 'tab.user_data.file_size > 10',
    
63.    QUEUE_SPEC=> 'filewatcher2',
    
64.    AUTO_DROP=> FALSE,
    
65.    ENABLED=> true);
    
66. END;
    
67. 
    
68. 
    
69. begin
    
70.    dbms_scheduler.set_attribute('hr.job12','parallel_instances',true);
    
71. end;
    
72. 
    
73. select  * from  hr.tfilewatcher2;
    

复制代码

--------------------请在rcat上做：

1. SQL> create user hr identified by oracle_4U  account unlock;
   
2. 
   
3. User created.
   
4. 
   
5. SQL> grant resource,connect to hr;
   
6. 
   
7. Grant succeeded.
   
8. 
   
9. SQL> create table hr.t05317_job13( a  number )   ;
   
10. 
    
11. Table created.

复制代码

造成以上的错误是由于幻灯片P35的set_attribute造成的：

1. select  * from dba_scheduler_credentials;
   
2. 
   
3. begin
   
4.    dbms_scheduler.create_credential(credential_name => 'credential2',
   
5.    username => 'HR',
   
6.    password => 'oracle_4U');
   
7. end;
   
8.    
   
9. 
   
10. grant execute on credential2 to hr;
    
11. 
    
12. 
    
13. select  * from dba_scheduler_external_dests;
    
14. 
    
15. begin
    
16.    dbms_scheduler.create_database_destination(destination_name => 'rcat_station79',
    
17.    agent =>'STATION79',tns_name => 'rcat');  
    
18. end;
    
19. 
    
20. select  * from dba_scheduler_db_dests;
    
21. 
    
22. select  * from dba_scheduler_dests;
    
23. 
    
24. grant all on rcat_station79 to hr;
    
25. 
    
26. 
    
27. BEGIN
    
28.   sys.dbms_scheduler.create_job(
    
29.   job_name => 'HR.JOB13',
    
30.   program_name => 'HR.PROGRAM13',
    
31.   schedule_name => 'HR.SCHEDULE13',
    
32.   credential_name => 'SYS.CREDENTIAL2',
    
33.   destination_name => 'SYS.RCAT_STATION79',
    
34.    enabled => true );
    
35. <div>END;
    
36. </div>

复制代码

1. select  * from dba_scheduler_groups;
   
2. 
   
3. select  * from dba_scheduler_group_members  g
   
4. where g.group_name='WINDOWGROUP1';
   
5. 
   
6. 
   
7. select  * from dba_scheduler_windows;
   
8. 
   
9. select  * from dba_scheduler_window_groups;
   
10. 
    
11. BEGIN
    
12.   DBMS_SCHEDULER.create_group(
    
13.     group_name    => 'hr.group1',
    
14.     group_type    => 'DB_DEST',member => 'LOCAL,RCAT_STATION79'
    
15.     );
    
16. END;
    
17. 
    
18. 
    
19. BEGIN
    
20.   DBMS_SCHEDULER.create_group(
    
21.     group_name    => 'hr.group2',
    
22.     group_type    => 'EXTERNAL_DEST',member => 'sys.credential1@LOCAL,sys.credential1@STATION79'
    
23.     );
    
24. END;
    
25. 
    
26. 
    
27. BEGIN
    
28.   sys.dbms_scheduler.create_job(
    
29.   job_name => 'HR.JOB14',
    
30.   program_name => 'HR.PROGRAM14',
    
31.   schedule_name => 'HR.SCHEDULE14',
    
32.   credential_name => 'SYS.CREDENTIAL2',
    
33.   destination_name => 'HR.GROUP1',
    
34.    enabled => true );
    
35. end;
    
36. 
    
37. 
    
38. select  * from dba_scheduler_groups;

复制代码