Tuesday, July 18, 2006

Import status

Today a colleague started an import, approximately 9 gb (one scheme).
At a certain moment the import was running for three hours and I wanted to know what was happening.
I found the following sql script, which shows you the current table in which the import is inserting rows. It seemed our import was inserting over more then 18 million rows.

SELECT
SUBSTR(sql_text, INSTR(sql_text,'INTO "'),30) table_name
, rows_processed
, ROUND( (sysdate-TO_DATE(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes
, TRUNC(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_minute
FROM sys.v_$sqlarea
WHERE
sql_text like 'INSERT %INTO "%'
AND command_type = 2
AND open_versions > 0;

The script was published by Nasir Mahmood.
Here is an example output of the script....

TABLE_NAME ROWS_PROCESSED MINUTES ROWS_PER_MINUTE
------------------------------ -------------- ---------- ---------------
INTO "ICE_CVS_FAC_TUSSEN_TAB" 3626392 1436 2525

Especially the table sys.v _$sqlarea is very interesting, I am going to find out more about this table.

4 comments:

Anonymous said...

That was very helpful. Keep the good work going - RC

Oracle boy said...

thanks..it helped me in need.....able to see the rows count as 0 and checked that my import job got hunged...anyways thanks for sharing this ...

s vinay said...

Thanks Dude...:)

Anonymous said...

This was extremely helpful! We had an import running that we thought was hung and this query allowed us to see that it was moving, ever so slowly but moving. Thanks again for sharing!