Oracle Database Discussions

Hi All

On my 10.2.0.3 RAC database I am observing a behaviour contradicting my understanding off undo tablespaces. two undo tablespaces (1 per instance) were created when the RAC database was set-up using dbca. I accepted default parameters expecting the undo tablespace to grow by themselves which they did I believ.. none of my queries ever failed until now. Once after the RAC database creation, I've built in objects (import from another database) and have started running loads on the system continuously. I did not take a close look on the undo utilisation but they never grew to bigger sizes either.. because, every day as an excercise I would run the below query and trim all the datafiles which ever have larger space so tht all datafiles occupy about 90% of the size atleast.

select 'alter database datafile ''' || file_name || ''' resize ' ||
ceil( (nvl(hwm,1)*8192)/1024/1024 ) || 'm;' cmd from dba_data_files a,
( select file_id, max(block_id+blocks-1) hwm from dba_extents
group by file_id ) b
where a.file_id = b.file_id(+)
and ceil( blocks*8192/1024/1024) -
ceil( (nvl(hwm,1)*8192)/1024/1024 ) > 10

Now the issue is suddenly i notice that the asm diskgroup which is catering to the datafiles is getting filled up. it grew from 70GB to 90 GB in no time.. when I verify the undo tablespace on second instance UNDOTBS2 is 16GB in size..I am worried if I trim the size to a smaller size and if loads need more undo space would this tablespace grow automatically because I've the Auto-Extensible option turned ON on this one?

From the undo advisor, I see that about 5 GB of undo space is being used. with automatic undo management and undo retention of 15 minutes, I am totally confused that how could the undo tablespace grow so huge and remain so huge ever.. should it not shrink automatically when so much of space is no longer needed?

I also have a doubt on the query. I've been running the above query almost once each day and never did the undotbs2.dbf got listed.. although it is certain that not all 15gb is being used... also when I notice, there a set of other datafiles, which are at 60% utilisation. for example.. a datafile +datadg/test/sarat.dbf of total size 1200 MB off which the EM console shows 700MB as used. but the above query return it with a resize clause 1100MB .. only 100mb is trimmed..

alter database datafile '+datadg/test/sarat.dbf' resize 1100m;

what should I do now to reclaim the unused space? trimming the datafiles is not doing what I thought it would.. could the undo table space be resized to say 5 gb so that if any processing need more than 5gb would stil be able to extend it?

Please advice....my sincere apologies for the long post but I was intending to give as much info as I could
Thank you.
Sarat