I am a J2EE developer and have a very basic question about UNDO tablespace. I have a 9i database running in NO ARCHIVELOG mode and the UNDOTBS01.DBF file has eaten up more than 6GB of my disk space. There are three other datafiles each of 1 GB added to the UNDO Tablespace. The problem is- I am running out of space.
So can I make the UNDOTBS01.DBF (I think this one is created by default at the first transection) offline and than delete it safely?
I tried this with a development db in same envirnment. It gave me message that I need to enable Media Recovery first. I enabled Archivelog mode and then the file was allowed to make offline. But the db got shutdown and didnot open ! It gave me the message that the UNDOTBS01.dbf needs to be recovered. And then I found that to do that one needs to connect to OEM which I dont have. Under these circumstances, I think it is not at all safe to attempt deleting the UNDOTBS file.
Then I would like to ask you - can I shrink the size ? The file is 85 % used.
**I saw an existing question like this; but the discussion itself brings some questions about those participants**