ora-4030 error
- From: Joan Hsieh <joan.hsieh@xxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx '" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 23 Nov 2009 13:00:32 -0500
Hi List,
we have the following daily job run in psoft process scheduler, oracle
10.2.0.4 it run 2 min and never had problem, since last Friday after tax
upgrade and cpuoct2009 patch, the job failed immediately by ora-4030 run
of of memory error. I tried to increase the psoft ulimit. it didn't
help. I tried it run in sqlplus, got the same error. Does anyone have
any ideas where it could be wrong? The sga and pga didn't modified since
Feb. 2009.
SELECT
Job.EMPLID,
Job.EMPL_RCD,
Job.EFFSEQ,
to_char(Job.EFFDT),
Job.PAYGROUP,
Job.ACTION,
Job.ACTION_REASON,
Job.STD_HOURS,
Job.COMPRATE,
Job.JOBCODE,
Job.REG_TEMP,
Job.FULL_PART_TIME,
Job.EMPL_STATUS,
Job.OFFICER_CD,
to_char(Job.POSITION_ENTRY_DT),
Job.LOCATION,
Job.POSITION_NBR,
Pers.NAME,
Pers.SEX,
Pers.FIRST_NAME,
Pers.MIDDLE_NAME,
Pers.LAST_NAME,
to_char(Pers.BIRTHDATE),
Pers.ADDRESS1,
Pers.CITY,
Pers.STATE,
Pers.POSTAL,
to_char(Empl.CMPNY_SENIORITY_DT),
to_char(Empl.HIRE_DT),
Empl.REPORTS_TO,
Empl.BUSINESS_TITLE,
Location.DESCR,
Jobcode.DESCR,
Dept.DEPTID,
Dept.DESCR,
tft.TFTH_RPT_FTE,
tft.TFTH_ANN_SAL,
tft.TFTH_TENURE,
tft.TFTH_FTE_SALARY,
tft.TFTH_WORK_PER
FROM PS_JOB Job, PS_EMPLOYMENT Empl, PS_PERSONAL_DATA Pers,
PS_LOCATION_TBL Location,
PS_DEPT_TBL Dept, PS_JOBCODE_TBL Jobcode, PS_TFTH_JOB tft
WHERE Empl.EMPLID = Pers.EMPLID
AND Job.EMPLID = Empl.EMPLID
AND Job.EMPL_RCD = Empl.EMPL_RCD
AND DEPT.DEPTID = Job.DEPTID
AND Job.EMPLID = tft.EMPLID
AND Job.EMPL_RCD = tft.EMPL_RCD
AND Job.EFFDT = TFT.EFFDT
AND Job.EFFSEQ = TFT.EFFSEQ
AND Location.LOCATION = Job.LOCATION
AND (Job.EFFDT =
(SELECT MAX(Job1.EFFDT) FROM PS_JOB Job1
WHERE Job.EMPLID = Job1.EMPLID
AND Job.EMPL_RCD = Job1.EMPL_RCD
AND Job1.EFFDT <= sysdate)
AND Job.EFFSEQ =
(SELECT MAX(Job2.EFFSEQ) FROM PS_JOB Job2
WHERE Job.EMPLID = Job2.EMPLID
AND Job.EMPL_RCD = Job2.EMPL_RCD
AND Job.EFFDT = Job2.EFFDT)
AND Dept.EFFDT =
(SELECT MAX(Dept1.EFFDT) FROM PS_DEPT_TBL Dept1
WHERE Dept.DEPTID = dept1.DEPTID
AND Dept1.EFFDT <= sysdate)
AND Location.EFFDT =
(SELECT MAX(Location1.EFFDT) FROM PS_LOCATION_TBL Location1
WHERE Location.LOCATION = Location1.LOCATION
AND EFFDT <= sysdate)
AND Job.EMPL_STATUS <> 'T'
AND TFT.TFTH_EMPL_SEC_CLAS <> 'L'
AND Job.ACTION NOT IN ('LTD','RET','TER','TWB','TWP')
AND Job.PAYGROUP IN
('BGU','DIN','FAC','POL','SFE','SFN','NPY','TMP','NTC','NTS','VIS','PDC','ITW','ITS')
AND Empl.EMPL_RCD = 0
AND Jobcode.EFFDT =
(SELECT MAX(Jobcode1.EFFDT) FROM PS_JOBCODE_TBL Jobcode1
WHERE Jobcode.JOBCODE = Jobcode1.JOBCODE
AND EFFDT <= Job.EFFDT)
AND Jobcode.JOBCODE = Job.JOBCODE )
order by JOB.EMPLID
--
//www.freelists.org/webpage/oracle-l
Other related posts:
