Re: rowid value
Date: Fri, 8 Aug 2014 14:32:00 -0500
Message-Id: <744FD8CB-8325-4AEC-8B2B-60726B347D29_at_gmail.com>
While dealing with a separate issue I ran across another gotcha with ora_rowscn.
By enabling Fine Grained Auditing or Virtual Private Database you will lose the ability to query the column. You receive the error :"ORA-00904: ORA_ROWSCN: invalid identifier"
On Aug 6, 2014, at 12:27 PM, Ryan January <rjanuary_at_gmail.com> wrote:
> I did do some testing while dealing with a logical corruption issue with similar findings. Without rowdependencies set on the table it's not always going to be definitive. There are at least two scenarios where it breaks down.
>
> 1.) As we discussed previously; The rows were residing in the same block. All rows would show the SCN of the most recent update.
> 2.) Multiple rows are updated in multiple blocks, but all part of the same database transaction. This is because the row scn gets updated on commit, not on update.
>
>
> SQL> -- test single row update and impact on ora_rowscn
> SQL> -- multiple rows, multiple blocks, multiple transactions
>
> SQL> -- find row scn's of various rows housed within multiple blocks
> SQL> select DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid), min(rowid), max(rowid), ora_rowscn, count(0)
> from tab1 where DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) between 1879 and 1881
> group by DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid), ora_rowscn; 2 3
>
> DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) MIN(ROWID) MAX(ROWID) ORA_ROWSCN COUNT(0)
> ------------------------------------ ------------------ ------------------ ---------- ----------
> 1880 AACCE4AAEAAAAdYAAA AACCE4AAEAAAAdYAAi 9576379779 35
> 1879 AACCE4AAEAAAAdXAAA AACCE4AAEAAAAdXAAi 9576379779 35
> 1881 AACCE4AAEAAAAdZAAA AACCE4AAEAAAAdZAAi 9576379779 35
>
> SQL> -- update row in block 1880, commit
> SQL> update tab1 set cola = 'updated1' where rowid = 'AACCE4AAEAAAAdYAAA';
> 1 row updated.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> -- update row in block 1879, commit
> SQL> update tab1 set cola = 'updated1' where rowid = 'AACCE4AAEAAAAdXAAA';
> 1 row updated.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> -- check row scn's contained within same blocks
> SQL> select DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid), min(rowid), max(rowid), ora_rowscn, count(0)
> from tab1 where DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) between 1879 and 1881
> group by DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid), ora_rowscn; 2 3
>
> DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) MIN(ROWID) MAX(ROWID) ORA_ROWSCN COUNT(0)
> ------------------------------------ ------------------ ------------------ ---------- ----------
> 1880 AACCE4AAEAAAAdYAAA AACCE4AAEAAAAdYAAi 9576463462 35
> 1879 AACCE4AAEAAAAdXAAA AACCE4AAEAAAAdXAAi 9576463464 35
> 1881 AACCE4AAEAAAAdZAAA AACCE4AAEAAAAdZAAi 9576379779 35
>
>
> SQL> -- all 3 blocks have differing row scn times.
>
>
>
> SQL> -- Similar test but updating multiple rows in multiple blocks within same transaction
> SQL> -- find row scn's of various rows housed within multiple blocks
>
> SQL> select DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid), min(rowid), max(rowid), ora_rowscn, count(0)
> from tab1 where DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) between 1881 and 1883
> group by DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid), ora_rowscn;
> 2 3
>
> DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) MIN(ROWID) MAX(ROWID) ORA_ROWSCN COUNT(0)
> ------------------------------------ ------------------ ------------------ ---------- ----------
> 1882 AACCE4AAEAAAAdaAAA AACCE4AAEAAAAdaAAi 9576379779 35
> 1881 AACCE4AAEAAAAdZAAA AACCE4AAEAAAAdZAAi 9576379779 35
> 1883 AACCE4AAEAAAAdbAAA AACCE4AAEAAAAdbAAi 9576379779 35
>
> SQL> -- update row in block 1882
> SQL> update tab1 set cola = 'updated1' where rowid = 'AACCE4AAEAAAAdaAAA';
>
> 1 row updated.
>
> SQL> -- update row in block 1881
> SQL> update tab1 set cola = 'updated1' where rowid = 'AACCE4AAEAAAAdZAAA';
>
> 1 row updated.
>
> SQL> commit;
>
> Commit complete.
>
> SQL> -- check row scn's contained within same blocks
> SQL> select DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid), min(rowid), max(rowid), ora_rowscn, count(0)
> from tab1 where DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) between 1881 and 1883
> group by DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid), ora_rowscn; 2 3
>
> DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) MIN(ROWID) MAX(ROWID) ORA_ROWSCN COUNT(0)
> ------------------------------------ ------------------ ------------------ ---------- ----------
> 1882 AACCE4AAEAAAAdaAAA AACCE4AAEAAAAdaAAi 9576464404 35
> 1881 AACCE4AAEAAAAdZAAA AACCE4AAEAAAAdZAAi 9576464404 35
> 1883 AACCE4AAEAAAAdbAAA AACCE4AAEAAAAdbAAi 9576379779 35
>
> SQL> -- note that rows were updated at differing times, however the row scn's match between the blocks.
>
> On Aug 6, 2014, at 10:32 AM, Michael Cunningham <napacunningham_at_gmail.com> wrote:
>
>> I have not tested this, but I wonder if ora_rowscn could be used to help find the newest duplicate. Assuming rowdependencies is not set on the table then I would see if there were a max ora_rowscn for matching rows. If the ora_rowscn were the same then the rows are most likely (if I remember correctly) in the same block. At that point I could use max rowid and I'd, most likely, have the row inserted last. However, if rowdependencies is set on the table, then max ora_rowscn should find the latest row. >> >> Well, looks like I have another theory to check. >> >> Michael Cunningham >> >> On Aug 6, 2014 3:22 AM, "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote: >> >> >> If your tablespace is made up of a single file, and if you never drop or rebuild objects, and if there are no routine bulk deletes then you've got a good chance of the "free blocks" effect being true. >> >> Regards >> Jonathan Lewis >> http://jonathanlewis.wordpress.com >> _at_jloracle >> From: oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on behalf of Kim Berg Hansen [kibeha_at_gmail.com] >> Sent: 06 August 2014 09:15 >> To: Hemant-K.Chitale_at_sc.com >> Cc: Brian.Zelli_at_roswellpark.org; ORACLE-L >> Subject: Re: rowid value >> >> Exactly. >> >> If the idea of deleting the "higher" ROWID is to delete the "newest" row, that is not at all certain. But if the rows are identical duplicates, that shouldn't matter ;-) >> >> But supposing there are a *lot* of duplicates to remove (someone by mistake ran a big import job twice, for example), wouldn't always picking "higher" ROWID to delete be somewhat helpful in that there's a bigger chance of clearing space by "emptying" blocks? >> >> >> >> Regards >> >> >> Kim Berg Hansen >> >> http://dspsd.blogspot.com >> kibeha_at_gmail.com >> _at_kibeha >> >> >> >> On Wed, Aug 6, 2014 at 9:04 AM, Chitale, Hemant K <Hemant-K.Chitale_at_sc.com> wrote: >> AAAocvACXAAAPFJAAb is the “higher” ROWID. But it might be holding the “older” row. >> >> >> >> Hemant K Chitale >> >> >> >> >> >> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Zelli, Brian >> Sent: Tuesday, August 05, 2014 11:07 PM >> To: oracle-l (oracle-l_at_freelists.org) >> Subject: rowid value >> >> >> >> Another dba I’m covering for told me to delete a duplicate with the higher rowid? >> >> Which one is the higher rowid? AAAocvACXAAAPFJAAa or AAAocvACXAAAPFJAAb? >> >> >> >> >> >> Brian >> >> >> >> >> >> >> This email message may contain legally privileged and/or confidential information. If you are not the intended recipient(s), or the employee or agent responsible for the delivery of this message to the intended recipient(s), you are hereby notified that any disclosure, copying, distribution, or use of this email message is prohibited. If you have received this message in error, please notify the sender immediately by e-mail and delete this email message from your computer. Thank you. >> >> >> This email and any attachments are confidential and may also be privileged. If you are not the intended recipient, please delete all copies and notify the sender immediately. You may wish to refer to the incorporation details of Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at https://www.sc.com/en/incorporation-details.html. >>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Aug 08 2014 - 21:32:00 CEST