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