Re: help with drop tablespace

On 05 Nov 1999 17:35:17 PST, Swahanaga <sfinkelman_at_cncdsl.com> wrote:

>When I used the host (I presume you mean unix) command to delete (rename) the
>file,
>oracle broke and had a problem starting up. Is there someway to mark an existing
>file for reuse?
>
>
>
>Sybrand Bakker wrote:
>
>> Files are never deleted by Oracle.
>> There are two ways to resolve this:
>> either use a host command to delete the file
>> or (provided the size of the file remains unaltered) add the reuse clause to
>> the file specification
>>
>> Hth,
>>
>> --
>> Sybrand Bakker, Oracle DBA
>> Swahanaga <sfinkelman_at_cncdsl.com> wrote in message
>> news:3823520F.CF5E1EC2_at_cncdsl.com...
>> > Hi,
>> > I'm trying to create and drop tablespaces I have a simple script
>> > which chokes on the create tablespace stage
>> >
>> > With the error:
>> > 0ra-01119: error in creating database file '/u02/.. .. ../stage
>> > ora-27038: skgfrcre: file exists.
>> >
>> > This seems to mean that the definition of the tablespace is gone, but
>> > the physical file wasn't removed..
>> > When I manually kill the file, the create works.
>> >
>> > Can someone please tell me what is actually taking place?
>> > Is this an oracle grant issue or a unix rights issue or something else?
>> >
>> > tia--------- Steve Finkelman
>> >
>> > alter tablespace stage offline;
>> > drop tablespace stage including contents cascade constraints;
>> >
>> > create tablespace stage
>> > datafile '/u02/ora/oradata/ideas/stage.dbf' size 1M
>> > default storage(
>> > initial 10k
>> > next 10k
>> > maxextents UNLIMITED
>> > pctincrease 0 );
>> >
>> > alter tablespace stage permanent ;
>> > alter tablespace stage online ;
>> >
>> > Wh
>> >
>> >
>> >
>

No there isn't. What is the exact error message you are getting? If you have succesfully removed the tablespace, you should be able to delete the file. Most likely only the O/S owner of the database has the privilege to delete the file.
If the file has the same size, there's no need to delete it. Just append reuse after the size spec.

Hth,

Sybrand Bakker, Oracle DBA Received on Sat Nov 06 1999 - 02:03:40 CST