Re: Column drop issue

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