Re: rowid value
- From: Ryan January <rjanuary@xxxxxxxxx>
- To: napacunningham@xxxxxxxxx
- Date: Wed, 6 Aug 2014 12:27:09 -0500
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:
