Re: drop table from readonly TS

From: Karsten Schmidt <groups_at_karsten-schmidt.com>
Date: 21 Aug 2003 03:25:14 -0700
Message-ID: <c6711ac4.0308210225.216b5286_at_posting.google.com>


sybrandb_at_yahoo.com wrote in message news:<a1d154f4.0307312334.2198eb46_at_posting.google.com>...
> darshaks_at_coreobjects.com (Darshak Shah) wrote in message news:<92054707.0307310311.1138114d@posting.google.com>...
> > Hi,
> >
> > * Question background :
> >
> > My DB is in ArchiveLog mode.
> > I have TS in ReadOnly mode.
> > As i know,
> > - Oracle allows to DROP table resides in ReadOnly TS (& other objects
> > also) even though that TS is in ReadOnly mode.
> > - Oracle will not recover ReadOnly TS during Oracle startup.
> >
> > I have backup of db files "after" making TS readOnly but "before"
> > dropping tables from ReadOnly TS.
> > That means...
> > 1st, made TS as ReadOnly
> > 2nd, took backup of ReadOnly TS
> > 3rd, dropped few tables from ReadOnly TS
> > Assume now media failure occurred.
> >
> > * Question :
> > After restore + recovery operation, those dropped table will be
> > available or not ? WHY ?
> > I am in doubt because
> > 1) there is chance of having those tables BACK after restore +
> > recovery operation because restored files are before dropping tables.
> > 2) there is chance of NOT having those tables back after restore +
> > recovery operation because Oracle doesn't perform recovery for
> > ReadOnly TS.
> >
> > Which one is correct ???
> >
> >
> > Thanks,
> > Darshak
>
> Information about tables is maintained in the datadictionary in the
> system tablespace. The affected tables have been deleted from the
> dictionary.
> And NO you will not get them back EVER. Not recovering read only
> tablespaces has NOTHING to do with it.
>
> Sybrand Bakker
> Senior Oracle DBA

Hi,
 actually this is kind of interesting - I think.  If this tablespace is local managed, Oracle would have to update the free space bitmaps in the 'read only' datafiles, i.e. they change. Thus a new backup would be required.

 I did nt't test this, but i doubt that it would be allowed to drop tables from read-only local managed tablespaces.

Karsten Received on Thu Aug 21 2003 - 12:25:14 CEST

Original text of this message