My database version is 18.104.22.168
undo_retention parameter is set to 10800(sec) 3 hrs(as default value)
I have observed the undo tablespace size has been going on increasing.. I have never seen it getting down.. Currently its datafile size is 19.4GB
Still error like the following is coming and the metalink suggests that undo retention parameter be increased.
(ORA-12008: error in materialized view refresh path
ORA-01555: snapshot too old: rollback segment number 2 with name "RBS2" too small
ORA-02063: preceding line from MIS_LINK)
How do I manage the Tablespace size . It may fall short of the filesystem size someday!
down on the cube farm, left then right then another left
My answer is use the Oracle Managed Files (It might be called Oracle Managed segments) and do not manage it. I do not have much personal experience yet but I have it from 2 production DBAs that OMF (as I call it) is the way to go. Check out http://download-west.oracle.com/docs...1/omf.htm#1656
Hopefully a couple of things to try to help you out...
First, your datafile for your UNDO tablespace is set to automatically extend. While it is set this way, your datafile will continue to expand whenever it is needed to accomodate the need for more undo space. Try: ALTER DATABASE DATAFILE '...\file.dbf' AUTOEXTEND OFF;
Second, once a datafile expands to claim disk space, it will not let it go unless you resize it: ALTER DATABASE DATAFILE '...\file.dbf' RESIZE 1024M; However, to shrink a file down in size, data must not be present in the portion of the file you lose because of the file shrinking. Usually with undo tablespaces, I cannot resize them during the day; best time is at database start up when few, if any, transactions have been issued.
Finally, the undo_retention parameter tells the database to allow a transaction to retain undo information for up to (in your case) 3 hours. My guess is that your materialized view is taking more than 3 hours to refresh, correct? If so, you need to (like Metalink suggests) increase the parameter to accomodate the long running query. Also, if not necessary, instead of running a COMPLETE refresh use the FAST refresh.
Hey Thanks JoeB!
However I still have some queries.
1.When UNDO Management has been set to AUTO , I do not expect rollback segment's name to be RBS2. Where is it coming from?
Is it because the master site is Oracle8i and their teh rollback segments are managed in the conventional way. But when the snapshots are on 9i site the rollback segments used should be those at 9i site only.
SQL> SELECT SEGMENT_NAME FROM DBA_ROLLBACK_SEGS;
2.If I put AUTOEXTEND Off, will it not yield errors for lack of undo space
even though space is available in the filesystem(which I would not prefer) The idea of having that ON is to let the available space get used
when required but I thought it would get reclaimed when doen with.