-
i added a datafile to temp tablespace temp02.dbf
then i gave the cmd alter database datafile offline drop to drop the older one which lot of fragmentation.
and error of the kind ora3232.
then i removed it physically.
the dba_data_files still showed the temp01.dbf as available.
and queries started failing due to non-availability of temp01.
under v$datafile temp01 appeared as recover status and 0 bytes.
even under dba_free_space all records of temp01 are appearing with the original file id of 4.
recover datafile or tablespace or database are also
failing
it's an impasse now
[Edited by deswal_aja on 04-11-2001 at 07:42 PM]
-
It is not worth the while to drop and recreate the TEMP tablespace, because it will be fragmented pretty badly again. You could only achieve a v.minor performance boost.
Any how follow these steps, you would be fine:
1. Mount the database
2. Query the V$recovery_file and V4datafile to note the filenames that correspond to the file numbers:
select * from v$recovery_file;
3. Alter the database to take the file off line and drop
alter database datafile '.../TEMP01.dbf' OFFLINE DROP;
4. alter database open;
7. Drop the TEMP tablespace
select * from v$tablespace;
drop tablespace temp;
8. Create your temprory tablespace as:
CREATE tablespace temp datafile
'.../TEMP01.dbf' SIZE 500K REUSE;
9. Now you are all set.
Good luck,
Sam
[Edited by sambavan on 04-11-2001 at 09:35 PM]
-
though guidance came belated i must thank you sambavan
When i posted the problem i didn't have much time to
get the DB online.
i droped the temp tablespace and created a new one with 2 datafiles.
that solved the problem
mine is ora 8.1.6 on nt server with archive log enaled.
what bugged me was inconsistent behaviour of oracle.
i went about the task systematically by first adding a new datafile to the temp tablespace and then offlining then dropping followed by physically dropping the original datafile.
alter db datafile <> offline drop command got the datafile in v$datafile as 0 bytes and recover status.
and the same cmd when fired repeatedly gave the same response: statement processed.
why still the datafile appeared in dba_data_files as available and with original size?
why dba_free_space continued to show all the records for the dropped datafile?
and why execution of queries failed with error that datafile no.<> is locked/unavailable.
even export and recover tablespace/datafile cmds failed .
i can see any logic in this insistence on a particular file for just about any operation.
i even backed up the control file to trace and edited the trace file removing the line specifying that particular datafile.
at svrmgr i executed the edited tracefile.
then at least the dba_datafiles didn't show that datafile.
but queries like those creating indexes failed.
export also didn't work
on oracle 8.1.6 on hp-ux i remember having successfully removed the only datafile in temp tbs and the adding new datafile with no problem being reported later.
-
From my understanding, and I could be wrong, was that you cannot 'normally' drop datafiles, except in recovery procedure. Has this changed, or have I missed something?
Nirasha
-
adding and dropping datafiles which otherwise is a routine affair seems to somewhat different when it comes to the primary datafile of the temp tablespace.
i myself don't have a convincing answer to that.
i'll retrace all these steps on my test db before i can get a clear idea.
-
I don't think it has changed.
The command is really misleading and tempting many to do the "drop data file".
There are many threads here which discuss the repercussions of issuing command.
-
1) You can only drop a tablespace but not a datafile, although your can bring a datafile offline.
2) Deframentation in temp tablespace is not something you should worry.
3) In case temp tablespace is corrupted or not working, create another temporary tablepsace and change default temporay tablespace of all users to the new temporary tablespace. But it should be done with care, especially during peak time.
