would you query dba_data_files and dba_temp_files to get the file_id first ? then stop db/ mount it , drop the file using file_id (alter database or alter tablespace ...), then open the DB to try again? On Thu, Feb 5, 2009 at 4:24 AM, Xu, Roger <Roger.Xu@xxxxxxxx> wrote: > > > Hi List, > > > > In order to change the SID, we recreated the controlfile but I left 2 > dbfiles out – oops! > > > > Now, Oracle KNOWS about the datafile as its in the dictionary so puts a > "MISSING" entry in its place. > > > > I know there is no data in these dbfiles. What is the easiest way for me to > drop them? > > > > We are on 10g which allows dropping a single dbfile but the status of the > dbfile needs to be online? > > > > Thanks in advance, > > > > Roger Xu > > > > P.S. > > > > SQL> select name, status from v$datafile; > > > > NAME > STATUS > > ----------------------------------------------------------------------------- > ------- > > /SRV_ACEQ/server/database/data/system01.dbf > SYSTEM > > /SRV_ACEQ/server/database/data/undo01.dbf > ONLINE > > /SRV_ACEQ/server/database/data/sysaux01.dbf > ONLINE > > /SRV_ACEQ/server/database/data/inv_arch/ts_data_lob_5m_01.dbf > ONLINE > > /SRV_ACEQ/server/database/data/inv_arch/ts_data_lob_5m_02.dbf > ONLINE > > /SRV_ACEQ/server/database/data/inv_arch/ts_data_lob_5m_03.dbf > ONLINE > > /SRV_ACEQ/server/database/data/ts_data_00_01.dbf > ONLINE > > /SRV_ACEQ/server/database/data/ts_data_00_02.dbf > ONLINE > > /SRV_ACEQ/server/database/data/ts_data_00_03.dbf > ONLINE > > /SRV_ACEQ/server/database/data/ts_index_00_01.dbf > ONLINE > > /SRV_ACEQ/server/database/data/inv_arch/ts_data_lob_5m_04.dbf > ONLINE > > /oracle/OraHome2/dbs/MISSING00012 > RECOVER > > /oracle/OraHome2/dbs/MISSING00013 > RECOVER > > > > 13 rows selected. > > > > SQL> select * from v$version; > > > > BANNER > > ---------------------------------------------------------------- > > Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi > > PL/SQL Release 10.2.0.4.0 - Production > > CORE 10.2.0.4.0 Production > > TNS for Solaris: Version 10.2.0.4.0 - Production > > NLSRTL Version 10.2.0.4.0 - Production > > > > SQL> > > > > Please be conscious of the environment and print this email only if > absolutely necessary. > > This e-mail (including any attachments) is confidential and may contain > privileged information of Dr Pepper Snapple Group, Inc. and/or its > subsidiaries ("Dr Pepper Snapple Group"). If you are not the intended > recipient or receive it in error, you may not use, distribute, disclose or > copy any of the information contained within it and it may be unlawful to do > so. If you are not the intended recipient, please notify us immediately by > returning this e-mail to us at mailerror@xxxxxxxx and destroy all > copies. Any views expressed by individuals within this e-mail do not > necessarily reflect the views of Dr Pepper Snapple Group. This e-mail does > not constitute a binding offer, acceptance, amendment, waiver or other > agreement, unless the intent that an e-mail will constitute such is clearly > stated in the body of the email. Recipients are advised to subject this > e-mail and attachments to their own virus checking, in keeping with good > computing practice. Please note that e-mail received by Dr Pepper Snapple > Group may be monitored in accordance with applicable law. > > -- ("'-''-/").___..--''"'-._ '7_ 7 ) '_. ( ).'-.__.') (_Y_.) ._ ) '._ '. ''-..-' _.'--'_..-_/ /--'_.' ,' (il),-'' (li),' ((!.-' Best regards, Yours sincerely House baojiejie@xxxxxxxxx