Re: optimizer uses objects in Recyclebin or not!- Bug?

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sun, 24 Nov 2019 15:38:18 +0000
Message-ID: <CWXP265MB1750C973437B2FB135F64F26A54B0_at_CWXP265MB1750.GBRP265.PROD.OUTLOOK.COM>

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_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Vishnu Potukanuma <vishnupotukanuma_at_gmail.com> 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

--
http://www.freelists.org/webpage/oracle-l

Received on Sun Nov 24 2019 - 16:38:18 CET