SQL> create tablespace tbs_fda datafile '/u01/app/oracle/oradata/orcl/tbs_fba_01.dbf' size 10M;
Tablespace created.
SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/orcl/undotbs2_01.dbf' size 10M;
Tablespace created.
SQL> ALTER SYSTEM SET UNDO_TABLESPACE = undotbs2;
System altered.
SQL> create flashback archive fda tablespace tbs_fda retention 1 month;
Flashback archive created.
SQL> alter flashback archive fda set default;
Flashback archive altered.
SQL> alter table hr.employee flashback archive fda;
alter table hr.employee flashback archive fda
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> alter table hr.employees flashback archive fda;
Table altered.
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1057235
SQL> select employee_id,first_name,salary from hr.employees where first_name='Pat';
EMPLOYEE_ID FIRST_NAME SALARY
----------- -------------------- ----------
202 Pat 6000
SQL> update hr.employees set salary=salary+500 where first_name='Pat';
1 row updated.
SQL> commit;
Commit complete.
SQL> set autotrace on;
SQL> select employee_id,first_name,salary from hr.employees as of scn 1059870 wh ere first_name = 'Pat';
EMPLOYEE_ID FIRST_NAME SALARY
----------- -------------------- ----------
202 Pat 6500
Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 15 | 6 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES | 1 | 15 | 6 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("FIRST_NAME"='Pat')
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
557 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autotrace off;
SQL> crate tablehr.obj as select * from dba_objects;
SP2-0734: unknown command beginning "crate tabl..." - rest of line ignored.
SQL> crate table hr.obj as select * from dba_objects;
SP2-0734: unknown command beginning "crate tabl..." - rest of line ignored.
SQL> create table hr.obj as select * from dba_objects;
Table created.
SQL> insert into hr.obj select * from hr.obj;
72460 rows created.
SQL> commit;
Commit complete.
SQL> delete from hr.obj;
delete from hr.obj
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS3'
SQL> set autotrace on;
SQL> select employee_id,first_name,salary from hr.employees as of scn 105 7235 where first_name = 'Pat';
EMPLOYEE_ID FIRST_NAME SALARY
----------- -------------------- ----------
202 Pat 7000
Execution Plan
----------------------------------------------------------
Plan hash value: 4199766622
-------------------------------------------------------------------------------- -------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%C PU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------- -------------------------------
| 0 | SELECT STATEMENT | | 2 | 76 | 11 ( 10)| 00:00:01 | | |
| 1 | VIEW | | 2 | 76 | 11 ( 10)| 00:00:01 | | |
| 2 | UNION-ALL | | | | | | | |
| 3 | PARTITION RANGE SINGLE| | 1 | 64 | 2 ( 0)| 00:00:01 | 1 | 1 |
|* 4 | TABLE ACCESS FULL | SYS_FBA_HIST_73933 | 1 | 64 | 2 ( 0)| 00:00:01 | 1 | 1 |
|* 5 | FILTER | | | | | | | |
|* 6 | HASH JOIN OUTER | | 1 | 2055 | 9 ( 12)| 00:00:01 | | |
|* 7 | TABLE ACCESS FULL | EMPLOYEES | 1 | 27 | 6 ( 0)| 00:00:01 | | |
|* 8 | TABLE ACCESS FULL | SYS_FBA_TCRV_73933 | 1 | 2028 | 2 ( 0)| 00:00:01 | | |
-------------------------------------------------------------------------------- -------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("FIRST_NAME"='Pat' AND "ENDSCN">1057235 AND "ENDSCN"<=1060175 AND ("STARTSCN" IS NULL OR
"STARTSCN"<=1057235))
5 - filter("STARTSCN"<=1057235 OR "STARTSCN" IS NULL)
6 - access("T".ROWID=CHARTOROWID("RID"(+)))
7 - filter("T"."VERSIONS_STARTSCN" IS NULL AND "T"."FIRST_NAME"='Pat')
8 - filter(("ENDSCN"(+) IS NULL OR "ENDSCN"(+)>1060175) AND ("STARTSCN"(+) IS NULL OR
"STARTSCN"(+)<1060175))
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
26 recursive calls
0 db block gets
29 consistent gets
0 physical reads
0 redo size
557 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> set autotrace off;
SQL> alter table hr.emplyees no flashback archive;
alter table hr.emplyees no flashback archive
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> alter table hr.employees no flashback archive;
Table altered.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/15874751/viewspace-757507/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
