Re: HELP!Remove Data Files

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 <SNIP>
LOGFILE
<SNIP>
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 <SNIP>
LOGFILE
<SNIP>
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_at_LynxFinancialSystems.co.uk
Technical Consultant		Phone:	0113 289 6265
Lynx Financial Systems Ltd.	Fax:	0113 201 7265
			URL:	http://www.LynxFinancialSystems.com
------------------------------------------------------------------------