RE: rowid value

From: Michael Cunningham <napacunningham_at_gmail.com>
Date: Wed, 6 Aug 2014 08:32:45 -0700
Message-ID: <CAPt39tvwFVS+yTgHWjAPFhiSmd1fSkVhuFa+WQ3hHhMm-qeioQ_at_mail.gmail.com>

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-l

Received on Wed Aug 06 2014 - 17:32:45 CEST