最后一次71次：2014-11-25

botang

2014-11-25:

1. select  * from dba_scheduler_external_dests;
   
2. select * from dba_scheduler_dests;
   
3. select  * from dba_scheduler_db_dests;
   
4. 
   
5. 
   
6. begin
   
7.    dbms_scheduler.create_database_destination(destination_name => 'DB_2',agent => 'agent_1',
   
8.    tns_name => '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.199)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = utforcl.example.com)))');
   
9.    end;
   
10.    
    
11. begin
    
12.    dbms_scheduler.create_database_destination(destination_name => 'DB_1',agent => 'agent_1',
    
13.    tns_name => '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.199)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl.example.com)))');
    
14.    end;
    
15.    
    
16.    
    
17. select  * from dba_scheduler_credentials;  
    
18.    
    
19.    BEGIN
    
20. sys.dbms_scheduler.create_job(
    
21. job_name => '"HR"."JOB_REMOTE_DB_ORCL"',
    
22. job_type => 'PLSQL_BLOCK',
    
23. job_action => 'begin
    
24.    update employees set salary=salary+1  where employee_id=100;
    
25.    commit;
    
26. end;',
    
27. start_date => systimestamp at time zone 'Asia/Shanghai',
    
28. job_class => '"DEFAULT_JOB_CLASS"',
    
29. comments => 'JOB_REMOTE_DB2',
    
30. auto_drop => FALSE,
    
31. credential_name =>'"SYS"."CREDENTIAL2"' ,destination_name =>'SYS.DB_1' ,
    
32. enabled => true);
    
33. END;
    
34. 
    
35.    BEGIN
    
36. sys.dbms_scheduler.create_job(
    
37. job_name => '"HR"."JOB_REMOTE_DB_UTFORCL"',
    
38. job_type => 'STORED_PROCEDURE',
    
39. job_action => 'HR.PROC1',
    
40. start_date => systimestamp at time zone 'Asia/Shanghai',
    
41. job_class => '"DEFAULT_JOB_CLASS"',
    
42. comments => 'JOB_REMOTE_DB2',
    
43. auto_drop => FALSE,
    
44. credential_name =>'"SYS"."CREDENTIAL2"' ,destination_name =>'SYS.DB_2' ,
    
45. enabled => true);
    
46. END;
    
47. 
    
48. 
    
49. select  * from dba_scheduler_groups;
    
50. select  * from dba_scheduler_group_members;
    
51. 
    
52. BEGIN
    
53.   DBMS_SCHEDULER.create_group(
    
54.     group_name    => 'hr.group1',
    
55.     group_type    => 'DB_DEST',member => 'LOCAL,DB_1,DB_2'
    
56.     );
    
57. END;
    
58. 
    
59. 
    
60. select  * from dba_scheduler_groups  g where g.group_name='GROUP1';
    
61. select  * from dba_scheduler_group_members  gm where gm.group_name='GROUP1';
    
62. 
    
63. 
    
64. BEGIN
    
65. sys.dbms_scheduler.create_job(
    
66. job_name => '"HR"."JOB_REMOTE_DB_GROUP"',
    
67. job_type => 'PLSQL_BLOCK',
    
68. job_action => 'begin
    
69.    update employees set salary=9999  where employee_id=100;
    
70.    commit;
    
71. end;',
    
72. start_date => systimestamp at time zone 'Asia/Shanghai',
    
73. job_class => '"DEFAULT_JOB_CLASS"',
    
74. comments => 'JOB_REMOTE_GROUP',
    
75. auto_drop => FALSE,
    
76. credential_name =>'"SYS"."CREDENTIAL2"' ,destination_name =>'HR.GROUP1' ,
    
77. enabled => true);
    
78. END;
    
79. 
    
80. select  *  from  dba_SCHEDULER_JOB_RUN_DETAILS;
    

复制代码

1. select  * from dba_scheduler_db_dests;
   
2. select  * from dba_scheduler_external_dests;
   
3. select * from dba_scheduler_dests;
   
4. 
   
5. begin
   
6.    dbms_scheduler.create_database_destination(destination_name => 'DB_2',agent => 'agent_1',
   
7.    tns_name => '');
   
8.    end;
   
9.    
   
10.    
    
11. BEGIN
    
12. sys.dbms_scheduler.create_job(
    
13. job_name => '"HR"."JOB_REMOTE_DB3"',
    
14. job_type => 'PLSQL_BLOCK',
    
15. job_action => 'begin
    
16.    update employees set salary=salary+1  where employee_id=100;
    
17.    commit;
    
18. end;',
    
19. start_date => systimestamp at time zone 'Asia/Shanghai',
    
20. job_class => '"DEFAULT_JOB_CLASS"',
    
21. comments => 'JOB_REMOTE_DB2',
    
22. auto_drop => FALSE,
    
23. credential_name =>'"SYS"."CREDENTIAL2"' ,destination_name =>'SYS.DB_2' ,
    
24. enabled => true);
    
25. END;
    
26. 
    
27. 
    
28. 
    
29.    

复制代码

1. select  * from DBA_TAB_STAT_PREFS;
   
2. 
   
3. select   t.num_rows   from dba_tables t where t.owner='HR' and t.table_name='T05314';
   
4. 
   
5. select   *   from   dba_tab_col_statistics tc where tc.owner='HR'  and tc.table_name='T05314';
   
6. 
   
7. select  * from dba_tab_histograms th where th.owner='HR' and th.table_name='T05314';
   
8. 
   
9. begin
   
10. dbms_Stats.gather_table_stats('HR','T05314');
    
11. end;
    
12. ---prefer
    
13. begin
    
14.    dbms_Stats.gather_table_stats('HR','T05314');
    
15.    end;
    
16.    
    
17.    ---no prefer
    
18.    
    
19.    begin
    
20.        dbms_stats.gather_table_stats(ownname => 'HR',
    
21.        tabname => 'T05314',
    
22.        estimate_percent => 99,method_opt => 'for columns a size 80');
    
23.        end;
    
24. 
    
25. ----
    
26. begin
    
27.    dbms_Stats.gather_table_stats('HR','T05314');
    
28.    end;
    
29. ---
    
30. begin
    
31.    dbms_stats.set_table_prefs(ownname => 'HR',tabname => 'T05314',pname => 'estimate_percent',pvalue => 100);
    
32.    end;
    
33.    
    
34. begin
    
35.    dbms_stats.set_table_prefs(ownname => 'HR',tabname => 'T05314',pname => 'method_opt',pvalue => 'for columns a size 254');
    
36.    end;
    
37.    
    
38.    select  * from DBA_TAB_STAT_PREFS;
    
39. ----
    
40. 
    
41. begin
    
42.    dbms_Stats.gather_table_stats('HR','T05314');
    
43.    end;
    
44.    
    
45.    ---
    
46.    ----
    
47.    select  * from dba_tables  t where  t.owner='SYS' and t.tablespace_name='SYSAUX'  and t.table_name  like 'WRH%';
    
48.    
    
49.    -----
    
50.    
    
51.    select * from dba_sql_profiles;
    
52.    
    
53.    

复制代码