OraFAQ Forum: SQL & PL/SQL » Partition index Tablespace

Home » SQL & PL/SQL » SQL & PL/SQL » Partition index Tablespace

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Partition index Tablespace [message #267191]

Wed, 12 September 2007 12:25

feroze
Messages: 144
Registered: September 2005
Location: chicago

Senior Member

Hi,

Oracle 9.2.0.7

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

TABLESPACE_NAME                PARTITION_NAME
------------------------------ ------------------------------
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.

Thanks

[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

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Regards
Michel

Report message to a moderator

Re: Partition index Tablespace [message #267198 is a reply to message #267191]

Wed, 12 September 2007 13:03

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Nov 13 20:50:38 CST 2025