Re: Shrink table space strategy

There is a limitation on "move including" - you can't use it at the PARENT end of referential integrity (though it's valid at the child end).

ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

Regards
Jonathan Lewis

On Mon, 3 Jan 2022 at 12:48, Eriovaldo Andrietta <ecandrietta_at_gmail.com> wrote:

> 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