-
A data file for Locally Managed Temporary Tablespace was accidentally dropped on the OS level.
I tried to restore, but got the following errors. How do you solve it?
SQL> select tablespace_name, file_name, bytes/1048576 "MB" from dba_temp_files;
select tablespace_name, file_name, bytes/1048576 "MB" from dba_temp_files
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
ORA-01110: data file 201: '/u03/oradata/ocom/big_temp01.dbf'
SQL> ALTER TABLESPACE BIG_TEMP ADD TEMPFILE '/u03/oradata/ocom/big_temp01.dbf' SIZE 1073741824;
ALTER TABLESPACE BIG_TEMP ADD TEMPFILE '/u03/oradata/ocom/big_temp01.dbf' SIZE 1073741824
*
ERROR at line 1:
ORA-01537: cannot add data file '/u03/oradata/ocom/big_temp01.dbf' - file already part of database
SQL> alter database create datafile '/u03/oradata/ocom/big_temp01.dbf' as '/u03/oradata/ocom/big_temp01.dbf';
alter database create datafile '/u03/oradata/ocom/big_temp01.dbf' as '/u03/oradata/ocom/big_temp01.dbf'
*
ERROR at line 1:
ORA-01516: nonexistent log file, datafile or tempfile '/u03/oradata/ocom/big_temp01.dbf'
-
Since it was a temp table, you can just drop it and recreate one, without any worry of loosing information.
Sam
-
Do you know how to drop and recreate it?
-
Hi
since its a temproray tablespace you are safe to drop and recreate it.
drop tablespace
-----------------------
ALTER DATABASE BIG_TEMP '/u03/oradata/ocom/big_temp01.dbf' DROP;
here the control file would be written to saying that your tempfile is dropped
recreate the tablespace
----------------------------
CREATE TEMPORARY TABLESPACE BIG_TEMP
TEMPFILE '/u03/oradata/ocom/big_temp01.dbf'
SIZE 20M REUSE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;
regards
hrishy
-
this just drops the datafile virtually
but physically it still xists on ur machine
u would have to delete the file manually...
it wont create any kinda problem..but it does eat up the space alloted to it...
