OraFAQ Forum: Server Administration » undo tablespace error while executing Alter statement

Home » RDBMS Server » Server Administration » undo tablespace error while executing Alter statement

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

undo tablespace error while executing Alter statement [message #141336]

Sun, 09 October 2005 05:49

sameern
Messages: 15
Registered: March 2005
Location: andheri

Junior Member

Hi,

I am facing a serious issue in our production database.
While executing the following Alter statement

alter table ORDER_HIST modify ( HIST_DESC NVARCHAR2(1500) )

The existing HIST_DESC column is of VARCHAR2(500). It is to be modified to NVARCHAR2(1500).
The table is having about 100 million records. While executing this query got the following error

ERROR at line 1:
ORA-30036: unable to extend segment by in undo tablespace '' .

Please let me know
1)why such an error should occur in Alter command and
2) what is the resolution for this issue

Thanks in advance

Report message to a moderator

Re: undo tablespace error while executing Alter statement [message #141387 is a reply to message #141336]

Mon, 10 October 2005 00:21

tarundua
Messages: 1080
Registered: June 2005
Location: India

Senior Member

--> 1)why such an error should occur in Alter command

As you said your table contains millions of records so when you alter the datatype it will log that change information in the undo segment ( others correct me if wrong) because if at any point of time you cancel this command then Database should regain its original structure.

-->2) what is the resolution for this issue
From the error you mentioned it seems that AUTOEXTEND for undo tablespace is OFF , turn it ON. You can check it through following command


 SQL> select file_id, file_name, autoextensible from dba_data_files;

so turn it on using the following command

 SQL> Alter database file_id autoextend ON;

Report message to a moderator

Previous Topic:	Compact DB size after delete data
Next Topic:	regardin /as sysdba login

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jan 10 10:48:43 CST 2025