【学习笔记】Oracle 通过v$session查看imp进程信息的案例

天萃荷净 运维DBA反映需求在执行Oracle 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"

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

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

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

scott，这里千万要注意了。

oracleplus.net>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@oracleplus(TNS******)这样的信息

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

oracleplus.net>create user htz identified by oracle default tablespace users;
User created.
oracleplus.net>grant unlimited tablespace to htz;
Grant succeeded.
oracleplus.net>grant connect,resource to htz;
Grant succeeded.
oracleplus.net>!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"

oracleplus.net>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@oracleplus(TNS V1-V3)   241g5yhwkbg9f

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

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

2.通过客户端的进程号来查看导入进程的信息

[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"
已经开始导入数据库

oracleplus.net>!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

oracleplus.net>/
       SID USERNAME             STATUS   PROGRAM                                  SQL_ID
---------- -------------------- -------- ---------------------------------------- -------------
       153 SCOTT                ACTIVE   imp@oracleplus(TNS V1-V3)             241g5yhwkbg9f

oracleplus.net>col sql_text for a80
oracleplus.net>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 | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle 通过v$session查看imp进程信息的案例

本文由大师惜分飞原创分享，网址：http://www.oracleplus.net/arch/995.html

Oracle研究中心

关键词：

Oracle imp进程信息如何查询

如何查询Oracle数据库imp导入时的用户进程