124 lines
4.4 KiB
Plaintext
124 lines
4.4 KiB
Plaintext
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
|
|
|
|
|
|
|
|
|