Datafile delted but........
I noticed a funny thing,
What i did was, i deleted a datafile belonging to users tablespace
manually at the OS level.
Then i created a tabe in that tablespace, which works fine; even
though the underlying datafile is missing;
Then i insert some rows....and commmit...ok it works fine...
then i switch logfile...then also it works fine.....
then i shutdown the database normally, then also it works fine.
Then problem occurs only when you shutdown and restart the database.....
why it is so ?
because when you start the database the control file will go looking for that datafile and when it cant find it, will chuck an error back at you
well davey23uk has stated it just right..it is because the control file cannot find the file you deleted
when u try to restart the database it will say that cannot start the db as the datafile is missing, u need to do a offline drop .
but whats the thing u are trying to acheive ?
Eat , Drink & Enjoy life -
But for the first insert into a recently created table, the DBWR process has to read in the datafile to put the segment blocks into the database buffer cache ... Or not ?
How that could be done if there is no more datafile ...
What about the logswitch? I thought a logswitch updates physical structure with an LSN and a chkpoint issued across to make sure db is in sync. Moreover bang_dba also shutdown the database (I assume it is not abort) and how did that happen successfully without all datafiles in sync? Thanks, ST2000
do alter system checkpoint
switch log although forces a checkpoint it takes some time to update the datafile headers (seems like deferred) but explicit checkpoint update datafile instantaneously
Click Here to Expand Forum to Full Width