Home » SQL & PL/SQL » SQL & PL/SQL » Partition index Tablespace
Partition index Tablespace [message #267191] Wed, 12 September 2007 12:25 Go to next message
Messages: 144
Registered: September 2005
Location: chicago
Senior Member


When we alter a table by adding a partition with this statement
alter table abc add partition P_4 values less than 
(TO_DATE(' 2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) 
tablespace R1APP_DATA_Y08 

For existing Local Partitioned index it creates a new partition index for this table partition but on Data tablespace which was used while adding table partition,
SQL> select tablespace_name,partition_name from user_ind_partitions where Index_name='ABC_LOCAL';

------------------------------ ------------------------------
R1APP_INDX_Y06                 P_1
R1APP_INDX_Y07                 P_2
R1APP_DATA_Y08                 P_3
R1APP_DATA_Y08                 P_4

How can we make sure index goes on different tablespace while using the alter table add partition command.
or else I have to go explicitly and run this for all tables where ever we have added partitions
alter index abc_local rebuild  partition P_4  tablespace R1APP_INDX_Y10
and move that partition to respective TS.


[Updated on: Wed, 12 September 2007 13:02] by Moderator

Report message to a moderator

Re: Partition index Tablespace [message #267197 is a reply to message #267191] Wed, 12 September 2007 13:03 Go to previous message
Michel Cadot
Messages: 64418
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't specify index tablespace on alter table, you have to manually do it after... until 11g and INTERVAL PARTITION.

Re: Partition index Tablespace [message #267198 is a reply to message #267191] Wed, 12 September 2007 13:03 Go to previous message
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
SQL> desc dba_ind_partitions
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 INDEX_OWNER                                        VARCHAR2(30)
 INDEX_NAME                                         VARCHAR2(30)
 COMPOSITE                                          VARCHAR2(3)
 PARTITION_NAME                                     VARCHAR2(30)
 SUBPARTITION_COUNT                                 NUMBER
 HIGH_VALUE                                         LONG
 HIGH_VALUE_LENGTH                                  NUMBER
 PARTITION_POSITION                                 NUMBER
 STATUS                                             VARCHAR2(8)
 TABLESPACE_NAME                                    VARCHAR2(30)
 PCT_FREE                                           NUMBER
 INI_TRANS                                          NUMBER
 MAX_TRANS                                          NUMBER
 INITIAL_EXTENT                                     NUMBER
 NEXT_EXTENT                                        NUMBER
 MIN_EXTENT                                         NUMBER
 MAX_EXTENT                                         NUMBER
 PCT_INCREASE                                       NUMBER
 FREELISTS                                          NUMBER
 FREELIST_GROUPS                                    NUMBER
 LOGGING                                            VARCHAR2(7)
 COMPRESSION                                        VARCHAR2(8)
 BLEVEL                                             NUMBER
 LEAF_BLOCKS                                        NUMBER
 DISTINCT_KEYS                                      NUMBER
 AVG_LEAF_BLOCKS_PER_KEY                            NUMBER
 AVG_DATA_BLOCKS_PER_KEY                            NUMBER
 CLUSTERING_FACTOR                                  NUMBER
 NUM_ROWS                                           NUMBER
 SAMPLE_SIZE                                        NUMBER
 LAST_ANALYZED                                      DATE
 BUFFER_POOL                                        VARCHAR2(7)
 USER_STATS                                         VARCHAR2(3)
 PCT_DIRECT_ACCESS                                  NUMBER
 GLOBAL_STATS                                       VARCHAR2(3)
 DOMIDX_OPSTATUS                                    VARCHAR2(6)
 PARAMETERS                                         VARCHAR2(1000)

Previous Topic: using Collection variable???
Next Topic: viewing parameters
Goto Forum:

Current Time: Sun Feb 19 12:58:24 CST 2017

Total time taken to generate the page: 0.04233 seconds