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.
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'
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.
Click Here to Expand Forum to Full Width