DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13
  1. #11
    Join Date
    Sep 2000
    Chennai, India
    Originally posted by pando
    you CANNOT drop a datafile from a tablespace

    True Boss !

    Once you make a datafile part of a tablespace, the datafile CANNOT be removed, although we can use some workarounds.

  2. #12
    Join Date
    Apr 2001
    Louisville KY
    Try this:

    1- Alter Tablespace Read Only;
    2-Alter Tablespace offline (don't drop it);
    3- at the os level, rename the data file;
    4- alter database rename datafile 'old name' to 'new name';
    5- alter tablespace online;
    6- alter tablespace read write;

    As for the initial extent, check the default parameters for the SYSTEM (and other) tablespace. It sounds like you are using PCTINCREASE and have hit a large next. Try setting the initial, next and PCTINCREASE to reasonable values.
    Joseph R.P. Maloney, CSP,CDP,CCP
    'The answer is 42'

  3. #13
    Join Date
    May 2002
    You can't "drop" a datafile from a tablespace, but you can do a couple of workarounds. Here is the header of NOTE 111316.1 on MetaLink, "How to 'DROP' a Datafile from a Tablespace." The re-sizing comment (make it 1K) is close - the minimum size is five blocks, so 1K, being smaller than five blocks (normally), would really be five blocks in size.

    PURPOSE This note explains how a datafile can be removed from a database.

    Since there can be confusion as to how a datafile can be dropped because of the ALTER DATABASE DATAFILE OFFLINE DROP command, this note explains the steps needed to delete a datafile and in contrast, and when the OFFLINE DROP command is used.

    SCOPE & APPLICATION There are two situations where people may want to 'remove' a datafile from a tablespace:

    1. You have just mistakenly added a file to a tablespace, or perhaps you made the file much larger than intended and now want to remove it.

    2. You are involved in a recovery scenario and the database won't start because a datafile is missing.

    This article is meant to discuss circumstance 1 above. There are other articles that discuss recovery scenarios where a database cannot be brought online due to missing datafiles. Please see the 'Related Documents' section at the bottom of this article.

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