天萃荷净
运维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