partition table question.....

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. 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 - 09:47:43 CDT