Bo's Oracle Station

查看: 2381|回复: 0

第33次、第34次:2015-04-11上午和下午

[复制链接]

1005

主题

1469

帖子

1万

积分

管理员

Rank: 9Rank: 9Rank: 9

积分
12012
发表于 2015-4-12 08:16:47 | 显示全部楼层 |阅读模式
2015-04-11-a.sql
  1. select  * from v$transaction;

  2. select * from v$session where sid in ( select  blocking_session from v$session where  blocking_session is not null) ;

  3. alter system kill session '197,9' immediate;

  4. select * from dba_Tablespaces;

  5. alter tablespace undotbs1 retention guarantee;

  6. select  * from  dict where table_name like '%UNDO%'  or table_name like '%ROLL%';

  7. select  * from V$UNDOSTAT;
复制代码
2015-04-11-b.sql:
  1. select    *   from dba_temp_files;

  2. alter tablespace temp add tempfile '/media/123_BACKUP2OF5/4/temp02.dbf' size 20G ;

  3. alter tablespace temp drop tempfile '+FRA/orcl/tempfile/temp.301.875531631';

  4. select * from dba_tablespaces;

  5. create  undo   tablespace  undotbs2 datafile size 265K autoextend off;

  6. alter tablespace undotbs1  retention noguarantee;

  7. select * from v$transaction;

  8. drop tablespace undotbs1;

  9. select  * from v$session where taddr='00000001E45BA768';

  10. alter system kill session '139,347' immediate;

  11. ---

  12. select  * from dba_rollback_segs;
复制代码
2015-04-11-c.sql:
  1. select  * from v$transaction;

  2. select  * from flashback_transaction_query  where table_name='EMPLOYEES'  and table_owner='HR' ;

  3. select   d.SUPPLEMENTAL_LOG_DATA_MIN,
  4.             d.SUPPLEMENTAL_LOG_DATA_PK,
  5.             d.SUPPLEMENTAL_LOG_DATA_UI,
  6.             d.SUPPLEMENTAL_LOG_DATA_FK,
  7.             d.SUPPLEMENTAL_LOG_DATA_ALL,
  8.             d.SUPPLEMENTAL_LOG_DATA_PL
  9.         from v_$database  d  ;
  10.         
  11.         alter database add supplemental log data ;
  12.         
  13.         select versions_xid, versions_startscn  , versions_starttime, versions_endscn , versions_endtime , versions_operation, salary  from hr.employees
  14.            versions  between timestamp   sysdate-15/1440 and sysdate  where employee_id=100 ;
复制代码

2015-04-11-d.sql:
  1. select  * from v$restore_point;

  2. create restore point  update90000 ;

  3. select  t.row_movement  from dba_Tables t where t.owner='HR' and t.table_name='EMPLOYEES';

  4. alter table hr.employees enable row movement;


  5.         select versions_xid, versions_startscn  , versions_starttime, versions_endscn , versions_endtime , versions_operation, salary  from hr.employees
  6.            versions  between timestamp   sysdate-15/1440 and sysdate  where employee_id=100 ;

  7. create restore point  t05310_a ;
复制代码

2015-04-11-e.sql:
  1. select    xid , count(*)    from flashback_transaction_query  where table_owner='HR' and table_name='FLASH1'
  2. group by xid;

  3. select  * from flashback_transaction_query  where table_owner='HR' and table_name='FLASH1'  ;

  4. 0A000D00BC040000

  5. declare
  6.    v_1 sys.xid_array;
  7.   begin
  8.     v_1 := sys.xid_array('0A000D00BC040000');
  9.      dbms_flashback.transaction_backout(1,v_1,dbms_flashback.cascade);
  10.   end;

  11. commit;

  12. select  * from  DBA_FLASHBACK_TXN_STATE ;
  13. --0500150028060000
  14. select * from  DBA_FLASHBACK_TXN_REPORT ;
复制代码

t05310.sql:
  1. undefine s1
  2. create table &&s1( id number ) ;
  3. ! sleep 5
  4. insert into &&s1 values (1) ;
  5. insert into &&s1 values (2) ;
  6. insert into &&s1 values (3) ;
  7. commit;
  8. update &&s1 set id=11 where id=1;
  9. update &&s1 set id=22 where id=2;
  10. update &&s1 set id=33 where id=3;
  11. commit;
  12. update &&s1 set id=222 where id=22;
  13. update &&s1 set id=333 where id=33;
  14. commit;
  15. update &&s1 set id=22 where id=222;
  16. commit;
复制代码











1. 单点查询     undo  xxx
2. 版本查询     undo   xxx
3. 事务查询     undo    xxx;  undo_sql  要开 alter database  add supplement log data;  flashback_transaction_query (start_scn)



4. flashback table   undo  xxx, (关不掉)

5. flashback drop table  recycle bin xxx (可以关掉)

6.  flashback database 需要打开,不需要专门supplement log data   记文件系统delta  , delta2 , delta3
6.1 guarantee flashback database;

select   * from flashback_transaction_query  where table_name = 'T7'  order by  start_scn desc;
declare
   xids sys.xid_array;
begin
    xids := sys.xid_array('080001002E030000');
    dbms_flashback.transaction_backout(1,xids,options=>dbms_flashback.cascade);
   end;


回复

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|Bo's Oracle Station   

GMT+8, 2024-3-29 00:20 , Processed in 0.123892 second(s), 24 queries .

快速回复 返回顶部 返回列表