|
Re: What is Corrected By RowID? I wish to know Missing Numbers in a column [message #656551 is a reply to message #656550] |
Tue, 11 October 2016 03:30 |
John Watson
Messages: 8938 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
How about a flashback query?orclz>
orclz> delete from emp where deptno=30;
6 rows deleted.
orclz> select * from emp as of timestamp (sysdate - 2/1440)
2 minus
3 select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 1981-02-20:00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22:00:00:00 1250 500 30
7654 MARTIN SALESMAN 7698 1981-09-28:00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01:00:00:00 2850 30
7844 TURNER SALESMAN 7698 1981-09-08:00:00:00 1500 0 30
7900 JAMES CLERK 7698 1981-12-03:00:00:00 950 30
6 rows selected.
orclz> note that there are some limitations which may or may not apply to your case.
|
|
|
Re: What is Corrected By RowID? I wish to know Missing Numbers in a column [message #656552 is a reply to message #656551] |
Tue, 11 October 2016 03:38 |
|
Littlefoot
Messages: 21811 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
Here's how I understood the question: you're looking for deleted IDs, so:
SQL> CREATE TABLE TEST
2 AS
3 SELECT LEVEL id
4 FROM DUAL
5 CONNECT BY LEVEL <= 10;
Table created.
SQL>
SQL> -- delete some IDs
SQL> DELETE FROM test
2 WHERE id BETWEEN 3 AND 5 OR id = 8;
4 rows deleted.
SQL>
SQL> -- Which ones are now missing (i.e. are deleted)?
SQL> WITH all_of_them
2 AS ( SELECT LEVEL id
3 FROM DUAL
4 CONNECT BY LEVEL <= (SELECT MAX (id) FROM test))
5 SELECT id FROM all_of_them
6 MINUS
7 SELECT id FROM test;
ID
----------
3
4
5
8
SQL>
[Updated on: Tue, 11 October 2016 03:38] Report message to a moderator
|
|
|
|
|