Home » RDBMS Server » Server Administration » LogMiner - select for update
LogMiner - select for update [message #689086] Mon, 18 September 2023 05:29 Go to next message
lodimas
Messages: 5
Registered: September 2023
Junior Member
According to Oracle's documentation, LogMiner is prepared to provide select for update operations (operation = SELECT_FOR_UPDATE, operation_code = 25, in view v$logmnr_contents). Still, I am unable to get any select for update when using LogMiner. Does anybody know if some special configuration is required to get these operations? Is it only under some special circumstances that select for update is gotten from LogMiner?
Re: LogMiner - select for update [message #689087 is a reply to message #689086] Mon, 18 September 2023 06:07 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

Quote:
According to Oracle's documentation

Can you provide a link to this documentation as well as your Oracle version.

Re: LogMiner - select for update [message #689088 is a reply to message #689087] Mon, 18 September 2023 06:56 Go to previous messageGo to next message
lodimas
Messages: 5
Registered: September 2023
Junior Member
Sure!

- This is link for description of v$logmnr_contents in Oracle 19: https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/V-LOGMNR_CONTENTS.html#GUID-B9196942-07BF-4935-B603-FA875064F5C3

- This is link for description of v$logmnr_contents in Oracle 21: https://docs.oracle.com/en/database/oracle/oracle-database/21/refrn/V-LOGMNR_CONTENTS.html#GUID-B9196942-07BF-4935-B603-FA875064F5C3

Both mention possible value SELECT_FOR_UPDATE for column operation.

I tested this in Oracle 19 and 21 with the same result: SELECT FOR UPDATE is missing in the contents of a LogMiner session.

To be more precise, I did:

select *
 from <some_table>
where <some condition>
  for update;
commit;

Then I established a LogMiner session to query for available contents. No row contains operation type SELECT_FOR_UPDATE.

[Updated on: Mon, 18 September 2023 06:57]

Report message to a moderator

Re: LogMiner - select for update [message #689089 is a reply to message #689088] Mon, 18 September 2023 08:38 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Well, it works for me:
SQL> col member      new_value member format a50
SQL> col current_scn new_value scn
SQL> select member, current_scn
  2  from v$logfile f, v$log l, v$database
  3  where f.group# = l.group# and l.status='CURRENT' and rownum=1
  4  /
MEMBER                                             CURRENT_SCN
-------------------------------------------------- -----------
E:\ORACLE\BASES\MIKB2\RL_G1_1.RDO                    201132050

1 row selected.

SQL> select * from emp for update;
     EMPNO ENAME      JOB              MGR HIREDATE           SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-1980        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-1981       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-1981       1250        500         30
      7566 JONES      MANAGER         7839 02-APR-1981       2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-1981       1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-1981       2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-1981       2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-1987       3000                    20
      7839 KING       PRESIDENT            17-NOV-1981       5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-1981       1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-1987       1100                    20
      7900 JAMES      CLERK           7698 03-DEC-1981        950                    30
      7902 FORD       ANALYST         7566 03-DEC-1981       3000                    20
      7934 MILLER     CLERK           7782 23-JAN-1982       1300                    10

14 rows selected.

SQL> commit;

Commit complete.

SQL> begin
  2    sys.dbms_logmnr.add_logfile (logfilename=>'&member', options=>sys.dbms_logmnr.NEW);
  3    sys.dbms_logmnr.start_logmnr (
  4      startScn => &scn,
  5      options  =>   sys.dbms_logmnr.skip_corruption
  6                  + sys.dbms_logmnr.committed_data_only
  7                  + sys.dbms_logmnr.dict_from_online_catalog
  8    );
  9  end;
 10  /

PL/SQL procedure successfully completed.

SQL> select distinct operation from v$logmnr_contents
  2  /
OPERATION
--------------------------------
START
SELECT_FOR_UPDATE
COMMIT

3 rows selected.

SQL> exec sys.dbms_logmnr.end_logmnr;

PL/SQL procedure successfully completed.

SQL> @v

Oracle version: 11.2.0.4.181016 EE - JVM v1.6.0_43 - timezone files v11
But note that I have a minimal supplemental logging set:
SQL> select SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK, SUPPLEMENTAL_LOG_DATA_UI
  2  from v$database
  3  /
SUPPLEME SUP SUP
-------- --- ---
YES      NO  NO
This parameter (from doc) "Ensures that LogMiner (and any products building on LogMiner technology) will have sufficient information to support chained rows and various storage arrangements such as cluster tables" (which is not the case here but maybe it enables support of SELECT FOR UPDATE).

[Updated on: Mon, 18 September 2023 08:39]

Report message to a moderator

Re: LogMiner - select for update [message #689090 is a reply to message #689089] Mon, 18 September 2023 10:10 Go to previous messageGo to next message
lodimas
Messages: 5
Registered: September 2023
Junior Member
Thank you for your detailed answer.

In general terms, we are doing essentially the same, and we seem to have equivalent configurations. The only meaningful difference I am able to see is the Oracle version we are using. Therefore, I repeated my tests on Oracle 11, and, to my surprise, SELECT_FOR_UPDATE, and other useful information, are there.
So, it seems that Oracle changed the way information is exposed in LogMiner depending on the version. My question is then, is there some configuration setting or option that can make Oracle provide back the information as provided in Oracle 11?

Note: Actually, it seems that the SELECT_FOR_UPDATE row is provided, but with operation type INTERNAL.

[Updated on: Mon, 18 September 2023 10:11]

Report message to a moderator

Re: LogMiner - select for update [message #689093 is a reply to message #689090] Mon, 18 September 2023 12:30 Go to previous messageGo to next message
Michel Cadot
Messages: 68712
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator

By default, minimal supplemental logging is off in 19c and 21c.
However, I tried with both options in both versions and I find no SELECT_FOR_UPDATE rows like you.

The only reason I saw is in-memory undo mechanism which prevents from writing records in logs if transaction is short enough and having no information in logs for this one does not harm.
But I tried disabling it at session level and got the same result (no SELECT_FOR_UPDATE rows).

I move this topic to Server Administration forum maybe you'll have a larger audience.


Re: LogMiner - select for update [message #689095 is a reply to message #689093] Mon, 18 September 2023 14:08 Go to previous message
lodimas
Messages: 5
Registered: September 2023
Junior Member
Thank you for taking care of this! Let's see if somebody can provide a solution or explanation for this behavior.
Previous Topic: oracle compression issues
Next Topic: Power Shell script to send alert
Goto Forum:
  


Current Time: Fri Nov 15 17:23:16 CST 2024