RE: Shrink table space strategy

One other thing comes to mind. IF this is a business decision to make old data unavailable and NOT a near term space consideration, then you could make the older data disappear with a covering view and the appropriate renames.  

Now why might you do that? With the views in place, you could then take your time, most important table first (where most important is defined by your evaluation of your query requirements), and partition those objects, probably oldest keepers first which likely avoids contention until the last few time periods. So you copy a period at a time into the new partitioned version of the table and then update the covering view to filter out the completely copied oldest not-too-old period from the current base table and connect it via union all to the new partitioned table. Eventually all the table you want to keep is in the new period partitioned table, with which name you replace the covering view.  

This can be done gradually, possibly with a bit of short outages for data of time periods that are not completely quiescent by business rule. But the date range of that is probably small enough for a copy out to be quite fast.  

This overall strategy WILL consume more data space along the way. It will quickly make the old data inaccessible and you won’t incur the delete overhead of anything. At the end of the game you can optionally archive the original, but you should ask if the point is to permanently make no longer legally required data actually unavailable. IF THAT IS THE CASE, then you need to clean up (destroy) all your archived old data and paper copies. This is sometimes an actual requirement.  

A conversation of the business reasons (if any) for getting rid of the old data is likely very useful. Space savings is a very different issue from information management. A likely suspect is the CFO or his pet actuary or legal representative (of both, in a meeting).  

If ask these questions and they say “Don’t y’all worry your purdy head about those things above your pay grade” that will still likely put you on their minds that you care about what they need in addition to what they directly ask for. Or they may immediately bring you into the business requirements conversation. Likely that is a career enhancing moment. Remember to listen carefully and ask if anything they say sounds like a foreign language.  

One last thing: It’s entirely possible the interim version with a cover view is noticeably or unacceptably unperformant. Or it could be just fine.  

Good luck, I think others have laid out excellent advice on technical deletion.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis Sent: Monday, January 03, 2022 8:18 AM
To: ORACLE-L
Subject: 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