SQL - Find statement that insert specific values(SQL - 查找插入特定值的语句)
本文介绍了SQL - 查找插入特定值的语句的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在使用一个 Oracle 数据库，通过 Oracle SQL Developer 和 R 与它进行交互.这个数据库在几个人之间共享，但由于某些公司规则，我们都使用同一个用户与之交互.
I'm working with an Oracle database, interacting with it both from Oracle SQL Developer and from R. This database is shared between several people, but because of some company rules we all use the same user to interact with it.
在我们的一个表中，我们发现了一种奇怪的行为，即在没有我们知道的任何顺序的情况下不断插入一行.
In one of our tables we found a strange behaviour, were a line is constantly inserted without any order that we know of.
比如说，在 Table1 上，有时有一行值 Name = "Test" 创建了超过 8K 次，我们无法弄清楚为什么会发生这种情况.
Say, on Table1, sometimes there is a line with value Name = "Test" created >8K times, and we can't figure out why is this happening.
我想要做的是创建某种自动机制(触发器)，当在 Table1 上插入 Name = "Test" 的行时，会保存执行此操作的查询和日期/时间.
What I wanted to do is to create some sort of automatism (triger) that, when a line with Name = "Test" is inserted on Table1 saves the query that did it and the date/time.
推荐答案
如果有搜索期的存档日志.您必须使用实用程序 LogMiner.示例 1.
If there are archive logs for the search period. You must use the utility LogMiner. Example 1.
EXECUTE DBMS_LOGMNR.add_logfile(LOGFILENAME => '/oracle/app/oracle/product/11.2/admin/edcu/arc_redo_log/1_39306_769799469.dbf', OPTIONS => DBMS_LOGMNR.NEW);
EXECUTE DBMS_LOGMNR.add_logfile(LOGFILENAME => '/oracle/app/oracle/product/11.2/admin/edcu/arc_redo_log/1_39307_769799469.dbf', OPTIONS => DBMS_LOGMNR.addfile);
EXECUTE DBMS_LOGMNR.add_logfile(LOGFILENAME => '/oracle/app/oracle/product/11.2/admin/edcu/arc_redo_log/1_39308_769799469.dbf', OPTIONS => DBMS_LOGMNR.addfile);
EXECUTE DBMS_LOGMNR.add_logfile(LOGFILENAME => '/oracle/app/oracle/product/11.2/admin/edcu/arc_redo_log/1_39309_769799469.dbf', OPTIONS => DBMS_LOGMNR.addfile);
EXECUTE DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
SELECT
to_char(timestamp,'DD-MM-YYYY HH24:MI:SS'),
operation,username,
os_username,
machine_name,
session_info,
sql_redo
FROM v$logmnr_contents
where
seg_owner='MANAGER' and seg_name='TEST2'
14-12-2017 09:40:20 DDL MANAGER DeminDV KRWIVC-PTK-DEMIN login_username=MANAGER client_info= OS_username=DeminDV Machine_name=KRWIVC-PTK-DEMIN create table test2 (p1 number);
14-12-2017 09:40:47 INSERT MANAGER DeminDV KRWIVC-PTK-DEMIN login_username=MANAGER client_info= OS_username=DeminDV Machine_name=KRWIVC-PTK-DEMIN insert into "MANAGER"."TEST2"("COL 1") values (HEXTORAW('c117'));
14-12-2017 09:40:53 INSERT MANAGER DeminDV KRWIVC-PTK-DEMIN login_username=MANAGER client_info= OS_username=DeminDV Machine_name=KRWIVC-PTK-DEMIN insert into "MANAGER"."TEST2"("COL 1") values (HEXTORAW('c119'));
14-12-2017 09:40:57 INSERT MANAGER DeminDV KRWIVC-PTK-DEMIN login_username=MANAGER client_info= OS_username=DeminDV Machine_name=KRWIVC-PTK-DEMIN insert into "MANAGER"."TEST2"("COL 1") values (HEXTORAW('c137'));
14-12-2017 09:41:01 INSERT MANAGER DeminDV KRWIVC-PTK-DEMIN login_username=MANAGER client_info= OS_username=DeminDV Machine_name=KRWIVC-PTK-DEMIN insert into "MANAGER"."TEST2"("COL 1") values (HEXTORAW('c20219'));
14-12-2017 09:41:45 DDL MANAGER DeminDV KRWIVC-PTK-DEMIN login_username=MANAGER client_info= OS_username=DeminDV Machine_name=KRWIVC-PTK-DEMIN alter table test2 add (p2 varchar2(200));
14-12-2017 09:42:12 INSERT MANAGER DeminDV KRWIVC-PTK-DEMIN login_username=MANAGER client_info= OS_username=DeminDV Machine_name=KRWIVC-PTK-DEMIN insert into "MANAGER"."TEST2"("COL 1","COL 2") values (HEXTORAW('c20219'),HEXTORAW('746573743220746573743120'));
14-12-2017 09:42:24 INSERT MANAGER DeminDV KRWIVC-PTK-DEMIN login_username=MANAGER client_info= OS_username=DeminDV Machine_name=KRWIVC-PTK-DEMIN insert into "MANAGER"."TEST2"("COL 1","COL 2") values (HEXTORAW('c2021a'),HEXTORAW('746573743420746573743420'));
14-12-2017 09:46:24 INSERT MANAGER DeminDV KRWIVC-PTK-DEMIN login_username=MANAGER client_info= OS_username=DeminDV Machine_name=KRWIVC-PTK-DEMIN insert into "MANAGER"."TEST2"("COL 1","COL 2") values (HEXTORAW('c2021a'),HEXTORAW('746573743420746573743420'));
14-12-2017 09:46:25 INSERT MANAGER DeminDV KRWIVC-PTK-DEMIN login_username=MANAGER client_info= OS_username=DeminDV Machine_name=KRWIVC-PTK-DEMIN insert into "MANAGER"."TEST2"("COL 1","COL 2") values (HEXTORAW('c2021a'),HEXTORAW('746573743420746573743420'));
14-12-2017 09:46:26 INSERT MANAGER DeminDV KRWIVC-PTK-DEMIN login_username=MANAGER client_info= OS_username=DeminDV Machine_name=KRWIVC-PTK-DEMIN insert into "MANAGER"."TEST2"("COL 1","COL 2") values (HEXTORAW('c2021a'),HEXTORAW('746573743420746573743420'));
14-12-2017 09:46:27 INSERT MANAGER DeminDV KRWIVC-PTK-DEMIN login_username=MANAGER client_info= OS_username=DeminDV Machine_name=KRWIVC-PTK-DEMIN insert into "MANAGER"."TEST2"("COL 1","COL 2") values (HEXTORAW('c2021a'),HEXTORAW('746573743420746573743420'));
14-12-2017 09:46:28 INSERT MANAGER DeminDV KRWIVC-PTK-DEMIN login_username=MANAGER client_info= OS_username=DeminDV Machine_name=KRWIVC-PTK-DEMIN insert into "MANAGER"."TEST2"("COL 1","COL 2") values (HEXTORAW('c2021a'),HEXTORAW('746573743420746573743420'));
14-12-2017 09:54:37 DDL MANAGER DeminDV KRWIVC-PTK-DEMIN login_username=MANAGER client_info= OS_username=DeminDV Machine_name=KRWIVC-PTK-DEMIN comment on table test2 is 'test';
14-12-2017 10:16:36 DDL MANAGER DeminDV KRWIVC-PTK-DEMIN login_username=MANAGER client_info= OS_username=DeminDV Machine_name=KRWIVC-PTK-DEMIN alter table test2 add (p3 varchar2(100));
14-12-2017 10:17:07 INSERT MANAGER DeminDV KRWIVC-PTK-DEMIN login_username=MANAGER client_info= OS_username=DeminDV Machine_name=KRWIVC-PTK-DEMIN insert into "MANAGER"."TEST2"("P1","P2","P3") values ('125','test6','test4 ');
14-12-2017 10:17:08 INSERT MANAGER DeminDV KRWIVC-PTK-DEMIN login_username=MANAGER client_info= OS_username=DeminDV Machine_name=KRWIVC-PTK-DEMIN insert into "MANAGER"."TEST2"("P1","P2","P3") values ('125','test6','test4 ');
'
示例 2. 使用闪回功能
Example 2. Using Flashback Features
update manager.test2 set p2='test10' where p1=125;
commit;
SELECT versions_xid XID, versions_startscn START_SCN,
versions_endscn END_SCN, versions_operation OPERATION,
p1, p2, p3 FROM manager.test2
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;
XID START_SCN END_SCN OPERATION P1 P2 P3
---------------------- ------------------------ ---------------------- --------------- ---------- -------- ------
08000E000DD70900 1374388524564 U 125 test10 test4
08000E000DD70900 1374388524564 U 125 test10 test4
08000E000DD70900 1374388524564 U 125 test10
08000E000DD70900 1374388524564 U 125 test10
1374388524564 125 test4 test4
1374388524564 125 test4 test4
1374388524564 125 test6 test4
1374388524564 125 test6 test4
SELECT xid, start_scn , commit_scn COMMIT,operation OP, logon_user ,undo_sql FROM flashback_transaction_query
WHERE xid = HEXTORAW('08000E000DD70900');
XID START_SCN COMMIT OP LOGON_USER UNDO_SQL
-------------------------------- -------------------------------- -------------------------------------- --------------------------------- ------------------------------------
08000E000DD70900 1374388524537 1374388524564 UPDATE MANAGER update "MANAGER"."TEST2" set "P2" = 'test6' where ROWID = 'AAA2wgAAbAAAAEsAAM';
08000E000DD70900 1374388524537 1374388524564 UPDATE MANAGER update "MANAGER"."TEST2" set "P2" = 'test6' where ROWID = 'AAA2wgAAbAAAAEsAAL';
08000E000DD70900 1374388524537 1374388524564 UPDATE MANAGER update "MANAGER"."TEST2" set "P2" = 'test4 test4 ' where ROWID = 'AAA2wgAAbAAAAEsAAK';
08000E000DD70900 1374388524537 1374388524564 UPDATE MANAGER update "MANAGER"."TEST2" set "P2" = 'test4 test4 ' where ROWID = 'AAA2wgAAbAAAAEsAAJ';
08000E000DD70900 1374388524537 1374388524564 UPDATE MANAGER update "MANAGER"."TEST2" set "P2" = 'test4 test4 ' where ROWID = 'AAA2wgAAbAAAAEsAAI';
08000E000DD70900 1374388524537 1374388524564 UPDATE MANAGER update "MANAGER"."TEST2" set "P2" = 'test4 test4 ' where ROWID = 'AAA2wgAAbAAAAEsAAH';
08000E000DD70900 1374388524537 1374388524564 UPDATE MANAGER update "MANAGER"."TEST2" set "P2" = 'test4 test4 ' where ROWID = 'AAA2wgAAbAAAAEsAAG';
08000E000DD70900 1374388524537 1374388524564 UPDATE MANAGER update "MANAGER"."TEST2" set "P2" = 'test4 test4 ' where ROWID = 'AAA2wgAAbAAAAEsAAF';
08000E000DD70900 1374388524537 1374388524564 BEGIN MANAGER
这篇关于SQL - 查找插入特定值的语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！
沃梦达教程
本文标题为：SQL - 查找插入特定值的语句
基础教程推荐
猜你喜欢
- 将数据从 MS SQL 迁移到 PostgreSQL? 2022-01-01
- 如何在 SQL Server 的嵌套过程中处理事务? 2021-01-01
- 使用pyodbc“不安全"的Python多处理和数据库访问? 2022-01-01
- Sql Server 字符串到日期的转换 2021-01-01
- ERROR 2006 (HY000): MySQL 服务器已经消失 2021-01-01
- 无法在 ubuntu 中启动 mysql 服务器 2021-01-01
- SQL Server 中单行 MERGE/upsert 的语法 2021-01-01
- 在 VB.NET 中更新 SQL Server DateTime 列 2021-01-01
- SQL Server 2016更改对象所有者 2022-01-01
- SQL Server:只有 GROUP BY 中的最后一个条目 2021-01-01
