Home » RDBMS Server » Server Administration » tablespace UNDOTBS1 almost full, Any help
tablespace UNDOTBS1 almost full, Any help [message #218667] Fri, 09 February 2007 04:16 Go to next message
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.
Re: tablespace UNDOTBS1 almost full, Any help [message #218730 is a reply to message #218667] Fri, 09 February 2007 10:24 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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.
Re: tablespace UNDOTBS1 almost full, Any help [message #218887 is a reply to message #218730] Sun, 11 February 2007 22:42 Go to previous messageGo to next message
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.
Re: tablespace UNDOTBS1 almost full, Any help [message #218959 is a reply to message #218887] Mon, 12 February 2007 04:28 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
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.
Re: tablespace UNDOTBS1 almost full, Any help [message #219004 is a reply to message #218959] Mon, 12 February 2007 09:07 Go to previous message
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;
Previous Topic: INDEX Servers ...
Next Topic: Why is one index unused?
Goto Forum:
  


Current Time: Mon Dec 05 06:47:20 CST 2016

Total time taken to generate the page: 0.13095 seconds