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
ALTER TABLESPACE FINANCE DATAFILE 44 OFFLINE DROP;
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:

CODE :

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

SQL> select log_mode from v$database;

LOG_MODE
------------
NOARCHIVELOG

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;

 COUNT(*)
----------
    10000

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;

 COUNT(*)
----------
    10000

Have a Oracle Question
Do you have an Oracle Question?

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

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips