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

huangtingzhong 发布于 2013-06-08 23:26exp/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)