DB version: 10.2
I have table like below and it doesn't have a date column.
SQL> desc xyz
Name Null? Type
----------------------------------------- -------- ----------------------------
TXN_ID NUMBER(38)
STATUS_CD NUMBER(38)
SERVICE_NUM NUMBER(38)
PBIT LONG RAW
I want to delete all rows which are older than 1 hour from this table. Since, this table doesn't have a DATE column, I was told that ORA_ROWSCN psuedo column will be useful in this case. So, I did a quick test as shown below. I was under the impression that every row inserted will have a unique ORA_ROWSCN .
But, ORA_ROWSCN doesn't seem to be incrementing !!
So, is there any other way to chronologically DELETE rows from a table which doesn't have a date column ?
create table xyz
(
txn_id number(38),
status_cd number(38),
service_num number(38),
pbit long raw
);
insert into xyz values (2, 392, 88228382, null);
insert into xyz values (8, 269, 68228385, null);
insert into xyz values (3, 392, 69228347, null);
insert into xyz values (36, 791, 14528927, null);
commit;
insert into xyz values (52, 914, 63910284, null); ----- > This row was inserted 45 minutes after the above row was inserted.
commit;
SQL> set numf 9999999999999999999
SQL> set lines 200
SQL> select ORA_ROWSCN , xyz.* from xyz;
ORA_ROWSCN TXN_ID STATUS_CD SERVICE_NUM P
-------------------- -------------------- -------------------- -------------------- -
8146896913663 2 392 88228382
8146896913663 8 269 68228385
8146896913663 3 392 69228347
8146896913663 36 791 14528927
8146896913663 52 914 63910284