Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> How to get DDL of db-object JOB ?, DBMS_METADATA.GET_DDL doesn't work for JOB ?
bernhardG
post Aug 4 2017, 02:42 AM
Post #1


Newbie
*

Group: Members
Posts: 8
Joined: 29-September 15
From: Düsseldorf
Member No.: 50,884



Hello,

I have to scan through a lot of database schemas to get the DDL of their JOBs.
I did this using DBMS_METADATA.GET_DDL with 'JOB' and also with 'PROCOBJ' , but it doesn't deliver the DDL.

Using Hora I am able to see the DDL of a job, e.g.

CODE
DECLARE
  job BINARY_INTEGER;
BEGIN
  job := 21;
  dbms_job.submit( /* use isubmit to preserve job number */
  job,
  'dbms_refresh.refresh(''"XYZ"."POACCOUNTBASE_MVD"'');',
  TO_DATE('04-AUG-2017 12.00.00 PM', 'DD-MON-RRRR HH.MI.SS AM'),
  'TRUNC(SYSDATE) + 1 + 12/24', True);
END;
/


I searched the web but found no solution that works for me.

Perhaps you have an advice ?

:-) Bernhard

PS:

I used DBMS_METADATA.GET_DDL to retrieve DDL for a lot of database objects successfully.
For example to get the DDL of PACKAGE :

CODE
set long 500000
set pages 0
set lines 300
set heading off
set echo off
set verify off
set feedback off
set trimspool on

--
-- generate skript: PACKAGE
--
spool 'DDL_PACKAGE.sql'
SELECT 'set long 500000' FROM DUAL;
SELECT 'set pages 0' FROM DUAL;
SELECT 'set lines 300' FROM DUAL;
SELECT 'set heading off' FROM DUAL;
SELECT 'set echo off' FROM DUAL;
SELECT 'set verify off' FROM DUAL;
SELECT 'set feedback off' FROM DUAL;
SELECT 'set trimspool on' FROM DUAL;
SELECT 'Col DDL format a10000' FROM DUAL;

SELECT 'SPOOL ''' ||'..\..\work\04_package\'|| object_name || '.sps''' || CHR(10) ||
        'SELECT REGEXP_REPLACE(DBMS_METADATA.GET_DDL(''PACKAGE_SPEC'',''' || object_name || '''), ''CREATE OR REPLACE PACKAGE [^.]+.|CREATE OR REPLACE EDITIONABLE PACKAGE [^.]+.'', ''CREATE OR REPLACE PACKAGE '') DDL FROM DUAL;'
FROM  all_objects
WHERE owner = 'XYZ';
AND   object_type = 'PACKAGE'
ORDER BY object_name;

SELECT 'spool off' FROM DUAL;
spool off

--
-- execute skript: PACKAGE
@@DDL_PACKAGE.sql
/

Go to the top of the page
 
+Quote Post
burleson
post Aug 4 2017, 08:41 AM
Post #2


Advanced Member
***

Group: Members
Posts: 13,492
Joined: 26-January 04
Member No.: 13



Hi Bernhard,

You may need to reconstruct the DLL for a job by querying dba_jobs and using the results to build the statement. The data is all there:


http://www.dba-oracle.com/tips_oracle_dbms_job.htm

Good luck!


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 22nd August 2017 - 02:22 AM