Few more examples - Analytical Functions

FINDING MISSING SEQUENCE / NUMBER from a table

SELECT /*+ ALL_ROWS */
pre_gap+1 start_gap, max_seq_gap
FROM
(SELECT /*+ ALL_ROWS */ seq_column post_gap, LAG(seq_column,1,0) OVER (ORDER BY seq_column) pre_gap ,
(SELECT /*+ ALL_ROWS */ MAX(seq_column) FROM table_name) max_seq_gap
FROM table_name)
WHERE pre_gap != 0
AND post_gap - pre_gap > 1

Delete duplicates

DELETE FROM table_name tnm WHERE tnm.rowid IN
(SELECT rowid FROM (SELECT ROWID, ROW_NUMBER () OVER (PARTITION BY ORDER BY ) duplicate FROM table_name ) qry
WHERE qry.duplicate > 1);