Re: Shrink table space strategy

HI,

1.) The oracle version is 12.2
 the ALTER TABLE ... MOVE ONLINE INCLUDING ROWS WHERE <predicates filtering which rows you want to keep> is a good approach. I will check it.

2.) About the integrity : the script that will delete data is organized to respect the FK , deferred and guarantee the referential integrity of the data on commit.
Delete will be applied only for old data .. too old...

3.) The volume is actually an important. I think that it is the item that defines the strategy. In this case, I can use one or more strategy during all "cleaned task".

Thanks for all the answers.
Eriovaldo

Em seg., 3 de jan. de 2022 às 06:14, Jonathan Lewis <jlewisoracle_at_gmail.com> escreveu:

>
> Since you mention partitioning I assume you are running enterprise edition.
> You haven't mentioned a version - but if you're on 12.2 or above then
> Sayan's suggestion is an obvious first choice - though it has limitations
> (you haven't said anything about referential integrity) , see:
> https://jonathanlewis.wordpress.com/2020/07/12/massive-deletes/
>
> A significant problem with any strategy that has to take place while the
> system is active is that online deletes. copies, index updates generate a
> lot of undo and redo, and a lot of I/O, so you need to think about the type
> and volume of I/O that might take for each table in turn and maybe do one
> table with its indexes at a time.
>
> Regards
> Jonathan Lewis
>
>
>
> On Mon, 3 Jan 2022 at 02:11, 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