Topic
  • No replies
SystemAdmin
SystemAdmin
6902 Posts

Pinned topic I am unable to reclaim the space after removing datafiles

‏2012-11-30T05:11:11Z |
Dear All.

I am unable to reclaim the space after removing datafiles.

Scenario:

One of my mount point reached full due to undotablespace (undotbs2) growth, so I switched new one (undotbs3) and removed the old one.
Problem 1.
Unable to reclaim the space after removing the undotbs2.
Problem 2.
The switched mount also become full..:(

The steps which I followed for switching the undo:

BANNER


Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.3.0 - Productio
NLSRTL Version 10.2.0.3.0 - Production

SQL> show parameter undo
NAME TYPE VALUE




undo_management string AUTO
undo_retention integer 32400
undo_tablespace string UNDOTBS2

SQL> select BYTES/1024/1024,FILE_NAME from dba_data_files where TABLESPACE_NAME='UNDOTBS2';
BYTES/1024/1024


FILE_NAME


45688.125
/u04/oradata/RADPRDMH/undotbs02b.dbf
6268
/u04/oradata/RADPRDMH/undotbs02a.dbf
CREATE UNDO TABLESPACE undotbs3
DATAFILE '/u05/oradata/RADPRDMH/undotbs03a.dbf'
SIZE 5000M AUTOEXTEND ON;
ALTER SYSTEM SET UNDO_TABLESPACE=undotbs3 SCOPE=BOTH;
DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES;