Re: Ran out of space adding a file to a tablespace?

If the data file never actually got created, it should not be part of the database, at least it should not have any data in it, so you can take the data file offline, and bring up the database.

Start by mounting the database STARTUP MOUNT and then ALTER DATABASE DATAFILE '....' OFFLINE; After the database is up, check that there are no EXTENTS in datafile 17 with;
SELECT * from DBA_EXTENTS where FILE_ID = 17;

Then you should be able to drop the datafile OK.

Andrew

jrb_6502_at_my-dejanews.com wrote:

> Hello,
>
> During a normal import process over the weekend, a table exceeded the
> space allocated its logical tablespace. A new file was added to the
> tablespace, but in the process of adding that file, the drive on which
> that file was to live ran out of room.
>
> Right now, the instance won't come up. Attempting to start it in svrmgrl
> leads to:
>
> SVRMGR> startup
> ORACLE instance started.
> Total System Global Area 18722064 bytes
> Fixed Size 48400 bytes
> Variable Size 11948032 bytes
> Database Buffers 6553600 bytes
> Redo Buffers 172032 bytes
> Database mounted.
> ORA-01122: database file 17 failed verification check
> ORA-01110: data file 17: '/u2/oracle/ts/big_ts_9'
> ORA-01205: not a data file - type number in header is 0
>
> The recommended step at this point is to recover a valid copy of that
> file. However, no valid copy exists, because the file was never made
> correctly in the first place.
>
> Any recommendations?
>
> --== Sent via Deja.com http://www.deja.com/ ==--
> ---Share what you know. Learn what you don't.---
Received on Mon May 24 1999 - 19:19:43 CDT