Different ways to delete duplicate rows in a table

CREATE TABLE duplicate_test(name VAR CHAR2(20), age NUMBER);

begin
INSERT INTO duplicate_test VALUES('srik',20);
INSERT INTO duplicate_test VALUES('srik',20);
INSERT INTO duplicate_test VALUES('sr',22);
INSERT INTO duplicate_test VALUES('sr',22);
INSERT INTO duplicate_test VALUES('ik',24);
INSERT INTO duplicate_test VALUES('ik',24)
end;
/

way1:

DECLARE
CURSOR c1
IS
SELECT DISTINCT *
FROM duplicate_test;
r1 c1%ROWTYPE;
BEGIN
OPEN c1;
DELETE FROM duplicate_test;
FETCH c1
INTO r1;
WHILE c1%FOUND
LOOP
INSERT INTO duplicate_test
VALUES r1;
FETCH c1
INTO r1;
END LOOP;
COMMIT;
END;
/

Way2:
CREATE TABLE duplicate_test(age NUMBER);

INSERT INTO duplicate_test VALUES(20)
INSERT INTO duplicate_test VALUES(20)
INSERT INTO duplicate_test VALUES(22)
INSERT INTO duplicate_test VALUES(22)

CREATE OR REPLACE TYPE ntable IS TABLE OF NUMBER;

DECLARE
t1 ntable;
BEGIN
SELECT age BULK COLLECT INTO t1 FROM duplicate_test;
t1 := SET(t1);
DELETE FROM duplicate_test;
INSERT INTO duplicate_Test SELECT * FROM TABLE(t1);
END;

Way 3:
Using the flashback query

Way 4:
Using the distinct key word

Way 5:
Using the rowid

Function to find the number of times a pattern is repeated.

CREATE FUNCTION pattern_count(INSTRING VARCHAR2, INPATTERN VARCHAR2)

RETURN NUMBER

IS

COUNTER NUMBER;

NEXT_INDEX NUMBER;

STRING VARCHAR2(2000);

PATTERN VARCHAR2(2000);

BEGIN

COUNTER := 0;

NEXT_INDEX := 1;

STRING := LOWER(INSTRING);

PATTERN := LOWER(INPATTERN);

FOR I IN 1 .. LENGTH(STRING) LOOP

IF (LENGTH(PATTERN) <= LENGTH(STRING)-NEXT_INDEX+1)

AND (SUBSTR(STRING,NEXT_INDEX,LENGTH(PATTERN)) = PATTERN) THEN

COUNTER := COUNTER+1;

END IF;

NEXT_INDEX := NEXT_INDEX+1;

END LOOP;

RETURN COUNTER;

END;

Performance Views:

v$sql the details -if you have multiple copies of the query:
"select * from T" in shared pool, v$sql will have a row per query. This can happen if user U1 and user U2 both have a table T and both issue "select * from T". Those are entirely different queries with different plans and so on. v$sql will have 2 rows.

v$sqlarea is a aggregate of v$sql. It selects out DISTINCT sql.
"select * from T" will appear there.

v$sqltext is simply a way to see the entire query.
The v$sql and v$sqlarea views only show the first 1000 bytes. newlines and other control characters are replace with whitespace.

v$sqltext_with_newlines is v$sqltext without the whitespace replacment.

Get the current sql query or sql query inside a plsql or plsql call that is being exceuted by a session:

SELECT sql_text
FROM v$session s, V$SQLTEXT T
WHERE s.sql_id = T.sql_id AND s.SID =
ORDER BY piece

This query does not show the plsql code that is being executed rather it will show the sql query inside the plsql block as plsql code is executed as a unit. It will show only the top block of the plsql code.

Find the current session id :

SELECT sid FROM v$mystat WHERE ROWNUM<=1
or
SELECT DISTINCT sid FROM v$mystat
or
SELECT sid FROM v$session WHERE audsid=sys_context('userenv','sessionid')
or
SELECT sid FROM V$SESSION WHERE audsid = USERENV('sessionid');