Home » RDBMS Server » Server Utilities » how to monitor an export
| how to monitor an export [message #125898] |
Wed, 29 June 2005 16:18  |
balavignesh
Messages: 180
Registered: March 2005
|
Senior Member |
|
|
hai,
we have been exporting the database. we have to ensure that the export is being run by oracle.how to monitor the export process.
is there any query to monitor or anything else..?
thanks in advance
bala
|
|
|
|
|
|
| Re: how to monitor an export [message #125901 is a reply to message #125900] |
Wed, 29 June 2005 16:32  |
balavignesh
Messages: 180
Registered: March 2005
|
Senior Member |
|
|
hi
thanks for that quick reply
but look at this scenario.
I trigger an export from a station which runs on windows
The db runs on a linux server
i see the export in progress in the windows station.
I wud like to see the export process in oracle from a sql command like using v$sqlarea or something. which datatdictionary shud i query for this.
Is it possible
|
|
|
|
| Re: how to monitor an export [message #125905 is a reply to message #125901] |
Wed, 29 June 2005 17:08 |
 |
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
from dictionaries, yes you can.
You can see which sql is currently being executed by session.
But it gives you not very useful information as you wont see an sql like
select count(*) from table_name_that_currently_exported.
THats it!.
I dont have a handy script around, but you can write one!.
Any GUI tool should help with this.
I still dont understand your problem.
You can still use feedack option when used with windows client against a unix database!.
[Updated on: Wed, 29 June 2005 17:18] Report message to a moderator |
|
|
|
| Re: how to monitor an export [message #126008 is a reply to message #125905] |
Thu, 30 June 2005 08:44 |
balavignesh
Messages: 180
Registered: March 2005
|
Senior Member |
|
|
mahesh ,
let me clear the scenario.
our client is in US and they are exporting their database using export utility.here the server is linux and the clients are connected to server thru windows box.since the export process is taking so much time than the actual time they have experienced , they want us to monitor whether is going fine in oracle server.
so , is there any to monitor export process similarly when we monitor the import process using the query which tells how many rows processed with hours taken for that..?
hope this clear our scenario.
is it possible..? if yes,provide some query to find..?
bala
|
|
|
|
| Re: how to monitor an export [message #126014 is a reply to message #126008] |
Thu, 30 June 2005 09:22 |
|
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
|
Senior Member
Account Moderator |
|
|
Please refer my previous posting.
I am not aware of any dictionary object that gives direct information about the object being exported.
When you start the export,
the dictionary is queried to fetch the ddl of object and a dump of data is unloaded into a dumpfile.
These are operations should be witnessed in your session.
and will not give any Direct meaningfull 'monitor' activity.
Other Option is to trace the session.
---
-- THis piece of code is written by Tom_Kyte.
--- I changed it to my need, just wrapped it in a shell script and executed every 3 seconds.
-- I initiated an export in a different session.
-- You can seee the results.
--
oracle@mutation#while true
> do
> show_sql
> sleep 3
> done
USERNAME SID SERIAL# PROCESS STATUS
---------- ---------- ---------- ------------ ----------
SCOTT 9 14455 9321 ACTIVE
SCOTT 12 1304 9318 ACTIVE
--------------------
SCOTT(9,14455) ospid = 9321 program = [email]sqlplus@mymachine.mydomain[/email] (TNS V1-V3)
Thursday 09:09 Thursday 09:09 last et = 0
select username||'('||sid||','||serial#||
') ospid = ' || process ||
' program = ' || program username,
to_char(LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_
--------------------
SCOTT(12,1304) ospid = 9318 program = [email]exp@mymachine.mydomain[/email] (TNS V1-V3)
Thursday 09:09 Thursday 09:09 last et = 0
SELECT WMSYS.WM$VTID.NEXTVAL FROM DUAL
USERNAME MODULE ACTION CLIENT_INFO
--------------- --------------- --------------- ------------------------------
SCOTT(9,14455) SQL*Plus
SCOTT(12,1304) [email]exp@mutation.wi[/email]
.mit.edu (TNS
V1-V3)
USERNAME SID SERIAL# PROCESS STATUS
---------- ---------- ---------- ------------ ----------
SCOTT 9 14457 9325 ACTIVE
SCOTT 12 1304 9318 INACTIVE
--------------------
SCOTT(9,14457) ospid = 9325 program = [email]sqlplus@mymachine.mydomain[/email] (TNS V1-V3)
Thursday 09:09 Thursday 09:09 last et = 0
select username||'('||sid||','||serial#||
') ospid = ' || process ||
' program = ' || program username,
to_char(LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_
USERNAME MODULE ACTION CLIENT_INFO
--------------- --------------- --------------- ------------------------------
SCOTT(9,14457) SQL*Plus
SCOTT(12,1304) [email]exp@mutation.wi[/email]
.mit.edu (TNS
V1-V3)
USERNAME SID SERIAL# PROCESS STATUS
---------- ---------- ---------- ------------ ----------
SCOTT 9 14459 9332 ACTIVE
SCOTT 12 1304 9318 INACTIVE
--------------------
SCOTT(9,14459) ospid = 9332 program = [email]sqlplus@mymachine.mydomain[/email] (TNS V1-V3)
Thursday 09:10 Thursday 09:10 last et = 0
select username||'('||sid||','||serial#||
') ospid = ' || process ||
' program = ' || program username,
to_char(LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_
USERNAME MODULE ACTION CLIENT_INFO
--------------- --------------- --------------- ------------------------------
SCOTT(9,14459) SQL*Plus
SCOTT(12,1304) [email]exp@mutation.wi[/email]
.mit.edu (TNS
V1-V3)
USERNAME SID SERIAL# PROCESS STATUS
---------- ---------- ---------- ------------ ----------
SCOTT 9 14461 9337 ACTIVE
SCOTT 12 1304 9318 ACTIVE
--------------------
SCOTT(9,14461) ospid = 9337 program = [email]sqlplus@mymachine.mydomain[/email] (TNS V1-V3)
Thursday 09:10 Thursday 09:10 last et = 0
select username||'('||sid||','||serial#||
') ospid = ' || process ||
' program = ' || program username,
to_char(LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_
--------------------
SCOTT(12,1304) ospid = 9318 program = [email]exp@mymachine.mydomain[/email] (TNS V1-V3)
Thursday 09:09 Thursday 09:10 last et = 3
SELECT NAME, PROPERTY, BOBJID, FUNCTION, FUNCLEN FROM SYS.EXU8ICO WHERE TOBJID = :1
ORDER BY COLID
USERNAME MODULE ACTION CLIENT_INFO
--------------- --------------- --------------- ------------------------------
SCOTT(9,14461) SQL*Plus
SCOTT(12,1304) [email]exp@mutation.wi[/email]
.mit.edu (TNS
V1-V3)
USERNAME SID SERIAL# PROCESS STATUS
---------- ---------- ---------- ------------ ----------
SCOTT 9 14464 9341 ACTIVE
SCOTT 12 1304 9318 INACTIVE
--------------------
SCOTT(9,14464) ospid = 9341 program = [email]sqlplus@mymachine.mydomain[/email] (TNS V1-V3)
Thursday 09:10 Thursday 09:10 last et = 0
select username||'('||sid||','||serial#||
') ospid = ' || process ||
' program = ' || program username,
to_char(LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_
USERNAME MODULE ACTION CLIENT_INFO
--------------- --------------- --------------- ------------------------------
SCOTT(9,14464) SQL*Plus
SCOTT(12,1304) [email]exp@mutation.wi[/email]
.mit.edu (TNS
V1-V3)
USERNAME SID SERIAL# PROCESS STATUS
---------- ---------- ---------- ------------ ----------
SCOTT 9 14466 9345 ACTIVE
SCOTT 12 1304 9318 ACTIVE
--------------------
SCOTT(9,14466) ospid = 9345 program = [email]sqlplus@mymachine.mydomain[/email] (TNS V1-V3)
Thursday 09:10 Thursday 09:10 last et = 0
select username||'('||sid||','||serial#||
') ospid = ' || process ||
' program = ' || program username,
to_char(LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_
--------------------
SCOTT(12,1304) ospid = 9318 program = [email]exp@mymachine.mydomain[/email] (TNS V1-V3)
Thursday 09:09 Thursday 09:10 last et = 0
SELECT /*+NESTED_TABLE_GET_REFS+*/ "R_SELF"."DBINC".* FROM "R_SELF"."DBINC"
USERNAME MODULE ACTION CLIENT_INFO
--------------- --------------- --------------- ------------------------------
SCOTT(9,14466) SQL*Plus
SCOTT(12,1304) [email]exp@mutation.wi[/email]
.mit.edu (TNS
V1-V3)
^C
-------------------------------------------------------------------
---SHow_sql script by tomkyte.
-------------------------------------------------------------------
oracle@mutation#cat show_sql
sqlplus -s scott/tiger <<EOF
column status format a10
set feedback off
set serveroutput on
column username format a10
select username, sid, serial#, process, status
from v\$session
where username is not null
/
column username format a20
column sql_text format a55 word_wrapped
set serveroutput on size 1000000
declare
x number;
begin
for x in
( select username||'('||sid||','||serial#||
') ospid = ' || process ||
' program = ' || program username,
to_char(LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_time,
sql_address, LAST_CALL_ET
from v\$session
where status = 'ACTIVE'
and rawtohex(sql_address) <> '00'
and username is not null order by last_call_et )
loop
for y in ( select max(decode(piece,0,sql_text,null)) ||
max(decode(piece,1,sql_text,null)) ||
max(decode(piece,2,sql_text,null)) ||
max(decode(piece,3,sql_text,null))
sql_text
from v\$sqltext_with_newlines
where address = x.sql_address
and piece < 4)
loop
if ( y.sql_text not like '%listener.get_cmd%' and
y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%')
then
dbms_output.put_line( '--------------------' );
dbms_output.put_line( x.username );
dbms_output.put_line( x.logon_time || ' ' ||
x.current_time||
' last et = ' ||
x.LAST_CALL_ET);
dbms_output.put_line(
substr( y.sql_text, 1, 250 ) );
end if;
end loop;
end loop;
end;
/
column username format a15 word_wrapped
column module format a15 word_wrapped
column action format a15 word_wrapped
column client_info format a30 word_wrapped
select username||'('||sid||','||serial#||')' username,
module,
action,
client_info
from v\$session
where module||action||client_info is not null;
exit;
EOF
[Updated on: Thu, 30 June 2005 09:33] Report message to a moderator |
|
|
|
|
|
|
|
Goto Forum:
Current Time: Thu Apr 25 19:46:12 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
