如何在不访问源代码的情况下确定 Oracle 查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/442634/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to determine an Oracle query without access to source code?
提问by inferis
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 后端（尽管可能不是管理访问权限）和一个我们没有源代码的前端。数据库很大，不容易理解——我们没有文档。总的来说，我对 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），但我很想知道其他任何想法，以及是否有人能想到这个特定的陷阱方法。
回答by Dave Costa
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 并且他们不让您进入机器，这可能会出现问题。此外，如果您有许多客户端或客户端应用程序打开多个会话，则识别要跟踪的正确会话可能会很困难。
回答by tuinstoel
Start with querying Oracle system views like V$SQL, v$sqlarea and v$sqltext.
从查询 Oracle 系统视图开始，例如 V$SQL、v$sqlarea 和 v$sqltext。
回答by bortzmeyer
Wiresharkis 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 这样的数据包嗅探器特别有趣。
回答by zendar
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 oraclewhich is quite capable piece of software, but expensive.
您可以通过TOAD for oracle获得相同的数据，这是一款功能强大的软件，但价格昂贵。
回答by Gary Myers
"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/管理员访问权限的用户。管理员应该能够使用“SQLPLUS / AS SYSDBA”语法登录，这将提供完全访问权限（这可能非常危险）。root 可以对 oracle 用户“su”并执行相同的操作。
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
但是，客户端可能会调用存储过程并检索数据作为输出参数或引用游标，这意味着您可能看不到通过该机制执行的查询。如果是这样，您将需要对 db 服务器的管理员访问权限，并按照 Dave Costa 的回答进行跟踪
回答by JosephStyons
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
回答by WW.
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$ 视图才能工作。通常这意味着作为系统连接。
