Get Failed ODI Messages

The following script is to get some information of failed package when we run it in ODI. This information should be saved by an ODI variable and we can pass it to ETL process and to capture the error messages sent out by the ODI server. These messages are also good in case we want to show it in the alert email and we don't need to open ODI Operator.

--Create a Global Variable called “ GET_ERROR_MSG” and paste the following codes
SELECT ' The  '||CASE
WHEN SSS.STEP_TYPE='F' THEN 'INTERFACE'
WHEN SSS.STEP_TYPE='VD' THEN 'VARAIBLE DECLARATION'
WHEN SSS.STEP_TYPE='VS' THEN 'SET/INCREMENT VARAIBLE'
WHEN SSS.STEP_TYPE='VE' THEN 'EVALUATE VARIABLE'
WHEN SSS.STEP_TYPE='V' THEN 'REFRESH VARIABLE'
WHEN SSS.STEP_TYPE='T' THEN 'PROCEDURE'
WHEN SSS.STEP_TYPE='OE' THEN 'OS COMMAND'
WHEN SSS.STEP_TYPE='SE' THEN 'ODI TOOL'
WHEN SSS.STEP_TYPE='RM' THEN 'REVERSE ENGINEERING MODEL'
WHEN SSS.STEP_TYPE='CM' THEN 'CHECK MODEL'
WHEN SSS.STEP_TYPE='CS' THEN 'CHECK SUB MODEL'
WHEN SSS.STEP_TYPE='CD' THEN 'CHECK DATA STORE'
WHEN SSS.STEP_TYPE='JM' THEN 'JOURNALIZE MODEL'
WHEN SSS.STEP_TYPE='JD' THEN 'JOURNALIZE DATA STORE'
END||' '||SSS.STEP_NAME||
' , has failed due to - '||
SNET.TXT
FROM <%=odiRef.getObjectName("SNP_SESSION")%> SS,
<%=odiRef.getObjectName("SNP_SESS_STEP")%> SSS,
<%=odiRef.getObjectName("SNP_EXP_TXT")%> SNET
WHERE SSS.SESS_NO=SS.SESS_NO
AND SNET.I_TXT=SS.I_TXT_SESS_MESS
AND SNET.TXT_ORD=0
AND SS.PARENT_SESS_NO ='<%=odiRef.getPrevStepLog("SESS_NO")%>'

Get Oracle Database basic information

1. Name/ Versions

set heading off

set verify off

column today NEW_VALUE p_currdate noprint 

select TO_CHAR(SYSDATE,'fmMonth ddth, yyyy') today from   dual; 

clear breaks

clear computes

clear columns 

set heading off

column name heading '' justify center format a74 

select   'Database Name (SID): ' || name "name" from     v$database; 

prompt

prompt

prompt Version/SGA Information: 

set heading off

select * from v$version; 

column sgatot justify left format 9,999,999,999 

select   'Total System Global Area as of &p_currdate.:' hd1,          sum(value) sgatot, 'bytes' hd2 from     v$sga; 

  2. Tablespace/ Data files

prompt

prompt

prompt Tablespaces and Datafiles: 

clear breaks

clear computes

clear columns 

set heading on

column tablespace_name heading 'Tablespace' justify left format a15

column file_id heading 'File|ID' justify center format 9999

column file_name heading 'Datafile' justify center format a60 word_wrapped

column size Heading 'Size|in MB.' justify center format 9,99990.99 

break on tablespace_name skip 1 on report skip 2

compute sum label 'TS SIZE:' of size on tablespace_name

compute sum label 'DB SIZE:' of size on report 

select tablespace_name,        file_id,        file_name,       

bytes/1024/1024 "size" from dba_data_files order by tablespace_name, file_id, file_name; 

3. Space Usage

prompt Space usage (as of &p_currdate.):

prompt 

clear breaks

clear computes

clear columns 

set heading on

column tspace heading 'Tablespace' justify left format a20 truncated

