SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Query to find out rows deleted from a table

ORA_AJDec 8 2009 — edited Oct 19 2010

Hi

Suppose i have a table with a sequence i.e its an header table containing GRN nos.Now suppose someone has deleted some of serials from it like i am having 1,2,4,5,6 etc 3 is missing.Now what will be the query to find out what are the serials that are missed out in the table.

--Ajay Sharma

MaheshKaila

Try this one

WITH test AS (SELECT '1' id
              FROM DUAL
              UNION ALL
              SELECT '2' id
              FROM DUAL
              UNION ALL
              SELECT '4' id
              FROM DUAL
              UNION ALL
              SELECT '5' id
              FROM DUAL
              UNION ALL
              SELECT '6' id
              FROM DUAL)
SELECT TO_CHAR (l)
FROM (SELECT LEVEL l
      FROM DUAL
      CONNECT BY LEVEL <= (SELECT MAX (id)
                           FROM test))
MINUS
SELECT id
FROM test

Regards,
Mahesh Kaila

ORA_AJ

Dear Mahesh

In my case Number of records is not fixed i mean to say its not neccesary that data is there for 1-6 only.It can be upto 100..

--Ajay Sharma

MaheshKaila

SELECT TO_CHAR (l)
FROM (SELECT LEVEL l
      FROM DUAL
      CONNECT BY LEVEL <= (SELECT MAX (id)
                           FROM <YOUR_TABLE>))
MINUS
SELECT id
FROM <YOUR_TABLE>

- Mahesh Kaila

Karthick2003

What if i delete 4,5 and 6 will your query find it?

Aketi Jyuuzou

I like recursive with clause B-)
Hehe I used PostgreSQL8.4 because I do not have Oracle11gR2 :D

WITH recursive t(Val) AS (
values(1),(2),(4),(5),(6),(12)),
rec(Val,LeadVal) as(
select Val+1,LeadVal
  from (select Val,Lead(Val) over(order by Val) as LeadVal
          from t) tmp
 where Val+1 < LeadVal
union all
select Val+1,LeadVal
  from rec
 where Val+1 < LeadVal)
select Val from rec;

 val
-----
   3
   7
   8
   9
  10
  11

MaheshKaila

WITH test AS (SELECT '1' id
              FROM DUAL
              UNION ALL
              SELECT '2' id
              FROM DUAL
              UNION ALL
              SELECT '4' id
              FROM DUAL
              UNION ALL
              SELECT '5' id
              FROM DUAL
              UNION ALL
              SELECT '6' id
              FROM DUAL)
SELECT TO_CHAR (l)
FROM (SELECT LEVEL l
      FROM DUAL
      WHERE LEVEL >= (SELECT MIN (id)
                 FROM test)
      CONNECT BY LEVEL <= (SELECT MAX (id)
                           FROM test))
MINUS
SELECT id
FROM test

ORA_AJ

Hi mahesh

I am using Oracle 8.1.5.0 and whenever i uses your query containing Connect by level I gets an errror saying Ora-01436 : CONNECT BY loop in user data..

Any comments about this.

--Ajay Sharma

JustinCave

CONNECT BY LEVEL was not available in 8.1.5. In 8.1.5, the simplest solution would be to select a ROWNUM from a table that has more rows than the TEST table. Assuming that DBA_OBJECTS is sufficiently large

SELECT rownum id
  FROM dba_objects
MINUS
SELECT id
  FROM test

As an FYI, if you're using an ancient version of Oracle like 8.1.5, it's helpful to mention that up front. There have been a lot of advances in the 8 major releases since then.

Justin

788759

Mahesh Kaila wrote:

Try this one

<PRE class=jive-pre><CODE class="jive-code jive-java">WITH test AS (SELECT '1' id

FROM DUAL

UNION ALL

SELECT '2' id

FROM DUAL

UNION ALL

SELECT '4' id

FROM DUAL

UNION ALL

SELECT '5' id

FROM DUAL

UNION ALL

SELECT '6' id

FROM DUAL)

SELECT TO_CHAR (l)

FROM (SELECT LEVEL l

FROM DUAL

CONNECT BY LEVEL <= (SELECT MAX (id)

FROM test))

MINUS

SELECT id

FROM test

</CODE></PRE>

Regards,

Mahesh Kaila

Where can I find the related documentation? It has been solved, but why's that? Many thanks to your <A class=bodylinkwhite href="http://www.software-to-convert.com/myspace-video-conversion-software/myspace-video-to-iriver-lplayer-software.html">explanation</A>!

1 - 9

Locked Post

New comments cannot be posted to this locked post.

Locked on Nov 16 2010

Added on Dec 8 2009

9 comments

18,877 views

SQL & PL/SQL

Query to find out rows deleted from a table

Comments

Post Details