Results 1 to 10 of 10
  1. #1
    Join Date
    Mar 2004
    Posts
    50

    Arrow Unanswered: how to drop all indexes on a table

    Hi,
    Can we make a script to drop all indexes on a table.

    Thanks,
    Ishan

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Code:
    SPOOL drop_idx.sql
    
    SELECT 'DROP INDEX ' || index_name ||';'
    FROM USER_INDEXES
    WHERE table_name = 'YOUR_TABLE_NAME';
    
    SPOOL OFF;
    @drop_idx
    This might require some modifications, but - basically - that's it.

  3. #3
    Join Date
    Mar 2004
    Posts
    50

    Arrow

    Thanks.

    Basically,what I wanted was a way in which both these steps could be executed together without human intervention.

    Ishan

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    He did include it. The final line "@drop_idx" will execute the spool file that was just created.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Alternatively write a stored procedure which uses execute immediate to do it and then call the stored procedure when its required.

    Alan

  6. #6
    Join Date
    Mar 2004
    Posts
    50

    Arrow

    Thanks Bill.

    I think I am not making myself clear.
    In the script given, first we have to generate the file by spooling.
    Then we have to execute that file.

    I want some way in both these things can be carried out together.

    Thanks,
    Ishan

  7. #7
    Join Date
    Apr 2004
    Posts
    246
    have you actually put any thought into this yet, or your just waiting for us to hand it to you. Littlefoot gave example code - now it's time to turn your brain on. Ideas - why not put his code (which he labeled "code"), into a script, so you can call it. Then change his hard coded table name to a parameter. Any idea how to use parameters in sqlplus? I'll give a hint - &
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  8. #8
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Make a file called drop_indexes.sql

    In the file have the following commands

    -----------------------
    SPOOL drop_idx.sql

    SELECT 'DROP INDEX ' || index_name ||';'
    FROM USER_INDEXES
    WHERE table_name = '&1';

    SPOOL OFF;
    @drop_idx
    -----------------------

    To execute it type (in sqlplus)
    @drop_indexes YOUR_TABLE_NAME_IN_CAPS

    and all the commands in drop_indexes.sql will be run automatically.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  9. #9
    Join Date
    Mar 2004
    Posts
    50

    Arrow

    Thanks Alan. You solved my problem.

    Dear shoblock,
    I wish could have been a bit more nice to preserve the spirit of such forums.
    If i knew of a way to do it, I wont be wasting my as well as others' time writing in this forum.

    Thanks Anyway for your suggestions.

    Ishan

  10. #10
    Join Date
    Mar 2004
    Posts
    50

    Arrow

    Thanks Bill.

    Thats a wonderful idea.

    Ishan

Posting Permissions

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