We have a system with an Oracle backend to which we have access (though possibly not administrative access) and a front end to which we do not have the source code. The database is quite large and not easily understood - we have no documentation. I'm also not particularly knowledgable about Oracle in general.
我们有一个带有Oracle后端的系统,我们可以访问它(虽然可能不是管理访问权限)和我们没有源代码的前端。数据库非常庞大,不易理解 - 我们没有文档。我对甲骨文一般也不是特别了解。
One aspect of the front end queries the database for a particular set of data and displays it. We have a need to determine what query is being made so that we can replicate and automate it without the front end (e.g. by generating a csv file periodically).
前端的一个方面向数据库查询特定数据集并显示它。我们需要确定正在进行的查询,以便我们可以在没有前端的情况下复制和自动化它(例如,通过定期生成csv文件)。
What methods would you use to determine the SQL required to retrieve this set of data?
您将使用哪些方法来确定检索此数据集所需的SQL?
Currently I'm leaning towards the use of an EeePC, Wireshark and a hub (installing Wireshark on the client machines may not be possible), but I'm curious to hear any other ideas and whether anyone can think of any pitfalls with this particular approach.
目前我倾向于使用EeePC,Wireshark和集线器(在客户端机器上安装Wireshark可能无法实现),但我很想听到任何其他想法以及是否有人能想到这个特定的任何陷阱做法。
7 个解决方案
#1
Clearly there are many methods. The one that I find easiest is:
显然有很多方法。我认为最容易的是:
(1) Connect to the database as SYS or SYSTEM
(1)以SYS或SYSTEM身份连接到数据库
(2) Query V$SESSION to identify the database session you are interested in. Record the SID and SERIAL# values.
(2)查询V $ SESSION以识别您感兴趣的数据库会话。记录SID和SERIAL#值。
(3) Execute the following commands to activate tracing for the session:
(3)执行以下命令以激活会话的跟踪:
exec sys.dbms_system.set_bool_param_in_session( *sid*, *serial#*, 'timed_statistics', true )
exec sys.dbms_system.set_int_param_in_session( *sid*, *serial#*, 'max_dump_file_size', 2000000000 )
exec sys.dbms_system.set_ev( *sid*, *serial#*, 10046, 5, '' )
(4) Perform some actions in the client app
(4)在客户端应用程序中执行一些操作
(5) Either terminate the database session (e.g. by closing the client) or deactivate tracing ( exec sys.dbms_system.set_ev( sid, serial#, 10046, 0, '' ) )
(5)终止数据库会话(例如,通过关闭客户端)或停用跟踪(exec sys.dbms_system.set_ev(sid,serial#,10046,0,''))
(6) Locate the udump folder on the database server. There will be a trace file for the database session showing the statements executed and the bind values used in each execution.
(6)在数据库服务器上找到udump文件夹。将有一个数据库会话的跟踪文件,显示执行的语句和每次执行中使用的绑定值。
This method does not require any access to the client machine, which could be a benefit. It does require access to the database server, which may be problematic if you're not the DBA and they don't let you onto the machine. Also, identifying the proper session to trace can be difficult if you have many clients or if the client application opens more than one session.
此方法不需要任何访问客户端计算机,这可能是一个好处。它确实需要访问数据库服务器,如果您不是DBA并且他们不允许您进入计算机,这可能会有问题。此外,如果您有许多客户端或客户端应用程序打开多个会话,则识别正确的跟踪会话可能很困难。
#2
Start with querying Oracle system views like V$SQL, v$sqlarea and v$sqltext.
首先查询Oracle系统视图,如V $ SQL,v $ sqlarea和v $ sqltext。
#3
Which version of Oracle? If it is 10+ and if you have administrative access (sysdba), then you can relatively easy find executed queries through Oracle enterprise manager.
哪个版本的Oracle?如果它是10+并且您具有管理访问权限(sysdba),那么您可以通过Oracle企业管理器相对容易地找到执行的查询。
For older versions, you'll need access to views that tuinstoel mentioned in his answer.
对于旧版本,您需要访问tuinstoel在其答案中提到的视图。
Same data you can get through TOAD for oracle which is quite capable piece of software, but expensive.
你可以通过TOAD获得相同的数据,这是一个非常强大的软件,但价格昂贵。
#4
Wireshark is indeed a good idea, it has Oracle support and nicely displays the whole conversation.
Wireshark确实是一个好主意,它有Oracle支持并很好地显示整个对话。
A packet sniffer like Wireshark is especially interesting if you don't have admin' access to the database server but you have access to the network (for instance because there is port mirroring on the Ethernet switch).
如果您没有管理员访问数据库服务器但是您可以访问网络(例如,因为以太网交换机上有端口镜像),像Wireshark这样的数据包嗅探器特别有趣。
#5
I have used these instructions successfully several times: http://www.orafaq.com/wiki/SQL_Trace#Tracing_a_SQL_session
我已多次成功使用这些说明:http://www.orafaq.com/wiki/SQL_Trace#Tracing_a_SQL_session
#6
"though possibly not administrative access". Someone should have administrative access, probably whoever is responsible for backups. At the very least, I expect you'd have a user with root/Administrator access to the machine on which the oracle database is running. Administrator should be able to login with a "SQLPLUS / AS SYSDBA" syntax which will give full access (which can be quite dangerous). root could 'su' to the oracle user and do the same.
“虽然可能不是行政访问”。有人应该具有管理访问权限,可能是谁负责备份。至少,我希望您的用户具有运行oracle数据库的计算机的root / Administrator访问权限。管理员应该能够使用“SQLPLUS / AS SYSDBA”语法登录,该语法将提供完全访问权限(这可能非常危险)。 root可以'su'到oracle用户并执行相同的操作。
If you really can't get admin access then as an alternative to wireshark, if your front-end connects to the database through an Oracle client, look for the file sqlnet.ora. You can set trace_level_client, trace_file_client and trace_directory_client and get it to log the Oracle network traffic between the client and database server.
如果您真的无法获得管理员权限,那么作为wireshark的替代方案,如果您的前端通过Oracle客户端连接到数据库,请查找文件sqlnet.ora。您可以设置trace_level_client,trace_file_client和trace_directory_client,并使其记录客户端和数据库服务器之间的Oracle网络流量。
However it is possible that the client will call a stored procedure and retrieve the data as output parameters or a ref cursor, which means you may not see the query being executed through that mechanism. If so, you will need admin access to the db server, and trace as per Dave Costa's answer
但是,客户端可能会调用存储过程并将数据作为输出参数或引用游标进行检索,这意味着您可能看不到通过该机制执行的查询。如果是这样,您将需要对数据库服务器的管理员访问权限,并根据Dave Costa的答案进行跟踪
#7
A quick and dirty way to do this, if you can catch the SQL statement(s) in the act, is to run this in SQL*Plus:-
如果你能捕获行为中的SQL语句,那么快速而肮脏的方法就是在SQL * Plus中运行它: -
set verify off lines 140 head on pagesize 300
column sql_text format a65
column username format a12
column osuser format a15
break on username on sid on osuser
select S.USERNAME, s.sid, s.osuser,sql_text
from v$sqltext_with_newlines t,V$SESSION s
where t.address =s.sql_address
and t.hash_value = s.sql_hash_value
order by s.sid,t.piece
/
You need access those v$ views for this to work. Generally that means connecting as system.
您需要访问这些v $视图才能使用此功能。通常这意味着连接为系统。
#1
Clearly there are many methods. The one that I find easiest is:
显然有很多方法。我认为最容易的是:
(1) Connect to the database as SYS or SYSTEM
(1)以SYS或SYSTEM身份连接到数据库
(2) Query V$SESSION to identify the database session you are interested in. Record the SID and SERIAL# values.
(2)查询V $ SESSION以识别您感兴趣的数据库会话。记录SID和SERIAL#值。
(3) Execute the following commands to activate tracing for the session:
(3)执行以下命令以激活会话的跟踪:
exec sys.dbms_system.set_bool_param_in_session( *sid*, *serial#*, 'timed_statistics', true )
exec sys.dbms_system.set_int_param_in_session( *sid*, *serial#*, 'max_dump_file_size', 2000000000 )
exec sys.dbms_system.set_ev( *sid*, *serial#*, 10046, 5, '' )
(4) Perform some actions in the client app
(4)在客户端应用程序中执行一些操作
(5) Either terminate the database session (e.g. by closing the client) or deactivate tracing ( exec sys.dbms_system.set_ev( sid, serial#, 10046, 0, '' ) )
(5)终止数据库会话(例如,通过关闭客户端)或停用跟踪(exec sys.dbms_system.set_ev(sid,serial#,10046,0,''))
(6) Locate the udump folder on the database server. There will be a trace file for the database session showing the statements executed and the bind values used in each execution.
(6)在数据库服务器上找到udump文件夹。将有一个数据库会话的跟踪文件,显示执行的语句和每次执行中使用的绑定值。
This method does not require any access to the client machine, which could be a benefit. It does require access to the database server, which may be problematic if you're not the DBA and they don't let you onto the machine. Also, identifying the proper session to trace can be difficult if you have many clients or if the client application opens more than one session.
此方法不需要任何访问客户端计算机,这可能是一个好处。它确实需要访问数据库服务器,如果您不是DBA并且他们不允许您进入计算机,这可能会有问题。此外,如果您有许多客户端或客户端应用程序打开多个会话,则识别正确的跟踪会话可能很困难。
#2
Start with querying Oracle system views like V$SQL, v$sqlarea and v$sqltext.
首先查询Oracle系统视图,如V $ SQL,v $ sqlarea和v $ sqltext。
#3
Which version of Oracle? If it is 10+ and if you have administrative access (sysdba), then you can relatively easy find executed queries through Oracle enterprise manager.
哪个版本的Oracle?如果它是10+并且您具有管理访问权限(sysdba),那么您可以通过Oracle企业管理器相对容易地找到执行的查询。
For older versions, you'll need access to views that tuinstoel mentioned in his answer.
对于旧版本,您需要访问tuinstoel在其答案中提到的视图。
Same data you can get through TOAD for oracle which is quite capable piece of software, but expensive.
你可以通过TOAD获得相同的数据,这是一个非常强大的软件,但价格昂贵。
#4
Wireshark is indeed a good idea, it has Oracle support and nicely displays the whole conversation.
Wireshark确实是一个好主意,它有Oracle支持并很好地显示整个对话。
A packet sniffer like Wireshark is especially interesting if you don't have admin' access to the database server but you have access to the network (for instance because there is port mirroring on the Ethernet switch).
如果您没有管理员访问数据库服务器但是您可以访问网络(例如,因为以太网交换机上有端口镜像),像Wireshark这样的数据包嗅探器特别有趣。
#5
I have used these instructions successfully several times: http://www.orafaq.com/wiki/SQL_Trace#Tracing_a_SQL_session
我已多次成功使用这些说明:http://www.orafaq.com/wiki/SQL_Trace#Tracing_a_SQL_session
#6
"though possibly not administrative access". Someone should have administrative access, probably whoever is responsible for backups. At the very least, I expect you'd have a user with root/Administrator access to the machine on which the oracle database is running. Administrator should be able to login with a "SQLPLUS / AS SYSDBA" syntax which will give full access (which can be quite dangerous). root could 'su' to the oracle user and do the same.
“虽然可能不是行政访问”。有人应该具有管理访问权限,可能是谁负责备份。至少,我希望您的用户具有运行oracle数据库的计算机的root / Administrator访问权限。管理员应该能够使用“SQLPLUS / AS SYSDBA”语法登录,该语法将提供完全访问权限(这可能非常危险)。 root可以'su'到oracle用户并执行相同的操作。
If you really can't get admin access then as an alternative to wireshark, if your front-end connects to the database through an Oracle client, look for the file sqlnet.ora. You can set trace_level_client, trace_file_client and trace_directory_client and get it to log the Oracle network traffic between the client and database server.
如果您真的无法获得管理员权限,那么作为wireshark的替代方案,如果您的前端通过Oracle客户端连接到数据库,请查找文件sqlnet.ora。您可以设置trace_level_client,trace_file_client和trace_directory_client,并使其记录客户端和数据库服务器之间的Oracle网络流量。
However it is possible that the client will call a stored procedure and retrieve the data as output parameters or a ref cursor, which means you may not see the query being executed through that mechanism. If so, you will need admin access to the db server, and trace as per Dave Costa's answer
但是,客户端可能会调用存储过程并将数据作为输出参数或引用游标进行检索,这意味着您可能看不到通过该机制执行的查询。如果是这样,您将需要对数据库服务器的管理员访问权限,并根据Dave Costa的答案进行跟踪
#7
A quick and dirty way to do this, if you can catch the SQL statement(s) in the act, is to run this in SQL*Plus:-
如果你能捕获行为中的SQL语句,那么快速而肮脏的方法就是在SQL * Plus中运行它: -
set verify off lines 140 head on pagesize 300
column sql_text format a65
column username format a12
column osuser format a15
break on username on sid on osuser
select S.USERNAME, s.sid, s.osuser,sql_text
from v$sqltext_with_newlines t,V$SESSION s
where t.address =s.sql_address
and t.hash_value = s.sql_hash_value
order by s.sid,t.piece
/
You need access those v$ views for this to work. Generally that means connecting as system.
您需要访问这些v $视图才能使用此功能。通常这意味着连接为系统。