如何在 Oracle 中获取命名事务的名称? [英] How to get the name of a named transaction in Oracle?

问题描述

我想在触发器中使用事务的名称，以便将其写入列中.

I would like to use the name of a transaction in a trigger in order to write it into a column.

我试过这个(在 SQL Developer 中):

I tried this (in SQL Developer):

set transaction name 'hello';
select DBMS_TRANSACTION.LOCAL_TRANSACTION_ID from dual;
commit;

但我得到的不是交易名称，而是某种随机值:

But instead of the name of the transaction I get some kind of random value:

transaction NAME succeeded.
SUBSTR(DBMS_TRANSACTION.LOCAL_TRANSACTION_ID,0,20)
--------------------------------------------------
1.25.19794                                         

committed.

如何获取命名事务的名称?

How can I get the name of a named transaction?

推荐答案

我刚刚尝试了类似的东西，对于 DBMS_TRANSACTION.LOCAL_TRANSACTION_ID 我得到了值 9.7.1270>.当我按名称查找交易时，我发现了以下内容:

I just tried something similar, and for the DBMS_TRANSACTION.LOCAL_TRANSACTION_ID I got back the value 9.7.1270. When I looked up the transaction by name, I found the following:

• v$transaction.XIDUSN = 9
• v$transaction.XIDSLOT = 7
• v$transaction.XIDSQN = 1270

把它们放在一起，你就得到了 9.7.1270.因此(并注意这可能是错误的 - 我发现的文档没有涵盖这一点)，您可以像这样获取当前的交易名称:

Put them together and you get the 9.7.1270. So (and note that this could be wrong - the docs I found don't cover this), you may be able to get the current transaction name like this:

SELECT Name
FROM v$transaction
WHERE xidusn ||'.'|| xidslot ||'.'|| xidsqn = DBMS_TRANSACTION.LOCAL_TRANSACTION_ID;

我可以确定的一个事实:v$transaction 行不会显示，除非您执行以下操作之一:

One fact I could establish: the v$transaction row won't show up until you do one of the following:

1. INSERT/UPDATE/DELETE/MERGE 类型的操作，或
2. 调用 DBMS_TRANSACTION.LOCAL_TRANSACTION_ID 自己 - 这意味着仅在上面的查询中使用它似乎不足以填充 v$transaction 行.

1. An INSERT/UPDATE/DELETE/MERGE type of operation, or
2. Call DBMS_TRANSACTION.LOCAL_TRANSACTION_ID on its own - meaning that just having it in the query above doesn't seem to be enough to populate the v$transaction row.

但是，如果我执行上述任何一项操作，查询就会起作用(我现在已经测试了 4 或 5 次)，并且由于您将在触发器中尝试它，因此上面的第 2 项应该涵盖.

But if I do either of the above, the query works (I've tested it 4 or 5 times now), and since you'll be trying it in a trigger you should be covered by item #2 above.

最后一点:v$transaction 访问受到相当的限制，因此大多数用户将无法看到它.使事情复杂化，v$transaction 不是实际视图；它是视图 sys.v_$transaction 的同义词(注意美元符号前的下划线)，因此这是您在授予时需要使用的名称.而且我很确定您必须以 SYSDBA 身份登录才能进行授权 - 我能够以 SYSTEM 身份查询 v$transaction> 用户，但我没有足够的权限来GRANT SELECT 给另一个用户.

One final note: v$transaction access is fairly restricted, so most users won't be able to see it. Complicating matters, v$transaction isn't the actual view; it's a synonym for the view sys.v_$transaction (note the underscore before the dollar sign) so that's the name you need to use when granting. And I'm pretty sure you have to be logged in as SYSDBA to do the grant - I was able to query v$transaction as the SYSTEM user but I didn't have sufficient privileges to GRANT SELECT to another user.

这篇关于如何在 Oracle 中获取命名事务的名称?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！