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

botang · 发表于 2015-4-12 08:16:47

2015-04-11-a.sql

select * from v$transaction;
select * from v$session where sid in ( select blocking_session from v$session where blocking_session is not null) ;
alter system kill session '197,9' immediate;
select * from dba_Tablespaces;
alter tablespace undotbs1 retention guarantee;
select * from dict where table_name like '%UNDO%' or table_name like '%ROLL%';
select * from V$UNDOSTAT;

复制代码

2015-04-11-b.sql：

select * from dba_temp_files;
alter tablespace temp add tempfile '/media/123_BACKUP2OF5/4/temp02.dbf' size 20G ;
alter tablespace temp drop tempfile '+FRA/orcl/tempfile/temp.301.875531631';
select * from dba_tablespaces;
create undo tablespace undotbs2 datafile size 265K autoextend off;
alter tablespace undotbs1 retention noguarantee;
select * from v$transaction;
drop tablespace undotbs1;
select * from v$session where taddr='00000001E45BA768';
alter system kill session '139,347' immediate;
---
select * from dba_rollback_segs;

复制代码

2015-04-11-c.sql:

select * from v$transaction;
select * from flashback_transaction_query where table_name='EMPLOYEES' and table_owner='HR' ;
select d.SUPPLEMENTAL_LOG_DATA_MIN,
d.SUPPLEMENTAL_LOG_DATA_PK,
d.SUPPLEMENTAL_LOG_DATA_UI,
d.SUPPLEMENTAL_LOG_DATA_FK,
d.SUPPLEMENTAL_LOG_DATA_ALL,
d.SUPPLEMENTAL_LOG_DATA_PL
from v_$database d ;
alter database add supplemental log data ;
select versions_xid, versions_startscn , versions_starttime, versions_endscn , versions_endtime , versions_operation, salary from hr.employees
versions between timestamp sysdate-15/1440 and sysdate where employee_id=100 ;

复制代码

2015-04-11-d.sql:

select * from v$restore_point;
create restore point update90000 ;
select t.row_movement from dba_Tables t where t.owner='HR' and t.table_name='EMPLOYEES';
alter table hr.employees enable row movement;
select versions_xid, versions_startscn , versions_starttime, versions_endscn , versions_endtime , versions_operation, salary from hr.employees
versions between timestamp sysdate-15/1440 and sysdate where employee_id=100 ;
create restore point t05310_a ;

复制代码

2015-04-11-e.sql:

select xid , count(*) from flashback_transaction_query where table_owner='HR' and table_name='FLASH1'
group by xid;
select * from flashback_transaction_query where table_owner='HR' and table_name='FLASH1' ;
0A000D00BC040000
declare
v_1 sys.xid_array;
begin
v_1 := sys.xid_array('0A000D00BC040000');
dbms_flashback.transaction_backout(1,v_1,dbms_flashback.cascade);
end;
commit;
select * from DBA_FLASHBACK_TXN_STATE ;
--0500150028060000
select * from DBA_FLASHBACK_TXN_REPORT ;

复制代码

t05310.sql:

undefine s1
create table &&s1( id number ) ;
! sleep 5
insert into &&s1 values (1) ;
insert into &&s1 values (2) ;
insert into &&s1 values (3) ;
commit;
update &&s1 set id=11 where id=1;
update &&s1 set id=22 where id=2;
update &&s1 set id=33 where id=3;
commit;
update &&s1 set id=222 where id=22;
update &&s1 set id=333 where id=33;
commit;
update &&s1 set id=22 where id=222;
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;