Add range partition clause question

[AntiEarnie]

Oracle 8.1.7.4.1

Can someone double check me on this? Is there no way to specify what tablespace a new partition is to be created under? I have sifted through the toad knowlege xpert and my oracle 8i complete reference and I can find nothing that says how to avoid the default tablespace for a partitioned table.

For example I have TableA which wants to use TablespaceA through some setting I can not find (does not show in script tab in toad yet shows in OEM). However I want to create my new partition in TablespaceB. The syntax for adding the range partition I have found is this:

ALTER TABLE SomeTable ADD PARTITION SomePartition VALUES LESS THAN (value_list)

Simply adding on a TABLESPACE SomeTablespace to the end does not add the new partition to the tablespace specified, nor does it generate an error.

Is it not possible to specify a tablespace when adding a range partition? If not, is there some way I can change what the default tablespace is for the partitioned table?

I know I can just move the partition after it is created but I would like to stick to one statment if I can.

 

[LKBrwnDBA]

You are wrong, partitions can be allocated in any tablespace:

SQL>create table test_p
  2  (id number(9), text varchar2(30), eff_date date)
  3  partition by range (eff_date)
  4  (partition p2003 values less than (to_date('20040101','YYYYMMDD'))
  5      tablespace users
  6  ,partition p2004 values less than (to_date('20050101','YYYYMMDD'))
  7      tablespace users
  8  )
  9  /

Table created.

SQL>alter table test_p add
  2      partition p2005 values less than (to_date('20060101','YYYYMMDD'))
  3      tablespace tools
  4  /

Table altered.

SQL>select partition_name, tablespace_name
  2    from dba_tab_partitions where table_name='TEST_P'
  3  /

PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
P2004                          USERS
P2005                          TOOLS
P2003                          USERS

SQL>

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[AntiEarnie]

LKBrwnDBA - I am not really wrong, just not clear enough on the problem. Though I admit I didn't test a basic case like you posted. We are using IOT for our partitioned tables. If you create the table with ORGANIZATION INDEX you will also get the same result as what I posted. i.e.

SQL> create table test_p
  2  (aDate date,
  3  CONSTRAINT TEST_P_PK
  4  PRIMARY KEY
  5   (aDate)
  6  )
  7  organization index
  8  partition by range (aDate)
  9  (partition p2003 values less than (to_date('20040101','YYYYMMDD'))
 10    TABLESPACE SMALL
 11  ,partition p2004 values less than (to_date('20050101','YYYYMMDD'))
 12    TABLESPACE SMALL
 13  );

Table created.

SQL> COMMIT;

Commit complete.

SQL> SELECT INDEX_NAME,  TABLESPACE_NAME
  2  FROM ALL_IND_PARTITIONS WHERE INDEX_NAME = 'TEST_P_PK';

INDEX_NAME                     TABLESPACE_NAME                                  
------------------------------ ------------------------------                   
TEST_P_PK                      SMALL                                            
TEST_P_PK                      SMALL                                            

SQL> ALTER TABLE TEST_P ADD
  2    PARTITION P2005 VALUES LESS THAN (TO_DATE('20060101','YYYYMMDD'))
  3    TABLESPACE LARGE;

Table altered.

SQL> SELECT INDEX_NAME,  TABLESPACE_NAME
  2  FROM ALL_IND_PARTITIONS WHERE INDEX_NAME = 'TEST_P_PK';

INDEX_NAME                     TABLESPACE_NAME                                  
------------------------------ ------------------------------                   
TEST_P_PK                      SMALL                                            
TEST_P_PK                      SMALL                                            
TEST_P_PK                      USR

[red]*note that you have to check against ALL_IND_PARTITIONS[/red]

I am not sure where tablespace is set for a paritioned table but according to OEM the table has one. This is where it always seems to create my IOT partitions. I thought this tablespace was coming from the schema but we have one old IOT that has a different tablespace then schema it is assigned to. I have not found where this is listed anywhere but in OEM.

 

[AntiEarnie]

Hmm. Guess if there are no suggestions I will just add a move statment to the job.