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@xxxxxxxxx>:
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@xxxxxxxxx>
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
