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

botang · 发表于 2014-11-27 16:13:51

本帖最后由 botang 于 2014-11-29 10:40 编辑

2014-11-25:

select * from dba_scheduler_external_dests;
select * from dba_scheduler_dests;
select * from dba_scheduler_db_dests;
begin
dbms_scheduler.create_database_destination(destination_name => 'DB_2',agent => 'agent_1',
tns_name => '(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.199)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = utforcl.example.com)))');
end;
begin
dbms_scheduler.create_database_destination(destination_name => 'DB_1',agent => 'agent_1',
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_credentials;
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"HR"."JOB_REMOTE_DB_ORCL"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
update employees set salary=salary+1 where employee_id=100;
commit;
end;',
start_date => systimestamp at time zone 'Asia/Shanghai',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'JOB_REMOTE_DB2',
auto_drop => FALSE,
credential_name =>'"SYS"."CREDENTIAL2"' ,destination_name =>'SYS.DB_1' ,
enabled => true);
END;
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"HR"."JOB_REMOTE_DB_UTFORCL"',
job_type => 'STORED_PROCEDURE',
job_action => 'HR.PROC1',
start_date => systimestamp at time zone 'Asia/Shanghai',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'JOB_REMOTE_DB2',
auto_drop => FALSE,
credential_name =>'"SYS"."CREDENTIAL2"' ,destination_name =>'SYS.DB_2' ,
enabled => true);
END;
select * from dba_scheduler_groups;
select * from dba_scheduler_group_members;
BEGIN
DBMS_SCHEDULER.create_group(
group_name => 'hr.group1',
group_type => 'DB_DEST',member => 'LOCAL,DB_1,DB_2'
);
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
sys.dbms_scheduler.create_job(
job_name => '"HR"."JOB_REMOTE_DB_GROUP"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
update employees set salary=9999 where employee_id=100;
commit;
end;',
start_date => systimestamp at time zone 'Asia/Shanghai',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'JOB_REMOTE_GROUP',
auto_drop => FALSE,
credential_name =>'"SYS"."CREDENTIAL2"' ,destination_name =>'HR.GROUP1' ,
enabled => true);
END;
select * from dba_SCHEDULER_JOB_RUN_DETAILS;

复制代码

select * from dba_scheduler_db_dests;
select * from dba_scheduler_external_dests;
select * from dba_scheduler_dests;
begin
dbms_scheduler.create_database_destination(destination_name => 'DB_2',agent => 'agent_1',
tns_name => '');
end;
BEGIN
sys.dbms_scheduler.create_job(
job_name => '"HR"."JOB_REMOTE_DB3"',
job_type => 'PLSQL_BLOCK',
job_action => 'begin
update employees set salary=salary+1 where employee_id=100;
commit;
end;',
start_date => systimestamp at time zone 'Asia/Shanghai',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'JOB_REMOTE_DB2',
auto_drop => FALSE,
credential_name =>'"SYS"."CREDENTIAL2"' ,destination_name =>'SYS.DB_2' ,
enabled => true);
END;

复制代码

select * from DBA_TAB_STAT_PREFS;
select t.num_rows from dba_tables t where t.owner='HR' and t.table_name='T05314';
select * from dba_tab_col_statistics tc where tc.owner='HR' and tc.table_name='T05314';
select * from dba_tab_histograms th where th.owner='HR' and th.table_name='T05314';
begin
dbms_Stats.gather_table_stats('HR','T05314');
end;
---prefer
begin
dbms_Stats.gather_table_stats('HR','T05314');
end;
---no prefer
begin
dbms_stats.gather_table_stats(ownname => 'HR',
tabname => 'T05314',
estimate_percent => 99,method_opt => 'for columns a size 80');
end;
----
begin
dbms_Stats.gather_table_stats('HR','T05314');
end;
---
begin
dbms_stats.set_table_prefs(ownname => 'HR',tabname => 'T05314',pname => 'estimate_percent',pvalue => 100);
end;
begin
dbms_stats.set_table_prefs(ownname => 'HR',tabname => 'T05314',pname => 'method_opt',pvalue => 'for columns a size 254');
end;
select * from DBA_TAB_STAT_PREFS;
----
begin
dbms_Stats.gather_table_stats('HR','T05314');
end;
---
----
select * from dba_tables t where t.owner='SYS' and t.tablespace_name='SYSAUX' and t.table_name like 'WRH%';
-----
select * from dba_sql_profiles;

复制代码