Re: why undotbs01.dbf too big
Date: Tue, 26 Aug 2008 11:15:56 -0700 (PDT)
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"
> SYS.SM$TS_AVAIL A,
> SYS.SM$TS_USED B,
> SYS.SM$TS_FREE C
> WHERE A.TABLESPACE_NAME=B.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.
-- @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 LewisReceived on Tue Aug 26 2008 - 13:15:56 CDT