Skip to Main Content
  • Questions

  • How to move the Jobs from one User to another User Present in the Different DB server

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Vinesh.

Asked: October 05, 2021 - 6:00 am UTC

Last updated: October 27, 2021 - 2:16 am UTC

Version: 19c

Viewed 1000+ times

You Asked

Team,

This is is continuation with the below link :

https://asktom.oracle.com/pls/apex/f?p=100:12::::RP:P12_QUESTION_ID,P12_PREV_PAGE:9543884800346428497,11

I could not continue on that link , so sorry to raise a new request.

The solution provided there works well in case of both the DB users are in the same server.

But now we are migrating to new DB user, we want to migrate the scheduled jobs as well.

Please let us know if it can be done via SQl or PL/SQL.

Any sample code on this is most welcome.

Regards,
Vinesh




and Connor said...

DBMS_METADATA can get the DDL for jobs for you, which you can run anywhere

SQL> select dbms_metadata.get_ddl('PROCOBJ','ATE_MAINT_EVERY_1_HOUR','ASKTOM') from dual;

DBMS_METADATA.GET_DDL('PROCOBJ','ATE_MAINT_EVERY_1_HOUR','ASKTOM')
--------------------------------------------------------------------------------


BEGIN
dbms_scheduler.create_job('"ATE_MAINT_EVERY_1_HOUR"',
job_type=>'STORED_PROCEDURE', job_action=>
'ASKTOM.ATE_BL.MAINT_EVERY_1_HOUR'
, number_of_arguments=>0,
start_date=>TO_TIMESTAMP_TZ('17-SEP-2015 08.18.06.465429000 PM +00:00','DD-MON-R
RRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), repeat_interval=>
'FREQ=HOURLY;INTERVAL=1'
, end_date=>NULL,
job_class=>'"DEFAULT_JOB_CLASS"', enabled=>FALSE, auto_drop=>TRUE,comments=>
NULL
);
sys.dbms_scheduler.set_attribute('"ATE_MAINT_EVERY_1_HOUR"','NLS_ENV','NLS_LANGU
AGE=''AMERICAN'' NLS_TERRITORY=''AMERICA'' NLS_CURRENCY=''$'' NLS_ISO_CURRENCY='
'AMERICA'' NLS_NUMERIC_CHARACTERS=''.,'' NLS_CALENDAR=''GREGORIAN'' NLS_DATE_FOR
MAT=''DD-MON-RR'' NLS_DATE_LANGUAGE=''AMERICAN'' NLS_SORT=''BINARY'' NLS_TIME_FO
RMAT=''HH.MI.SSXFF AM'' NLS_TIMESTAMP_FORMAT=''DD-MON-RR HH.MI.SSXFF AM'' NLS_TI
ME_TZ_FORMAT=''HH.MI.SSXFF AM TZR'' NLS_TIMESTAMP_TZ_FORMAT=''DD-MON-RR HH.MI.SS
XFF AM TZR'' NLS_DUAL_CURRENCY=''$'' NLS_COMP=''BINARY'' NLS_LENGTH_SEMANTICS=''
BYTE'' NLS_NCHAR_CONV_EXCP=''FALSE''');
dbms_scheduler.enable('"ATE_MAINT_EVERY_1_HOUR"');
COMMIT;
END;


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

How to move the Jobs from one User to another User Present in different servers now.

Vinesh, October 22, 2021 - 6:04 am UTC

Thanks for your response Chris.

The code shared by you works fine when we have only few jobs. But our production server has around 300 jobs. We are migrating our DB to 19c Version.

Any SQL code that would help us in this regards.

Regards
Vinesh

Connor McDonald
October 26, 2021 - 2:38 am UTC

Not sure how the number really make a big difference, ie

select dbms_metadata.get_ddl('PROCOBJ',job_name, owner) from dba_scheduler_jobs

gets the DDL for all them

Thank You Note

vinesh, October 26, 2021 - 4:59 pm UTC

Thanks a lot for your help !!.

Sorry to say that I missed the basic fundamentals over here !!
Connor McDonald
October 27, 2021 - 2:16 am UTC

No problem. We all learn everyday
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database