19c dbms-m6米乐安卓版下载

oracle 19c注意事项： dbms_job 变化

dbms_scheduler 是一种新的job调度形式，提供了功能更加强大和跟踪的功能，说是新是相对dbms_job, schedure从10g时引入已经十多年， 用于替换dbms_job，从12c 开始就已经dbms_jobs是deprecated，但是一直可以使用向前兼容，

注意：从oracle 19c开始 dbms_job总是以dbms_scheduler的形式创建，并且dbms_job仍然有效只是多了一层对应关系。
dbms_job也只是调用了dbms_scheduler.

官方文档描述：
oracle scheduler replaces the dbms_job package. although dbms_job is still supported for backward compatibility, oracle strongly recommends that you switch from dbms_job to oracle scheduler.

in upgrades of oracle database 19c and later releases, if the upgrade can recreate existing dbms_job jobs using dbms_scheduler, then for backward compatibility, after the upgrade, dbms_job continues to act as a legacy interface to the dbms_scheduler job.

1. 不同版本测试

11g 环境下测试

sql> select * from v$version;
banner
----------------------------------------------------------------------------------------
oracle database 11g enterprise edition release 11.2.0.4.0 - 64bit production
pl/sql release 11.2.0.4.0 - production
core    11.2.0.4.0      production
tns for linux: version 11.2.0.4.0 - production
nlsrtl version 11.2.0.4.0 - production
-- 创建dbms job
sql> create table sxc.tj_arch (time  varchar2(20),id int,cnt int);
table created.
sql> declare
  x number;
begin
  sys.dbms_job.submit
  ( job       => x
   ,what      => 'insert into sxc.tj_arch
  select *
    from (select to_char(first_time, ''yyyy-mm-dd hh24'') firsttime,
                 thread#,
                 count(*)
            from v$log_history
           where to_char(first_time, ''yyyy-mm-dd hh24'') =to_char(sysdate - 1 / 24, ''yyyy-mm-dd hh24'')
           group by to_char(first_time, ''yyyy-mm-dd hh24''), thread#);'
   ,next_date => to_date('23-06-2016 11:00:04','dd/mm/yyyy hh24:mi:ss')
   ,interval  => 'sysdate 1/24'
   ,no_parse  => false
  );
  sys.dbms_output.put_line('job number is: ' || to_char(x));
commit;
  end;
  /
pl/sql procedure successfully completed.
-- 检查dbms job
sql> set line222
sql> col log_user for a10
sql> col priv_user for a10
sql> col schema_user for a20
sql> select job,log_user,priv_user ,schema_user  from dba_jobs order by 1;
       job log_user   priv_user  schema_user
---------- ---------- ---------- --------------------
        84 sxc        sxc        sxc
-- 检查scheduler job
sql> col owner for a20
sql> col job_name for a40
sql> select owner, job_name, state ,substr(job_action,1,50) from dba_scheduler_jobs where owner='sxc';
no rows selected

结论： 只创建了dbms job，并未创建scheduler job.

19c 环境下测试

-- 因为是cdb架构，我们选择同tns的方式连接到其中一个pdb
[oracle@ora19c ~]$ sqlplus sxc/sxc@orclpdb1;
sql*plus: release 19.0.0.0.0 - production on thu jul 7 23:13:33 2022
version 19.3.0.0.0
米乐app官网下载 copyright (c) 1982, 2019, oracle.  all rights reserved.
last successful login time: thu jul 07 2022 23:11:33  08:00
connected to:
oracle database 19c enterprise edition release 19.0.0.0.0 - production
version 19.3.0.0.0
sql> select * from v$version;
banner             banner_full              banner_legacy                                     con_id
---------------------------------------------------------------------------------- -------------------------
oracle database 19c enterprise edition release 19.0.0.0.0 - production     oracle database 19c enterprise edition release 19.0.0.0.0 - production   oracle database 19c enterprise edition release 19.0.0.0.0 - production                                                                                     0
                                                                           version 19.3.0.0.0
-- 创建job 
sql> create table sxc.tj_arch (time  varchar2(20),id int,cnt int);
table created.
sql> declare
  x number;
begin
  sys.dbms_job.submit
  ( job       => x
   ,what      => 'insert into sxc.tj_arch
  select *
    from (select to_char(first_time, ''yyyy-mm-dd hh24'') firsttime,
                 thread#,
                 count(*)
            from v$log_history
           where to_char(first_time, ''yyyy-mm-dd hh24'') =to_char(sysdate - 1 / 24, ''yyyy-mm-dd hh24'')
           group by to_char(first_time, ''yyyy-mm-dd hh24''), thread#);'
   ,next_date => to_date('23-06-2016 11:00:04','dd/mm/yyyy hh24:mi:ss')
   ,interval  => 'sysdate 1/24'
   ,no_parse  => false
  );
  sys.dbms_output.put_line('job number is: ' || to_char(x));
commit;
  end;
  /
pl/sql procedure successfully completed.
-- 检查dbms job
sql> set line222
sql> col log_user for a10
sql> col priv_user for a10
sql> col schema_user for a20
sql> select job,log_user,priv_user ,schema_user  from dba_jobs order by 1;
       job log_user   priv_user  schema_user
---------- ---------- ---------- --------------------
        22 sxc        sxc        sxc
-- 检查scheduler job
sql> col owner for a20
sql> col job_name for a40
sql> select owner, job_name, state ,substr(job_action,1,50) from dba_scheduler_jobs where owner='sxc';
owner                job_name                                 state                         substr(job_action,1,50)
-------------------- ---------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
sxc                  dbms_job$_22                             scheduled                      insert into sxc.tj_arch select * from (selec

结论： 即创建了dbms job，也创建scheduler job.

注意： dba_jobs view中仍然可以查到 job，同样在dba_scheduler_jobs中也可以查到对应的记录，并且job_name 为 “dbms_job$” 和job num号。

删除job

-- 1.删除dbms job
begin
sys.dbms_job.remove(22);
commit;
end;
 /
pl/sql procedure successfully completed.
-- 2.删除scheduler job
begin
dbms_scheduler.drop_job(job_name => 'dbms_job$_23');
end;
/
pl/sql procedure successfully completed.

结论： 19c中，不管用上面哪个方式删除job，dbms_job 和 scheduler_job都会被同时删除.

数据迁移job问题

​ 因为oracle数据库用户很多，当我们采用数据泵方式迁移时候，一般会统一用system用户进行导入/导出，可能会导致普通用户的job作业停止工作。

使用一下语句查看系统里所有的job:

sql> select log_user,priv_user ,schema_user,next_date,broken  from dba_jobs order by 1;
log_user   priv_user  schema_user          next_date           b
---------- ---------- -------------------- ------------------- -
system        eep        eep                  2022-07-03 01:00:00 n

​ 发现所有普通用户job的log_user和priv_user字段都变成了system,，而schema_user还是原来的用户的schema名字。这是由于imp导入用户与job的属主用户不同造成的。

解决方法:

1. 用job属主用户进行导入，不过重新导入麻烦。

2. 更新dba_jobs视图

-- login sys as sysdba（以sysdba角色登录，执行一下语句修正两个字段log_user和priv_user的值为schema_user字段的值）
sql> update dba_jobs set log_user='username',priv_user='username' where schema_user='username';
sql> commit;

注意： 在19c环境下，执行更新操作，会抛出如下错误，不在支持，在11g环境中可以执行。

sql> update dba_jobs set priv_user='eep' where schema_user='eep';
update dba_jobs set priv_user='eep' where schema_user='eep'
                    *
error at line 1:
ora-01779: cannot modify a column which maps to a non key-preserved table