Re: Shrink table space strategy

Hi Eriovaldo,

Probably it would be better to use a new "including rows" clause for alter table, like:
ALTER TABLE ... MOVE ONLINE INCLUDING ROWS WHERE <predicates filtering which rows you want to keep>

Best regards,
Sayan Malakshinov
Oracle performance tuning expert
Oracle Database Developer Choice Award winner Oracle ACE
http://orasql.org

пн, 3 янв. 2022 г., 5:34 Vishnu <vishnukumarmp_at_gmail.com>:

> You may consider expdp with query option to filter records and import them
> back..? This will require some downtime ...
>
> On Sun, Jan 2, 2022 at 8:12 PM Eriovaldo Andrietta <ecandrietta_at_gmail.com>
> wrote:
>
>> Hello,
>>
>> I have big tables with millions of lines in the production environment ...
>> We have a request to delete old data in these tables. The filter will be
>> defined by the business rules, considering data and some status ...
>> Partition is an option but not at this moment. Business rules are
>> complex to define the best partition and tables are too many (+-150).
>>
>> Considering that shrink table causes lock in the table, what is the best
>> strategy :
>>
>> a.) delete a lot of lines, rebuild indexes (the maximum is possible) and
>> execute shrink a few times
>> b.) delete few lines and execute shrink many times and rebuild indexes
>> few times.
>> c.) are there other ways to do it ???
>>
>>
>> I did not find how to execute the shrink online without lock.
>>
>> Regards
>> Eriovaldo
>>
>>
>>

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