From: Norman Dunbar Newsgroups: comp.databases.oracle.misc Subject: Re: HELP!Remove Data Files Date: Tue, 10 Oct 2000 14:24:53 +0100 Organization: Customer of Energis Squared Lines: 98 Sender: Norman Dunbar Message-ID: <94690301E74BD311B7F800805FEAD70AF214EB@apps.lynx-fsc.co.uk> References: <8rqgdi$cbu$5@coco.singnet.com.sg> <39e0cd0a@news.iprimus.com.au> <971042547.8405.2.pluto.d4ee154e@news.demon.nl> <8rta11$68b$1@news.sinet.slb.com> NNTP-Posting-Host: mailhost.lynx-fsc.co.uk X-Trace: newsreaderg1.core.theplanet.net 971184392 15997 195.92.160.194 (10 Oct 2000 13:26:32 GMT) NNTP-Posting-Date: 10 Oct 2000 13:26:32 GMT X-Complaints-To: abuse@theplanet.net X-Newsreader: Microsoft (R) Exchange Internet News Service Version 5.5.2650.12 As far as I remember from my DBA courses, OFFLINE DROP is simply OFFLINE when running in NOARCHIVELOG mode. To REALLY remove a datafile that is no longer required : alter database datafile 'xxxx' offline [drop]; Make sure everything still works !!! Then the hard bit : alter database backup controlfile to trace; copy the tracefile into an sql script file, and edit it : Remove (or comment out) all the cr*p at the top down to the line starting CREATE CONTROLFILE (I don't like scripts that startup my database thank you !) On the same line, (CREATE CONTROLFILE)( replace NORESETLOGS with RESETLOGS) Now scroll down to the list of datafiles and simply remove the one you don't want. Beware, if it is the last one, you must remove the trailing comma from the line above ! Finally, remove all the trailing cr*p starting from (and including) the comment line '#Recovery is required ....' Don't forget to save the file :o) EXCERPT (BEFORE) : STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "QA605" NORESETLOGS ARCHIVELOG LOGFILE DATAFILE '/data1/QA605/system.dbf', '/data2/QA605/rbs.dbf', '/data3/QA605/temp.dbf', '/data4/QA605/tools.dbf', '/data5/QA605/foliocom.dbf', '/data6/QA605/foliocrd.dbf', '/data7/QA605/foliolse.dbf', '/data8/QA605/foliostk.dbf', '/data9/QA605/folioban.dbf' ; AFTER : STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "QA605" RESETLOGS ARCHIVELOG LOGFILE DATAFILE '/data1/QA605/system.dbf', '/data2/QA605/rbs.dbf', '/data3/QA605/temp.dbf', '/data4/QA605/tools.dbf', # file removed here # '/data6/QA605/foliocrd.dbf', '/data7/QA605/foliolse.dbf', '/data8/QA605/foliostk.dbf', '/data9/QA605/folioban.dbf' ; shutdown the instance startup mount the instance run the script Assuming all went well, alter database open resetlogs; There you have it, one datafile removed ! Hope this helps. Regards, Norman. PS. CAVEATS : 1. Backup the database files, redo logs etc 2. Don't even attempt this on a live/production database unless you have tested it on a 'silly' database first. 3. Usual disclaimers apply - you take your own risks ! 4. You STILL have to manually delete the file from the operating system. ------------------------------------------------------------------------ -------- Norman Dunbar EMail: NDunbar@LynxFinancialSystems.co.uk Technical Consultant Phone: 0113 289 6265 Lynx Financial Systems Ltd. Fax: 0113 201 7265 URL: http://www.LynxFinancialSystems.com ------------------------------------------------------------------------ --------