oracle10g英文原厂培训

1、Recovering from User ErrorsObjectivesAfter completing this lesson, you should be able to:Perform Flashback table operationManage the recycle binRecover from user errors using Flashback versions queryPerform transaction level recovery using Flashback Transaction query Flashback Time NavigationFlashba

2、ck QueryQuery all data at a specified point in timeFlashback Versions QuerySee all versions of a row between two timesSee the transactions that changed the rowFlashback Transaction QuerySee all changes made by a transactionTx3Tx1Tx2TimeFlashbackFlashback Drop OverviewDROP TABLE employees;FLASHBACK T

3、ABLEemployeesTO BEFORE DROP;Mistake wasmadeRecycle BinDROP TABLE employees; BIN$zbjra9wy=$0EMPLOYEES_PKEMPLOYEESRecyclebinDBA_FREE_SPACEBIN$zbjrBdpw=$0USER_OBJECTSBIN$zbjrBdpw=$0 EMPLOYEESBIN$zbjra9wy=$0 EMPLOYEES_PK1234Querying the Recycle BinSELECT owner, original_name, object_name, type, ts_name,

4、 droptime, related, spaceFROM dba_recyclebinWHERE can_undrop = YES;SELECT original_name, object_name, type, ts_name, droptime, related, spaceFROM user_recyclebinWHERE can_undrop = YES;SQL SHOW RECYCLEBINFlashback Dropped Tables Using EMRestoring Objects from the Recycle BinUse the FLASHBACK TABLE co

5、mmand to restore dropped tables and dependent objects.If multiple recycle bin entries have the same original name:Use unique system-generated names to restore a particular version.When using original names, restored table is LIFO.Rename the original name if that name is currently used.FLASHBACK TABL

6、E TO BEFORE DROP RENAME TO Recycle Bin Automatic Space ReclamationBIN$zbjrBdpw=$0 BIN$zbjra9wy=$0 BIN$zbjra9wy=$0BIN$zbjrBdpw=$0123Recycle binDBA_FREE_SPACE - RECYCLEBINAutoextendRecycle Bin Manual Space ReclamationPURGE TABLE |INDEX PURGE TABLESPACE USER PURGE USER_|DBA_RECYCLEBINBypassing the Recy

7、cle BinDROP TABLESPACE INCLUDING CONTENTS ;DROP USER CASCADE ;DROP TABLE PURGE ;Querying Dropped TablesSELECT . FROM BIN$zbjrBdpw=$0 AS OF .WHERE . RecyclebinUSER_INDEXESYESINDEX_NAMEDROPPEDNOSALES_PKUSER_TABLESBIN$zbjrBdpw=$0 EMPLOYEESBIN$zbjra9wy=$0 EMPLOYEES_PKTABLE_NAMEDROPPEDYESNOSALESFlashback

8、 Drop Considerations Protected tables:Are non-SYSTEM tablespace tablesAre stored in locally managed tablespacesDo not use fine-grained auditing or virtual private databaseThe following dependencies are not protected:Bitmap-join indexesMaterialized view logsReferential integrity constraintsIndexes dr

9、opped before tablesPurged tables cannot be flashed backFlashback Versions Query Overviewt1t2Tx1Tx2SELECT versions_xid, salary FROM employeesVERSIONS BETWEEN TIMESTAMP t1 and t2WHERE last_name = Fox;Tx0Tx0Tx1Tx2 Employees EmployeesFox EmployeesFlashback Versions Query Using EMFlashback Versions Query

10、 SyntaxPseudocolumnDescriptionVERSIONS_STARTTIMEVERSIONS_STARTSCNVersion validity range lower boundVERSIONS_ENDTIMEVERSIONS_ENDSCNVersion validity range upper boundVERSIONS_XIDTransaction that created the versionVERSIONS_OPERATIONOperation that produced the versionSELECT PseudocolumnsFROM VERSIONS B

11、ETWEEN SCN|TIMESTAMP expr|MINVALUE AND expr|MAXVALUE AS OF SCN|TIMESTAMP exprWHERE PseudocolumnsFlashback Versions Query ExampleSELECT versions_xid AS XID, versions_startscn AS START_SCN, versions_endscn AS END_SCN, versions_operation AS OPERATION, first_nameFROM employees VERSIONS BETWEEN SCN MINVA

12、LUE AND MAXVALUEAS OF SCN 5525300WHERE employee_id = 111;XID START_SCN END_SCN O First_NAME- - - - -8C0025003A000000 5525293 I Tom8C0024003A000000 5525291 D Mike8C0022003A000000 5525277 5525291 I MikeFlashback Versions Query ConsiderationsThe VERSIONS clause cannot be used to query:External tablesTe

13、mporary tablesFixed tablesViewsThe VERSIONS clause cannot span DDL commands.Segment shrink operations are filtered out.Flashback Transaction Query OverviewUndoSQLFLASHBACK_TRANSACTION_QUERYDBAUSERErroneousDMLQuerying FLASHBACK_TRANSACTION_QUERYSELECT operation, undo_sql, table_nameFROM FLASHBACK_TRA

14、NSACTION_QUERY;SELECT operation, undo_sql, table_nameFROM FLASHBACK_TRANSACTION_QUERYWHERE xid = HEXTORAW(8C0024003A000000)ORDER BY undo_change#;SELECT operation, undo_sql, table_nameFROM FLASHBACK_TRANSACTION_QUERYWHERE start_timestamp = TO_TIMESTAMP(2003-10-21 11:00:00,YYYY-MM-DD HH:MI:SS)AND comm

15、it_timestamp CREATE UNDO TABLESPACE undotbs1 2 DATAFILE undotbs01.dbf 3 SIZE 100M AUTOEXTEND ON 4 RETENTION GUARANTEE ;SQL SELECT tablespace_name, RETENTION 2 FROM dba_tablespaces;TABLESPACE_NAME RETENTION- -UNDOTBS1 GUARANTEE. .SQL ALTER TABLESPACE undotbs1 2 RETENTION NOGUARANTEE; SCN and Time Map

16、ping EnhancementsThe mapping granularity is three seconds.The mapping is retained for Max(five days, UNDO_RETENTION)Access the mapping by using the following SQL functions:SCN_TO_TIMESTAMPTIMESTAMP_TO_SCNSELECT current_scn, SCN_TO_TIMESTAMP(current_scn)FROM v$database;CURRENT_SCN SCN_TO_TIMESTAMP(CURRENT_SCN)- -3274940 23-SEP-03 02.56.03.000000000 AMSummaryIn this lesson, you should have learned how to:Perform Flashback table operationManage the recycle binRecover from user errors using Flashback versions queryPerform transaction level recovery using FB T