Re: Remove datafile

You cannot drop a datafile. You must drop the tablespace and recreate it without that particular datafile. Export your data, drop the tablespace, recreate the tablespace minus that file, and reimport your data.

In article <36c106f3_at_news1.us.ibm.net>,   "Alianto" <alianto_at_mincom.co.id> wrote:
> Dear All,
>
> I have tried to remove a datafile in a tablespace, but it didn't come out. I
> have shutdown dan startup the instance, but it looks same as before.
> And here the statements that I capture from sqldba :
>
> SQLDBA> select * from v$datafile where status='RECOVER';
> FILE# STATUS ENABLED CHECKPOINT BYTES NAME
> ---------- ------- ---------- ---------- ---------- ------------------------
> ----
> 47 RECOVER READ WRITE 0 415236096 MISSING0047
> 48 RECOVER READ WRITE 10109621 4096
> /data4/oracle/idxmsf/idxmsf902b.dbf
> 51 RECOVER READ WRITE 10497500 37748736
> /data5/oracle/tabmsf/tabmsf1xxa.dbf
> 3 rows selected.
>
> SQLDBA> alter database datafile '/data5/oracle/tabmsf/tabmsf1xxa.dbf'
> offline drop;
> Statement processed.
>
> SQLDBA> commit;
> Statement processed.
>
> SQLDBA> select * from v$datafile where status='RECOVER';
> FILE# STATUS ENABLED CHECKPOINT BYTES NAME
> ---------- ------- ---------- ---------- ---------- ------------------------
> ----
> 47 RECOVER READ WRITE 0 415236096 MISSING0047
> 48 RECOVER READ WRITE 10109621 4096
> /data4/oracle/idxmsf/idxmsf902b.dbf
> 51 RECOVER READ WRITE 10497500 37748736
> /data5/oracle/tabmsf/tabmsf1xxa.dbf
> 3 rows selected.
> SQLDBA>
>
> Comment are welcome.
>
> Regards,
> Alianto
> alianto_at_mincom.co.id
>
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Feb 10 1999 - 12:31:45 CST