How to fetch latest 100 rows (inserted or updated) from Oracle table?-CodePudding

How to fetch latest 100 rows (inserted or updated) from Oracle table ? Need queries for below situations - 1.Table does not have any date column. 2.Table has date column.

The query should work in Oracle 11g and later on in Oracle 12c.

CodePudding user response：

Your table should have enabled rowdependencies option and fetch by ora_rowscn pseudocolumn:

Select * from t order by ora_rowscn desc fetch first 100 rows only

If your table has no rowdependencies option, you need to recreate it with rowdependencies

CodePudding user response：

Oracle does track how many inserts, updates, and deletes are performed on a table in the DBA_TAB_MODIFICATIONS view. It is not kept real-time so if you need to examine it over a small time period then you have to flush the stats so you can see (easily).


 execute dbms_stats.flush_database_monitoring_info;

-- Do work 

 select table_owner,table_name,inserts,updates,deletes from dba_tab_modifications  where table_name = 'RDS_LOG';

TABLE_OWNER                    TABLE_NAME                        INSERTS    UPDATES    DELETES
------------------------------ ------------------------------ ---------- ---------- ----------
MYUSER                         RDS_LOG                           4660111          0    1119531

CodePudding user response：

SELECT * FROM table ORDER BY column DESC WHERE rownum < 100

CodePudding user response：

SQL> create table dt_del_ex(id number);

Table created.

SQL> set serveroutput on
SQL> BEGIN
  2
  3      INSERT INTO dt_del_ex VALUES(1);
  4
  5      DBMS_OUTPUT.put_line(TO_CHAR(SQL%ROWCOUNT)||' rows inserted');
  6
  7      INSERT INTO dt_del_ex select rownum from dual connect by level <=10;
  8
  9      DBMS_OUTPUT.put_line(TO_CHAR(SQL%ROWCOUNT)||' rows inserted');
 10
 11      UPDATE dt_del_ex SET id = id   3 WHERE id >= 9;
 12
 13      DBMS_OUTPUT.put_line(TO_CHAR(SQL%ROWCOUNT)||' rows updated');
 14
 15      DELETE FROM dt_del_ex WHERE id <= 10;
 16
 17      DBMS_OUTPUT.put_line(TO_CHAR(SQL%ROWCOUNT)||' rows deleted');
 18
 19  END;
 20  /
1 rows inserted
10 rows inserted
2 rows updated
9 rows deleted