column tot_ts_size heading 'Size|in MB.' justify left format 9,99999999990

column free_ts_size heading 'Used|in MB.' justify right format 9,9999999990

column tbusedpct heading '' justify left format a6

column tbfreepct heading '' justify left format a6 

break on report

compute sum label 'Totals:' of tot_ts_size free_ts_size on report 

col tspace form a25 Heading "Tablespace"

col tot_ts_size form 99999999999999 Heading "Size (Mb)"

col free_ts_size form 99999999999999 Heading "Free (Mb)"

col ts_pct form 999 Heading "% Free"

col ts_pct1 form 999 Heading "% Used"

SELECT df.tablespace_name tspace,

       df.bytes/(1024*1024) tot_ts_size,

       sum(fs.bytes)/(1024*1024) free_ts_size,

       round(sum(fs.bytes)*100/df.bytes) ts_pct,

       round((df.bytes-sum(fs.bytes))*100/df.bytes) ts_pct1

FROM dba_free_space fs,

     (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name ) df

WHERE fs.tablespace_name = df.tablespace_name

GROUP BY df.tablespace_name, df.bytes;

4. Redo Log Files

prompt

prompt

prompt Online Redo Logfiles: 

clear breaks

clear computes

clear columns 

column member heading 'Logfile' justify center format a60 word_wrapped

column group heading 'Group|Number' justify center format 9999

column size heading 'Size|in MB.' justify center format 999990.99 

select f.member "member",        f.group# "group",        l.bytes/1024/1024 "size" from v$logfile f, v$log l where f.group#=l.group# order by f.group#,f.member;

5. Control Files

prompt

prompt

prompt Control files: 

clear breaks

clear computes

clear columns 

column name heading 'File Name' format a60 word_wrapped 

select name from v$controlfile; 

6. Rollback Segments

prompt

prompt

prompt Rollback Segments (sizes as of &p_currdate.): 

clear breaks

clear computes

clear columns 

set heading on

column tablespace_name heading 'Tablespace' justify left format a15 truncated

column segment_name heading 'Seg|Name' justify center format a7

column status heading 'Status' justify center format a8

column initial_extent heading 'Initial|(in M)' justify center format 99990.9

column next_extent heading 'Next|(in M)' justify center format 99990.9

column min_extents heading 'Min|Ext' justify center format 99990

column max_extents heading 'Max|Ext' justify center format 999999999990

column pct_increase heading 'Pct|Inc' justify center format 99990

column rbsize heading 'Curr Size|(in M)' justify left format 9,99990 

break on tablespace_name skip 1 on report skip 2 

select    r.tablespace_name,           r.segment_name,           r.status,   

 r.initial_extent/1024/1024 "initial_extent", 

 r.next_extent/1024/1024 "next_extent",

 r.min_extents,           r.max_extents,     

 r.pct_increase,     

sum(e.bytes)/1024/1024 "rbsize"

from   dba_rollback_segs r, dba_extents e

 where     e.segment_name = r.segment_name

 group by  r.tablespace_name, r.segment_name, r.status,

           r.initial_extent/1024, r.next_extent/1024,

           r.min_extents, r.max_extents, r.pct_increase;

7. Parameters

prompt

prompt Parameters (non-defaults): 

clear breaks

clear computes

clear columns 

column name heading 'Name' format a35 word_wrapped

column pvalue heading 'Value' format a50 word_wrapped 

select name, rtrim(value) "pvalue" from v$parameter order by name; 

8. User Details

prompt

prompt user details : 

clear breaks

clear computes

clear columns 

select username,profile,default_tablespace,temporary_tablespace from dba_users;

select * from nls_database_parameters;

select TABLESPACE_NAME,BLOCK_SIZE,EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces;

select owner,object_type,count(1) from dba_objects group by owner,object_type order by owner;

SELECT file_name,tablespace_name,autoextensible,maxbytes/1048576 FROM dba_data_files;

spool off