Datafile delted but........

[bang_dba]

hi ,
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 ?

[davey23uk]

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

[paddy04]

well davey23uk has stated it just right..it is because the control file cannot find the file you deleted

[pravin]

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 ?

[htanga]

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 ...

[st2000]

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

~

[pando]

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