Re: partition table question.....

Carl Mercer wrote:
>
> > "Howard J. Rogers" <howardjr_at_www.com> wrote in message news:<3ba94044_at_news.iprimus.com.au>...
> > > "Carl Mercer" <cmercer_at_vibrant-1.com> wrote in message
> .........
> > > I took a tablespace for one of the partitions offline. After which,
> > > selects against the table failed.
> .....
> >
> > It is. Provided that your select is not trying to access data in one of the
> > offline partitions. Or that you don't have a global index which your select
> > is using (since the entire index would be marked as unusable).
> >
> > The exact syntac escapes me, but to select directly from a partition it's
> > something like select * from table partition (partition_name).
> .........
>
> > Regards
> > HJR
>
> Thanks
>
> My mistake... select count(*) from inventory fails, Duh! I guess it
> was a silly question. Keep the DML out of the affected partitions and
> access to the table still works. I am also assuming that I can take
> partitions offline by taking the tablespace they are in offline. If
> there is a way to actually take just the partition offline, please let
> me know. Also, you are right, the syntax for accessing individual
> partitions is "select count(*) from inventory (inv1);"
>
> Thanks for the reply.

Also Oracle is smart enough to know that if it can eliminate a partition from a query then it wont use it. So you're not necessarily limited to 'select * from table partition (x)'. If you have 'where blah = other_blah' or such a predicate which is sufficient to avoid an offline partition, then it all works just sweet.

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."