OraFAQ Forum: Server Administration » tablespace UNDOTBS1 almost full, Any help

Home » RDBMS Server » Server Administration » tablespace UNDOTBS1 almost full, Any help

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

tablespace UNDOTBS1 almost full, Any help [message #218667]

Fri, 09 February 2007 04:16

simcheetong
Messages: 3
Registered: February 2007

Junior Member

Hi.. when I enter this following SQLplus command, I found my tablespace UNDOTBS1 is almost full, it left 1Mb only. Any cause of concern ? What can we do to free up the space ? Please help me.

SQL> select tablespace_name, sum(bytes)/1024/1024 from dba_free_space group by tablespace_name;

TABLESPACE_NAME SUM(BYTES)/1024/1024
------------------------------ --------------------
IDX_02 23.9375
SYSAUX 87.125
SYSTEM 7.8125
UNDOTBS1 1.4375
UNDOTBS2 397.6875
USERS 364
XML_IDX_01 210.9375
XML_TBS_01 29232.875

8 rows selected.

Report message to a moderator

Re: tablespace UNDOTBS1 almost full, Any help [message #218730 is a reply to message #218667]

Fri, 09 February 2007 10:24

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>>Any cause of concern ?
May be. May not be.
UNDO_RETENTION is what that matters here. Search this forum/documenation for importance of UNDO_RETENTION.

>> What can we do to free up the space ?

UNDO will get re-used. A 99% utilization is not essentially bad.
But if a long running transaction needs more UNDO segments and if it cannot reuse existing space, you will get error.
In that case, you need to Increase UNDO Tablepsace. But again, all depends on UNDO_RETENTION.

Report message to a moderator

Re: tablespace UNDOTBS1 almost full, Any help [message #218887 is a reply to message #218730]

Sun, 11 February 2007 22:42

simcheetong
Messages: 3
Registered: February 2007

Junior Member

The UNDO Retention is set to default 900sec. Does it mean that i have a chance to get error?

What is the command to get what is the size of the UNDOTBS1, Free space of UNDOTBS1 is 1.43Mb

By the way, the free space of system tablespace is only 7.8Mb, any cause of concern? How can we increase more free space for it or do we need to increase the tablespace. (By the way, it is set to auto extend the tablespace.

Report message to a moderator

Re: tablespace UNDOTBS1 almost full, Any help [message #218959 is a reply to message #218887]

Mon, 12 February 2007 04:28

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

>> set to default 900sec. Does it mean that i have a chance to get error
Depends on your application. If your UNDO SEGMENTS needs to hold a long running transaction more than 900 seconds, you may error out.
>>What is the command to get what is the size of the UNDOTBS1, Free space of UNDOTBS1 is 1.43Mb
You may not want to 'directly' free the undo space.
Just add more or
create a new one, assign it and drop the old.
It is all explained in docset.
http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96521/undo.htm#9097

>> the free space of system tablespace is only 7.8Mb
I would not have autoextend on for system tablespace.
I would check whether anyother user/regular schema is using SYSTEM tablespace as default tablespace or creating objects in it.
IF you have enabled auditing and if the information is stored in system tablespace, it may also grow.

Report message to a moderator

Re: tablespace UNDOTBS1 almost full, Any help [message #219004 is a reply to message #218959]

Mon, 12 February 2007 09:07

clioteux
Messages: 5
Registered: February 2007
Location: algiers

Junior Member

Hi,
check if the files are autoextensibles; if so, There will be no problems generated; Oracle will extend the files if necessary.

Select file_name, autoextensible from dba_data_files;

To check the autoextensible property of the datafiles.

And then :
Alter database datafile file_name autoextend on next n[k|m] maxsize [unlimited|n[k|m]];

SY;

Report message to a moderator

Previous Topic:	INDEX Servers ...
Next Topic:	Why is one index unused?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 05:48:02 CDT 2024