Oracle Server - System tablespace 99% full a problem?

Author

System tablespace 99% full a problem?

ora_lrnr@yahoo.com

2005-05-31, 7:23 am

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.

tina london

2005-05-31, 7:23 am

<ora_lrnr@yahoo.com> wrote in message
news:1117538603.478129.68080@o13g2000cwo.googlegroups.com...
> 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.
>

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

Joel Garry

2005-05-31, 8:23 pm

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/union..._1n31velzy.html

Sponsored Links