Managing Datafiles

Datafiles cannot be dropped by themselves, the whole tablespace has to be dropped. Drop the affected tablespace and then recreate it with the correct datafiles. Be careful not to drop any data without backing up the database.

Hello,

Can you be more specific ?


PT

PS: I have 2 or 3 datafiles referencing the same tablespace..
In fact, I only need one of the datafiles, the other 2 I should remove.

How can I go about that ?

PS2: I tought that the the Enterprise Manager could do the job ?

Hello,

I guess I should shutdown the database, remove the datafiles that are not needed. Is that the way I should go about it ?

PT

Hi PT,

If you have 3 datafiles in a tablespace, you CANNOT remove 1 or 2 datafiles from it.
You have to drop the entire tablespace with ALL 3 datafiles.
Then create the tablespace fresh with the required datafile again.

Here are some details related to your question:

Dropping Datafiles
There is no SQL statement that specifically drops a datafile. The only means of
dropping a datafile is to drop the tablespace that contains the datafile. For example,
if you want to remove a datafile from a tablespace, you could do the following:
1. Create a new tablespace
2. Move the data from the old tablespace to the new one
3. Drop the old tablespace
You can, however, drop a tempfile using the ALTER DATABASE statement. For
example:
ALTER DATABASE TEMPFILE '/u02/oracle/data/lmtemp02.dbf' DROP
INCLUDING DATAFILES;


To drop a tablespace, use the DROP TABLESPACE statement. The following
statement drops the users tablespace, including the segments in the tablespace:
DROP TABLESPACE users INCLUDING CONTENTS;


Renaming and Relocating Datafiles
You can rename datafiles to either change their names or relocate them. Some
options, and procedures which you can follow, are described in the following
sections:
n Renaming and Relocating Datafiles for a Single Tablespace
For example, renaming filename1 and filename2 in tablespace1, while the rest of
the database is open.
n Renaming and Relocating Datafiles for Multiple Tablespaces
For example, renaming filename1 in tablespace1 and filename2 in tablespace2, while
the database is mounted but closed.
When you rename and relocate datafiles with these procedures, only the pointers to
the datafiles, as recorded in the database’s control file, are changed. The procedures
do not physically rename any operating system files, nor do they copy files at the
operating system level. Renaming and relocating datafiles involves several steps.
Read the steps and examples carefully before performing these procedures.
When you rename and relocate datafiles with these procedures, only the pointers to
datafiles, as recorded in the database’s control file, are changed. The procedures
not physically rename any operating system files, nor do they copy files at the
operating system level. Renaming and relocating datafiles involves several steps.
the steps and examples carefully before performing these procedures.
Note: To rename or relocate datafiles of the SYSTEM tablespace,
you must use the second option, because you cannot take the
SYSTEM tablespace offline.


Renaming and Relocating Datafiles for a Single Tablespace
The section offers some procedures for renaming and relocating datafiles in a single
tablespace. You must have the ALTER TABLESPACE system privilege to rename
datafiles of a single tablespace.
Renaming Datafiles in a Single Tablespace
To rename datafiles from a single tablespace, complete the following steps:
1. Take the non-SYSTEM tablespace that contains the datafiles offline.
For example:
ALTER TABLESPACE users OFFLINE NORMAL;
2. Rename the datafiles using the operating system.
3. Use the ALTER TABLESPACE statement with the RENAME DATAFILE clause to
change the filenames within the database.
For example, the following statement renames the datafiles
/u02/oracle/rbdb1/user1.dbf and /u02/oracle/rbdb1/user2.dbf
to/u02/oracle/rbdb1/users01.dbf and /u02/oracle/rbdb1/users02.dbf,
respectively:
ALTER TABLESPACE users
RENAME DATAFILE '/u02/oracle/rbdb1/user1.dbf',
'/u02/oracle/rbdb1/user2.dbf'
TO '/u02/oracle/rbdb1/users01.dbf',
'/u02/oracle/rbdb1/users02.dbf';
The new files must already exist; this statement does not create the files. Also,
always provide complete filenames (including their paths) to properly identify
the old and new datafiles. In particular, specify the old datafile name exactly as
it appears in the DBA_DATA_FILES view of the data dictionary.
4. Back up the database. After making any structural changes to a database,
always perform an immediate and complete backup.
Relocating and Renaming Datafiles in a Single Tablespace
Here is an example that illustrates the steps involved for relocating a datafile.
Assume the following conditions:
n An open database has a tablespace named users that is made up of datafiles
all located on the same disk.

Hello,

When I drop a tablespace, should I reimport the data afterwards ?

PT

Before dropping a tablespace, take a backup of the objects you want to reimport

1)Backup the objects using export utility
exp username/password@sid tables=table1,table2,table3...  file=ts_1.dmp

2)Drop the tablespace including the datafiles and contents
drop tablespace <ts_name> including datafiles and contents;

3)Recreate the tablespace with 1 datafile
create tablespace <ts_name> datafile 'location_of_file' size 100m autoextend on

4)Import the tables that you have exported into the new tablespace
imp username/password@sid file=ts_1.dmp full=y


BTW, why do you want to remove a TS just because it has multiple files?Does it afffect you in any way?

Hello,

I can ask you that same question.
I have 3 datafiles referencing the same tablespace, when in fact I only need one. I want to clean thing up.

1) Would keeping all those datafiles effect me in any way down the road ?

PT

Hello,

Ok, I dropped my tablespace and recreated it with the correct datafile names and locations.

Now,I cannot get me tablespace back online.

I get the following errors:

alter tablespace alcieind online;
Error at line 1
ORA-01122: Database file 13 failed verification check
ORA-01110 database '/disk1/oradata/alcieind.ora'
ORA-01250: unknown file header version read for file number 13

1) How can I get my tablespace back online and working normally ?

PT

Hello

1) create tablespaces + users
2) import the export dump file
3) rename the datafiles

When renaming the datafiles, I noticed that their size goes back to 0 + they are no longer online. WHen I try to edit those changes using OEM, I get the following errors:

alter tablespace alcieind online;
Error at line 1
ORA-01122: Database file 11/13 failed verification check
ORA-01110 database '/disk1/oradata/alcieind.ora'
ORA-01250: unknown file header version read for file number 11/13

1) How can I give my new datafiles the size and bring them back online ?

PT