drop table scott.t0517_2;

create table t0517_2 tablespace TS0512_1 flashback archive fba0516_1 as select * from dba_objects where 1=2;
insert into t0517_2 select * from dba_objects where object_id is not null;
commit;

create unique index ind_t0517_2 on t0517_2(object_id) tablespace TS0512_1;

exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T0517_2',cascade=>TRUE);

explain plan for select * from t0517_2 where object_id=100;

set pagesize 100 linesize 150
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1917533861

-------------------------------------------------------------------------------------------
| Id? | Operation? ? ? ? ? ? ? ? ? | Name? ? ? ? | Rows? | Bytes | Cost (%CPU)| Time? ? |
-------------------------------------------------------------------------------------------
|? 0 | SELECT STATEMENT? ? ? ? ? ? |? ? ? ? ? ? |? ? 1 |? ? 91 |? ? 2? (0)| 00:00:01 |
|? 1 |? TABLE ACCESS BY INDEX ROWID| T0517_2? ? |? ? 1 |? ? 91 |? ? 2? (0)| 00:00:01 |
|*? 2 |? INDEX UNIQUE SCAN? ? ? ? | IND_T0517_2 |? ? 1 |? ? ? |? ? 1? (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

? 2 - access("OBJECT_ID"=100)

select count(*) from t0517_2;
? ? ? ? COUNT(*)
----------------
? ? ? ? ? 20176
? ? ? ? ?
set numwidth 16
select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
? ? ? ? ? 12723380596675

delete t0517_2;
commit;

col object_name format a20
set linesize 100
SCOTT@tstdb1-SQL> select object_name,created,object_id from dba_objects where object_name='T0517_2';

OBJECT_NAME? ? ? ? ? CREATED? ? ? ? ? ? ? ? ? OBJECT_ID
-------------------- ----------------- ----------------
T0517_2? ? ? ? ? ? ? 20150517 11:52:32? ? ? ? ? ? 95824

SCOTT@tstdb1-SQL> select owner,table_name,partitioned from dba_tables where table_name like '%95824';

OWNER? ? ? ? ? ? ? ? ? ? ? ? ? TABLE_NAME? ? ? ? ? ? ? ? ? ? PAR
------------------------------ ------------------------------ ---
SYS? ? ? ? ? ? ? ? ? ? ? ? ? ? SYS_MFBA_NHIST_95824? ? ? ? ? NO
SCOTT? ? ? ? ? ? ? ? ? ? ? ? ? SYS_FBA_HIST_95824? ? ? ? ? ? YES
SCOTT? ? ? ? ? ? ? ? ? ? ? ? ? SYS_FBA_TCRV_95824? ? ? ? ? ? NO
SCOTT? ? ? ? ? ? ? ? ? ? ? ? ? SYS_FBA_DDL_COLMAP_95824? ? ? NO

explain plan for select * from t0517_2 as of scn 12723380596675 where object_id=100;
set pagesize 100 linesize 150
select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 153423369