Ora 1652 error Unable to extend tablespace temp by 1280

[ora -1652 error. please]

I am running a query against a view and it is causing this problem. Any help will be greatly appreciated.

Thanks
Ram

[Yang Qin_1]

Hi, Ram, you can run oerr for detailed error message and action suggestion:

oerr ORA 1652
01652, 00000, "unable to extend temp segment by %s in tablespace %s"
// *Cause: Failed to allocate an extent for temp segment in tablespace.
// *Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
// files to the tablespace indicated.


Yang

[ora -1652 error. please]

Thanks for this. But the problem is i modified an existing view that is live. All i did was, removed two conditions
say a= 10 and b = 1 from the query on which the view is built, which would obviously result in more records being processed.

the existing view will be like this...

select .......... from t1,t2,t3,t4
where
and a=10 and b=1.

We create reports based on the view say,
select * from v1;

now we need to introduce 3 new reports with
a=20 and b=2
a=30 and b=3
a=40 and b=4

so instead of creating 4 views, i removed the condition from the original view and built 4 new reports we will do something like this from now on.

select * from v1 where a=10 and b=1
select * from v1 where a=20 and b=2
select * from v1 where a=30 and b=3
select * from v1 where a=40 and b=4

what i try to tell this is very much an existing query effectively.

I have also gone through the explain plan. that looks ok. no cartesians or others. Any urgent help would be greatly appreciated.

Thanks
Ram

[Yogeeraj_1]

hi Ram,

Since it is a live system, it could also be other events impacting on your queries. You may wish to query its utilisation by querying v$sort_usage.

Can you create a new temporary tablespace and retry the same query?

e.g.

CREATE SMALLFILE TEMPORARY TABLESPACE TEMP01 TEMPFILE '/u01/oracle/oradata/mydb/temp01.dbf' SIZE 1024M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE 1024M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K

alter user temporary tablespace temp01;

reconnect as the user

re-run the query.


hope this helps!
kind regards
yogeeraj

[Peter Godron]

Ram,
welcome to the forums!

I think you have found the problem yourself.
Example:
Result of original view build = 100,000 records
Your original query did a table scan on 100,000 records.

Result of new view build = 1,000,000 records
Your new query does a table scan on 1,000,000 records , which then filters to the original 100,000 of your original query.

Is your query the only one using the temp tablespace?
Are you using indexes ?
Can you combine your queries with a group by clause?

Please also read:
http://forums1.itrc.hp.com/service/forums/helptips.do?#33 on how to reward any useful answers given to your questions.

[ora -1652 error. please]

thanks yogeeraj and peter for your help.
many thanks.

i find no records in v$sort_usage.

and i cannot tinker with the database like changing the temp tablespace of the user.

What i could tell you is the same query is running really fast within 8 seconds in another 10g database. But the dev 8.1.7 database, the query is failing. and the live is also 8.1.7. So i have to run the query successfully on the dev 8.1.7 for the code to be promoted.

And also i had a close look at the explain plan. there are places where full table scans are done in spite of the presence of indexes. i used hints to force use the indexes , though i know Full table scans could be more effective than indexs at times, but that also does not seem to help.

Please let me know if you need more information.

Thanks a ton
Ram

[Yogeeraj_1]

hi,

Make sure that your statistics are up-to-date.

verify that "monitoring" is ON,
select table_name, monitoring, num_rows, sample_size, to_char(last_analyzed, 'HH24-mi-ss') ANALYZED from user_tables
where table_name='';

if yes, then
exec dbms_stats.gather_table_stats( user, '', cascade=>true );

verify your execution plan and re-run your query


let us know what happens.

kind regards
yogeeraj

[Volker Borowski]

Hi,

lets have the complete statement of view and report selects as well as the execution plans of the queries you executed.

And the index definititons on all tables involved, as well as the size in blocks or bytes of the tables involved.

May be we can dig something out :-)

Volker

[TwoProc]

Do you indexes on both columns a and b? If both columns a and b are coming from two different tables, then you'd need a single index on each column on each table. The discussion below will assume that they two columns are from two different tables (you didn't indicate). If they are from the same table, you need to create a combination index. That is, Oracle queries will rarely want to use more than one index for a single table (an exception would be bitmap indexes - but that's another topic that I'm assuming we can't cover today).

How selective are columns a and b? Does the condition of "a=10 and b=1 " exclude more than half of the resultant records from tables t1, t2, t3,t4, etc? If not, then a full table scan would be called for as being more efficient.

Also, since you're using literals (and not bound variables) - a histogram on columns a and b could be quite useful here. Especially if the number of records matching each condition is not an even percentage of the total records. It could be that, if the cost optimizer knew how the records were skewed for each of the values of "a" and "b" it would properly choose, at different times - to use a full table scan (for lets say, a=30 and b=3) because of a large number of represented values, but for another query (for let's say, a=10 and b=1) this is only a few records, and the indexes would be of great value.

So, you need to doing a histogram analysis on the tables which hold the values for both a and b.

You can find out the selectivity of column a by doing the following:

select a,count(*) from t1
group by a;

And you'll get a result showing you the breakdown and counts of values for a.

Repeat for t2 on column b.

I think you'll probably see some skewed data here (that is, the count for each of the different number of values are not similar in size).

An example for table t1 might be:
A count(*)
---- -----------------
10 30
20 1
30 70
40 40
50 20

So, you've got a total of 161 rows, and only a search with a filter of "a=30" should do a full table scan ( 70 records is > 1/2 of 161). If I look for "a=10" I should definitely be using an index on column a.

So, the answer would be to get a histogram for the columns on t1 and t2 for columns a and b, respectfully.

So your analyze statment for table would look similar to:

analyze table schema.t1 estimate statistics sample 10 percent for columns a size 6;

The above says use a 10 percent sample across the whole table, and for column(s) a - use 10 "buckets" for the histogram ( I like to, and recommend, using a value of 1 higher than the actual count, just in case a new value starts showing up down the road). A higher value for the number of buckets than actual hurts nothing and takes up no extra space. The max value for the number of buckets is 255. If the number of distinct column values that you have is greater than 255, it will start combining values in buckets (where multiple values may be aggregated into a single bucket - still useful, and many times better than no histogram at all).

Now, run your analyze once again for table t2 which is presumably holding column "b", which in this example (though I've not shown it) presumably has 10 distinct values.

analyze table schema.t2 estimate statistics sample 10 percent for columns b size 11;

Now analyze your two indexes, and re-run your query, you may find it runs better. If not, then a full table scan may have been the best plan after all. Just keep in mind that the selectivity of the values of column tell how useful a filter is for a set of data, and therefore how great and useful an index is. The best way to let the cost optimizer know about the selectivity (ordinality) of your data is via histograms.

[TwoProc]

Correction:

The above says use a 10 percent sample across the whole table, and for column(s) a - use 10 "buckets" for the histogram ( I like to, and recommend, using a value of 1 higher than the actual count, just in case a new value starts showing up down the road).

has the sentence:
use 10 "buckets" for the histogram

should have read

use 6 "buckets" for the histogram

Excuse the error.