on 04-24-2009 3:32 AM
We got ORA-1652 for PSAPTEMP tablespace
Enviroment: Oracle 10.2.0.2
But in DB02, psaptemp 's free space is 100%
The max index /table size is 1.4G/2.9G
Even we add PSAPTEMP to 10G, still get same error. The error show every hour.
Any suggestion for this issue?
Thanks a lot.
Hi Amy,
I think that in this case it's not about making your PSAPTEMP larger.
The right question to ask here has to be: Why is so much temporary space required?
What SQL statements causes this?
Typically statements that use HASH or SORT/MERGE operations are 'good suspects' for this.
Check your shared cursor cache for those kind of statements and figure out whether the expected cardinality of the Join-steps are reasonable and correct.
It happens quite a lot that e.g. BW-queries that use HASH joins are expected to hold only few rows eat up GBs of temp. space just because a few 1000000 rows had had to be stored.
To prevent this from happening current CBO statistics and correct db parameter setup is most often sufficient, especially if you're using the current CBO merge fix.
If you cannot perform this kind of analysis yourself, then you may want to open a support message.
(be sure to reference to this thread so that the whole discussion about setting up PSAPTEMP correctly is not reiterated again).
regards,
Lars
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi, Lars,
Thanks for your reply.
I do check sql statements before I add datafiles for PSAPTEMP tablespace. This error show every hour at dedicated time. So I think it should be background job which cause this problem. When I trace it with ST05, I got nothing. And I don't found such 'HASH' or 'SORT' sql in shared cursor cache.
The system is Ecc6.0.
> I do check sql statements before I add datafiles for PSAPTEMP tablespace. This error show every hour at dedicated time. So I think it should be background job which cause this problem. When I trace it with ST05, I got nothing. And I don't found such 'HASH' or 'SORT' sql in shared cursor cache.
Well, actually to find those statements you would have to look into the execution plans for each statement in the shared cursor cache.
HASH and SORT aren't part of the sql-text, so you don't find them with the build-in DBACockpit monitors.
What I would do would be to - as you know the timeframe the problem is occuring - check the ASH-Tables for statements that:
a) ran during this timeframe
and
b) used single- or multipass temp-actions (since only those do use temp space).
As already written you may also just open a support message for this.
But before that make double sure that the db parameters are as recommended.
regards,
Lars
Hi Amy ,
Try ALTER TABLESPACE ADD DATAFILE statement to add one or more files to the tablespace indicated.
Abhijeet
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Amy
548221 - Temporary Files are created as sparse files
thanks
Bhudev
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
24 | |
12 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.