why undotbs01.dbf too big

On Aug 26, 5:37*am, martin_ian_le...@yahoo.com wrote:

>
> I get it now! well the situation in respect of undo tablespace anyway.
> read-consistency and that.
> Thanks very much for your help!!
> I ran a script that I googled - gave some interesting results - seems
> I have fixed the undo tbs problemette!
>
> [oracle-at-chrome ~]$ cat tbsusage.sql
> SELECT A.TABLESPACE_NAME,
> A.BYTES TOTAL,
> B.BYTES USED,
> C.BYTES FREE,
> (B.BYTES*100)/A.BYTES "% USED",
> (C.BYTES*100)/A.BYTES "% FREE"
> FROM
> SYS.SM$TS_AVAIL A,
> SYS.SM$TS_USED B,
> SYS.SM$TS_FREE C
> WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME
> AND
> A.TABLESPACE_NAME=C.TABLESPACE_NAME;
> ...
> ..
>
> TABLESPACE_NAME * * *TOTAL * * * USED * * * FREE * * % USED * % FREE
> SYSTEM * * * * * * * * * * *513802240 *509542400 * *4194304
> 99.1709184 * * .816326531
> USERS * * * * * * * * * * * * * * 5242880 ** 393216 * *4784128
> 7.5 * * * *91.25
> MYREP * * * * * * * * * * * 2.3803E+10 2.3789E+10 * 13697024
> 99.9421806 * * *.057544053
> SYSAUX * * * * * * * * * * * * *429916160 *417071104 * 12779520
> 97.0121951 2.97256098
> UNDOTBS1 * * * * * * * * * * * *524288000 * *8585216 *515637248
> 1.6375 * *98.35
>
> So the undotablespace is looking real healthy.
> The system, myrep and even sysaux look a bit suspect I think?
> Any obvious action points?

I would guess those "suspect" tablespaces are probably just set to
autoextend. Some people think you shouldn't do that with user data.
Personally, I set data files in user tablespaces to a fixed size,
except for the most recent which I set to autoextend with a max at
that size. In 10G, there are monitoring alerts you can set to however
you desire, I just still have old habits.

As far as googling scripts, see http://www.jlcomp.demon.co.uk/kiddy_scripts..html
(and for better scripts, see
http://www.jlcomp.demon.co.uk/ind_misc.html ).

jg
--
@home.com is bogus.
"If you’re trying to do trouble-shooting, kicking the database to
death is not a good way to go about it." - Jonathan Lewis