Hi Tom,
I'm trying to understand how it works Flashback Archive.

I have problem with commands create_temp_history_table and import_history

This is the script:


create table prueba_fb2 (c_cadena varchar2(1000), n_numero number);

CREATE FLASHBACK ARCHIVE fa_auditoria
TABLESPACE datos
RETENTION 1 YEAR;

ALTER TABLE prueba_fb2 FLASHBACK ARCHIVE fa_auditoria;

insert into prueba_fb2 values ('a', 1);
insert into prueba_fb2 values ('b', 2);
insert into prueba_fb2 values ('c', 3);
commit;


Create or replace view v_prueba_fb2_aud AS
select prueba_fb2.*,
VERSIONS_STARTTIME F_Auditoria,
nvl(VERSIONS_OPERATION,'I') OP,
decode (versions_xid, null,null, dbms_flashback_archive.get_sys_context(versions_xid, 'USERENV','SESSION_USER')) Usuario,
decode (versions_xid, null,null, dbms_flashback_archive.get_sys_context(versions_xid, 'USERENV','MODULE')) Programa,
decode (versions_xid, null,null, dbms_flashback_archive.get_sys_context(versions_xid, 'USERENV','TERMINAL')) Maquina
from prueba_fb2
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
where versions_xid is not null
order by F_Auditoria;


select *
from prueba_fb2;

select *
from v_prueba_fb2_aud;

exec DBMS_FLASHBACK_ARCHIVE.create_temp_history_table('PRUEBAS', 'PRUEBA_FB2')

select * from temp_history;

EXEC DBMS_FLASHBACK_ARCHIVE.extend_mappings();

insert into temp_history (RID, STARTSCN, ENDSCN, XID, OPERATION, C_CADENA, N_NUMERO)
values(null, --RID
timestamp_to_scn(to_date('3-02-2017 14:40:00','dd-mm-yyyy hh24:mi:ss')), -- Fecha de cambio transformada a timestamp
timestamp_to_scn(to_date('3-02-2017 14:50:00','dd-mm-yyyy hh24:mi:ss')), -- Idem anterios
null, -- XID
'I', -- Indicar si fue U, I, D (update, insert o delete)
'ZZ', 999);
commit;

exec DBMS_FLASHBACK_ARCHIVE.import_history ('PRUEBAS', 'PRUEBA_FB2', 'TEMP_HISTORY')

select *
from v_prueba_fb2_aud;


When I execute command create_temp_history_table, is's create table temp_history, but it is empty. Should not contain audit information?
And then, when I execute command import_history, I don't see the new data added to audit information (v_prueba_fb2_aud). Why? What is the problem?

Thanks,
Adrián

CREATE FLASHBACK ARCHIVE fa_auditoria
TABLESPACE users
RETENTION 1 YEAR;

create table prueba_fb2 (c_cadena varchar2(1000), n_numero number);

ALTER TABLE prueba_fb2 FLASHBACK ARCHIVE fa_auditoria;

insert into prueba_fb2 values ('a', 1);
insert into prueba_fb2 values ('b', 2);
insert into prueba_fb2 values ('c', 3);

commit;

exec DBMS_FLASHBACK_ARCHIVE.create_temp_history_table(user, 'PRUEBA_FB2');

select * from temp_history; 

 0 rows selected 

/* Get object_id to find name of SYS_FBA_HIST_nnnn table */
select object_id from user_objects
where  object_name = 'PRUEBA_FB2';

OBJECT_ID  
124,917   

desc sys_fba_hist_124917

Name      Null Type           
--------- ---- -------------- 
RID            VARCHAR2(4000) 
STARTSCN       NUMBER         
ENDSCN         NUMBER         
XID            RAW(8 BYTE)    
OPERATION      VARCHAR2(1)    
C_CADENA       VARCHAR2(1000) 
N_NUMERO       NUMBER 

desc temp_history

Name      Null Type           
--------- ---- -------------- 
RID            VARCHAR2(4000) 
STARTSCN       NUMBER         
ENDSCN         NUMBER         
XID            RAW(8 BYTE)    
OPERATION      VARCHAR2(1)    
C_CADENA       VARCHAR2(1000) 
N_NUMERO       NUMBER 

insert into temp_history
  select * from sys_fba_hist_124917;

insert into temp_history
  select rowid, versions_starttime, versions_endtime, versions_xid, 
         versions_operation, p.*
  from   prueba_fb2 versions between scn minvalue and maxvalue p;