job（一）

Job 初窥探

SQL> show user

USER is "SA"

SQL>

 

创建测试表

SQL> create table aa(

  2  bb varchar2(20)

  3  );

 

Table created.

 

插入

SQL> insert into aa select sysdate from dual;

1 row created.

SQL> select * from test;

T_DATE

---------

16-DEC-15

SQL>

 

SQL> select to_char(t_date,'day') from aa;

TO_CHAR(T

---------

tuesday

SQL>

 

创建存储过程anty 向aa插入数据

 SQL> create or replace procedure anty

  2     as

  3     begin

  4       insert into aa select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

  5     end anty;

  6  /

Procedure created.

 

查看插入的数据

SQL> select * from aa;

BB

--------------------

2015-12-16 13:44:33

16-DEC-15

 

调用一下存储过程

SQL> exec anty;

PL/SQL procedure successfully completed.

SQL> /

BB

--------------------

2015-12-16 13:44:33

16-DEC-15

2015-12-16 14:08:01

SQL>

 

声明一个job，每10s执行一次存储过程

SQL> declare

  2        jobn number;

  3   begin

  4       sys.dbms_job.submit(jobn,'anty;',sysdate,'sysdate+10/86400');

  5       commit;

  6   end;

  7  /

PL/SQL procedure successfully completed.

SQL>

 

手动运行job

SQL> select job,what from user_jobs;

       JOB WHAT

---------- ------------------------------

         1 anty;

SQL>

SQL> begin

  2  dbms_job.run(1);

  3  end;

  4  /

PL/SQL procedure successfully completed.

 

查看表空间数据文件大小

SQL> select name,bytes from v$datafile;

 

NAME                                          BYTES

---------------------------------------- ----------

/oracle/oradata/orcl/system01.dbf         461373440

/oracle/oradata/orcl/undotbs01.dbf         26214400

/oracle/oradata/orcl/sysaux01.dbf         272629760

/oracle/oradata/orcl/users01.dbf            5242880

/oracle/oradata/orcl/ant_1.dbf            104857600

/oracle/oradata/orcl/EPICARD.DBF           10485760

6 rows selected.

SQL>

 

删除一个job

SQL> begin

  2  dbms_job.remove(1);

  3  end;

  4  /

PL/SQL procedure successfully completed.

SQL>