Re: Is it possible to remove temporary added datafile from tablespace ?

satar_at_my-dejanews.com wrote:

]Thank you for clearing it up (I'm kinda slow). The company I'm consulting
]with has the exact same problem. The DBA created a data file and then removed
]it. They are in NOARCHIVELOG mode and are doing cold backups every night. You
]still can backup the database (Cold) and recover it, when needed. I did not
]understand your reasoning for not being able to do backups on the Tablespace.
]Are you running in ARCHIVELOG mode and were reffering to Hot backups? But you
]said you did an OFFLINE DROP, which only is needed in NOARCHIEVELOG mode.
]
]What I would Do, Assuming no backup of datafile: 1. Creat a Testing
]enviroment on another machine, and restore from the last full backup. 2. see
]if thier are any objects on that Offline datafile. They need to be recreated
](if possible). It seems you either have no objects in the datafile or objects
]that are of no grave importance to your database. 3. Create a New Tablespace
]to take over the damaged one. 4. Export all the objects of the old
]tablespace's datafiles and import them to the new tablespace. (Easier said
]than done, I know) 5. Issue the following command to the Damaged Tablespace:
]DROP TABLESPACE tablespacename INCLUDING CONTENTS.

Why is OFFLINE DROP only relevant when in NOARCHIVELOG mode? I missed something there. Yes, we are running in ARCHIVELOG mode and only ever do hot backups and occasional exports to multiple tapes (database export wont fit on one tape).

There is absolutely nothing at all in the deleted data file. The tablespace in question is used for online reports - nothing in there lasts more than 12 hours. The acutal case is not important, I'm more interested in the theoretical problem of how you would get rid of this file so that hot backups would again be possible (in the broader sense). Maybe the only way would be to re-create the tablepsace.

I can't see any technical reason why Oracle could not allow the removal of a data file that is not used without having to recreate the tablespace, but maybe it is not possible.
--

 ,-,_|\   George Dau,                                             __
/    * \  Unix (Solaris, DEC Unix, Linux), Oracle, Internet.     (OO)
\_,--\_/  Home: gedau_at_pobox.com      Work: gedau_at_isa.mim.com.au ( \/ )
      v   WWW:  http://pobox.com/~gedau                          W--W

MIM Internet policy item 3.4 compliance - "I am clearly stating that any views or opinions expressed above do * NOT * necessarily represent the views or opinions of MIM." Received on Wed Nov 18 1998 - 16:10:05 CST