Re: ALTER TABLE...ADD PARTITION...TABLESPACE <tablespace_name> fails

Thanks, Jonathan! Certainly, it is not yet fixed in 8.1.7.0.

(1) Are such partition moves synchronous with the ALTER TABLE...MOVE PARTITION statement, or does background work continue in the Oracle server after the statement returns?

In other words, if I do this:

------------------cut here 8<------------------
ALTER TABLE...ADD PARTITION P1...
ALTER TABLE...MOVE PARTITION P1...

------------------cut here 8<------------------

in a single SQL script, is it possible to get the 00600 error, or is the P1 move guaranteed to be complete before the P2 move begins?

(2) Is there a system table or view I can query that shows the progress of the partition move, so that I can serialize the multiple moves explicitly?

(I ask this because I've noticed that when using a tool like Oracle DBA Studio, *some* commands seem to continue executing on the server after control returns to the application.)

Thanks!
-Milo Chan
mail to mchan at vestant dot com

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message news:989474297.28880.0.nnrp-08.9e984b29_at_news.demon.co.uk...
>
> Its a known bug with partitioned IOTs,
> appeared some time around 8.1.6, fixed
> in 8.1.7.1 (I think).
>
> The quoted workaround is to create the
> partition in the wrong tablespace and
> then move it. However there is another
> bug which can result in a 00600 error
> if you start moving several such partitions
> at the same time.
>
>
> --
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Practical Oracle 8i: Building Efficient Databases
> Publishers: Addison-Wesley
>
> Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html
>
>
>
> Neoteric5 wrote in message ...
> >I am trying to create a partitioned, Index Organized Table, with each
> >partition in a dedicated tablespace.
> >
> >But when I try to add a partition, the TABLESPACE clause is not working.
> >The ALTER TABLE...ADD PARTITION statement always creates new partitions
 in
> >the default
> >partition that the table was created in, rather than in the tablespace
> >specified in the ALTER
> >TABLE statement.
> >
> >What am I doing wrong? Does this work for others? (is my Oracle
> >installation broken?)
> >I am using 8.1.7EE on Linux 2.2.17.
> >
> >I don't want to create all partitions at table creation time once,
> >because of space and hardware limitations.
> >
> >I've attached a sample that fails in my Oracle environment.
...deleted... Received on Thu May 10 2001 - 06:46:40 CDT