Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

how to create temp files in temp tablespace

Dear all,

Due to outage of our SAN, we our out of production for the

last 3 days. By the grace of Almighty we have restored production by

database recovery from our standby backup. Since temp tablespace and

temp data files do not taken as backup for standby, now after recovery

we are getting abap dumps asking for temp_1 and temp_2 datafiles.

Please guide us how to create temp files. v$tablespace is showing tablespace PSAPTEMP but datafile are not there

Abap dumps are giving these errors as mentioned below

====================================================

The exception must either be prevented, caught within the procedure

"DATA_SELECTION"

"(FORM)", or declared in the procedure's RAISING clause.

To prevent the exception, note the following:

Database error text........: "ORA-01157: cannot identify/lock data file 256 -

see DBWR trace file#ORA-01110: data file 256:

'/oracle/SD1/sapdata4/temp_2/temp.data2'"

Internal call code.........: "[RSQL/FTCH/MARA ]"

===================================================

another one asking for

'/oracle/SD1/sapdata3/temp_1/temp.data1'"

Best Regards

Waqas

replied

you can create a new TEMP Tablespace and drop the old one.

e.g:

They are only <b><u>examples</u></b>: You should change the <b><u>SIZES</u></b>.

SQL> <i>CREATE TEMPORARY TABLESPACE "<b><u>PSAPTEMP1</u></b>" TEMPFILE '<<b><u>PATH_NAME_OF_NEW_FILE</u></b>>' SIZE <<b><u>SIZE</u></b>>M REUSE AUTOEXTEND ON NEXT 20480K MAXSIZE 10000M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K</i>;

SQL> alter database default temporary tablespace <b><u>PSAPTEMP1</u></b>;

SQL> drop tablespace PSAPTEMP including contents and datafiles;

SQL> CREATE TEMPORARY TABLESPACE "<b><u>PSAPTEMP</u></b>" TEMPFILE '<<b><u>PATH_NAME_OF_NEW_FILE</u></b>>" SIZE <<b><u>SIZE</u></b>>M REUSE AUTOEXTEND ON NEXT 20480K MAXSIZE 10000M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1024K;

SQL> alter database default temporary tablespace <b><u>PSAPTEMP</u></b>;

SQL>drop tablespace <b><u>PSAPTEMP1</u></b> including contents and datafiles;

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question