This forum is currently locked. You can't register or post questions at this time. (read more)

ORA-08103

All posts relating to Oracle database administration.

Moderator: Tim...

royalyogi
Senior Member
Posts: 181
Joined: Tue Apr 23, 2013 3:59 pm

ORA-08103

Postby royalyogi » Fri Aug 30, 2013 12:27 pm

Select query will run fine. But when will run full query (insert + select). we will get below error after 8-10Hrs. No rows insert on table but table is exist.
Please help here
Oracle Database Version: 11.2.0.3


17:06:30 SQL> @PNLP.sql
17:06:46 128 /
insert into SN.I$_GLBL
*
ERROR at line 1:
ORA-08103: object no longer exists


03:28:21 SQL>
03:28:21 SQL> select count(*) from SN.I$_GLBL;

COUNT(*)
----------
0

================ Script==========
insert into SN.I$_GLBL
(
COD_TYP_EVT_GBL,
COD_SS_TYPE_EVT_GBL,
ID_CONTRAT,
DAT_LAST_MODIF,
DAT_EFFET_EVT,
MNT_TOT_BRUT,
MNT_TOT_NET,
MNT_TOT_FRAIS,
MNT_FRAIS_LIES_DETAIL,
PLUS_VALUE_TAXABLE,
PRELVTS_SOCIAUX,
PRELVTS_FISCAUX,
COD_STATUT_EVT,
COMMENTAIRES,
ORDRE,
NUM_MVT,
IND_UPDATE
)
select DISTINCT
case when MA_MOUVEMENT.MVT_ACTE_GESTION='AFN' then 'OUV' else REFERENCES2.REF_ID_PLP end,
REFERENCES1.REF_ID_PLP,
CONTRAT.ID_CONTRAT,
max(MA_REPARTITION_INVESTIS.R_I_DATE_INVESTISSEMENT),
MA_MOUVEMENT.MVT_DATE_EFFET,
CASE
WHEN REFERENCES2.REF_ID_PLP ='FG' THEN MA_MOUVEMENT.MT_MT_INVESTIMENT
WHEN REFERENCES2.REF_ID_PLP IN('RP','TY') THEN MA_MOUVEMENT.MT_MT_INVESTIMENT
ELSE
MA_MOUVEMENT.MVT_MONTANT_INITIAL
END,
CASE
WHEN REFERENCES2.REF_ID_PLP ='FG' THEN 0
WHEN REFERENCES2.REF_ID_PLP IN('RP','TY') THEN
DECODE(MA_MOUVEMENT.MVT_MONTANT_INITIAL,0,MA_MOUVEMENT.MT_MT_INVESTIMENT,MA_MOUVEMENT.MVT_MONTANT_INITIAL)
ELSE
MA_MOUVEMENT.MT_MT_INVESTIMENT
END,
case
when REFERENCES2.REF_ID_PLP ='FG' THEN ABC(MA_MOUVEMENT.MT_MT_INVESTIMENT)
else
case when MA_MOUVEMENT.MVT_ACTE_GESTION<> 'OST' then
case when MA_MOUVEMENT.MVT_ACTE_GESTION<> 'ABR' then
ABC(MA_MOUVEMENT.MVT_MONTANT_INITIAL-MA_MOUVEMENT.MT_MT_INVESTIMENT)
else 0
end
end
end,
case
when REFERENCES2.REF_ID_PLP ='FG' THEN 0
else
case when MA_MOUVEMENT.MVT_ACTE_GESTION<> 'OST' then
case when MA_MOUVEMENT.MVT_ACTE_GESTION<> 'ABR' then
ABC(MA_MOUVEMENT.MVT_MONTANT_INITIAL-MA_MOUVEMENT.MT_MT_INVESTIMENT)
else 0
end
end
end,
MA_RACHATS.RAC_PLUS_VALUE,
MA_RACHATS.RAC_PRELEVEMENT_SOCIAL,
MA_RACHATS.RAC_PRELEVEMENT_FISCAL,
'VAL',
REFERENCES3.REF_LIBELLE,
V_PLP_ORDRE_MVT.NUMERO - V_PLP_ORDRE_MVT_MIN.ORDRE+1,
MA_MOUVEMENT.MVT_N_MOUVEMENT,

'I' IND_UPDATE

from PLP.CONTRAT CONTRAT, HUB_ODS.MA_RACHATS MA_RACHATS, PLP.REFERENCES REFERENCES1, PLP.REFERENCES REFERENCES2, PLP.REFERENCES REFERENCES3, HUB_PMPR.MA_MOUVEMENT MA_MOUVEMENT, HUB_PMPR.V_PLP_ORDRE_MVT_MIN V_PLP_ORDRE_MVT_MIN, HUB_PMPR.V_PLP_ORDRE_MVT V_PLP_ORDRE_MVT, HUB_PMPR.V_PLP_MVT_ABC_DTINVEST V_PLP_MVT_ABC_DTINVEST, HUB_PMPR.MA_REPARTITION_INVESTIS MA_REPARTITION_INVESTIS, PLP.INTERMEDIAIRES INTERMEDIAIRES
where (1=1)
And (REFERENCES2.REF_ID_SKANDIA=MA_MOUVEMENT.MVT_ACTE_GESTION)
AND (V_PLP_ORDRE_MVT_MIN.CONTRAT=V_PLP_ORDRE_MVT.CONTRAT)
AND (MA_MOUVEMENT.MVT_ACTE_GESTION=REFERENCES1.REF_ID_SKANDIA (+))
AND (CONTRAT.NUMCONTRAT=MA_MOUVEMENT.MVT_N_CONTRAT)
AND (MA_RACHATS.RAC_N_MOUVEMENT (+)=MA_MOUVEMENT.MVT_N_MOUVEMENT)
AND (REFERENCES3.REF_ID_SKANDIA (+)=MA_MOUVEMENT.MVT_ACTE_GESTION)
AND ((V_PLP_ORDRE_MVT.CONTRAT=MA_MOUVEMENT.MVT_N_CONTRAT) AND V_PLP_ORDRE_MVT.MOUVEMENT=MA_MOUVEMENT.MVT_N_MOUVEMENT)
AND (MA_REPARTITION_INVESTIS.R_I_N_MOUVEMENT=V_PLP_MVT_ABC_DTINVEST.MVT_N_MOUVEMENT)
AND (MA_REPARTITION_INVESTIS.R_I_N_MOUVEMENT=MA_MOUVEMENT.MVT_N_MOUVEMENT)
AND (CONTRAT.ID_CONSEILLER=INTERMEDIAIRES.ID_INTERMEDIAIRES)
And (REFERENCES1.REF_DOMAINE (+) = 'SOUS_TYPE_EVENEMENT_GLOBAL')
And (REFERENCES2.REF_DOMAINE = 'TYPE_EVENEMENT_GLOBAL')
And (REFERENCES3.REF_DOMAINE (+) ='TYPE_EVENEMENT_GLOBAL')
And (INTERMEDIAIRES.CD_INTER1=30842)
And (MA_MOUVEMENT.MVT_DATE_EFFET <= add_months(sysdate,-1))

Group By case when MA_MOUVEMENT.MVT_ACTE_GESTION='AFN' then 'OUV' else REFERENCES2.REF_ID_PLP end,
REFERENCES1.REF_ID_PLP,
CONTRAT.ID_CONTRAT,
MA_MOUVEMENT.MVT_DATE_EFFET,
CASE
WHEN REFERENCES2.REF_ID_PLP ='FG' THEN MA_MOUVEMENT.MT_MT_INVESTIMENT
WHEN REFERENCES2.REF_ID_PLP IN('RP','TY') THEN MA_MOUVEMENT.MT_MT_INVESTIMENT
ELSE
MA_MOUVEMENT.MVT_MONTANT_INITIAL
END,
CASE
WHEN REFERENCES2.REF_ID_PLP ='FG' THEN 0
WHEN REFERENCES2.REF_ID_PLP IN('RP','TY') THEN
DECODE(MA_MOUVEMENT.MVT_MONTANT_INITIAL,0,MA_MOUVEMENT.MT_MT_INVESTIMENT,MA_MOUVEMENT.MVT_MONTANT_INITIAL)
ELSE
MA_MOUVEMENT.MT_MT_INVESTIMENT
END,
case
when REFERENCES2.REF_ID_PLP ='FG' THEN ABC(MA_MOUVEMENT.MT_MT_INVESTIMENT)
else
case when MA_MOUVEMENT.MVT_ACTE_GESTION<> 'OST' then
case when MA_MOUVEMENT.MVT_ACTE_GESTION<> 'ABR' then
ABC(MA_MOUVEMENT.MVT_MONTANT_INITIAL-MA_MOUVEMENT.MT_MT_INVESTIMENT)
else 0
end
end
end,
case
when REFERENCES2.REF_ID_PLP ='FG' THEN 0
else
case when MA_MOUVEMENT.MVT_ACTE_GESTION<> 'OST' then
case when MA_MOUVEMENT.MVT_ACTE_GESTION<> 'ABR' then
ABC(MA_MOUVEMENT.MVT_MONTANT_INITIAL-MA_MOUVEMENT.MT_MT_INVESTIMENT)
else 0
end
end
end,
MA_RACHATS.RAC_PLUS_VALUE,
MA_RACHATS.RAC_PRELEVEMENT_SOCIAL,
MA_RACHATS.RAC_PRELEVEMENT_FISCAL,
REFERENCES3.REF_LIBELLE,
V_PLP_ORDRE_MVT.NUMERO - V_PLP_ORDRE_MVT_MIN.ORDRE+1,
MA_MOUVEMENT.MVT_N_MOUVEMENT

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: ORA-08103

Postby Tim... » Fri Aug 30, 2013 2:09 pm

Hi.

Well, read the error message in the docs.

http://docs.oracle.com/cd/E11882_01/ser ... #ORA-08103

Seems pretty clear to me.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

royalyogi
Senior Member
Posts: 181
Joined: Tue Apr 23, 2013 3:59 pm

Re: ORA-08103

Postby royalyogi » Fri Aug 30, 2013 5:33 pm

As per above link:
ORA-08103: object no longer exists
Cause: The object has been deleted by another user since the operation began, or a prior incomplete recovery restored the database to a point in time during the deletion of the object.
Action: Delete the object if this is the result of an incomplete recovery

Tim: as per above script, we are selecting and inserting on table SN.I$_GLBL and SN.I$_GLBL table has created on July 25,2013. Not made any changes on this table as well on database from July 25,2013 to till and don't have any ORA- on alert<$SID>_log file also. Please through some more light on this issue to understand better.

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: ORA-08103

Postby Tim... » Fri Aug 30, 2013 6:05 pm

Hi.

I can't throw any light on it. Either the issue is as the error message says, or something else unknown is happening. If it is the latter you need to contact Oracle support and raise this as a bug.

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

royalyogi
Senior Member
Posts: 181
Joined: Tue Apr 23, 2013 3:59 pm

Re: ORA-08103

Postby royalyogi » Fri Aug 30, 2013 6:17 pm

Thanks Tim, I have raised this as a Bug to oracle Support now

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: ORA-08103

Postby Tim... » Fri Aug 30, 2013 7:18 pm

OK. :)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle Database Administration”

Who is online

Users browsing this forum: No registered users and 4 guests

cron