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