Home » SQL & PL/SQL » SQL & PL/SQL » What is Corrected By RowID? I wish to know Missing Numbers in a column
What is Corrected By RowID? I wish to know Missing Numbers in a column [message #656550] Tue, 11 October 2016 03:17 Go to next message
graphe
Messages: 23
Registered: October 2014
Junior Member

Hello Every One i am looking to find the solution for some thing like this.
I Have a table which is having a Primary key column in this i inserted data by a sequence. But after some time i deleted some data as per the requirement with some conditions.
Now i want to know what are the data deleted by me i mean that Missing Numbers or after that data is missing there in that Primary key Column.
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 Go to previous messageGo to next message
John Watson
Messages: 7620
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 Go to previous messageGo to next message
Littlefoot
Messages: 21439
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

Re: What is Corrected By RowID? I wish to know Missing Numbers in a column [message #656553 is a reply to message #656551] Tue, 11 October 2016 03:38 Go to previous messageGo to next message
cookiemonster
Messages: 13291
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you just want to know the missing pk values then use a row generator query to get all the numbers from 1 to the current max value and then minus the actual pk from it.
Re: What is Corrected By RowID? I wish to know Missing Numbers in a column [message #656554 is a reply to message #656553] Tue, 11 October 2016 03:39 Go to previous message
Littlefoot
Messages: 21439
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
/forum/fa/5767/0/

Matter of nanoseconds!
Previous Topic: rows to column text
Next Topic: Easy Query
Goto Forum:
  


Current Time: Sat Oct 20 19:32:52 CDT 2018