One of the features in 10g is flashback which allows to restores tables that were dropped. Note: If an index is dropped but not a table it can’t be restored from flashback but if a table is dropped that has an index you can restore the table and the index from flashback.

The example below shows a case where table is dropped that has an index.
— create table
SQL> create table test ( x number(1) constraint test not null);

Table created.

— create index on the table test
SQL> create index test_idx on test(x);

Index created.

— show recyclebin it’s empty
SQL> show recyclebin;

SQL> column object_name format a30

— show objects
SQL> select object_name from user_objects;

OBJECT_NAME
——————————
TEST_IDX
TEST

— drop table
SQL> drop table test;

Table dropped.

— show recyclebin which only shows the table
SQL> show recyclebin;

ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
—————- —————————— ———— ——————-
TEST BIN$WLAh2JvbQDDgQ6wbCh1AMA==$0 TABLE 2008-10-07:13:58:17

— flashback the table
SQL> flashback table test to before drop;

Flashback complete.

— shows the object is restored and index too but it has name from the recycle-bin
SQL> select object_name from user_objects;

OBJECT_NAME
——————————
TEST
BIN$WLAh2JvaQDDgQ6wbCh1AMA==$0

— show indexes it still has the name from the recyle-bin
SQL> select index_name from user_indexes;

INDEX_NAME
——————————
BIN$WLAh2JvaQDDgQ6wbCh1AMA==$0

— shows the index is linked to the table
SQL> select table_name, index_name from user_indexes;

TABLE_NAME INDEX_NAME
—————————— ——————————
TEST BIN$WLAh2JvaQDDgQ6wbCh1AMA==$0

— restore the index name back
SQL> ALTER INDEX “BIN$WLAh2JvaQDDgQ6wbCh1AMA==$0” RENAME TO test_idx;

Index altered.

— shows the updated index name
SQL> select table_name, index_name from user_indexes;

TABLE_NAME INDEX_NAME
—————————— ——————————
TEST TEST_IDX

Here is second example showing index is not part of recyclebin when dropped.
SQL> create table test ( x number(1) constraint test not null);

Table created.

SQL> create index test_idx on test(x);

Index created.

SQL> select object_name from user_objects;

OBJECT_NAME
——————————————————————————

TEST
TEST_IDX

SQL> show recyclebin;
SQL> select * FROM user_recyclebin;

no rows selected

SQL> drop index test_idx;

Index dropped.

SQL> select * FROM user_recyclebin;

no rows selected

SQL> show recyclebin;
SQL> select index_name from user_indexes;

no rows selected