create bigfile tablespace ARCHIVE_TS datafile size 32M autoextend on next 32M;

create flashback archive default ARCHIVE_7_DAY
        tablespace ARCHIVE_TS 
        quota 1G
        retention 7 DAY;


create table TAB1 (
  ID           number,
  DESCRIPTION  varchar2(50),
  constraint TAB_1_PK primary key (id)
);

alter table TAB2 flashback archive ARCHIVE_7_DAY;


insert into TAB2 values(1,'One',TIMESTAMP'2023-01-01 00:00:00');
commit;
insert into TAB2 values(2,'Two',TIMESTAMP'2023-01-01 00:00:00');
commit;
insert into TAB2 values(3,'Three',TIMESTAMP'2023-01-01 00:00:00');
commit;

alter table TAB2 add C2 varchar2(3);

update TAB2 set C2='abc' where n1=1;
update TAB2 set C2='***' where n1=1;
commit;
update TAB2 set C2='def' where n1=1;
commit;


alter table TAB2 drop column C2;
alter table TAB2 rename column C1 to C3; 

update TAB2 set d1=systimestamp where n1=1;
commit;

update TAB2 set d1=TIMESTAMP'1973-10-05 10:00:00',C3='birthday' where n1=1;
commit;

update TAB2 set d1=systimestamp,C3='right now' where n1=1;
commit;





Query: select * from TAB2 as of timestamp systimestamp-1/24+21/24/60 where N1=1;





SQL> @desc SYS_FBA_DDL_COLMAP_26338
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      STARTSCN                                 NUMBER
    2      ENDSCN                                   NUMBER
    3      XID                                      RAW(8)
    4      OPERATION                                VARCHAR2(1)
    5      COLUMN_NAME                              VARCHAR2(255)
    6      TYPE                                     VARCHAR2(255)
    7      HISTORICAL_COLUMN_NAME                   VARCHAR2(255)

SQL> @desc SYS_FBA_HIST_26338
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      RID                                      VARCHAR2(4000)
    2      STARTSCN                                 NUMBER
    3      ENDSCN                                   NUMBER
    4      XID                                      RAW(8)
    5      OPERATION                                VARCHAR2(1)
    6      N1                                       NUMBER
    7      C3                                       VARCHAR2(10)
    8      D1                                       DATE
    9      D_4335990_C2                             VARCHAR2(3)

    

set lines 200
col STARTSCN for 9999999999
col ENDSCN for 9999999999
col HISTORICAL_COLUMN_NAME for a30
col COLUMN_NAME for a30
col XID noprint
col TYPE for a20

select * from SYS_FBA_DDL_COLMAP_26338 order by STARTSCN;


   STARTSCN      ENDSCN O COLUMN_NAME                    TYPE                 HISTORICAL_COLUMN_NAME
----------- ----------- - ------------------------------ -------------------- ------------------------------
    4297455               N1                             NUMBER               N1
    4297455     4336109   C3                             VARCHAR2(10)         C1
    4297455               D1                             DATE                 D1
    4335662     4335990   D_4335990_C2                   VARCHAR2(3)          C2
    4336109               C3                             VARCHAR2(10)         C3


col RID noprint
col XID noprint
col OPERATION noprint

select * from SYS_FBA_HIST_26338 order by STARTSCN;

   STARTSCN      ENDSCN XID              O         N1 C3         D1                  D_4
----------- ----------- ---------------- - ---------- ---------- ------------------- ---
    4336404     4336452 08000200AE020000 U          1 birthday   1973-10-05 10:00:00
    4298014     4335762 08000700A5020000 U          1 One        2023-12-31 23:59:59
    4336266     4336404 06000400A2020000 U          1 One        2023-06-18 15:12:51
    4335762     4335824 09000A00B4020000 U          1 One        2023-12-31 23:59:59 ***
    4335996     4336266                  U          1 One        2023-12-31 23:59:59
    4335824     4335996 02000300AE020000 U          1 One        2023-12-31 23:59:59 def
    4297497     4335996 0300190095020000 I          2 Two        2023-01-01 00:00:00
    4297630     4335996 0600200092020000 I          3 Three      2023-01-01 00:00:00
    4297491     4298014 0400180090020000 I          1 One        2023-01-01 00:00:00
    4336452     4337054 07001200A1020000 U          1 birthday   2023-06-18 15:15:13