Renaming A Datafile

I'm using Windows XP and Oracle 9i R2.  The issue are as follows:

1. I found 2 strange names in the V$datafiles. They are .ORA and logdata03 .  What I wanted is all datafiles have .DBF extension.
2. In order to rename the file I shutdown the database and rename the physical file (.ORA first)
3. I think I've done something wrong here because I do not really need to shuttdown the database, I just need to take that datafile offline and change the name physically and in the database.
4. Since I'm using a Laptop which is impossible to always generate ARC files, I set my database to NO ARCHIVE LOG.

The questions are:
When I wanted to take the datafile offline, I use this command
Alter tablespace finance datafile 44 offline;
But an error appears that I supposed to have the database in ARCHIVE LOG mode.
then, I just try my luck with this syntax
It can, then I can start renaming the datafile again.

1. What is the meaning of OFFLINE DROP? because at first I thought it will take the datafile offline and Drop the datafile as if you are deleting it.

The DROP keyword must be specified if the database is in NOARCHIVELOG mode. The datafile is not dropped, it is flagged as either OFFLINE or RECOVER.

2. What actually happened to the redolog file when I did that command?  I think that when we are going to make one of the datafile offline, Oracle will reset all the log in the logfile after applying the changes to that offline datafile.

When a datafile is placed offline while the database is open, media recovery must be performed to bring the datafile back online.

The online redo logs will be used to perform media recovery before you can bring the datafile online but the redo logs are not reset.

3. I did this in my laptop which is me the only person who did the transactions. what might happened if I did this in the live system with busy transaction 24 hours?

Data in the datafile that was taken offline would not be accessible until it is online once again. Consider the following:


Connected to:
Oracle9i Enterprise Edition Release - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release - Production

SQL> select log_mode from v$database;


SQL> create tablespace test datafile 'G:\ORACLE\ORADATAIDB\TEST.DBF' size 8M
 2  extent management local segment space management auto;

Tablespace created.

SQL> create table test_table tablespace test as select * from all_objects where rownum <= 10000;

Table created.

SQL> select count(*) from test_table;


SQL> alter database datafile 'G:\ORACLE\ORADATAIDB\TEST.DBF' offline;
alter database datafile 'G:\ORACLE\ORADATAIDB\TEST.DBF' offline
ERROR at line 1:
ORA-01145: offline immediate disallowed unless media recovery enabled

SQL> alter database datafile 'G:\ORACLE\ORADATAIDB\TEST.DBF' offline drop;

Database altered.

SQL> select count(*) from test_table;
select count(*) from test_table
ERROR at line 1:
ORA-00376: file 17 cannot be read at this time
ORA-01110: data file 17: 'G:\ORACLE\ORADATAIDB\TEST.DBF'

SQL> alter database datafile 'G:\ORACLE\ORADATAIDB\TEST.DBF' online;
alter database datafile 'G:\ORACLE\ORADATAIDB\TEST.DBF' online
ERROR at line 1:
ORA-01113: file 17 needs media recovery
ORA-01110: data file 17: 'G:\ORACLE\ORADATAIDB\TEST.DBF'

SQL> alter database recover datafile 'G:\ORACLE\ORADATAIDB\TEST.DBF';

Database altered.

SQL> alter database datafile 'G:\ORACLE\ORADATAIDB\TEST.DBF' online;

Database altered.

SQL> select count(*) from test_table;


Quick Links:
Got a Question?
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Best regards,
Oracle Database, SQL, Application, Programming Tips

All the site contents are Copyright © and the content authors. All rights reserved.
All product names are trademarks of their respective companies.
The site is not affiliated with or endorsed by any company listed at this site.
Every effort is made to ensure the content integrity.  Information used on this site is at your own risk.
 The content on this site may not be reproduced or redistributed without the express written permission of or the content authors.