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

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.

>

>Thanks!

>-Milo Chan

>mail to mchan at vestant dot com

>

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

>

>-- Create a tablespace for each partition.

>CREATE TABLESPACE "FOO200101" LOGGING DATAFILE '/oradata/FOO200101.dbf'

> SIZE 5M REUSE AUTOEXTEND ON NEXT  5M MAXSIZE  2000M EXTENT MANAGEMENT

>LOCAL;

>

>CREATE TABLESPACE "FOO200102" LOGGING DATAFILE '/oradata/FOO200102.dbf'

> SIZE 5M REUSE AUTOEXTEND ON NEXT  5M MAXSIZE  2000M EXTENT MANAGEMENT

>LOCAL;

>

>-- Create IOT table with the first partition.

>CREATE TABLE FOO

>(

> SYMBOL VARCHAR2(10) NOT NULL,

> TIMESTAMP DATE NOT NULL,

> PRICE NUMBER,

> CONSTRAINT PK_FOO PRIMARY KEY(SYMBOL, TIMESTAMP)

>)

>ORGANIZATION INDEX

>COMPRESS 1

>PARTITION BY RANGE (TIMESTAMP) (

>PARTITION "FOO200101"

> VALUES LESS THAN  (to_date('1/31/2001 23:59','mm/dd/yyyy hh24:mi'))

>TABLESPACE "FOO200101"

>);

>

>-- Next month, try to create new partition in tablespace "FOO200102", but

 it


>winds up

>-- being created in tablespace "USERS", which is the default tablespace

>-- for the current schema.

>ALTER TABLE FOO ADD PARTITION "FOO200102"

> VALUES LESS THAN (to_date('2/28/2001 23:59','mm/dd/yyyy hh24:mi'))

> TABLESPACE "FOO200102";

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

>

>

>