Re: Renaming Partitions
From: onedbguru <onedbguru_at_yahoo.com>
Date: Sat, 19 Feb 2011 17:43:21 -0800 (PST)
Message-ID: <41ee1a5b-59d7-4791-a201-27418d92b061_at_o10g2000vbg.googlegroups.com>
On Feb 18, 2:29 pm, Adrian <bull..._at_ffoil.org.uk> wrote:
> In message <pan.2011.02.17.22.47..._at_email.here.invalid>, Mladen Gogala
> <n..._at_email.here.invalid> writes
>
>
>
>
>
>
>
>
>
> >On Thu, 17 Feb 2011 21:54:58 +0000, Adrian wrote:
>
> >> I know I can use ALTER INDEX x RENAME PARTITION y TO z, but to do that I
> >> have to work out what partition SYS_xxxx really is.
>
> >I was just about to point you to the documentation which says that:
>
> >http://tinyurl.com/4hstv83
>
> >There is even a simple example:
>
> >Renaming an Index Partition: Example The following statement renames an
> >index partition of the cost_ix index (created in "Creating a Range-
> >Partitioned Global Index: Example"):
>
> >ALTER INDEX cost_ix
> > RENAME PARTITION p3 TO p3_Q3;
>
> >You apparently know this. I don't understand what the question is?
>
> The question is how do I identify what the partition should be called.
> We are partitioning by date. When the partition is created, I can run
> an alter table command that says, for the table my_table rename the
> partition for February 18 2011 to my_table_110218. I don't need to find
> out that it is created as SYS_123 and then run "ALTER TABLE table_name
> RENAME PARTITION SYS_123 TO my_table_110218. What I would like to be
> able to do is something similar for the indexes. Is this possible, and
> if so how (without going to PL/SQL) ?
>
> Adrian
> --
> To Reply :
> replace "bulleid" with "adrian" - all mail to bulleid is rejected
> Sorry for the rigmarole, If I want spam, I'll go to the shops
> Every time someone says "I don't believe in trolls", another one dies.
Date: Sat, 19 Feb 2011 17:43:21 -0800 (PST)
Message-ID: <41ee1a5b-59d7-4791-a201-27418d92b061_at_o10g2000vbg.googlegroups.com>
On Feb 18, 2:29 pm, Adrian <bull..._at_ffoil.org.uk> wrote:
> In message <pan.2011.02.17.22.47..._at_email.here.invalid>, Mladen Gogala
> <n..._at_email.here.invalid> writes
>
>
>
>
>
>
>
>
>
> >On Thu, 17 Feb 2011 21:54:58 +0000, Adrian wrote:
>
> >> I know I can use ALTER INDEX x RENAME PARTITION y TO z, but to do that I
> >> have to work out what partition SYS_xxxx really is.
>
> >I was just about to point you to the documentation which says that:
>
> >http://tinyurl.com/4hstv83
>
> >There is even a simple example:
>
> >Renaming an Index Partition: Example The following statement renames an
> >index partition of the cost_ix index (created in "Creating a Range-
> >Partitioned Global Index: Example"):
>
> >ALTER INDEX cost_ix
> > RENAME PARTITION p3 TO p3_Q3;
>
> >You apparently know this. I don't understand what the question is?
>
> The question is how do I identify what the partition should be called.
> We are partitioning by date. When the partition is created, I can run
> an alter table command that says, for the table my_table rename the
> partition for February 18 2011 to my_table_110218. I don't need to find
> out that it is created as SYS_123 and then run "ALTER TABLE table_name
> RENAME PARTITION SYS_123 TO my_table_110218. What I would like to be
> able to do is something similar for the indexes. Is this possible, and
> if so how (without going to PL/SQL) ?
>
> Adrian
> --
> To Reply :
> replace "bulleid" with "adrian" - all mail to bulleid is rejected
> Sorry for the rigmarole, If I want spam, I'll go to the shops
> Every time someone says "I don't believe in trolls", another one dies.
What is your problem with PL/SQL? look at high_value in dba_tab_partitions. this is a "long" field, so you will need to do some parsing. I have a "get_high" procedure I use to find this value, but not here at home. Once you create the procedure and grant it to public or whatever your security model is, you should have no problems. Received on Sat Feb 19 2011 - 19:43:21 CST