on 09-17-2007 12:12 PM
Hi Gurus ,
I have resently Copied my Production database to quality databse.
After the copy i am getting the following dump in st22 in the Quality system.
<b>Runtime Error</b>: DBIF_RSQL_SQL_ERROR
<b>Except</b>.: CX_SY_OPEN_SQL_DB
<b>ShrtText</b> :An SQL error occurred when accessing a table.
<b>How to correct the error</b>
Database error text........: "ORA-25153: Temporary Tablespace is Empty"
Internal call code.........: "[RSQL/FTCH//SAPAPO/V_TRPROD ]".
Also , in the db02 of Quality the "PSAPTEMP" is showing 0KB size.
If trying to add new data file , it gives an error as file already exists.
Please suggest...
Regards
Anthony
The cause for the ORA-25153 error is because attempt was made to use space in a temporary tablespace with no files (no datafiles defined).
To solve the problem, the solution is just by adding files (datafiles) to the TEMP tablespace by using ADD TEMPFILE command, or by using Add Datafiles in Oracle Enterprise Manager
I mean :
1. If you are you able to create any other temporary tablespaces you can
create another temporary tablespace
CODE
create temporary tablespace temp1
tempfile '/database/temp_b.dbf' size 10m;
2. Then make this default temporary tablespace and drop the original default temporary tablespace
CODE
alter database default temporary tablespace temp1;
drop tablespace temporary including contents and datafiles;
3. Now create temporay tablespace with original name and make it as default tablespace of database
CODE
create temporary tablespace temporary
tempfile '/database/temp01.dbf' size 10240m
extent management local uniform size 128k;
alter database default temporary tablespace temporary;
4. Drop the the old one ( first you created)
CODE
drop tablespace temp1 including contents and datafiles;
if it helpful reward points are appreciated
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello,
Don't add a datafile ! It sounds like that the datafile is already there
What you need to do is to issue command
connect / as sysdba
alter tablespace PSAPTEMP add tempfile '<filename>' reuse;
Note 600513 - ORA-25153 after recovery due to missing tempfiles
explains exactly why
Please let me know if you need any more information
Regards
Andreas
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.