Get last 3 month data without any date column in table-CodePudding

is there a way to fetch last 3 months data in a table if there is no date column present in table. Does Oracle db maintain some sort of internal column that maintains order in which data is entered in table.

CodePudding user response：

If you have flashback enabled (with a large enough retention period) then you can get all the new rows and changed rows using:

SELECT * FROM table_name
MINUS
SELECT * FROM table_name AS OF TIMESTAMP ( ADD_MONTHS(SYSDATE, -3) );

CodePudding user response：

It's kind of doable. In oracle, there is a virtual column called ORA_ROWSCN. Which holds the version of the row. It is updated with the row itself (So It doesn't keep the insert order). Basically, it's a timestamp and it can be converted to a timestamp with the help SCN_TO_TIMESTAMP function. Document

Need to mention that ORA_ROWSCN by default works on the block level. So all the rows in the same block will have the same SCN. Unless you create your table with ROWDEPENDENCIES. Document