Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2003
    Location
    Germany
    Posts
    5

    Question Unanswered: Howto rename an index (Oracle 9.0.1.4)

    Hi,

    I've to rename some indexes on very large tables (Oracle 9.0.1.4) and a I don't want to create this indexes new because of 24*7. A 10 minute maintenance is ok, but I think that a index create to a 500,000,000 recordset table will take longer ;-)

    Additionally I've to rename some PKs and FKs...

    Is there a way with updates on the data dictionary tables?

    Bye
    Matze

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Howto rename an index (Oracle 9.0.1.4)

    This works in 9.2, don't know about your version:

    alter index X rename to Y;

    alter table T rename constraint X to Y;

  3. #3
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow Online

    Hi
    I was reading through one manual for taking the OCP, and read that there is something called "UPDATE ONLINE".

    When you alter a table for primary or foreign keys, it would make sense to use ONLINE keyword in the ALTER table query. This would mean that there would minimum number of locks on the table, and the users of the DB wont notice the change being taking place.

    Thanx and Regards
    Aruneesh

  4. #4
    Join Date
    Sep 2003
    Location
    Germany
    Posts
    5

    Re: Howto rename an index (Oracle 9.0.1.4)

    Originally posted by andrewst
    This works in 9.2, don't know about your version:

    alter index X rename to Y;

    alter table T rename constraint X to Y;
    Hi Tony,

    thank you for your informations, but we're running over 30 instances and a change to 9.2 will be done in december... I've to change the indexes till 1th October :-(

    Bye
    Matthias

  5. #5
    Join Date
    Sep 2003
    Location
    Germany
    Posts
    5

    Re: Online

    Originally posted by aruneeshsalhotr
    Hi
    I was reading through one manual for taking the OCP, and read that there is something called "UPDATE ONLINE".

    When you alter a table for primary or foreign keys, it would make sense to use ONLINE keyword in the ALTER table query. This would mean that there would minimum number of locks on the table, and the users of the DB wont notice the change being taking place.

    Thanx and Regards
    Aruneesh
    Hi Aruneesh,

    thanks for your info. I'll go to check if this works fine with 9.0.1.4...

    Bye
    Matze

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    alter index rename is in 8.1.7 so you should be OK.

    Alan

  7. #7
    Join Date
    Sep 2003
    Location
    Germany
    Posts
    5

    Cool It works... Thanks to all!

    Hi,

    many thanks to all !!!

    It works fine with "alter index xyz_old rename to xyz_new;".

    The names of the PKs + FKs have to be updated in the SYS.CON$ table...

    Bye

    Matze

  8. #8
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    be careful updating the SYS tables directly, you may need to do shared pool flush or a bounce to get it working. Try on a test system to confirm it is OK.

    Alan

  9. #9
    Join Date
    Sep 2003
    Location
    Germany
    Posts
    5

    Smile

    Originally posted by AlanP
    be careful updating the SYS tables directly, you may need to do shared pool flush or a bounce to get it working. Try on a test system to confirm it is OK.

    Alan
    Hi Alan,

    I'm always very careful because I'm the DBA ;-)

    Thanks for your information. I've tested it on a test system and on the production system there will be no user at the moment of the update (next maintenance window). After this updates we will shutdown + startup the databases. I think that this is safe enough.

    Matze

  10. #10
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198
    Originally posted by Matze
    Hi Alan,

    I'm always very careful because I'm the DBA ;-)

    Thanks for your information. I've tested it on a test system and on the production system there will be no user at the moment of the update (next maintenance window). After this updates we will shutdown + startup the databases. I think that this is safe enough.

    Matze
    The one I would look into with that table, and indexes is to start partitioning them.

    I took a 4 millon+ record table and split it into a monthly partitions. The access time for an indiviual record went for 0 to 60 msec but to pull a group by recordset for a couple of months went from 1400+msec down to about 350. If an end user notices a 60msec increase, I'll eat his hat (just give me salt and tobasco ).
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •