cancel
Showing results for 
Search instead for 
Did you mean: 

ORA-25153: Temporary Tablespace is Empty After Database Copy

Former Member
0 Kudos

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

Accepted Solutions (1)

Accepted Solutions (1)

former_member239282
Active Participant
0 Kudos

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

Answers (1)

Answers (1)

Former Member
0 Kudos

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