From the manual
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/FLASHBACK-TABLE.html#GUID-FA9AF2FD-2DAD-4387-9E62-14AFC26EA85C
Notes on Flashing Back Dropped Tables The following notes apply to flashing
back dropped tables:
Oracle Database retrieves all indexes defined on the table retrieved from
the recycle bin except for bitmap join indexes and domain indexes. (Bitmap join
indexes and domain indexes are not put in the recycle bin during a DROP TABLE
operation, so cannot be retrieved.)
The database also retrieves all triggers and constraints defined on the
table except for referential integrity constraints that reference other tables.
The retrieved indexes, triggers, and constraints have recycle bin names.
Therefore it is advisable to query the USER_RECYCLEBIN view before issuing a
FLASHBACK TABLE ... TO BEFORE DROP statement so that you can rename the
retrieved triggers and constraints to more usable names.
===========================
The behaviour you describe is expected, and the manuals advise a strategy to
deal with it.
It's not too surprising that this is the case - you wouldn't want a flashback
to fail because it attempted (for example) to recreate an index name and found
that someone else had created an index of the same name on a different table.
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Vishnu Potukanuma <vishnupotukanuma@xxxxxxxxx>
Sent: 24 November 2019 12:56
To: Oracle L
Subject: optimizer uses objects in Recyclebin or not!- Bug?
Can across an interesting scenario today and the case is as follows: it is
reproducible.
create table temp (roll number, name varchar2(20), mark1 number, mark2 number,
mark3 number);
alter table temp add constraint temp_pk primary key (roll);
insert into temp select rownum,'asdf',1,1,1) from dual connect by level < 10000;
commit;
populating the table with entries.. .
drop table temp;
flashback table temp to before drop;
This is where things get crazy. Can optimizer make use of access paths related
to the objects in recyclebin? when the query references a table that is not
dropped...
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 46 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEMP2 | 1 | 46 | 2 (0)|
00:00:01 |
|* 2 | INDEX UNIQUE SCAN | BIN$mBfmHJAdEPvgUw8AAAqw7g==$0 | 1 | |
1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
Oracle 19c documentation suggests that Oracle Database retrieves all indexes
defined on the table except for bitmap join indexes, and all triggers and
constraints defined on the table except for referential integrity constraints
that reference other tables.
in a sense it is retriving all the indexes defined on the table, but the naming
format appears to be BIN$ which is the case for the objects in recyclebin...
things get even weird here... object name appears to be in BIN...
SQL> purge index "BIN$mBfmHJAdEPvgUw8AAAqw7g==$0";
purge index "BIN$mBfmHJAdEPvgUw8AAAqw7g==$0"
*
ERROR at line 1:
ORA-38307: object not in RECYCLE BIN
So the object is not in recyclebin but the naming doesn't make sense....
1. flashback index doesn't make sense as the syntax is not valid
2. Index is already restored.
the only way to restore to its previous name is to rename it.
SQL> alter index "BIN$mBfmHJAdEPvgUw8AAAqw7g==$0" rename to temp_pk;
Index altered.
Can someone please tell me whether i missed something something here or Is it a
bug?
Thanks,
Vishnu
--
//www.freelists.org/webpage/oracle-l