Re: Lost datafile for dataless partition

CT,

I'm guessing you've already figured out some solution to this since its been a few days but in the future, you should be able to.

1) offline drop the data file
2) alter table drop partition
3) drop tablespace including contents

I tried this the other day and it worked.

Hope this helps
"CT" <CT_at_nomail.com> wrote in message
news:aulrg0$jkd$1_at_newsg4.svr.pol.co.uk...
> Thanks John for the resopnse,
>
> I tried to offline drop the datafile as per the textbooks (for non
> partiitioned tablespaces) and Oracle acknowledged this as good, but the
drop
> of the tablespace yeilded and error (ORA-nnn...can confirm at an eariler
> hour of the morning) tablespace still belonged to an active table (so
> dropping was not an option, and Oracle still recognised the datafile as
> alive and kicking) . You see my deilema?!
>
> No such instance of my problem has surfaced in recent times on this news
> group via metalink or through google search which gives me an insight as
to
> whether i'm at a point of no return regarding this problem and trying to
do
> something that this version of Oracle doesn't allow, or being dense and
> missing something obvious.
>
> Thanks for the input.
> CT
>
>
>
>
> "John Darrah" <nospam_at_nospam.net> wrote in message
> news:ZZsP9.365582$GR5.109524_at_rwcrnsc51.ops.asp.att.net...
> > I don't have access to a DB with the partitioning option installed at
the
> > moment so I apologize in advance for offering up a suggestion that I
> haven't
> > tried. I think you should be able to offline drop the datafile in
> question
> > and drop the partition. If that works, you can drop the tablespace.
> > Hope this helps,
> >
> > John
> > "CT" <CT_at_nomail.com> wrote in message
> > news:auj0l4$gas$1_at_news6.svr.pol.co.uk...
> > > Oracle 8.0.5, on Tru64.
> > >
> > > I have a partitioned table which has lost (physically deleted) one of
> it's
> > > datafiles for the partitions. The partition in question contains no
> data,
> > so
> > > no data has been lost, how can I recreate/remove this partition given
> the
> > > absence of any viable physical backups (yes...I know....!) and the
> > absence
> > > of archive logs.
> > >
> > > Oracle doesn't seem to like the deletion of the tablespace (and
> associated
> > > datafile) for this partition because the datafile isn't there for it
to
> > > interrogate and remove contents.
> > >
> > > Is there a way to force the deletion given I knwo the partition has
not
> > been
> > > used, or associate a datafile with this paritions tablespace.
> > >
> > > Thanks
> > > CT
> > >
> > >
> > >
> > >
> > >
> >
> >
>
>
Received on Wed Jan 01 2003 - 20:02:44 CST