Alter Tablespace Add Datafile Reuse Failing

[sabita_ban]

Hi,

I have a tablespace named ORGSDATA.

I was getting the following errors ORA-01653 as a result of which I decided to add a datafile.The existing data file is 'DATA02ORGS.DBF of size 100M

I tried using

ALTER TABLESPACE ORGSDATA ADD DATAFILE 'F:\\ORGS\\DBFILES\DATA02ORGS.DBF' SIZE 100M REUSE AUTOEXTEND ON NEXT 10K MAXSIZE UNLIMITED

But got the following errors

ORA-01537: cannot add data file 'F:\\ORGS\\DBFILES\DATA02ORGS.DBF' - file already part of database

Can't I reuse the same existing Datafile instead of adding a new one?
What then is the use of the REUSE clause?

I use Oracle7 in Win NT


[Edited by sabita_ban on 02-18-2002 at 04:12 AM]

[gnagesh]

Try This:

ALTER DATABASE DATAFILE 'F:\\ORGS\\DBFILES\DATA02ORGS.DBF' RESIZE 100M

[sabita_ban]

gnagesh,
Thanks for the answer.
RESIZE is ok.

But suppose I created the original Datafile as

ALTER TABLESPACE ORGSDATA ADD DATAFILE 'F:\\ORGS\\DBFILES\DATA02ORGS.DBF' SIZE 100M

Now I am trying to add a new datafile using the options
AUTOEXTEND ON NEXT 10K MAXSIZE UNLIMITED

In such a case if I want to change the existing datafile to satisfy my needs...can I do it?Or in this case I HAVE to add a fresh datafile.
Further what is the usefulness of REUSE clause?

[marist89]

alter database datafile 'F:\\ORGS\\DBFILES\DATA02ORGS.DBF' autoextend on next 10M maxsize 2048M;

[marist89]

The reason you couldn't 'reuse' the file is because it already exists as part of the database. You would have first had to drop your tablespace (which you probably wouldn't want to do because it sounds like you have data in there), and then re-create your database reusing your file.

[saurabhg29]

use the following

ALTER DATABASE
DATAFILE 'C:\ORACLE\ORADATA\TEST\TEST2.DBF' AUTOEXTEND
ON NEXT 124K MAXSIZE 124K

[sabita_ban]

Thanks Jeff and Saurabh,

This was what I actually needed....