can u delete a datafile
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: can u delete a datafile

  1. #1
    Join Date
    May 2002
    Posts
    35
    how can one delete a single datafile from a database .. is it possible

    regards
    sharmila

  2. #2
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    Yeah.. Drop tablespace and remove the datafile using os command.

    Sanjay

  3. #3
    Join Date
    Jul 2002
    Location
    Lincolnshire, IL
    Posts
    203
    Sanjay...what will happen if my Tablespace have more then one Datafiles and I want to remove only one?
    Anyway...In that case
    1. Take tablespace offline
    2. alter database datafile datafile_name/Number offline drop
    3. Remove datafile using OS command.
    Hope it helps...
    Sandy
    "Greatest Rewards come only with Greatest Commitments!"

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    wrong

    you can never ever remove a datafile from a tablespacw without dropping the tablespace

  5. #5
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    Pando is right..

    The datafile metadata will still be in the dictionary.

    You could create a new tablespace with desired datafiles.
    Move objects to new tablespace.
    drop old tablespace.
    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  6. #6
    Join Date
    Jun 2002
    Posts
    65
    Lets say we create new Tablespace T1 with two datafiles (D1 and D2)...and now I want to transport all data from old tablespace to new one but only in D1want to keep D2 empty for later use...Thanks

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    If we put aside the fact that for this case it would be much more natural to create a new tablespace with a single datafile D1, and add the other datafile D2 only after you've moved all your data to D1, there are a couple of ways to force all transported data to reside only in D1:

    a) Resize D2 to such a small size that none of the moved objects will fit into it. After you've moved all your data to D1, resize D2 back to its original size.

    b) Lets say both of your files are of size 100M. Create a dummy table of size 100M (actually it will have to be a bit smaller, because few blocks are allready occupied by the file header), so that it fills the entire file. Check in which file it got created. If it is in D2 you are all set, if it was in D1 you'll have to create another dummy table that will fill datafile D2 and drop the first dummy table in file D1. Now move your data in the new tablespace, it will all go in D1. At the end, drop the dummy table residing in file D2.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    Originally posted by pando
    wrong

    you can never ever remove a datafile from a tablespacw without dropping the tablespace
    If you do, you are not far from sleepless nights (assuming you don't have right backup in place) . My co-worker was there couple of years ago... the cost of it was 36hours 2 DBAs effort and 200 Users sitting idle. Of course! it was Government Site . Who cares really !
    Reddy,Sam

  9. #9
    Join Date
    Aug 2002
    Location
    Atlanta
    Posts
    1,187
    specifically if you drop OFFLINE drop a datafile from a tablespace with more than one datafile FET$ will still show that there are available extents in the OFFLINE'd file. Next time it needs to allocate an extent to the TS it might try and allocate one to the offline'd file and whamo! end of story

    steve
    I'm stmontgo and I approve of this message

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