Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2009
    Posts
    3

    Unanswered: Why does this procedure fail?

    Hi Everybody!

    I'm new in Oracle world. I need to run the next PLSQL statement:

    BEGIN
    FOR test IN (
    select inex from user_idx
    where tablespace_Name= 'TB0012'
    ) LOOP
    Alter Index test.inex Rebuild Online Tablespace TB0012;
    END LOOP;
    END

    But when I try to run it, it fails. Could you tell me what it's wrong?

    Thx in advance!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    DDL can not be done within PL/SQL

    You must (ab)use EXECUTE IMMEDIATE statement

    1) what you want can be done with SQL; no need for PL/SQL
    2) what you are attempting is a waste of system resources.
    Last edited by anacedent; 06-11-09 at 15:29.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Next time cut and paste into the issue, your code was full of typos.

    Code:
    BEGIN
     FOR test IN (   select index_name from user_indexes
                          where tablespace_Name= 'TB0012' ) LOOP
       execute immediate 'Alter Index test.index_name Rebuild Online Tablespace TB0012';
    END LOOP;
    END;
    /
    it should be done in sql as a simple

    Alter Index test.index_name Rebuild Online Tablespace TB0012;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Dec 2007
    Posts
    253
    Quote Originally Posted by beilstwh
    it should be done in sql as a simple

    Alter Index test.index_name Rebuild Online Tablespace TB0012;
    Although, even more likely, it shouldn't be done at all. But that's a whole other can of worms.

  5. #5
    Join Date
    Jun 2009
    Posts
    3
    Hi everybody!

    Thxs for your explanations (sorry for the typos, It was a transcription problem ).

    I know that one "alter index" should be done in a simple sql. But what I need (and I think I'm doing very bad) is:

    1. First I'd like to do the next SQL:

    "Select index_name from user_indexes where tablespace_Name = 'TB0012'"

    2. For each index_name from this SQL I'd like to do an "Alter index". For example, If the SQL result is

    index_name

    idx001
    idx002
    idx003
    ...
    idx00n

    I need to do n-SQLs:

    Alter Index 'idx001' Rebuild Online Tablespace TB0012;
    Alter Index 'idx002' Rebuild Online Tablespace TB0012;
    ...
    Alter Index 'idx00n' Rebuild Online Tablespace TB0012;

    I think it's necesary to use a variable, or a cursor or something like this, but I don't know how to do it. Could you help me? Thxs again!

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I need to do n-SQLs:
    No you don't.
    Prove it.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Dec 2007
    Posts
    253
    The crucial thing to remember here is that rebuilding indexes is very much the exception rather than the rule. Read this thoroughly.

  8. #8
    Join Date
    Jun 2009
    Posts
    3
    Thx, your explanations were very useful!

Posting Permissions

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