System tablespace 99% full a problem?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ora_lrnr@yahoo.com - 31 May 2005 12:23 GMT

Hello,

1) I am using Oracle 9i. I checked the tablespace usage in Enterprise
manager. The system tablespace is 99% full. Is this is
a problem? If it needs more space will it extend automatically?

When we create a tablespace we set the variable "MAXSIZE =<SOME
VALUE>" for a tablepace. Is it the MAX_EXTENTS column in
dba_tablespaces view?

07:05:02 SQL> select * from dba_tablespaces
07:07:37 2 where TABLESPACE_NAME = 'SYSTEM';
more...

TABLESPACE_NAME BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT
MIN_EXTENTS
------------------------------ ---------- -------------- -----------
-----------
MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS CONTENTS LOGGING FOR
EXTENT_MAN
----------- ------------ ---------- --------- --------- --------- ---
----------
ALLOCATIO PLU SEGMEN
--------- --- ------
SYSTEM 8192 65536
1
2147483645 65536 ONLINE PERMANENT LOGGING NO
LOCAL
SYSTEM NO MANUAL

2) UNDO tablespace is also 98% full. Is it OK? No transaction is
running against it now.

Orlando.

Reply to this Message

tina london - 31 May 2005 13:16 GMT

> Hello,
>
[quoted text clipped - 30 lines]
>
> Orlando.

check the data-dictionary to see if autoextend is on for system tablespace.
see docn for further details.

Reply to this Message

Joel Garry - 01 Jun 2005 00:22 GMT

Regarding the undo tablespace: If you've got autoextend on and a large
undo_retention, Oracle is much more profligate with it's rollback
usage. So if you have a maximum amount of extension (which you should
if you have autoextend!) you will reach it much sooner than expected,
and Oracle will barf with "Failure to extend rollback segment because
of 30036 condition."

So don't use autoextend with a large undo_retention. Normally, Oracle
will attempt to free up undo that is merely kept for retention, but
won't if you have these settings.

Besides that, your undo_retention will determine how long until your
undo ts will be less full. So if you are trying to shrink your undo,
sometimes you just have to wait the retention time. Other times
there's no way, and you have to simply recreate your undo ts (create a
new one, alter system to use it, drop old one).

See metalink notes 268870.1 and 247184.1.

It is normal for system to be autoextend and just about full, so be
real careful not to put user objects there, especially watch user
default tablespaces and exp/imp.

jg
--
@home.com is bogus.
Dude!
http://www.signonsandiego.com/uniontrib/20050531/news_1n31velzy.html

Reply to this Message