unable to rename a datafile

[ggnanaraj]

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.

[jrpm]

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.

[stecal]

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.