RE: dropped datafile problem

From: Andrey Bronfin <andreyb_at_elrontelesoft.com>
Date: Mon, 21 Jan 2002 09:45:40 -0800
Message-ID: <F001.003F61BD.20020121093206@fatcity.com>

nope.
The file was physically
deleted at the OS level after "offline drop". Thanks

DBAndrey

03-9254520
053-464562
mailto:andreyb_at_elrontelesoft.com

-----Original Message-----
Sent: 21 January 2002 17:51
To: Andrey Bronfin <andreyb
Cc: LazyDBA.com Discussion

How about bringing it back online and resizing it to 1M or a nominal size which will never allow extents to be allocated in it ?

                      Andrey Bronfin

                      <andreyb_at_elrontel        To:       "LazyDBA.com
Discussion" <oracledba_at_lazydba.com> 
                      esoft.com>               cc:

                                               Subject:  RE: dropped
datafile problem                     
                      21/01/02 15:39

Hi Mike & other gurus!
Thanks for your reply.
I'm aware of what u have said - i just have no choice , i can't rebuild the tablespace.
I just need to prevent Oracle from allocating extents in that "bad" datafile.
Thanks!

-----Original Message-----
Sent: 21 January 2002 16:51
To: 'Andrey Bronfin'; 'LazyDBA.com Discussion'

OFFLINE DROP does not allow the dropping of a datafile. There is no supported way to drop an existing datafile from a tablespace without dropping the entire tablespace. The answer is, you can't without hacking the
data dictionary which voids your support.

You can try recreating the control file without the datafile mentioned but success with this has been spotty.

Mike

-----Original Message-----
Sent: Monday, January 21, 2002 9:38 AM
To: LazyDBA.com Discussion

Dear gurus !

I have an "offline dropped" datafile at my database. The file was physically
deleted at the OS level after "offline drop". Before dropping the file , all the objects that had extents allocated in that file (from dba_extents) were dropped. Now , V$datafile shows this file as of size=0 and status = 'RECOVER' which is fine.
BUT , file$ show this file as of size 8GB, which is the file size as it used
to be before being dropped.
Moreover , fet$ shows 8GB as free extents residing at this file . And , after all , tables get created in that file , i.e. dba_extents show extents with FILE_ID of that file !
When a select statement tries to access a table with extents residing in that file , i get an error message , off course.

So , the question is , how do i make the file's extents disappear from fet$ ?
Thanks a lot.

Andrey.

Oracle documentation is here:
http://tahiti.oracle.com/pls/tahiti/tahiti.homepage To unsubscribe: send a blank email to oracledba-unsubscribe_at_LAZYDBA.com To subscribe: send a blank email to oracledba-subscribe_at_LAZYDBA.com Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl Tell yer mates about http://www.farAwayJobs.com By using this list you agree to these
terms:http://www.lazydba.com/legal.html

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Andrey Bronfin
  INET: andreyb_at_elrontelesoft.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Mon Jan 21 2002 - 11:45:40 CST