Re: partition table question.....

"Carl Mercer" <cmercer_at_vibrant-1.com> wrote in message news:d0bb6654.0109190647.733a36f6_at_posting.google.com...
> Hopefully this isn't a totally stupid question...
>
>
> I have just started playing with table and index partitioning and it
> is not working as I initially thought it would. I have some
> questions....
>
> I created a table with 14 range partitions based on date (less than
> 01-JAN-2001 thru 01-JAN-2002, MAXVALUE)
>
> I added data to the table so that different partitions were populated
> with data.
>
> I took a tablespace for one of the partitions offline. After which,
> selects against the table failed. At first I thought this sucked
> since a big benefit of partitions is that the data is available when
> partitions are lost or corrupted.

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). See whether that works for one of the partitions you've left online (and you can find the exact syntax by searching for it at technet.oracle.com...sorry, too busy to look myself right now).

Regards
HJR
>After thinking about it, I figured
> it is not too hard to create a view which selects from the online
> partitions.
>
> My questions are.....
>
> 1) is there a way to alter a partition offline so that I can select
> from table and have the select work? I could not find a partition
> offline command in the online text (doing a search).
>
> 2) is there a dictionary table which will tell me that a partition is
> not available? I found one for indexes but I could not find a status
> for partitions on tables. If I could find this, I proably could create
> a shell script that generated the view to get around unavailable
> partitions.
>
> 3) Are the tools that affect partitions smart enough to not disrupt my
> application from accessing the table while it is being worked on. In
> other words, if I am backing-up/direct loading/rebuilding
> indexes/importing a partition, will the table be accessable or will a
> view have to be created with affected partitions removed.
>
> 4) what about other commands like insert, update and delete? DO I have
> to create an insteadof trigger to get around unavailable partitions
> that reqiure the creation of a view to be accessed?
>
>
> Thanks for answers to any of these questions... I hope the answer to
> (1) is yes so that questions (3) and (4) go away.
>
>
> Carl Mercer
> cmercer_at_vibrant-1.com
Received on Wed Sep 19 2001 - 20:05:57 CDT