Skip to content
April 8, 2011 / doganay

MONITOR IMPORT PROGRESS SCRIPT

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,

Hi,

If you import a large table and want to know your current progress, simply you can select from dba_segments. You can use this sql:

--Script by Mustafa DOGANAY - 08.04.2011

col owner format a20

col segment_name format a20

col SIZE(GB) format 999999999.99

col SQL_TEXT format a77

undefine v_owner

undefine v_segment_name

accept v_owner prompt 'OWNER: '

accept v_segment_name prompt 'TABLE_NAME: '

select owner, segment_name, bytes/1024/1024/1024 "SIZE(GB)" from dba_segments

where owner='&&v_owner' and segment_name='&&v_segment_name';

select 'select owner, segment_name, bytes/1024/1024/1024 "SIZE(GB)" from dba_segments where owner=''&&v_owner'' and segment_name=''&&v_segment_name'';' "SQL_TEXT" from dual;

undefine v_owner

undefine v_segment_name

 

Then, you can re-run produced sql by typing “r”

 

try and pray 🙂

Advertisements

2 Comments

Leave a Comment
  1. hariharan / Dec 2 2013 11:48 am

    SAME AS ABOVE and SIMPLE :
    —————————————

    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;

    • doganay / Dec 2 2013 12:06 pm

      thx harihan.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: