finding modified tables/partitions
As part of our backup/recovery process we don a RMAN backup along with
a full export of a 4.5TB database. To limit what is pumped out I would
like to limit my export to only those tables/partitons, which have changed
for the current day or maybe the day before.
By change, I mean any inserts,updates or deletes.....
Does anybody know of such a query that will give me this information,
which I can use to dynamically build a parfile through a shell script than
Any examples would be greatly appreciated.
Thanks to all who answer.
Do you have a last_update_date column in those tables? If so, you can filter on those. Otherwise, no (with respect to Data Pump, and an export is not a backup). What would be exported for deletes?
No I dont have an update column on alot of these tables.
What I was looking for was a last modified time in the data dictionary for each table and or partition...
Just out curiosity, how does a RMAN incremental backup keep track of the
changes.... there must be some logic to figure out what blocks have changed
there is no option to find last modified DML time. RMAN tracks this using block change tracking option which needs to enable explicitly.
Try hard to get what you like OR you will be forced to like what you get.
Out of curiosity... may I ask what's the purpose of taking a full export of a 4.5TB database when you already have in place an RMAN based backup/recover strategy?
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Click Here to Expand Forum to Full Width