Finding a table in ODI logs

ODI is the one of the most common ETL tool , especially in Turkey. It is very user friendly , easy to learn , tons of docs at web and there are many other advantages.

Some times when we make a technical analysis about our ETL processes. And we need to find an ODI scenario that includes our table as a source or as a target. We can check our scenarios manually but when it is Oracle , there are always easier ways.

We can use this script for find which scenario log has our tables name in it.

SELECT sess_beg, sess_name 
FROM snp_session 
where 1=1
--AND to_char(sess_beg,'YYYYMMDD')='20191101'
  AND sess_beg >TRUNC(SYSDATE-2)
  AND sess_no in (select distinct sess_no from SNP_SESS_TASK_LOG 
 where upper(def_txt) like '%<<YOUR_TABLE_NAME>>%') 
ORDER BY sess_beg ;

There is one little rule for using this script successfully , scenario must run at least one time. That is a must , because we are using the log that generated by ODI. So it must be generated.

You can download ODI demo VM that link below ;

https://www.oracle.com/middleware/technologies/data-integrator/odi-demo-downloads.html

PL-SQL Stored Procedure : DROP_IF_EXISTS

Hi ,

While developing ETL processes with PL-SQL we use “drop-create” for loading data to temp tables. Before creating temp table, we need to drop if it is exists in the database. If you try directly drop the table, and the table did not exists yet , then you will get “ORA-00942table or view does not exist” error.

Here is a PL-SQL procedure to drop it so you can create it without any error;

CREATE OR REPLACE PROCEDURE TECHBROS.DROP_IF_EXISTS (p_table_name IN VARCHAR2) IS
v_control NUMBER(1);
BEGIN
SELECT COUNT(1) 
  INTO v_control 
  FROM user_tables a
  WHERE UPPER(a.table_name) = UPPER(p_table_name); 
IF v_control >= 1 THEN 
  EXECUTE IMMEDIATE('DROP TABLE '||p_table_name); 
END IF;
EXCEPTION WHEN OTHERS THEN NULL;
END drop_if_exists; 

For run the procedure;

call TECHBROS.DROP_IF_EXISTS('TEMP_TABLE_TO_DELETE');

Table Size with Query on Oracle

Sometimes you have lots of data but not enough space at your storage. And you need to check your tables sizes and tables spaces etc. I think it is easier use queries than use clients utilities.

We can use system tables for control size, parallelism degree , compression level and more. I use this two scripts for check things up.

First one the size ;

select 
 us.segment_name,
 us.TABLESPACE_NAME, 
 sum(us.bytes)/1024/1024/1024 GB ,
 sum(us.bytes)/1024/1024  MB 
from user_segments us
where  us.segment_type='TABLE'  
 and us.segment_name LIKE '%YOUR_TABLE_NAME%' 
group by 
 us.segment_name , 
 us.TABLESPACE_NAME;

The second one compress ,parallelism degrees and etc. ;

SELECT 
 ut.table_name ,
 ut.tablespace_name , 
 ut.logging, 
 ut.num_rows,
 ut.degree , 
 ut.instances , 
 ut.last_analyzed ,
 ut.partitioned    
FROM user_tables ut  

But generally I use them both as below ;

SELECT 
 us.table_name ,
 us.TABLESPACE_NAME,
 us.gb_SIZE,
 us.mb_SIZE,
 ut.logging, 
 ut.num_rows,
 ut.degree , 
 ut.instances , 
 ut.last_analyzed ,
 ut.partitioned   ,
 ut.compression 
FROM  
( select 
 us.segment_name table_name ,
 us.TABLESPACE_NAME, 
 sum(us.bytes)/1024/1024/1024 GB_SIZE ,
 sum(us.bytes)/1024/1024  MB_SIZE 
from user_segments us
where  us.segment_type='TABLE'  
 and us.segment_name LIKE '%Z$%' 
 AND TABLESPACE_NAME ='SATA'
group by 
 us.segment_name , 
 us.TABLESPACE_NAME ) us  
 INNER JOIN 
 (SELECT 
 ut.table_name ,
 ut.tablespace_name , 
 ut.logging, 
 ut.num_rows,
 ut.degree , 
 ut.instances , 
 ut.last_analyzed ,
 ut.partitioned   ,
 ut.compression 
FROM user_tables ut   ) ut  
ON us.table_name = ut.table_name 
AND us.TABLESPACE_NAME = ut.TABLESPACE_NAME 
ORDER BY MB_SIZE DESC

I will be glad if it helps ,

Have a nice day