Alter Tablespace Add Datafile Reuse Failing
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Alter Tablespace Add Datafile Reuse Failing

  1. #1
    Join Date
    Jan 2002
    Posts
    65
    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]
    Sabitabrata

  2. #2
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    323

    Try This:

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

    Nagesh

  3. #3
    Join Date
    Jan 2002
    Posts
    65
    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?



    Sabitabrata

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    alter database datafile 'F:\\ORGS\\DBFILES\DATA02ORGS.DBF' autoextend on next 10M maxsize 2048M;
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Sep 2001
    Posts
    120
    use the following

    ALTER DATABASE
    DATAFILE 'C:\ORACLE\ORADATA\TEST\TEST2.DBF' AUTOEXTEND
    ON NEXT 124K MAXSIZE 124K
    Saurabh Garg
    OCP 9i

  7. #7
    Join Date
    Jan 2002
    Posts
    65
    Thanks Jeff and Saurabh,

    This was what I actually needed....
    Sabitabrata

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width