Re: Column drop issue

Read up: oracle-base, Oracle documentation, Oracle support, whatever else you can find on google (in that order).

If you hit ora-1555 then (assuming you have reasonably sized undo) you need to do it at a quieter time, or add some parallelism to the start_redef, or in an emergency you can trigger reverse order full table scans (try without first). You already have row chaining so reading the table is going to be slower than another table of the same segment size.

CTAS: read up on how dbms_redefinition is implemented, it’s pretty similar.

Multiblock reads are rowid range scans, right? They’re just going to be slower when your rows are chained.

I trust you have done adequate consideration about the columns that are really needed in this table.

Thanks,
Andy

On Fri, 8 Apr 2022 at 08:07, Pap <oracle.developer35_at_gmail.com> wrote:

> Thank you Andy.
>
> Never implemented this type of stuff with dbms_redefinition in past
> though. Can you point to some doc or sample script. However in this
> strategy won't it break with ora-01555 even it runs longer?
>
> Also some team mates suggesting to do it as CTAS approach to have it done
> in faster time. And also CTAS can be run in parallel as opposed to normal
> Alter. Is this advisable here?
>
> Another question I had in mind , while this Alter is running its valid to
> see full scan/multiblock physical reads. But why I am seeing 'rowid range
> scan' in long ops?
>
>
> On Fri, 8 Apr 2022, 10:18 am Andy Sayer, <andysayer_at_gmail.com> wrote:
>
>> Dbms_redefinition
>>
>> On Fri, 8 Apr 2022 at 05:20, Pap <oracle.developer35_at_gmail.com> wrote:
>>
>>> Hello Experts, This is a 19c database. We encountered a scenario in
>>> which an alter query got stuck and eventually failed after many hours with
>>> Ora-1555. It's a range partitioned table with ~600million rows and ~600GB
>>> in size and the team is trying to drop ~20 columns out of it using "Alter
>>> table drop(col1,col2,col3.. coln);" syntax. The wait event we are seeing is
>>> 'log buffer space' also 'cell multiblock physical read". At one point we
>>> checked, the "long ops" was showing a 'rowid range scan' and the estimated
>>> time it was showing was ~60hrs+. So wondering how to make this Alter
>>> succeed?
>>>
>>> Had a talk with respective team, we got to know this table is
>>> holding ~256+ rows and encountering row chaining effect in queries , and
>>> as this table is having ~20+ columns which holds mostly nulls , so we want
>>> to have those columns placed towards the last of the row, so that row
>>> chaining effect can be eliminated. For this reason, we first try to drop
>>> those ~20 columns and then add those again using 'alter table add' , so
>>> that they will be created towards the end of the row. Any better/faster
>>> approach through which we can achieve this?
>>>
>>> Regards
>>> Pap
>>>
>>

--
http://www.freelists.org/webpage/oracle-l