We have just lost a tablespace on our live database (looks like someone ran the command "drop tablespace $$$ including contents"). The tablespace (GSIF) is gone! But we can see the datafile (e:oradatagsifgsifm01.dbf). Is there a way of recovering the tablespace with its original contents (schemas, etc.) without going to the backup?
Unfortunately, the DROP TABLESPACE INCLUDING CONTENTS command drastically modifies the Data Dictionary so that the segments (tables, indexes, etc.) in that tablespace are no longer known to the database. Even if you could "reattach" this file to the database, you would need some way to update the Data Dictionary. You could try to recreate the control file manually and include the tablespace; however the Data Dictionary still would not know of these segments.
Your best course of action here is to restore from a backup and roll forward up until the point in time of the DROP TABLESPACE command.
Dig Deeper on Oracle database backup and recovery
Oracle expert Brian Peasland answers one reader's question about common pitfalls when connecting Oracle to outside programs.
One reader asks expert Brian Peasland a question about datafile sizes with the Oracle RMAN duplicate 10g command.
Managing parent table-child table relations in Oracle SQL environments is key to efficient programming.