Re: Column drop issue

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