Solved

Managing Datafiles

Posted on 2004-09-27
13
646 Views
Last Modified: 2013-12-03
Hello,

Finishing up my database migration project, I realise that there are extra Datafiles that were defined and some existing datafiles need to be moved to different physical locations.

I tried to invoke the OEM to delete datafiles but I am unable to do so.

1) How can I remove uneeded datafiles from my database ?

PT

PS: I realise that some datafiles where created with reference to wrong tablespaces and now need to be removed.  

Your toughts ?

0
Comment
Question by:ptreves
13 Comments
 
LVL 10

Expert Comment

by:SDutta
ID: 12163205
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.
0
 

Author Comment

by:ptreves
ID: 12163403
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 ?
0
 

Author Comment

by:ptreves
ID: 12163420
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
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 10

Expert Comment

by:SDutta
ID: 12163449
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.

0
 
LVL 12

Expert Comment

by:geotiger
ID: 12164109
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.

0
 

Author Comment

by:ptreves
ID: 12166338
Hello,

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

PT
0
 
LVL 12

Expert Comment

by:catchmeifuwant
ID: 12166616
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?
0
 

Author Comment

by:ptreves
ID: 12182815
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
0
 

Author Comment

by:ptreves
ID: 12185061
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
0
 

Author Comment

by:ptreves
ID: 12185555
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


 
 
0
 
LVL 10

Accepted Solution

by:
SDutta earned 500 total points
ID: 12190419
While renaming the datafile from OEM it does not change the name of the actual physical file, so it goes offline. You have to then rename the physical file on the OS and then make it online in OEM. It may say that the datafile needs recovery before it can be onlined. Just do : ALTER DATABASE RECOVER DATAFILE 'filename';
It should come up after that.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle RAC 12c 8 88
1 FROM DUAL wont work with additional columns ?? 4 46
run sql script from putty 4 73
join actual table rows based on the column 25 22
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question