Re: Shrink table space strategy

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