driopping datafiles

[fraze]

Hi ,

I've dropped a datafile using
alter database datafile 'XXXXXXX' offline drop;

but now the datafile is offline but is still in the catalog. I cant bring it online cause it needs recovery and I'm in NOARCHIVELOG mode. How to I drop it out of the catalog?

Running 8.1.7.3 on AIX 4.3.3

[nekelly]

Hello Fraze,

is the datafile you dropped the only member of its tablespace?

I'm pretty sure you'll have to drop the tablespace now.

Regards,
Noel.

[tycho]

Hi,

You cann't get rid of it with out dropping the tablespace.

The procedure would be.

For a tablespace with tables in it:
- do an export containg all the tables
- drop and recreate the tablespace
- do an import

For a tablespace with indexes in it:
- get the create statements for all the indexes in the tablespace
- drop and recreate the tablespace
- create the indexes

HTH

Tycho

[fraze]

Thanx,

Forgot that once you create a datafile in cant be dropped by itself. Have recovered it and brought it online - guess I'll just live with it for now!

[tycho]

Hi,

Can you send in the output of the following query?

select *
from v$RECOVER_FILE;

Tycho