Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003
    Location
    kualalumpur
    Posts
    89

    Unanswered: using execute immediate

    Iam trying to rebuild all the indexes in the scheama at once.. using the following procedure..
    but could not.. please help me..

    CREATE OR REPLACE PROCEDURE REBUILDINDX IS
    CURSOR C IS SELECT INDEX_NAME FROM USER_INDEXES;
    INDX_NAME VARCHAR2(40);
    BEGIN
    FOR CREC IN C LOOP
    INDX_NAME := CREC.INDEX_NAME;
    --DBMS_OUTPUT.PUT_LINE(INDX_NAME);
    EXECUTE IMMEDIATE 'ALTER INDEX INDX_NAME REBUILD TABLESPACE INDX';
    END LOOP;
    END ;

    Iam getting the following error..
    ERROR at line 1:
    ORA-01418: specified index does not exist
    ORA-06512: at "CDIS3.REBUILDINDX", line 8
    ORA-06512: at line 1

    when I uncomment the dbms_output.. it displays all the indexes in the scheama..
    please help me to solve this
    thanks in advance..
    regards
    sridhar

  2. #2
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482

    Re: using execute immediate

    Originally posted by sridharreddy_d
    Iam trying to rebuild all the indexes in the scheama at once.. using the following procedure..
    but could not.. please help me..

    CREATE OR REPLACE PROCEDURE REBUILDINDX IS
    CURSOR C IS SELECT INDEX_NAME FROM USER_INDEXES;
    INDX_NAME VARCHAR2(40);
    BEGIN
    FOR CREC IN C LOOP
    INDX_NAME := CREC.INDEX_NAME;
    --DBMS_OUTPUT.PUT_LINE(INDX_NAME);
    EXECUTE IMMEDIATE 'ALTER INDEX INDX_NAME REBUILD TABLESPACE INDX';
    END LOOP;
    END ;

    Iam getting the following error..
    ERROR at line 1:
    ORA-01418: specified index does not exist
    ORA-06512: at "CDIS3.REBUILDINDX", line 8
    ORA-06512: at line 1

    when I uncomment the dbms_output.. it displays all the indexes in the scheama..
    please help me to solve this
    thanks in advance..
    regards
    sridhar
    Try to use schema prefix... for example:

    ALTER INDEX USERSCHEMA.INDX_NAME REBUILD TABLESPACE INDX

  3. #3
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    Hi ,
    Please check the correct code

    CREATE OR REPLACE PROCEDURE REBUILDINDX IS
    CURSOR C IS SELECT INDEX_NAME FROM USER_INDEXES;
    INDX_NAME VARCHAR2(40);
    sqlstmt VARCHAR2(200);
    BEGIN
    FOR CREC IN C LOOP
    INDX_NAME := CREC.INDEX_NAME;
    --DBMS_OUTPUT.PUT_LINE(INDX_NAME);
    sqlstmt:='ALTER INDEX '|| INDX_NAME ||'REBUILD TABLESPACE INDX';
    EXECUTE IMMEDIATE sqlstmt;
    END LOOP;
    END ;
    Pagnint
    (No need to search web before posting new question)

  4. #4
    Join Date
    Jan 2004
    Location
    India
    Posts
    191
    sorry try with this

    CREATE OR REPLACE PROCEDURE REBUILDINDX IS
    CURSOR C IS SELECT INDEX_NAME FROM USER_INDEXES;
    INDX_NAME VARCHAR2(40);
    sqlstmt VARCHAR2(200);
    BEGIN
    FOR CREC IN C LOOP
    INDX_NAME := CREC.INDEX_NAME;
    --DBMS_OUTPUT.PUT_LINE(INDX_NAME);
    sqlstmt:='ALTER INDEX '|| INDX_NAME ||' REBUILD TABLESPACE INDX';
    EXECUTE IMMEDIATE sqlstmt;
    END LOOP;
    END ;

    Thanks
    Pagnint
    (No need to search web before posting new question)

Posting Permissions

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