im a newbie.
We have a user tablespace say 'abc' for eg.
One of my task is to monitor the tablespace available in the database.
Tablespace abc size is 10 gb and free space left is 1gb.
For the past one month the free space remains same but suddenly freespace size got decreased to 400mb and after a day again it back to 1gb.
My manager asked me to find the reason for this sudden decrease in size for the abc tablespace...
I thought user might have added some data to the tablespace and dropped it later.
so if this is the reason can i able to find which table got updated.
Thanks in Advance
I am always surprised when someone suggests log miner for this sort of problem. Analyzing many gigabytes of redo information is often impossible. Just restoring the relevant archivelogs and populating the v$logmnr_contents view will be a huge task. If you can do it at all. Then, how do you intend to identify the statements of interest?
And what about DDL? In this case, almost certainly objects have been dropped, so without a previously extracted Log Miner dictionary you can never decode the redo.
I can only assume that many of the people who recommend using Log Miner have never tried to use it. I find that it is useful only if you know what you are looking for, and when it happened.
How are you monitoring the space? There's always the possibility your method gave misleading results for some reason. For example, you happened to check while someone was rebuilding an index or similar task that uses temporary segments, or your method includes recyclebin space that gets reclaimed.