Re: why undotbs01.dbf too big

On Aug 26, 5:37 am, martin_ian_le..._at_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