http://www.oracle-class.com/
rmanbackup.com
orafaq.com
http://www.toadworld.com/KNOWLEDGE/KnowledgeXpertforOracle/tabid/648/TopicID/OTOC44/Default.aspx
How to trace :
================
1)
set serveroutput on size 1000000 for wra
declare
paramname varchar2(256);
integerval binary_integer;
stringval varchar2(256);
paramtype binary_integer;
begin
paramtype:=dbms_utility.get_parameter_value('user_dump_dest',integerval,stringval);
if paramtype=1 then
dbms_output.put_line(stringval);
else
dbms_output.put_line(integerval);
end if;
end;
/
2)
select name,value
from v$parameter
where name='user_dump_dest';
3)
set lines 1000 pages 1000
select s.sid,s.serial#,s.username,s.osuser
from v$session s,v$process p
where s.paddr=p.addr;
SYSTEM : SID: 61 SERIAL : 325
4)
exec dbms_system.set_sql_trace_in_session(61,325,true);
exec dbms_system.set_sql_trace_in_session(61,325,false);
select * from v$session_longops where time_remaining>0 order by time_remaining desc
select event,seconds_in_wait,status from v$session where program like '%DW%';
The followng trace files are created :
-rw-r----- 1 oracle oinstall 1018 Jan 24 17:00 RAC2_j001_12799.trc
-rw-r----- 1 oracle oinstall 1946 Jan 24 17:03 RAC2_lmd0_4111.trc
-rw-r----- 1 oracle oinstall 3053 Jan 24 17:04 RAC2_pz97_12966.trc
-rw-r----- 1 oracle oinstall 4801 Jan 24 17:07 RAC2_dw00_12972.trc
-rw-r----- 1 oracle oinstall 12187 Jan 24 17:07 RAC2_pz98_12958.trc
-rw-r----- 1 oracle oinstall 5049 Jan 24 17:07 RAC2_dm00_12893.trc
-rw-r----- 1 oracle oinstall 81029 Jan 24 17:07 RAC2_pz99_12750.trc
-rw-r----- 1 oracle oinstall 649892 Jan 24 17:07 RAC2_ora_12789.trc
But in above files, only "RAC2_ora_12789.trc" is increasing , so that I update this file only .
DECLARE
hand NUMBER;
BEGIN
hand := Dbms_DataPump.Open(operation => 'EXPORT',
job_mode => 'FULL',
job_name => 'FULLEXPJOB',
version => 'COMPATIBLE');
Dbms_DataPump.Add_File(handle => hand,
filename => 'expdp_plsql.log',
directory => 'DMPDIR',
filetype => 3);
Dbms_DataPump.Add_File(handle => hand,
filename => 'expdp_plsql.dmp',
directory => 'DMPDIR',
filetype => 1);
-- Dbms_DataPump.Set_Parameter(handle => hand,
-- name => 'ESTIMATE',
-- value => 'STATISTICS');
Dbms_DataPump.Start_Job(hand);
END;
/
By DBMS_MONITOR :
=================
DBMS_MONITOR.SESSION_TRACE_ENABLE(
session_id IN BINARY_INTEGER DEFAULT NULL,
serial_num IN BINARY_INTEGER DEFAULT NULL,
waits IN BOOLEAN DEFAULT TRUE,
binds IN BOOLEAN DEFAULT FALSE,
plan_stat IN VARCHAR2 DEFAULT NULL);
DBMS_MONITOR.SESSION_TRACE_ENABLE(
session_id IN 67,
serial_num IN 413,
waits IN TRUE,
binds IN FALSE,
plan_stat IN NULL);
EXECUTE dbms_monitor.session_trace_enable (2827, 64);
EXECUTE dbms_monitor.session_trace_disable (2827, 64);
Can one monitor how fast a table is imported?
==============================================
If you need to monitor how fast rows are imported from a running import job, try one of the following methods:
Method 1:
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_min
from sys.v_$sqlarea
where sql_text like 'INSERT %INTO "%'
and command_type = 2
and open_versions > 0;
For this to work one needs to be on Oracle 7.3 or higher (7.2 might also be OK).
If the import has more than one table, this statement will only show information about the current
table being imported.
