Re: Help: unable to delete a tablespace

On 12 Feb 1995, Lee WN wrote:

> I got the following error message from sqldba when deleting a tablespace.
> The tablespace is 120MB, the SYSTEM, ROLLBACK-SEGMENT, TEMP and other
> default tablespaces are unchange since installed.
>
> ORA-00604: error occured at recursive SQL Level 1
> ORA-01562: failed to extend rollback segment (id=2)
> ORA-01547: failed to allocate extent of size 65 in tablespace 'RBS'
>
> Please help. Thanks in advance.
>
> Regards,
> Lee WN : leewn_at_oclmal.po.my
>
>

First of all, you don't have enough space for rollback segment to store the rollback data. When you drop a tablespace, oracle will also store the rollback statment inorder to recover the tablespace upon drop tablespace failed. So you need to extend your tablespace which the rollback segment stored in(which is SYSTEM on DEFAULT). Then drop the tablespace again. On the other hand, it is a good pratice to check that no any data is stored in the tablespace before you drop it. It can reduce the chance of extend rollback segments. (that is, export/move the data from the tablespace, then drop all objects in that tablespace and commit it)

      __       _        | Yick Ka Yuen, Henry | City Univserity of Hong Kong
 |_| |_  |\ | |_| \ /   | Email : 92908250_at_cpccux1.cityu.edu.hk
 | | |__ | \| | \  | .  | Computer Studies, Year 3/4 
-------------------------------------------------------------------------------