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]
ALTER DATABASE DATAFILE 'F:\\ORGS\\DBFILES\DATA02ORGS.DBF' RESIZE 100M
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?
alter database datafile 'F:\\ORGS\\DBFILES\DATA02ORGS.DBF' autoextend on next 10M maxsize 2048M;
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.
use the following
DATAFILE 'C:\ORACLE\ORADATA\TEST\TEST2.DBF' AUTOEXTEND
ON NEXT 124K MAXSIZE 124K
Thanks Jeff and Saurabh,
This was what I actually needed....