Re: rowid value
- From: Ryan January <rjanuary@xxxxxxxxx>
- To: "oracle-l@freelists org" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 8 Aug 2014 14:32:00 -0500
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@xxxxxxxxx> 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@xxxxxxxxx>
> 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@xxxxxxxxxxxxxxxxxx> 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
>> @jloracle
>> From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on
>> behalf of Kim Berg Hansen [kibeha@xxxxxxxxx]
>> Sent: 06 August 2014 09:15
>> To: Hemant-K.Chitale@xxxxxx
>> Cc: Brian.Zelli@xxxxxxxxxxxxxxx; 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@xxxxxxxxx
>> @kibeha
>>
>>
>>
>> On Wed, Aug 6, 2014 at 9:04 AM, Chitale, Hemant K <Hemant-K.Chitale@xxxxxx>
>> wrote:
>> AAAocvACXAAAPFJAAb is the “higher” ROWID. But it might be holding the
>> “older” row.
>>
>>
>>
>> Hemant K Chitale
>>
>>
>>
>>
>>
>> From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
>> On Behalf Of Zelli, Brian
>> Sent: Tuesday, August 05, 2014 11:07 PM
>> To: oracle-l (oracle-l@xxxxxxxxxxxxx)
>> 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.
>>
>
Other related posts:
