ITPub博客

首页 > 数据库 > Oracle > [20211206]toad下job建立查看问题.txt

[20211206]toad下job建立查看问题.txt

原创 Oracle 作者:lfree 时间:2021-12-06 10:13:58 0 删除 编辑

[20211206]toad下job建立查看问题.txt

--//上班检查发现同事建立的job不是很好,使用sys模式执行,我在测试环境演示遇到一些问题,做一个记录:

1.环境:
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

--//首先我跟踪一下查询,在sys用户下执行如下:
Select job, what, log_user, priv_user, schema_user,
       last_date, this_date, this_sec, next_date,
       next_sec, total_time, broken, interval,
       failures, nls_env
       , instance
from DBA_JOBS
where 1=1
and schema_user = 'SYS'

--//在scott用户下执行如下:
Select job, what, log_user, priv_user, schema_user,
       last_date, this_date, this_sec, next_date,
       next_sec, total_time, broken, interval,
       failures, nls_env
       , instance
from sys.user_jobs
where 1=1
and schema_user = 'SCOTT';


2.测试:
--//我使用sys用户登录建立job如下,注我建立2次,一次使用current_schema=SCOTT,一次使用current_schema=SYS.
DECLARE
  X NUMBER;
  user_name varchar2(30);
BEGIN
  select user into user_name from dual;
  execute immediate 'alter session set current_schema = SCOTT';
  BEGIN
    SYS.DBMS_JOB.SUBMIT
    ( job       => X
     ,what      => 'SCOTT.TEST_JOB_DEPTX;'
     ,next_date => to_date('05/12/2021 09:35:55','dd/mm/yyyy hh24:mi:ss')
     ,interval  => 'SYSDATE+5/1440 '
     ,no_parse  => FALSE
    );
    SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
    execute immediate 'alter session set current_schema = ' || user_name ;
  EXCEPTION
    WHEN OTHERS THEN
      execute immediate 'alter session set current_schema = ' || user_name ;
      RAISE;
  END;
  COMMIT;
END;
/

--//这样在toad下使用schema browser浏览,两个界面都无法发现我建立的job。

SCOTT@book> select * from DBA_JOBS where job in (392,393)
  2  @ pr
==============================
JOB                           : 392
LOG_USER                      : SYS
PRIV_USER                     : SYS
SCHEMA_USER                   : SCOTT
LAST_DATE                     : 2021-12-06 09:49:18
LAST_SEC                      : 09:49:18
THIS_DATE                     :
THIS_SEC                      :
NEXT_DATE                     : 2021-12-06 09:54:18
NEXT_SEC                      : 09:54:18
TOTAL_TIME                    : 0
BROKEN                        : N
INTERVAL                      : SYSDATE+5/1440
FAILURES                      : 0
WHAT                          : SCOTT.TEST_JOB_DEPTX;
NLS_ENV                       : NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'
MISC_ENV                      : 010200020A000000
INSTANCE                      : 0
==============================
JOB                           : 393
LOG_USER                      : SYS
PRIV_USER                     : SYS
SCHEMA_USER                   : SCOTT
LAST_DATE                     : 2021-12-06 09:47:18
LAST_SEC                      : 09:47:18
THIS_DATE                     :
THIS_SEC                      :
NEXT_DATE                     : 2021-12-06 09:52:18
NEXT_SEC                      : 09:52:18
TOTAL_TIME                    : 0
BROKEN                        : N
INTERVAL                      : SYSDATE+5/1440
FAILURES                      : 0
WHAT                          : SCOTT.TEST_JOB_DEPTX;
NLS_ENV                       : NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS' NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY'
MISC_ENV                      : 010200020A000000
INSTANCE                      : 0
PL/SQL procedure successfully completed.
--//上下比较,两者方式建立并没有什么不同。

--//scott用户在toad下使用sys.user_jobs视图。

CREATE OR REPLACE FORCE VIEW SYS.USER_JOBS
(
   JOB
  ,LOG_USER
  ,PRIV_USER
  ,SCHEMA_USER
  ,LAST_DATE
  ,LAST_SEC
  ,THIS_DATE
  ,THIS_SEC
  ,NEXT_DATE
  ,NEXT_SEC
  ,TOTAL_TIME
  ,BROKEN
  ,INTERVAL
  ,FAILURES
  ,WHAT
  ,NLS_ENV
  ,MISC_ENV
  ,INSTANCE
)
AS
   SELECT j."JOB"
         ,j."LOG_USER"
         ,j."PRIV_USER"
         ,j."SCHEMA_USER"
         ,j."LAST_DATE"
         ,j."LAST_SEC"
         ,j."THIS_DATE"
         ,j."THIS_SEC"
         ,j."NEXT_DATE"
         ,j."NEXT_SEC"
         ,j."TOTAL_TIME"
         ,j."BROKEN"
         ,j."INTERVAL"
         ,j."FAILURES"
         ,j."WHAT"
         ,j."NLS_ENV"
         ,j."MISC_ENV"
         ,j."INSTANCE"
     FROM dba_jobs j, sys.user$ u
    WHERE j.priv_user = u.name AND u.user# = USERENV ('SCHEMAID');
