Error
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 182
- Joined: Thu Jun 16, 2005 2:05 am
Error
Hai,
I got the following error.Kindly give me your valuable suggestions.
LoadStgSaleForecastSGPzWeek..SrcStgSaleForecastNigWeekOra: ORA-12801: error signaled in parallel query server P007
ORA-01652: unable to extend temp segment by 1024 in tablespace TEMP
Attempting to Cleanup after ABORT raised in stage LoadStgSaleForecastSGPzWeek
Regards,
Satheesh.Rajan
I got the following error.Kindly give me your valuable suggestions.
LoadStgSaleForecastSGPzWeek..SrcStgSaleForecastNigWeekOra: ORA-12801: error signaled in parallel query server P007
ORA-01652: unable to extend temp segment by 1024 in tablespace TEMP
Attempting to Cleanup after ABORT raised in stage LoadStgSaleForecastSGPzWeek
Regards,
Satheesh.Rajan
-
- Participant
- Posts: 232
- Joined: Fri Sep 30, 2005 4:52 am
- Contact:
To me, the error looks more like it's a parallel query coming from the source rather than the target side. In any case, talk to your DBA. They will berate you for whatever you are doing - something that needs to do alot of sorting, possibly - but then may increase the TEMP tablespace for you.
Either that or rethink your query so it doesn't need that much in the way of disk resources.
Either that or rethink your query so it doesn't need that much in the way of disk resources.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 182
- Joined: Thu Jun 16, 2005 2:05 am
Re: Error
Whenever such database errors pop up, always pay close attention to their codes.
ORA-01652 usually happens when a sort operation cannot be finished in memory alone. Thats when the disk space comes into play but even that is falling short for your case. This error is begging you to get more room in the table space.
So you have two options now, either increase your memory size , something like
to get a million bytes for the sort area or you can request your dba to increase the table space.
The first error maybe due to a parallel query. For that you definately need your dba's assistance.
ORA-01652 usually happens when a sort operation cannot be finished in memory alone. Thats when the disk space comes into play but even that is falling short for your case. This error is begging you to get more room in the table space.
So you have two options now, either increase your memory size , something like
Code: Select all
ALTER SESSION SET sort_area_size=1000000
The first error maybe due to a parallel query. For that you definately need your dba's assistance.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
That would depend on exactly what else is going on at the same time.satheesh_color wrote:The job is completed while reducing "The parallel degree for the sql statement from 8 to 1". Why parallel degree 8 works sometimes and not others?
And I doubt you needed to drop it to 1... why not try a smaller number, something that will play nicer with others? If everyone always requested maximum parallelism...
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 182
- Joined: Thu Jun 16, 2005 2:05 am
Hai Guy's,
Here is the Query:
select /*+ full(s) parallel(s,#PAR_DEG#) full(n) parallel(n,#PAR_DEG#) full(l) parallel(l,#PAR_DEG#) ordered */
s.PERIOD_ID,
s.DIVISION_CODE,
nvl(n.ITEM_GROUP_CODE,0),
s.NTNL_ITEM_GROUP_CODE,
nvl(n.PRICE_ZONE_CODE,0),
n.PRICE_ZONE_TYPE_CODE,
round(nvl(AVG(s.AVERAGE_FORECAST_PRICE),0.0),5),
round(SUM(decode(nvl(s.store_count_price_zone,0),0,0,
s.AVERAGE_EXTENDED_COST*SUM_FORECAST_UNIT/s.store_count_price_zone)),5),
nvl(count(distinct n.LOCATION_ID),0),
round(nvl(SUM(decode(nvl(s.store_count_price_zone,0),0,0,s.SUM_FORECAST_UNIT/s.store_count_price_zone)),0.0),5) ,
round(nvl(SUM(decode(nvl(s.store_count_price_zone,0),0,0,s.SUM_FORECAST_SALE_AMOUNT/s.store_count_price_zone)),0.0),5)
from stg_sale_forecast_nig_week s,
PDM_LOCATION_PRICE_ZONE_WEEK l,
stg_sale_forecast_nig_store n
#PAR_DEG# is the parameter we are passing from job level. As previously stated if we pass PAR_DEG=8 in the sense the job get's aborted else if we set PAR_DEG=1 in the sense the job get's completed.I just want to know the solution to resolve this issue.Kindly give me your valuable suggestions.
Regards,
Satheesh.R
Here is the Query:
select /*+ full(s) parallel(s,#PAR_DEG#) full(n) parallel(n,#PAR_DEG#) full(l) parallel(l,#PAR_DEG#) ordered */
s.PERIOD_ID,
s.DIVISION_CODE,
nvl(n.ITEM_GROUP_CODE,0),
s.NTNL_ITEM_GROUP_CODE,
nvl(n.PRICE_ZONE_CODE,0),
n.PRICE_ZONE_TYPE_CODE,
round(nvl(AVG(s.AVERAGE_FORECAST_PRICE),0.0),5),
round(SUM(decode(nvl(s.store_count_price_zone,0),0,0,
s.AVERAGE_EXTENDED_COST*SUM_FORECAST_UNIT/s.store_count_price_zone)),5),
nvl(count(distinct n.LOCATION_ID),0),
round(nvl(SUM(decode(nvl(s.store_count_price_zone,0),0,0,s.SUM_FORECAST_UNIT/s.store_count_price_zone)),0.0),5) ,
round(nvl(SUM(decode(nvl(s.store_count_price_zone,0),0,0,s.SUM_FORECAST_SALE_AMOUNT/s.store_count_price_zone)),0.0),5)
from stg_sale_forecast_nig_week s,
PDM_LOCATION_PRICE_ZONE_WEEK l,
stg_sale_forecast_nig_store n
#PAR_DEG# is the parameter we are passing from job level. As previously stated if we pass PAR_DEG=8 in the sense the job get's aborted else if we set PAR_DEG=1 in the sense the job get's completed.I just want to know the solution to resolve this issue.Kindly give me your valuable suggestions.
Regards,
Satheesh.R
You've already gotten them. Drop the degree of parallelism down or be prepared for it to occasionally crater if too many other parallel queries have been requested by other folks/jobs at the same time.
Discuss that with your DBA. It's not a DataStage issue.
Discuss that with your DBA. It's not a DataStage issue.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers