Home » SQL & PL/SQL » SQL & PL/SQL » Please help on this query
icon9.gif  Please help on this query [message #264345] Mon, 03 September 2007 01:32 Go to next message
vidya2005
Messages: 36
Registered: July 2005
Location: bangalore
Member
Hello experts,

I have written a query as below, it's taking lot of time
and it's giving following error

ORA-01630: max # extents (1500) reached in temp segment in tablespace HLLDB_TEMP

I have choosen the data one for 5 days then too it's giving the
error. Can any body help me about this.

select
upper(c.item_category) || '|' ||
' '||'|'||
upper(m.FACTORY_NAME) || '|' ||
g.stuffing_date || '|' ||
d.customer_name || '|' ||
UPPER(e.pk_export_order_no) || '|' ||
trim(e.CUSTOMER_ORDER_NO) || '|' ||
UPPER(b.pk_pi_no) || '|' ||
UPPER(c.item_description1) || '|' ||
upper(c.PK_ITEM_CODE) || '|' ||
UPPER(a.fk_uom_id) || '|' ||
round(a.actual_shipping_quantity,3)|| '|' ||
round((a.actual_shipping_quantity * c.net_wt)/1000,3)|| '|' ||
g.container_size || '|' ||
UPPER(a.pk_container_no) || '|' ||
UPPER(b.fk_delivery_id) || '|' ||
upper(e.FK_CURRENCY_ID) || '|' ||
round(a.actual_shipping_quantity * f.price,3)|| '|' ||
round((a.actual_shipping_quantity * f.price)*q.spot_exchange_rate,3)||'|'||
upper(h.port_name) || '|' ||
upper(k.port_name) || '|' ||
UPPER(b.FINAL_DESTINATION) || '|' ||
UPPER(r.country_name)||'|'||
j.shipping_line_name || '|' ||
i.cha_name || '|' ||
b.CREATED_DATETIME || '|' ||
b.MODIFIED_DATETIME || '|' ||
q.pk_ci_no || '|' ||
' '||'|'||
upper(p.pk_bol_no) || '|' ||
p.bol_date || '|' ||
p.vessel_name || '|' ||
p.voyage_no || '|' ||
p.departure_date ||'|'||
p.EXPECTED_ARRIVAL_DATE||'|'||
round(q.freight_in_fc/s.cnt,3)||'|'||
round(q.freight/s.cnt,3)||'|'||
round((p.INSURANCE_AMOUNT_inr/s.cnt)/q.spot_exchange_rate,3)||'|'||
round(p.insurance_amount_inr/s.cnt,3)||'|'||
q.modified_datetime
from
container_item_ref a,
proforma_invoice b,
item_mstr c,
customer_mstr d,
export_order e,
export_order_dtls f,
pi_container_ref g,
FACTORY_EO_DTLS_ALLOCATION l,
FACTORY_MSTR m,
port_mstr h,
cha_mstr i,
port_mstr k,
cha_business_ref n,
shipping_line_mstr j,
bol_pi_ref o,
bill_of_lading p,
commercial_invoice q,
country_mstr r,
(select a.pk_bol_no,a.pk_business_code,count(b.fk_item_code) cnt
from bol_pi_ref a,container_item_ref b,commercial_invoice e
where a.pk_pi_no = b.pk_pi_no
and a.pk_business_code=b.pk_business_code
and a.pk_bol_no=e.pk_bol_no
and a.pk_business_code = e.pk_business_code
group by a.pk_bol_no,a.pk_business_code) s
where
a.pk_export_order_no=e.pk_export_order_no and
a.pk_line_no=f.pk_line_no and
a.pk_pi_no=b.pk_pi_no and
a.pk_container_no=g.pk_container_no and
a.fk_item_code=c.pk_item_code and
b.fk_customer_id=d.pk_customer_id and
d.pk_customer_id=e.fk_customer_id and
f.pk_export_order_no=e.pk_export_order_no and
f.pk_line_no=a.pk_line_no and
f.fk_item_code=c.pk_item_code and
g.pk_pi_no=b.pk_pi_no and
l.PK_EXPORT_ORDER_NO=e.PK_EXPORT_ORDER_NO and
l.PK_LINE_NO=f.PK_LINE_NO and
l.FK_FACTORY_ID=b.fK_FACTORY_ID and
b.fk_factory_id=m.pk_factory_id and
b.fk_loading_port_id=h.pk_port_id and
b.fk_discharge_port_id=k.pk_port_id and
b.fk_cha_id=i.pk_cha_id and
b.fk_cha_id=n.pk_cha_id and
b.fk_shipping_line_code=j.pk_shipping_line_code and
b.pk_pi_no=o.pk_pi_no and
o.pk_bol_no=p.pk_bol_no and
p.pk_bol_no=q.pk_bol_no and
e.FK_FINAL_DESTINATION_COUNTRY_I=r.pk_country_id and
e.pk_business_code=r.pk_business_code and
a.pk_business_code=b.pk_business_code and
b.pk_business_code=c.pk_business_code and
c.pk_business_code=e.pk_business_code and
e.pk_business_code=f.pk_business_code and
f.pk_business_code=g.pk_business_code and
l.pk_business_code=b.pk_business_code and
g.pk_business_code=h.pk_business_code and
b.pk_business_code=k.pk_business_code and
k.pk_business_code=n.pk_business_code and
n.pk_business_code=j.pk_business_code and
b.pk_business_code=o.pk_business_code and
o.pk_business_code=p.pk_business_code and
p.pk_business_code=q.pk_business_code and
lower(e.pk_business_code)='hpc' and
q.ci_status='a'
and q.pk_bol_no=s.pk_bol_no and
q.pk_business_code=s.pk_business_code
and e.fk_customer_id in('xuiugf01','xuatll01')
and trunc(q.modified_datetime) between '01-JAN-07' and '05-JAN-07'

Thanks In advance
Vidya
Re: Please help on this query [message #264347 is a reply to message #264345] Mon, 03 September 2007 01:35 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
I'm not even going to LOOK at that code until it is formatted. Please read and follow the sticky at the top of the forum.
Re: Please help on this query [message #264351 is a reply to message #264345] Mon, 03 September 2007 01:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64118
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
ORA-01630: max # extents (1500) reached in temp segment in tablespace HLLDB_TEMP

I have choosen the data one for 5 days then too it's giving the
error. Can any body help me about this.

Enlarge your tablespace.

Regards
Michel
Re: Please help on this query [message #264357 is a reply to message #264345] Mon, 03 September 2007 01:50 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Increase the value of PCTINCREASE of tablespace HLLDB_TEMP.

Re: Please help on this query [message #264358 is a reply to message #264357] Mon, 03 September 2007 01:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64118
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ooops! I bad read the error message!
I think space and not number of extents.

If HLLDB_TEMP is a temporary tablespace, you have to recreate it with the command "create temporary tablespace" to prevent from this kind of problem.
Currently, check if you don't have a max extents limit set to 1500 and try to increase it.

Regards
Michel
Re: Please help on this query [message #264360 is a reply to message #264345] Mon, 03 September 2007 02:07 Go to previous message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
check your indexes.
Use
and q.modified_datetime between to_date('01-JAN-07 00:00:00','DD-MON-YY hh24:mi:ss') 
and to_date('05-JAN-07 23:59:59','DD-MON-YY hh24:mi:ss')


instead of

Quote:

 and trunc(q.modified_datetime) between '01-JAN-07' and '05-JAN-07'




and create index on modified_datetime of table commercial_invoice.

And at from clause arrange table according to data size. That mean put the small table at last position and large table at first position of your from clause.
Previous Topic: Search the value in all fields
Next Topic: Req Help in pl/sql query
Goto Forum:
  


Current Time: Tue Dec 06 04:53:16 CST 2016

Total time taken to generate the page: 0.08734 seconds