ORA-01157 in Oracle 8.1.7.0.1 in Suse Linux 7.3

From: Alex Paris <alex.paris_at_tin.it>
Date: 7 Oct 2002 00:57:04 -0700
Message-ID: <f9c1ccc5.0210062357.39454ed@posting.google.com>

I'm estimating Oracle 8.1.7.0.1 in order to have a datawarehouse linked to an other database Oracle 7.3.4. The 8.1.... is the most recent version I can use to link to 7.3.4.

I create many simple materialized views and 3 complex materialized views that
have been made on some simple materialized views. I create a group for simple views and an other group for complex views.
a job(under listed) commands their refresh. After some hours Oracle crash. No command can be made and if I try to restart
these errors occur:
ORA-01157: cannot identify/lock data file 3 - see DBWR trace file ORA-01110: data file 3:'/usr.../rbs01.dbf'

Any Idea?

---

CREATE OR REPLACE PROCEDURE RO_JOB_ICC_SCHEDULING AS

	v_errmsg	VARCHAR2(500);
	v_contesto	VARCHAR2(50);
	v_start_time	VARCHAR2(30);
	v_end_time		VARCHAR2(30);

BEGIN
  v_contesto := 'SEMPLICI_SNAPSHOT_EUROPROD';   v_start_time := rtrim(ltrim(to_char(sysdate,'DD-MM-YYYY HH:MI')));   DBMS_REFRESH.refresh('SEMPLICI_SNAPSHOT_EUROPROD');   v_end_time := rtrim(ltrim(to_char(sysdate,'DD-MM-YYYY HH:MI')));

  INSERT INTO DWH_TB_ICC_SCHEDULING_LOG values
('RO_JOB_ICC_SCHEDULING-' || v_contesto,

        'Completed Successfully START: ' || v_start_time || '; END: ' || v_end_time, sysdate);
  COMMIT; --Complessi snapshot
  v_contesto := 'PESANTI_SNAPSHOT_EUROPROD';   v_start_time := rtrim(ltrim(to_char(sysdate,'DD-MM-YYYY HH:MI')));   DBMS_REFRESH.refresh('PESANTI_SNAPSHOT_EUROPROD');   v_end_time := rtrim(ltrim(to_char(sysdate,'DD-MM-YYYY HH:MI')));   INSERT INTO DWH_TB_ICC_SCHEDULING_LOG values
('RO_JOB_ICC_SCHEDULING-' || v_contesto,

        'Completed Successfully START: ' || v_start_time || '; END: ' || v_end_time, sysdate);
  COMMIT; --Storicizzazione dati
--Portafoglio
  v_contesto := 'Storic. DWH_STO_PORTAF_APERTO';

  v_start_time := rtrim(ltrim(to_char(sysdate,'DD-MM-YYYY HH:MI')));   DELETE FROM DWH_STO_PORTAF_APERTO where data_elaborazione = to_date(to_char(sysdate, 'YYYYMMDD'),'YYYYMMDD');   INSERT INTO DWH_STO_PORTAF_APERTO SELECT A.*,

	TO_NUMBER(substr(LTRIM(TO_CHAR(A.DATA_CONSEGNA_CONFERMATA)),1,4)),
	TO_NUMBER(substr(LTRIM(TO_CHAR(A.DATA_CONSEGNA_CONFERMATA)),5,2)),
	to_date(to_char(sysdate, 'YYYYMMDD'),'YYYYMMDD')
	FROM DWH_RO_PORTAF_APERTO_W01 a;

  v_end_time := rtrim(ltrim(to_char(sysdate,'DD-MM-YYYY HH:MI')));   INSERT INTO DWH_TB_ICC_SCHEDULING_LOG values
('RO_JOB_ICC_SCHEDULING-' || v_contesto,

        'Completed Successfully START: ' || v_start_time || '; END: ' || v_end_time, sysdate);
  COMMIT;   EXCEPTION
  WHEN OTHERS THEN

	v_errmsg := substr(sqlerrm, 1, 500);
	ROLLBACK;
	INSERT INTO DWH_TB_ICC_SCHEDULING_LOG values


('RO_JOB_ICC_SCHEDULING-' || v_contesto,

		v_errmsg || ' START: ' || v_start_time, sysdate);
	COMMIT;

END RO_JOB_ICC_SCHEDULING;
/ Received on Mon Oct 07 2002 - 02:57:04 CDT