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

From: Neoteric5 <neoteric5_at_hotmail.com>
Date: Thu, 10 May 2001 03:09:04 GMT
Message-ID: <kDnK6.22591$l5.1015371@typhoon.nyc.rr.com>

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< ---------------------------
  

Received on Wed May 09 2001 - 22:09:04 CDT