You Asked

Problem statement: Delete 40%(4TB out of 10TB) of unwanted data using Partitioning techniques from Oracle db(11g) and reclaim space.

Details:
My application DB schema size 10 TB. Oracle 11g and the schema is co located with many other schema's, hence any option with DB down time is allowed.
• Top tables based on size will start from 3 TB (Table_1), 2 TB(Table_2), so on.
• Planned to delete 40% of unwanted data from each table(with a where criteria).
• Delete scripts are practically not possible on huge table data, as it is causing DB sever hung AND impacting other schema services.

Below are not an options due to downtime constraints and No FS to create temp tables and switch back.
• Removing data by creating a table
• Switch the tables over(CTAS)

Strategy for deletion proposed:
 Partitioning (split partition / sub-portioning) of these tables based on data patterns(where clause) to separate.
 After separating purge eligible data, truncate it in back ground (during off business hrs). --> drop / truncate unwanted partitions.
 After truncating the partition, make the table un-partitioned back to original.

Reclaiming of space: best option to choose ?

1. alter database datafile xxx.dbf resize
2. alter tablespace xxx coalesce
3. export-import
4. dbms_redefinition
5. alter table xxx shrink

Any guidance on the proposed solution ??? –
 what partitions to be used for a pattern that has joins/where clause that includes 3 to 4 tables ?
 Pattern based partitioning examples and is that supported in 11g ?
 How to un-partition the main table back to original after truncating the unwanted partitions ?

and Connor said...

You haven't told us what partitioning you *currently* have for the tables. But from what I'm reading - it might be none?

But here is the big kiler - if you *really* have no space for temporary data (ie, secondary copies of data) then your options are going to be hugely limited because

- you can't use DataPump (no where to dump files to)
- you can't use CTAS (same reason - you need space for the old and new temporarily)
- you can't use DBMS_REDEFINITION (same reason)
- you can't convert from unpartitioned to partitioned (same reason)

So if you *really* are saying you have no free space...then you're going to be back to doing deletes.

Over the years my experience has been when management tells me "you can't have space" it is really a case of "we'd would PREFER not to use any more space".

I would present them with some benchmarks on:

- how long its going to take to run a delete of 4TB
- how much archived redo you'll probably generate as a result
- how much server impact it will have

and they might be more generous :-) You only need the space temporarily and if there is *really* no free space left, then I'd imagine your entire database infrastructure will be coming to a standstill soon, because surely the other schemas/databases are still growing?

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, November 30, 2020 - 6:55 pm UTC

HI Connor,

Thanks for the response, I am reading here exactly what i am doing :)

I benchmarking this activity with 150GB db dump in a dev env and halfway through it. I partitioned a huge table with date range using DBMS_REDEFINITION and plan is to extrapolate this prod volume(at least to get approximate time).

To answer your questions,

- Yes, none of the tables partitioned so far.

I have some open ended questions:

The Prod env is DAAS env(Data as a Service) co-located with other schemas.

Can we compare the impacts of dev env vs DAAS env ?
The redo activity and other server overheads shared from other schemas, how to separate that schema wise and show the overhead of this schema(xyz) with this activity.

And lets assume that i get FS temporarily for this activity. Hence can you please guide on below.

Reclaiming of space: best option to choose ?

1. alter database datafile xxx.dbf resize
2. alter tablespace xxx coalesce
3. export-import
4. dbms_redefinition
5. alter table xxx shrink

Any guidance on the proposed solution ??? –
 what partitions to be used for a pattern that has joins/where clause that includes 3 to 4 tables ?
 Pattern based partitioning examples and is that supported in 11g ?
 How to un-partition the main table back to original after truncating the unwanted partitions ?

Appreciate your response.

Thanks
Tiru

December 07, 2020 - 3:36 am UTC

The redo activity and other server overheads shared from other schemas, how to separate that schema wise and show the overhead of this schema(xyz) with this activity.

Check out v$segment_statistics for block changes, and v$sesstat for redo bytes as a ration of specific schema/session changes to the entire database. That will be a reasonable metric of how much overhead you are exhibiting.

If the additional space is indeed *temporary* then you'll probably need two exercises here

- one to move data to the temporary
- one to move it back

But in essence you could do something like this:

- starting point is table X which is (say) 5TB
- create new empty table Y as clone on X on temp storage but partitioned into 2 partitions
- partition p1 - the data you want to keep
- partition p2 - the data you want to throw away
- dbms_redef table X to Y
- drop partition p2
- drop table Y

Now X is a single partition containing the data you want. If you can keep that storage forever, then its just

- create empty table Z as clone of X
- exchange partition X (P1) with Z
- drop X
- rename Z to X

If you have to give that temp storage back, then instead of the above its

- create empty table Z on orig storage as non-partitioned clone of X
- dbms_redef X to Z

But assuming that this problem will one day rise again, why not just look at *keeping* the partitioning

More to Explore

Backup/Recovery

Check out the complete guide to all of the Backup & Recovery techniques in the Oracle Database.

Breadcrumb