--//查询条件除了u.user# = USERENV ('SCHEMAID'),还加了j.priv_user = u.name,导致scoot用户无法看到job在schema browser浏览
--//界面上。

--//而sys用户查询DBA_JOBS,但是限定条件是schema_user = 'SYS'。

3.收尾:
--//如何删除:
SYS@book> @ desc_proc sys dbms_job remove;
INPUT OWNER PACKAGE_NAME OBJECT_NAME
sample : @desc_proc sys dbms_stats gather_%_stats

OWNER      PACKAGE_NAME         OBJECT_NAME                      SEQUENCE ARGUMENT_NAME        DATA_TYPE            IN_OUT    DEFAULTED
---------- -------------------- ------------------------------ ---------- -------------------- -------------------- --------- ----------
SYS        DBMS_JOB             REMOVE                                  1 JOB                  BINARY_INTEGER       IN        N

SYS@book> exec sys.dbms_job.remove(392);
PL/SQL procedure successfully completed.

SYS@book> exec sys.dbms_job.remove(393);
PL/SQL procedure successfully completed.

SYS@book> select * from DBA_JOBS where job in (392,393);
no rows selected

--//实际上这些都是细节问题。实际上如果在sys用户建立执行如下,就没有这个问题。
--//注:我的测试环境scott具有dba权限,执行一样没有问题,不过仅仅sys用户能看到。
DECLARE
  X NUMBER;
  user_name varchar2(30);
BEGIN
  select user into user_name from dual;
  execute immediate 'alter session set current_schema = SYS';
  BEGIN
    SYS.DBMS_JOB.SUBMIT
    ( job       => X
     ,what      => 'SCOTT.TEST_JOB_DEPTX;'
     ,next_date => to_date('05/12/2021 09:35:55','dd/mm/yyyy hh24:mi:ss')
     ,interval  => 'SYSDATE+5/1440 '
     ,no_parse  => FALSE
    );
    SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
    execute immediate 'alter session set current_schema = ' || user_name ;
  EXCEPTION
    WHEN OTHERS THEN
      execute immediate 'alter session set current_schema = ' || user_name ;
      RAISE;
  END;
  COMMIT;
END;
/

4.总结:
--//这些仅仅是一些细节问题,我们团队更多依赖toad查看,这样可能出现一些问题。
--//另外我们有一些规定就是这些job建立要经过dba的核审来建立,这样dba并没有用户的权限,这样只能以sys用户建立,这样建立下只能在
--//sys用户能看见,我个人认为这样不是很好。

--//在测试结束前我发现sys用户还是可以看到就是要设置scott在schema browser。这样查询条件是

Select job, what, log_user, priv_user, schema_user,
       last_date, this_date, this_sec, next_date,
       next_sec, total_time, broken, interval,
       failures, nls_env
       , instance
from DBA_JOBS
where 1=1
and schema_user = 'SCOTT';

--//主要区别普通用户使用user_XXX 视图,dba用户是DBA_XXX视图。顺便说一下我们团队job管理太乱了。

SYS@127.0.0.1:9105/dbcn> select job,log_user,priv_user,schema_user from dba_jobs where priv_user<>'APEX_030200';
       JOB LOG_USER                       PRIV_USER                      SCHEMA_USER
---------- ------------------------------ ------------------------------ ------------------------------
        61 SYSTEM                         SYSTEM                         TOAD
         3 SYSTEM                         SYSTEM                         PPPPPP
        41 SYSTEM                         SYSTEM                         PPPPPP_HHH
       101 SYSTEM                         SYSTEM                         PPPPPP_HHH
       121 SYSTEM                         SYSTEM                         PPPPPP_HHH
        12 SYS                            SYS                            SYS
      5614 SYS                            SYS                            SYS
       342 PPPPPP_HHH                     PPPPPP_HHH                     PPPPPP_HHH
8 rows selected.

--//有好几个实际上使用system用户建立。给维护添加不必要的麻烦。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2845881/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    3126
  • 访问量
    6834949