Hi and hope everyone is enjoying a nice Friday afternoon.
My day has been a bit rough since early this morning.
I have an Oracle 11.2.0.4 EE running on Rhel 6.4.
Early this morning, I found one of our critical DB servers was "hung".
Still don't know the root cause, but when we tried to reboot it, it tried to fsck the lvol that I have all my index tablespaces on and it just found one bad block after another and another.
Finally, it got past the fsck and all other file systems were fine, but of course, we lost the /u04 where the index datafiles where.
Fortunately, I only used that file system for indexes.
So, I mounted the database and issued commands to drop the index tablespace,
(alter database datafile '/path/to/filename' offline drop;)
and then was able to open the database (whew!!).
Next, I tried to drop tablespace tbsp_name including contents cascade constraints;
but I got errors that the constraints from indexes would not let me drop the tablespace.
So, for each index tied to the bad tablespace, I grabbed the constraints, and disabled them, dropped the indexes, created a new tablespace, and recreated all the indexes to the new tablespace.
All seems well, and the application and everything else is functioning as it should.
So, now, when I run the following query, I see something strange: temporary segments for the old indexes in the old tablespace.
I have not tried to drop it again since this is a critical application, and since all is working now, I don't want to risk doing something that would hose it up again.
So, here is the query that shows the temporary segments in the old tablespace, and also notice the segment_names.
BTW: I tried purging dba_recyclebin, but that didn't change anything.
SQL> select owner, segment_name, segment_type from dba_segments
2 where tablespace_name = 'INDEX1'
3 order by 1,2;
OWNER SEGMENT_NAME SEGMENT_TYPE
------------------------ -------------------------------- ------------------
PMILDENB 21.8386 TEMPORARY
PMILDENB 21.9906 TEMPORARY
PRVSCRPT 21.10194 TEMPORARY
PRVSCRPT 21.1024722 TEMPORARY
PRVSCRPT 21.10258 TEMPORARY
PRVSCRPT 21.107570 TEMPORARY
PRVSCRPT 21.11330 TEMPORARY
PRVSCRPT 21.11426 TEMPORARY
PRVSCRPT 21.11522 TEMPORARY
and many more....
Any ideas what these temporary segments are, and if I can safely drop the tablespace now using the command below:
drop tablespace index1 including contents cascade constraints;