（52-31）第50次：2016-03-01星期二

botang

（52-31）第50次：上完1Z0-053第11章
2016-03-01-otr.sql：

select * from dba_flashback_archive;

select  * from dba_flashback_archive_tables;

select  * from dba_flashback_archive_ts;
---
select * from dba_tablespaces;

create tablespace tbs1 datafile size 10M autoextend on;

create flashback archive   default fda1
tablespace tbs1 quota 5M  retention 5 year;

select * from dba_flashback_archive;

create flashback archive   fda2
tablespace tbs1 quota 5M  retention 1 year;

select * from dba_flashback_archive;

alter flashback archive fda2 set default;

select * from dba_flashback_archive;

---

grant flashback archive on  fda2 to hr;

grant flashback archive on fda1 to hr;

---
select  * from dba_flashback_archive_tables;

select object_id  , object_name from dba_objects o
  where o.object_name  like 'T05311%';
  
  ----
  
  select  * from hr.SYS_FBA_HIST_80575;
  
  select  * from hr.SYS_FBA_HIST_80576;
  
  ---
  create undo tablespace undotbs2 datafile size 20M autoextend on;
  
  alter system set undo_tablespace='UNDOTBS2';
  
  drop tablespace undotbs1;
  
  select  * from dba_data_files;
  
  alter database datafile '+DATA/orcl/datafile/undotbs1.258.816169553' offline;
  
  alter flashback archive fda2 add
   tablespace users quota  10M ;
  
  select  * from dba_flashback_archive_ts;
  
alter flashback archive fda2 modify retention 3  month;

alter  flashback archive fda2  modify tablespace users quota 1M ;

----




drop flashback archive fda1;

alter flashback archive fda2  remove  tablespace users;


----

select * from dba_flashback_archive;

select  * from dba_flashback_archive_tables;

select  * from dba_flashback_archive_ts;


select  * from hr.SYS_FBA_HIST_80575;
---
select  * from dba_flashback_archive_tables;


---

begin
   dbms_flashback_archive.disassociate_fba(owner_name => 'HR',
                         table_name => 'T05311_C');
                end;
      

begin
   dbms_flashback_archive.reassociate_fba(owner_name => 'HR',
                         table_name => 'T05311_C');
                end;
---
select  * from hr.SYS_FBA_HIST_80609;

update hr.SYS_FBA_HIST_80609 set a=100 ;
---------------------------------------------------------------


purge dba_recyclebin;

select  * from dba_recyclebin;

select  * from dba_indexes i where i.owner='HR' and i.table_name='T05311_D';

purge tablespace users user hr;