|
Mahesh
| |
| | Tuesday, March 06, 2007 - 9:13 am: | 
| 
|
Admin please move the message to appropriate board if this board is not correct.
I am facing one problem in Oracle 8i.
There is very big select query, it is taking hell lot of time to complete the execution
and at the end it is throwing following error.
ORA-01652: unable to extend temp segment by 513 in tablespace TEMP
When I searched on internet, I found many suggestions to increase size of tablespace or to add datafiles.
But currently size of TEMP tablespace is less than 1GB
and DBA is saying that there is no need to increase because already it is set to MAX 4GB.
Then I started analysis of the query. And found very interesting result.
In where clause of the query, there is one part as follows.
AND CNTR.CODE IN ('T01901')
Query is not giving error for following situations.
1. AND CNTR.CODE IN ('T01902') or any other code.
2. AND CNTR.CODE IN ('T01901', 'T01902') or more than these 2 values.
Now REAL SURPRISE is here.
If same value is taken 2 times as follows
AND CNTR.CODE IN ('T01901', 'T01901')
Then error is not occurring. But I am not convinced with this.
Can anybody suggest something ? Did you face this kind of problem ?
|
Mahesh
| |
| | Tuesday, March 13, 2007 - 2:09 pm: |
|
|
अरे वा माझ्या प्रश्नाने ओरकलच्या बोर्डाची सुरूवात. धन्यवाद.
मी विचारलेल्या प्रश्नाचे उत्तर सापडले नाही, पण मी SQL मधे बदल करून पाहिला आणी error गायब झाली.
खरेतर माझे समाधान झाले नाही पण सद्ध्या error येत नाहीये त्यामुळे ठीक आहे. नंतर R&D करावा म्हणतो.
मी या वर्षी Oracle 10g Release 2 ची DBA OCP परिक्षा द्यायचा विचार करत आहे.
जर अजुन कोणी उत्सुक असेल तर या बोर्डावर Knowledge Share करूयात.
अर्थात Admin ला काही हरकत नसेल तर.
|
Asami
| |
| | Tuesday, March 13, 2007 - 3:08 pm: |
|
|
माझी वाचण्यात काही गल्लत होते आहे का ? I think you mentioned that no error is thrown for all combinations you tried. I'm not sure why are you surprised by behavior of IN clause. Isn't that the expected behavior ? Parser does not parse i/p values, but syntax as far as I know.
|
Mahesh
| |
| | Wednesday, March 14, 2007 - 4:25 am: |
|
|
असामी की आसामी ?
Here sqls are generated in dot net code
IN मधे जर T01901 ही specific single value एकदाच आली तर एरर येत आहे की temp tablespace मधे segment वाढविता येणार नाही.
मी खालीलप्रमाणे प्रयोग करून पाहिले.
१. तीच value जर दोनदा लिहून पाहिली तर एरर येत नाहीये.
e.g. code IN (T01901, T01901)
२. जर दुसरी कोणतीही value like T01902 or T01903, etc. ही एकदा किंवा एकपेक्षा जास्त वेळा लिहून पाहिली तरी एरर येत नाहीये.
e.g. code IN (T01902, T01903)
e.g. code IN (T01902, T01902)
Note : त्या T01901 value ला कोणताही problem नाहीये. ईतर values प्रमाणेच साधा data आहे.
सद्ध्या यावर उपाय म्हणून मी उलट sql generate करत आहे.
where code NOT IN (values other than T01901)
आणी या sql ला पण एरर येत नाहीये.
|
Asami
| |
| | Wednesday, March 14, 2007 - 5:06 pm: |
|
|
just for heck of it, can you pl change SELECT clause to select no of rows returned in all cases. Your code may not be getting parsed as you expect it to be by .NET parser.
You can also try capturing actual SQL getting executed at database level
|
ते असामी आहे.
आसामी म्हणजे काय? ओसामा ऐकले आहे 
|
Mahesh
| |
| | Thursday, March 15, 2007 - 3:38 am: |
|
|
>>select no of rows returned in all cases.
Did not try it so far. I'll try it out.
>>You can also try capturing actual SQL getting executed at database level
Already tried it, but same error is occurring.
सव्यसाची, आसामी म्हणजे आसाम मधला...
|
Mahesh
| |
| | Thursday, March 15, 2007 - 8:28 am: |
|
|
Does Oracle 8i have any constraint for following functionality ?
http://www.techonthenet.com/access/functions/date/datediff.php
1.
select datediff('n', mydate, sysdate) from mytable
Error : Invalid column name
2.
select datediff('n', to_date(mydate, 'yyyy-mm-dd hh:mm:ss'), to_date(sysdate, 'yyyy-mm-dd hh:mm:ss')) from mytable
Error : Invalid column name
http://asktom.oracle.com/tkyte/Misc/DateDiff.html
select (mydate - sysdate)*24*60 from mytable
NO Error...
|
हे हे , हा अर्थ लक्षातच नाही आला. असाम्या, तु आसामी आहेस का रे?
|
Mahesh
| |
| | Tuesday, March 20, 2007 - 9:59 am: |
|
|
ओरकलबद्दल अजुन एक प्रश्न.
जेव्हा sequence_name.nextval वापरले जाते तेव्हा त्या sequence ची उपलब्ध असलेली value मिळते आणी तो sequence वाढतो.
मी एका data upload utility (kettle) मधे sequence वापरत आहे. जर काही एरर आली तर records insert होत नाहीत, पण sequence मात्र वाढतो.
प्रश्न असा आहे की nextval ला commit ची गरज नसते का ? आणी sequence increment हे prgram मधून control करता येत नाही का ? म्हणजे जर एरर आली तर sequence ची वाढलेली value step back करता येत नाही का ?
मी sql plus मधून करून पहात होतो.
select sequence_name.nextval from dual
say returned value 55 and incremented sequence to 56
rollback;
nothing happened but sequence is incremented to 57
कोणी सुचवू शकेल का ? धन्यवाद
|
Mahaguru
| |
| | Tuesday, March 20, 2007 - 7:36 pm: |
|
|
महेश, मला exact issue माहित नाही पण this is something:
try alter sequence and set it to nocache. If you have any cache set then all those values will be lost. Using this option, performance will be little slow but atleast you will not loose value.
If you have gaps and you want to reset value, you can use alter sequence again.
|
Mahaguru
| |
| | Saturday, March 24, 2007 - 4:24 pm: |
|
|
महेश, तुमचा oracle चा प्रश्न सुटला का नाही? काय केले लिहा ना, इतरांना पण कळु दे. नसेल सुटला तर जरा विस्तारीत करा,
|
Mahesh
| |
| | Sunday, March 25, 2007 - 4:33 am: |
|
|
महागुरू, अचानक वेगळ्या project work मधे काम चालू झाल्याने, sequence problem कडे लक्ष देणे झाले नाहीये. पण,
तुम्ही म्हणता त्याप्रमाणे alter sequence हे data upload utility Kettle मधे करता येणे शक्य नाहीये असे वाटते. तसेच हा sequence आधीपासुनच nocache आहे. मी माझा प्रश्न परत एकदा explain करतो.
There are 2 dbs, one is in MS access and one is in Oracle.
We are trying to transfer bulk data from access to oracle using data upload utility Kettle (open source).
This data transfer is not as it is. There are some conditional changes in data.
There is one sequence number column in Oracle side table.
And one oracle sequence is used inside kettle source to fill up sequential numbers in this column.
Problem is as follows,
Suppose sequence value is 1 at the starting of data transfer.
When we try to transfer records (say 100) and if it fails because of some reason, records are not uploaded in Oracle. But sequence value is incremented (i.e. 101)
Now when we try to transfer again after corrections, and if it works
then serial number value starts from 101 and not from 1
This transfer is periodical and everytime there is no gurantee successfull transfer.
Hence seqno column inside oracle table will not have sequential numbers. There might be gaps in between.
My question is : When nextval of sequence is selected, the value is incremented regardless commit or rollback. Is it correct ? If it is correct then how to prevent it at the time of failure in Kettle and rollback the value of seqeunce?
|
Ram3
| |
| | Friday, March 30, 2007 - 9:11 am: |
|
|
Kuni Datawarehosusing and Business Inteligence madhe kaam karat aahe ka??
I need some information abt that.
|
Mahaguru
| |
| | Saturday, March 31, 2007 - 2:23 am: |
|
|
ram3 , मी सध्या ह्याच क्षेत्रात काम करत आहे. मायबोलीची मेल सुविधा वापरुन तुम्ही मला संपर्क करु शकतात.
तुम्हाला ही मेल पाठवली आहे
|
Ram3
| |
| | Monday, April 02, 2007 - 4:40 am: |
|
|
Good.
DWH and BI madhe scope kiti aaahe future madhe.
Nahi mnaje openings faar kami aahet ase vatate.
|
Mahesh
| |
| | Wednesday, June 27, 2007 - 3:58 am: |
|
|
लोकहो, Oracle 10g संदर्भात एक चांगली blog site सापडली आहे.
http://thetendjee.wordpress.com/
|
Maanus
| |
| | Thursday, November 15, 2007 - 5:28 pm: |
|
|
What is your partitioning strategy.
dont give much details, but some hints will be useful.
date range, hashcode, composite.
How do you minimize load on redo logs.
I looked at asktom, but was not able to get much, or solutions they have are politically not possible in big firms.
|
mala SAP shikaichi ichha aahe. mala Accounting cha 8 years cha experience aahe. mumbait SAP training kute milel tyasambandhi kuni mahiti deu shakel ka?
|
|
| चोखंदळ ग्राहक |
|
| महाराष्ट्र धर्म वाढवावा |
|
| व्यक्तिपासून वल्लीपर्यंत |
|
| पांढर्‍यावरचे काळे |
|
| गावातल्या गावात |
|
| तंत्रलेल्या मंत्रबनात |
|
| आरोह अवरोह |
|
| शुभंकरोती कल्याणम् |
|
| विखुरलेले मोती |
|
|
|
| हितगुज गणेशोत्सव २००६ |
|
|