col object_name format a20
set linesize 100
select object_name,created,object_id from dba_objects where object_name='T0517_1';
OBJECT_NAME CREATED OBJECT_ID
-------------------- ----------------- ----------------
T0517_1 20150517 08:39:46 37584
select owner,table_name,partitioned from dba_tables where table_name like '%37584';
no rows selected
explain plan for select * from t0517_1 as of scn 12723378820886;
SYS@tstdb1-SQL> set pagesize 100 linesize 150
SYS@tstdb1-SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1027524507
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 82 | 1066 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| T0517_1 | 82 | 1066 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------
//////////////// Part 3. SYS_FBA表的性能优化 /////////////////
能够对SYS_FBA表进行的操作十分有限，除了select之外，就只有create index和收集统计信息，就连导入导出都受到限制(只能用exp/imp，不能用expdp/impdp)
alter table scott.t0517_2 no flashback archive;
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';