ser_jobs;
no rows selected
SQL> variable jobno number;
SQL> variable instno number;
SQL> begin
2 select instance_number into :instno from v$instance@STDBY_LINK_DG2;
3 dbms_job.submit(:jobno, 'statspack_dg2_dg2.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :instno);
4 commit;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> select job,schema_user,last_date,what,instance from user_jobs;
JOB SCHEMA_USER LAST_DATE WHAT INSTANCE
---------- ------------------------------ --------- ------------------------------------------------------------ ----------
23 STDBYPERF statspack_dg2_dg2.snap; 1
SQL>
便于验证修改job，使其5分钟运行一次。
SQL> variable jobno number;
SQL> variable instno number;
SQL> begin
2 select instance_number into :instno from v$instance@STDBY_LINK_DG2;
3 dbms_job.submit(:jobno, 'statspack_dg2_dg2.snap;', trunc(sysdate+1/288,'MI'), 'trunc(sysdate+1/288,''MI'')', TRUE, :instno);
4 commit;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
Session altered.
SQL> select last_date,this_date,next_date,what from user_jobs;
LAST_DATE THIS_DATE NEXT_DATE WHAT
------------------- ------------------- ------------------- ------------------------------------------------------------
2014-04-23 16:00:00 statspack_dg2_dg2.snap;
2014-04-23 15:20:00 statspack_dg2_dg2.snap;
SQL>
SQL> select job,next_date,what from user_jobs;
JOB NEXT_DATE WHAT
---------- ------------------- ------------------------------------------------------------
23 2014-04-23 16:00:00 statspack_dg2_dg2.snap;
24 2014-04-23 15:20:00 statspack_dg2_dg2.snap;
SQL> exec dbms_job.remove('23');
PL/SQL procedure successfully completed.
SQL> select job,next_date,what from user_jobs;
JOB NEXT_DATE WHAT
---------- ------------------- ------------------------------------------------------------
24 2014-04-23 15:20:00 statspack_dg2_dg2.snap;
SQL>
然后再次查看报告：
SQL>
SQL> conn STDBYPERF/Amy Connected. SQL> @ /rdbms/admin/sbreport.sql
Instances in this Statspack schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Unique Name Instance Name ------------------------------ ---------------- dg2 dg2
Enter the DATABASE UNIQUE NAME of the standby database to report Enter value for db_unique_name: dg2 You entered: dg2
Enter the INSTANCE NAME of the standby database instance to report Enter value for inst_name: dg2 You entered: dg2
Specify the number of days of snapshots to choose from ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Entering the number of days (n) will result in the most recent (n) days of snapshots being listed. Pressing without specifying a number lists all completed snapshots.
Listing all Completed Snapshots
Snap Instance Snap Id Snap Started Level Comment ------------ --------- ----------------- ----- -------------------- dg2 1 23 Apr 2014 14:36 5 2 23 Apr 2014 14:46 5 11 23 Apr 2014 15:20 5 12 23 Apr 2014 15:25 5 13 23 Apr 2014 15:30 5 14 23 Apr 2014 15:35 5 15 23 Apr 2014 15:40 5
Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap:
可以看到job执行正常。
至此完成。