当前位置: 首页 > exp/imp > 正文

通过v$session查看imp进程信息

     今天在客户现场,客户想查看imp进程的进度,想了想好像是没有什么方式来查看imp的进度的信息,在imp有个参数feedback是可以查看,但是客户没有增加这个参数。然而通过v$session切没有找到imp的进程,奇怪了,imp在正常的导数据,而v$session切没有发现进程,最后才明白一个道理:imp导入进程在服务器端v$session中的username列不是imp登陆用户名,而是imp命令中touser参数值。

    测试环境:RHEL 4.8 DB:10.2.0.4.8

1,通过监听方式导数据,在WIN平台导数据

E:\wendang\SkyDrive\rs2\sql>imp system/oracle@orcl10g file=e:/install/test.dmp fromuser=scott touser=scott
Import: Release 11.2.0.3.0 - Production on Sat Jun 8 22:54:08 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
. . importing table                         "TEST"

已经开始专入数据,通过下面切没有查看到有相当的进程

www.htz.pw >select count(*) from v$session where username='SYSTEM'; COUNT(*) ---------- 0

下面是通过machine来查看进程,这里我们查看到了进程的信息,这里的username不是在imp里面写的system/oracle

scott,这里千万要注意了。

www.htz.pw >select sid,username,status,program,sql_id from v$session where machine like '%WORKGROUP%';

       SID USERNAME             STATUS   PROGRAM                                  SQL_ID
---------- -------------------- -------- ---------------------------------------- -------------
       153 SCOTT                ACTIVE   imp.exe                                  241g5yhwkbg9f

其实这里我们可以通过program一下就可以查看到imp的进程,这里我们注意到program为imp.exe,在非WIN平台下面

应该是imp@rhel4.htz.pw (TNS******)这样的信息

下面是 fromuser与touser名字不一样的时候查看进程的信息

www.htz.pw >create user htz identified by oracle default tablespace users; User created. www.htz.pw >grant unlimited tablespace to htz; Grant succeeded. www.htz.pw >grant connect,resource to htz; Grant succeeded. www.htz.pw >!imp system/oracle@orcl10g file=/soft/test.dmp fromuser=scott touser=htz; Import: Release 10.2.0.4.0 - Production on Sat Jun 8 23:31:40 2013 Copyright (c) 1982, 2007, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Export file created by EXPORT:V10.02.01 via conventional path import done in US7ASCII character set and AL16UTF16 NCHAR character set import server uses ZHS16GBK character set (possible charset conversion) . importing SCOTT's objects into HTZ . . importing table "TEST"

 

 

www.htz.pw >select sid,username,status,program,sql_id from v$session where program like 'imp%'; SQL_ID SID USERNAME STATUS PROGRAM SQL_CHILD_NUMBER ---------- --------------- -------- ------------------------------ -------------------- 151 HTZ ACTIVE imp@rhel4.htz.pw (TNS V1-V3) 241g5yhwkbg9f

这里看到username的名字是htz跟touser名字一样,所以证明了我们之前说的v$session中的username列的值是imp中参数touser的值,而不是imp登陆用户名。

另外我们需要注意的是exp/imp是客户端的工具,我们通过ps –ef|grep imp这种方式查看到的是客户端的进程号,不能跟v$process来接合查询信息的。

下面是通过客户端的进程号来查看导入进程的信息

[oracle10g@rhel4 soft]$ imp system/oracle@orcl10g file=/soft/test.dmp fromuser=scott touser=scott

Import: Release 10.2.0.4.0 - Production on Sat Jun 8 23:10:42 2013

Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing option
Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
. importing SCOTT's objects into SCOTT
. . importing table                         "TEST"

已经开始导入数据库

www.htz.pw >!ps -ef|grep imp
211      11896  7736 22 23:10 pts/0    00:00:14 imp                       file=/soft/test.dmp fromuser=scott touser=scott
211      11958 11100  0 23:11 pts/2    00:00:00 /bin/bash -c ps -ef|grep imp
211      11960 11958  0 23:11 pts/2    00:00:00 grep imp
www.htz.pw >/
       SID USERNAME             STATUS   PROGRAM                                  SQL_ID
---------- -------------------- -------- ---------------------------------------- -------------
       153 SCOTT                ACTIVE   imp@rhel4.htz.pw (TNS V1-V3)             241g5yhwkbg9f
www.htz.pw >col sql_text for a80
www.htz.pw >select sql_text from v$sql where sql_id='241g5yhwkbg9f';
SQL_TEXT
--------------------------------------------------------------------------------
INSERT /*+NESTED_TABLE_SET_REFS+*/ INTO "TEST" ("OWNER", "OBJECT_NAME", "SUBOBJE
CT_NAME", "OBJECT_ID", "DATA_OBJECT_ID", "OBJECT_TYPE", "CREATED", "LAST_DDL_TIM
E", "TIMESTAMP", "STATUS", "TEMPORARY", "GENERATED", "SECONDARY") VALUES (:1, :2
, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13)

这里我们是通过v$session中的process来查询的,process是字符集,所以我们要加上单引号。

本文固定链接: http://www.htz.pw/2013/06/08/%e9%80%9a%e8%bf%87vsession%e6%9f%a5%e7%9c%8bimp%e8%bf%9b%e7%a8%8b%e4%bf%a1%e6%81%af.html | 认真就输

该日志由 huangtingzhong 于2013年06月08日发表在 exp/imp 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: 通过v$session查看imp进程信息 | 认真就输
关键字: