In our Production database is has been decided to drop a tablespace because already a datafile was lost due to
accidentally file was dropped at OS level with command rm -rf, somehow luckily there was not huge loss but some of index which were created later.
In that tablespace there were many datafiles ,later decided to move all rest of objects from existing tablespace to new tablespace and drop the old tablespace, Existing tablespace was put offline.
In this scenario we found we were not able to select the tables but we could drop the tables and flashback the table as well.
Please explain the scenario THANKS IN ADVANCE......
I would need to see some evidence of the flashback working. The 'drop' will work because it does not need to touch the data - just the dictionary.
Example - tablespace is online
==============================
SQL> create table t tablespace no_assm
2 as select * from dba_objects;
Table created.
SQL>
SQL> alter table t enable row movement;
Table altered.
SQL>
SQL> select count(*) from t;
COUNT(*)
----------
78804
1 row selected.
SQL>
SQL> select dbms_flashback.get_system_change_number x from dual;
X
----------------------------
14816199469542
1 row selected.
SQL>
SQL> delete from t
2 where rownum < 1000;
999 rows deleted.
SQL> commit;
Commit complete.
SQL> flashback table t to scn 14816199469543;
Flashback complete.
SQL> select count(*) from t;
COUNT(*)
----------
78804
1 row selected.
SQL>
Example - tablespace is offline
==============================
SQL> create table t tablespace no_assm
2 as select * from dba_objects;
Table created.
SQL>
SQL> alter table t enable row movement;
Table altered.
SQL>
SQL> select count(*) from t;
COUNT(*)
----------
78805
1 row selected.
SQL>
SQL> select dbms_flashback.get_system_change_number x from dual;
X
----------------------------
14816199469676
1 row selected.
SQL>
SQL> delete from t
2 where rownum < 1000;
999 rows deleted.
SQL> commit;
Commit complete.
SQL>
SQL> alter tablespace no_assm offline;
Tablespace altered.
SQL> flashback table t to scn 14816199469677;
flashback table t to scn 14816199469677
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-00376: file 11 cannot be read at this time
ORA-01110: data file 11: 'C:\ORACLE\ORADATA\DB122\NO_ASSM.DBF'
SQL> drop table t ;
Table dropped.