Getting a ORA-1403 AND 6512 on following script

5 pts.
Tags:
ORA
GETTING ORA 1403 AND -- This script will check indexes to find candidates for rebuilding. -- Run this script in SQL*Plus as a user with SELECT ANY TABLE -- privileges. set serveroutput on size 100000
DECLARE
SQL_STR VARCHAR2(1000);
vOwner SYS.ALL_INDEXES.owner%TYPE; /* Index Owner */
vIdxName SYS.ALL_INDEXES.index_name%TYPE; /* Index Name */
vAnalyze VARCHAR2(100); /* String of Analyze Stmt */
vCursor NUMBER; /* DBMS_SQL cursor */
vNumRows INTEGER; /* DBMS_SQL return rows */
vHeight index_stats.height%TYPE; /* Height of index tree */
vLfRows index_stats.lf_rows%TYPE; /* Index Leaf Rows */
vDLfRows index_stats.del_lf_rows%TYPE; /* Deleted Leaf Rows */
vDLfPerc NUMBER; /* Del lf Percentage */
vMaxHeight NUMBER; /* Max tree height */
vMaxDel NUMBER; /* Max del lf percentage */

count NUMBER;
v_code NUMBER;
v_errm VARCHAR2(64);

CURSOR cGetIdx IS (select OWNER, INDEX_NAME from SYS.ALL_INDEXES
WHERE TABLE_OWNER = 'PATKCDB');
BEGIN
/* Define maximums. This section can be customized. */
vMaxHeight := 3;
vMaxDel := 20;

count :=0;

/* For every index, validate structure */
OPEN cGetIdx;

LOOP
FETCH cGetIdx INTO vOwner,vIdxName;
EXIT WHEN cGetIdx%NOTFOUND;
/* Open DBMS_SQL cursor */
vCursor := DBMS_SQL.OPEN_CURSOR;
/* Set up dynamic string to validate structure */
vAnalyze := 'ANALYZE INDEX ' || vOwner || '.' || vIdxName || ' VALIDATE STRUCTURE';
DBMS_SQL.PARSE(vCursor,vAnalyze,DBMS_SQL.V7);
vNumRows := DBMS_SQL.EXECUTE(vCursor);
/* Close DBMS_SQL cursor */
DBMS_SQL.CLOSE_CURSOR(vCursor);
/* Does index need rebuilding? */
/* If so, then generate command */
SELECT height,lf_rows,del_lf_rows INTO vHeight,vLfRows,vDLfRows
FROM INDEX_STATS;
IF vDLfRows = 0 THEN /* handle case where div by zero */
vDLfPerc := 0;
ELSE
vDLfPerc := (vDLfRows / vLfRows) * 100;
END IF;
IF (vHeight > vMaxHeight) OR (vDLfPerc > vMaxDel) THEN
SQL_STR:='ALTER INDEX '|| vOwner ||'.'|| vIdxName ||' REBUILD ONLINE NOLOGGING PARALLEL 8';
EXECUTE IMMEDIATE SQL_STR;
DBMS_OUTPUT.PUT_LINE('ALTER INDEX ' || vOwner || '.' || vIdxName || 'REBUILD ONLINE NOLOGGING PARALLEL 8;');
END IF;

END LOOP;

EXCEPTION
WHEN OTHERS THEN
v_code := SQLCODE;
v_errm := SUBSTR(SQLERRM, 1 , 64);
DBMS_OUTPUT.PUT_LINE('Error code ' || v_code || ': ' || v_errm);
DBMS_OUTPUT.PUT_LINE('ALTER INDEX ' || vOwner || '.' || vIdxName || 'INDEX ERROR;');

CLOSE cGetIdx;

commit;

END;

/
0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • carlosdl
    ORA-01403 is 'no data found' and is usually caused by SELECT statements.

    I would try removing the WHEN-OTHERS exception handler to see the original error message, which will most likely tell you the line of code that caused the exception.

    I would also try putting some more dbms_output debug messages, to identify what is the part of the code causing the error.
    84,745 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: