DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Rebuild indexes

  1. #1
    Join Date
    Jul 2001
    Location
    Montreal
    Posts
    222

    Rebuild indexes

    I need to move 1500 indexes to another tablespace. Instead of creating a script to rebuild the indexes, is there a DBS command that I can run? Please let me know. thanks.

  2. #2
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    did you thought about import ?
    $import with rows=n and ignore=y grants=n .This will take the same time to create indexs thru script, only avoids creating script part.

    transpotable tablespaces you can take advantage of (8i onwards) if you have all indexes and in one tablespaces and figureout way to plugin/attach this tablespace.
    Reddy,Sam

  3. #3
    Join Date
    Jul 2001
    Location
    Montreal
    Posts
    222
    Is there a "dbms_schema_rebuild_index" command or something similar?

  4. #4
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185

    script

    You can use something *like*

    select "ALTER INDEX "¢®¢® index_name ¢®¢® "TABLESPACE blablabla"
    from dba_indexes
    where ...

    The basic idea works to change the tablespace. You need to replace the ¢®¢® with concatenation and identify screening conditions.

    Good luck.
    David Knight
    OCP DBA 8i, 9i, 10g

  5. #5
    Join Date
    Apr 2003
    Posts
    8
    Rebuild indexes
    I need to move 1500 indexes to another tablespace. Instead of creating a script to rebuild the indexes, is there a DBS command that I can run? Please let me

    -------------------------------------------------------------

    Hi,
    you can do it by writing a PL/SQL code that uses dynamic SQL.

    i.e,

    DECLARE
    CURSOR cur_name IS
    (LIST OF all the index from dba_indexes);
    BEGIN
    open the cursor for dynamic sql.
    FOR vname IN cur_name LOOP
    you can use dynamic sql here.
    END LOOP;
    close the opened cursor.
    END;
    /

    Thanks..
    Bhavin

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Take the opportunity to order the result of the select from dba_indexes by table_name -- if you're just going to run the generated script you'll get some performance benefits.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by slimdave
    Take the opportunity to order the result of the select from dba_indexes by table_name -- if you're just going to run the generated script you'll get some performance benefits.
    I know I shouldn't even touch this with a 10 foot pole, but...

    Why?
    Jeff Hunter

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    You know, cancel that comment -- i was thinking of the situation in which you are rebuilding unusable indexes, where the table itself gets read. In this case it's the "old" indexes that get read, so never mind.

    Too much data warehousing is taking it's toll on my mind. We rebuild unusable indexes so often it's developed into an obsession.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    1500 indexes to another tablespace... one has to ask the question... why are 1500 indees in the incorrect tablespace in teh first place? And what is the qualifier to detemine they are in the incorect tablespace. Is it a space issue or a performance issue, or simple a cosmetic administrative issue?
    OCP 8i, 9i DBA
    Brisbane Australia

  10. #10
    Join Date
    Oct 2000
    Location
    US
    Posts
    7
    U r right johnson, I guess its an admistrative issue. According to me, i think moving the indexes in seperate tablespace and then take a backup of that, is the best idea .. isn't it guys??
    Born with Aim

Posting Permissions

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


Click Here to Expand Forum to Full Width