Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2009
    Posts
    17

    Unhappy Unanswered: ORA-08103 : Object no longer exist

    Hi all,

    I am facing strange problem.


    On executing following procedure I am getting this error

    ORA-08103: object no longer exist

    Can anyone please advise.

    I have created following objects
    ------------------------------------------------------------------------

    CREATE OR REPLACE TYPE TY_CURRENCY_CD IS VARRAY(1000) OF VARCHAR2(3);
    CREATE OR REPLACE TYPE TY_PROD_TYPE IS VARRAY(1000) OF VARCHAR2(3);
    CREATE OR REPLACE TYPE TY_RATELIST_ID IS VARRAY(1000) OF VARCHAR2(2);
    CREATE OR REPLACE TYPE TY_RATES_TYPE IS VARRAY(1000) OF VARCHAR2(3);

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

    CREATE TABLE MIN_CRITERIA_IDS_TEMP (MIN_CRITERIA_ID NUMBER(30));

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


    PROCEDURE GET_CRITERIA_MIN_IDS_SP(P_RATELIST_STR VARCHAR2,
    P_CURRENCY_STR VARCHAR2,
    P_PROD_TYPE_STR VARCHAR2,
    P_RATES_STR VARCHAR2,
    P_CUR_OUT OUT SYS_REFCURSOR)
    IS
    string_to_parse varchar2(4000) := P_RATELIST_STR; --'ac|def|ghi|klmno|pqrst';

    V_MIN_CRITERIA_ID NUMBER;
    V_ERROR NUMBER;
    l_count number;
    l_value varchar2(4000);
    v_ratelist ty_ratelist_id;
    v_currency ty_currency_cd;
    v_prod_type ty_prod_type ;
    v_rates ty_rates_type;
    begin

    EXECUTE IMMEDIATE 'TRUNCATE TABLE MIN_CRITERIA_IDS_TEMP';
    v_ratelist := ty_ratelist_id();
    v_currency :=ty_currency_cd();
    v_prod_type := ty_prod_type() ;
    v_rates :=ty_rates_type() ;

    --Parsing RateList
    string_to_parse := string_to_parse||'|';
    --string_to_parse :='T|5|';
    l_count := length(string_to_parse) - length(replace(string_to_parse,'|',''));
    -- In oracle 11g use regexp_count to determine l_count

    --RATELIST
    for i in 1 .. l_count loop
    select regexp_substr(string_to_parse,'[^|]+',1,i)
    into l_value
    from dual;
    v_ratelist.extend;
    --Putting values in Ratelist array
    v_ratelist(i) :=l_value;
    end loop;

    --CURRNCY
    -------------------Parsing Currency
    string_to_parse :=P_CURRENCY_STR;
    --Parsing RateList
    string_to_parse := string_to_parse||'|';
    l_count := length(string_to_parse) - length(replace(string_to_parse,'|',''));
    -- In oracle 11g use regexp_count to determine l_count
    for i in 1 .. l_count loop
    select regexp_substr(string_to_parse,'[^|]+',1,i)
    into l_value
    from dual;
    v_currency.extend;

    dbms_output.put_line(l_value);
    v_currency(i) :=l_value;
    -- dbms_output.put_line('l_value');
    --dbms_output.put_line(v_ratelist(1));
    end loop;

    -------------------Parsing productss
    string_to_parse :=P_PROD_TYPE_STR;
    --Parsing RateList
    string_to_parse := string_to_parse||'|';
    l_count := length(string_to_parse) - length(replace(string_to_parse,'|',''));
    -- In oracle 11g use regexp_count to determine l_count

    --PROD TYPE
    for i in 1 .. l_count loop
    select regexp_substr(string_to_parse,'[^|]+',1,i)
    into l_value
    from dual;
    v_prod_type.extend;
    --Put value in array
    v_prod_type(i) :=l_value;
    end loop;

    --RATES
    -------------------Parsing Rates
    string_to_parse :=P_RATES_STR;


    --Parsing RateList
    string_to_parse := string_to_parse||'|';
    l_count := length(string_to_parse) - length(replace(string_to_parse,'|',''));
    -- In oracle 11g use regexp_count to determine l_count



    for i in 1 .. l_count loop
    select regexp_substr(string_to_parse,'[^|]+',1,i)
    into l_value
    from dual;
    v_rates.extend;

    dbms_output.put_line(l_value);
    v_rates(i) :=l_value;
    -- dbms_output.put_line('l_value');
    --dbms_output.put_line(v_ratelist(1));
    end loop;

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

    FOR I IN v_ratelist.first .. v_ratelist.last LOOP
    FOR J IN v_currency.first..v_currency.last LOOP
    FOR K IN v_prod_type.first..v_prod_type.last LOOP
    FOR L IN v_rates.first..v_rates.last LOOP
    BEGIN
    -- INSERT INTO MIN_CRITERIA_IDS_TEMP

    SELECT MIN(CRITERIA_ID)
    INTO V_MIN_CRITERIA_ID
    FROM CURRENCY_CRITERIA
    WHERE TRIM(RATELIST_ID)=v_ratelist(I)
    AND TRIM(CURRENCY_CD)=v_currency(J)
    AND TRIM(PROD_TYPE)=v_prod_type(K)
    AND TRIM(RATES)=v_rates(L);

    IF V_MIN_CRITERIA_ID IS NULL THEN
    EXECUTE IMMEDIATE 'TRUNCATE TABLE MIN_CRITERIA_IDS_TEMP';
    --PKG_COMMON.INSERT_VIPER_ERROR_LOGS_SP();
    V_ERROR := 1;
    --V_ERROR := 0;
    EXIT;
    END IF;
    INSERT INTO MIN_CRITERIA_IDS_TEMP VALUES(V_MIN_CRITERIA_ID);
    END;
    END LOOP;
    IF V_ERROR = 1 THEN
    EXIT;
    END IF;
    END LOOP;
    IF V_ERROR = 1 THEN
    EXIT;
    END IF;
    END LOOP;
    IF V_ERROR = 1 THEN
    EXIT;
    END IF;
    END LOOP;
    OPEN P_CUR_OUT FOR 'SELECT MIN_CRITERIA_ID FROM MIN_CRITERIA_IDS_TEMP';

    exception
    when others then
    pkg_common.INSERT_VIPER_ERROR_LOGS_SP();
    end;

  2. #2
    Join Date
    Aug 2009
    Posts
    262
    ORA-08103 object no longer exists

    Cause: The object has been deleted by another user since the operation began.

    Action: Remove references to the object.


    It maybe that the corrupted rowID is causing this to happen..

  3. #3
    Join Date
    Aug 2009
    Posts
    262
    create or replace PROCEDURE GET_CRITERIA_MIN_IDS_SP(P_RATELIST_STR VARCHAR2,
    P_CURRENCY_STR VARCHAR2,
    P_PROD_TYPE_STR VARCHAR2,
    P_RATES_STR VARCHAR2,
    P_CUR_OUT OUT SYS_REFCURSOR)
    IS
    string_to_parse varchar2(4000) := P_RATELIST_STR; --'ac|def|ghi|klmno|pqrst';

    V_MIN_CRITERIA_ID NUMBER;
    V_ERROR NUMBER;
    l_count number;
    l_value varchar2(4000);
    v_ratelist ty_ratelist_id;
    v_currency ty_currency_cd;
    v_prod_type ty_prod_type ;
    v_rates ty_rates_type;
    begin

    EXECUTE IMMEDIATE 'TRUNCATE TABLE MIN_CRITERIA_IDS_TEMP';
    v_ratelist := ty_ratelist_id();
    v_currency :=ty_currency_cd();
    v_prod_type := ty_prod_type() ;
    v_rates :=ty_rates_type() ;

    --Parsing RateList
    string_to_parse := string_to_parse||'|';
    --string_to_parse :='T|5|';
    l_count := length(string_to_parse) - length(replace(string_to_parse,'|',''));
    -- In oracle 11g use regexp_count to determine l_count

    --RATELIST
    for i in 1 .. l_count loop
    select regexp_substr(string_to_parse,'[^|]+',1,i)
    into l_value
    from dual;
    v_ratelist.extend;
    --Putting values in Ratelist array
    v_ratelist(i) :=l_value;
    end loop;

    --CURRNCY
    -------------------Parsing Currency
    string_to_parse :=P_CURRENCY_STR;
    --Parsing RateList
    string_to_parse := string_to_parse||'|';
    l_count := length(string_to_parse) - length(replace(string_to_parse,'|',''));
    -- In oracle 11g use regexp_count to determine l_count
    for i in 1 .. l_count loop
    select regexp_substr(string_to_parse,'[^|]+',1,i)
    into l_value
    from dual;
    v_currency.extend;

    dbms_output.put_line(l_value);
    v_currency(i) :=l_value;
    -- dbms_output.put_line('l_value');
    --dbms_output.put_line(v_ratelist(1));
    end loop;

    -------------------Parsing productss
    string_to_parse :=P_PROD_TYPE_STR;
    --Parsing RateList
    string_to_parse := string_to_parse||'|';
    l_count := length(string_to_parse) - length(replace(string_to_parse,'|',''));
    -- In oracle 11g use regexp_count to determine l_count

    --PROD TYPE
    for i in 1 .. l_count loop
    select regexp_substr(string_to_parse,'[^|]+',1,i)
    into l_value
    from dual;
    v_prod_type.extend;
    --Put value in array
    v_prod_type(i) :=l_value;
    end loop;

    --RATES
    -------------------Parsing Rates
    string_to_parse :=P_RATES_STR;


    --Parsing RateList
    string_to_parse := string_to_parse||'|';
    l_count := length(string_to_parse) - length(replace(string_to_parse,'|',''));
    -- In oracle 11g use regexp_count to determine l_count



    for i in 1 .. l_count loop
    select regexp_substr(string_to_parse,'[^|]+',1,i)
    into l_value
    from dual;
    v_rates.extend;

    dbms_output.put_line(l_value);
    v_rates(i) :=l_value;
    -- dbms_output.put_line('l_value');
    --dbms_output.put_line(v_ratelist(1));
    end loop;

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

    FOR I IN v_ratelist.first .. v_ratelist.last LOOP
    FOR J IN v_currency.first..v_currency.last LOOP
    FOR K IN v_prod_type.first..v_prod_type.last LOOP
    FOR L IN v_rates.first..v_rates.last LOOP
    BEGIN
    -- INSERT INTO MIN_CRITERIA_IDS_TEMP

    --SELECT MIN(CRITERIA_ID)
    --INTO V_MIN_CRITERIA_ID
    --FROM CURRENCY_CRITERIA <- Compilation errors for PROCEDURE TEST1.GET_CRITERIA_MIN_IDS_SP

    Error: PL/SQL: ORA-00942: table or view does not exist
    Line: 114
    Text: AND TRIM(CURRENCY_CD)=v_currency(J)

    Error: PL/SQL: SQL Statement ignored
    Line: 112
    Text: FROM CURRENCY_CRITERIA


    --WHERE TRIM(RATELIST_ID)=v_ratelist(I)
    --AND TRIM(CURRENCY_CD)=v_currency(J)
    --AND TRIM(PROD_TYPE)=v_prod_type(K)
    --AND TRIM(RATES)=v_rates(L);

    IF V_MIN_CRITERIA_ID IS NULL THEN
    EXECUTE IMMEDIATE 'TRUNCATE TABLE MIN_CRITERIA_IDS_TEMP';
    --PKG_COMMON.INSERT_VIPER_ERROR_LOGS_SP();
    V_ERROR := 1;
    --V_ERROR := 0;
    EXIT;
    END IF;
    INSERT INTO MIN_CRITERIA_IDS_TEMP VALUES(V_MIN_CRITERIA_ID);
    END;
    END LOOP;
    IF V_ERROR = 1 THEN
    EXIT;
    END IF;
    END LOOP;
    IF V_ERROR = 1 THEN
    EXIT;
    END IF;
    END LOOP;
    IF V_ERROR = 1 THEN
    EXIT;
    END IF;
    END LOOP;
    OPEN P_CUR_OUT FOR 'SELECT MIN_CRITERIA_ID FROM MIN_CRITERIA_IDS_TEMP';

    --exception
    --when others then
    --pkg_common.INSERT_VIPER_ERROR_LOGS_SP(); <---Compilation errors for PROCEDURE TEST1.GET_CRITERIA_MIN_IDS_SP

    Error: PLS-00201: identifier 'PKG_COMMON.INSERT_VIPER_ERROR_LOGS_SP' must be declared
    Line: 146

    Error: PL/SQL: Statement ignored
    Line: 146



    end;



    ================================================== =

  4. #4
    Join Date
    Aug 2009
    Posts
    262
    create or replace PROCEDURE GET_CRITERIA_MIN_IDS_SP(P_RATELIST_STR VARCHAR2,
    P_CURRENCY_STR VARCHAR2,
    P_PROD_TYPE_STR VARCHAR2,
    P_RATES_STR VARCHAR2,
    P_CUR_OUT OUT SYS_REFCURSOR)
    IS
    string_to_parse varchar2(4000) := P_RATELIST_STR; --'ac|def|ghi|klmno|pqrst';

    V_MIN_CRITERIA_ID NUMBER;
    V_ERROR NUMBER;
    l_count number;
    l_value varchar2(4000);
    v_ratelist ty_ratelist_id;
    v_currency ty_currency_cd;
    v_prod_type ty_prod_type ;
    v_rates ty_rates_type;
    begin

    EXECUTE IMMEDIATE 'TRUNCATE TABLE MIN_CRITERIA_IDS_TEMP';
    v_ratelist := ty_ratelist_id();
    v_currency :=ty_currency_cd();
    v_prod_type := ty_prod_type() ;
    v_rates :=ty_rates_type() ;

    --Parsing RateList
    string_to_parse := string_to_parse||'|';
    --string_to_parse :='T|5|';
    l_count := length(string_to_parse) - length(replace(string_to_parse,'|',''));
    -- In oracle 11g use regexp_count to determine l_count

    --RATELIST
    for i in 1 .. l_count loop
    select regexp_substr(string_to_parse,'[^|]+',1,i)
    into l_value
    from dual;
    v_ratelist.extend;
    --Putting values in Ratelist array
    v_ratelist(i) :=l_value;
    end loop;

    --CURRNCY
    -------------------Parsing Currency
    string_to_parse :=P_CURRENCY_STR;
    --Parsing RateList
    string_to_parse := string_to_parse||'|';
    l_count := length(string_to_parse) - length(replace(string_to_parse,'|',''));
    -- In oracle 11g use regexp_count to determine l_count
    for i in 1 .. l_count loop
    select regexp_substr(string_to_parse,'[^|]+',1,i)
    into l_value
    from dual;
    v_currency.extend;

    dbms_output.put_line(l_value);
    v_currency(i) :=l_value;
    -- dbms_output.put_line('l_value');
    --dbms_output.put_line(v_ratelist(1));
    end loop;

    -------------------Parsing productss
    string_to_parse :=P_PROD_TYPE_STR;
    --Parsing RateList
    string_to_parse := string_to_parse||'|';
    l_count := length(string_to_parse) - length(replace(string_to_parse,'|',''));
    -- In oracle 11g use regexp_count to determine l_count

    --PROD TYPE
    for i in 1 .. l_count loop
    select regexp_substr(string_to_parse,'[^|]+',1,i)
    into l_value
    from dual;
    v_prod_type.extend;
    --Put value in array
    v_prod_type(i) :=l_value;
    end loop;

    --RATES
    -------------------Parsing Rates
    string_to_parse :=P_RATES_STR;


    --Parsing RateList
    string_to_parse := string_to_parse||'|';
    l_count := length(string_to_parse) - length(replace(string_to_parse,'|',''));
    -- In oracle 11g use regexp_count to determine l_count



    for i in 1 .. l_count loop
    select regexp_substr(string_to_parse,'[^|]+',1,i)
    into l_value
    from dual;
    v_rates.extend;

    dbms_output.put_line(l_value);
    v_rates(i) :=l_value;
    -- dbms_output.put_line('l_value');
    --dbms_output.put_line(v_ratelist(1));
    end loop;

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

    FOR I IN v_ratelist.first .. v_ratelist.last LOOP
    FOR J IN v_currency.first..v_currency.last LOOP
    FOR K IN v_prod_type.first..v_prod_type.last LOOP
    FOR L IN v_rates.first..v_rates.last LOOP
    BEGIN
    -- INSERT INTO MIN_CRITERIA_IDS_TEMP

    --SELECT MIN(CRITERIA_ID)
    --INTO V_MIN_CRITERIA_ID
    --FROM CURRENCY_CRITERIA
    --WHERE TRIM(RATELIST_ID)=v_ratelist(I)
    --AND TRIM(CURRENCY_CD)=v_currency(J)
    --AND TRIM(PROD_TYPE)=v_prod_type(K)
    --AND TRIM(RATES)=v_rates(L);

    IF V_MIN_CRITERIA_ID IS NULL THEN
    EXECUTE IMMEDIATE 'TRUNCATE TABLE MIN_CRITERIA_IDS_TEMP';
    --PKG_COMMON.INSERT_VIPER_ERROR_LOGS_SP();
    V_ERROR := 1;
    --V_ERROR := 0;
    EXIT;
    END IF;
    INSERT INTO MIN_CRITERIA_IDS_TEMP VALUES(V_MIN_CRITERIA_ID);
    END;
    END LOOP;
    IF V_ERROR = 1 THEN
    EXIT;
    END IF;
    END LOOP;
    IF V_ERROR = 1 THEN
    EXIT;
    END IF;
    END LOOP;
    IF V_ERROR = 1 THEN
    EXIT;
    END IF;
    END LOOP;
    OPEN P_CUR_OUT FOR 'SELECT MIN_CRITERIA_ID FROM MIN_CRITERIA_IDS_TEMP';

    --exception
    --when others then
    --pkg_common.INSERT_VIPER_ERROR_LOGS_SP();
    end;



    successfully compiled and created .


    check out your exception section and the table "CURRENCY_CRITERIA" ,

  5. #5
    Join Date
    Aug 2009
    Posts
    17
    Thanks mishaalsy,

    Some time I am getting following error

    ORA-01410: invalid ROWID

    Can you please advice what I need to do to overcome this.
    I have analyzed currency_criteria table.

    I also could not undestand your message
    "check out your exception section and the table "CURRENCY_CRITERIA"



    Regards,
    Sandeep

  6. #6
    Join Date
    Aug 2009
    Posts
    262
    Since i donot have the table structure (ddl) and sample insert which you use to populate the table. Also i donot have your softwae version. it is difficult for me to tell the exact pin pointed solution.

    "check out your exception section and the table "CURRENCY_CRITERIA" means

    the package "pkg_common.INSERT_VIPER_ERROR_LOGS_SP();" and the whole line is invalid , it is not executing the way you think it should.

    check currency_criteria table for existence , also for invalid rowid .

    try simple sql > select count(*) from tablename;

    if it still gives you the ORA-08103: object no longer exist then check for corrupted rowID as it is causing this to happen..

    also check for your indexs if any index got corrupted ?

    You can verify it by this query.

    select * from dba_indexes where status<>'VALID'

    ROWIDS are
    physical addresses -- the data got put in in different places after the truncate then it
    did after an insert.

    In fact, the DELETE/INSERT could equally easily throw the invalid rowid error as well.

    ROWIDS are physical addresses and only exist for the life of a row. They can be reused
    by some new row later.

    and there may be some chances...it may occur...
    chances of index getting corrupted.


    It can happen due to another session is truncating table or deleting rows.


    also there is another case .

    .Can you please confirm the current size of the data files in your database for me? If the file that holds blocks from the "problem table" has exceeded 2GB, then there is a distinct possibility that your operating system could be one for which 2GB+ data files have issues.


    The insidious and obscure thing about the 2GB+ problem is that you don't even know you have a problem unless you just happen to access data that is in a block that is at or beyond the block that just happens to be on the 2GB boundary. The access problem occurs only to blocks that are at or beyond the block on the 2GB boundary in the same object (e.g., same table/index/et cetera). So, frankly, you could go for years and not know that you have a problem if the object is obscure or if accesses just happen to occur to blocks prior to the "problem block".
    Therefore, you can isolate if this is an issue for you by determining if the table with which you are having trouble happens to lie on the 2GB "threshold" of one of your 2GB+ data files. Here is a script that I wrote for you that will show the owner, name, and object_type of any object that is on a 2GB boundary in your database:

    col a heading "Owner.Object (Type/File)|on 2GB Boundaries" format a50
    select owner ||'.'||segment_name||' ('||lower(segment_type)
    ||' in file #'||file_id||')' a
    ,block_id, blocks
    from dba_extents
    where power(2,31)/8192 between block_id and block_id+blocks
    order by file_id,owner,segment_name
    /

    and For any tables that lie on the 2GB boundary(ies), run a "SELECT" (without a "WHERE" clause) of a single numeric column from the table (which forces a full table scan). If the SELECT fails, then you know you have a problem.
    i.e select count(id ) from tablename;

    Let me know of the results of running the above script and the SELECT to show if there are any problems.

Posting Permissions

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