oracle dba – KTEXPERTS

Tools for Administering the Oracle Database

4 years ago by Vinod SiramOracle DB Admin

Dear Readers, In this article, we will discuss Tools for Administering the Oracle Database Please Fill below Google Form (If we missed any Products, Tools, and Utilities that you use in achieving your goals as a Cassandra Administrator.) 👇🏻👇🏻👇🏻👇🏻👇🏻 https://forms.gle/oracledba Monitoring Tools: OEM Nagios EM… Read More

Protected: Oracle DBA Training Status

5 years ago by Ramesh ReddyOracle DB Admin

There is no excerpt because this is a protected post.

Performance Tuning in Oracle – Part3

6 years ago by SUMAN MORAOracle DB Admin

Performance Tuning Tools This is the continuation to the previous article about the Tools used in the performance Tuning. Please find the previous article here. Performance Tuning in Oracle – Part2 In this article we will discuss about Oracle SQL Tuning advisor and baseline.… Read More

Performance Tuning in Oracle – Part2

6 years ago by SUMAN MORAOracle DB Admin

Performance Tuning Tools We have discussed the tools that are being used for Performance Tuning in our previous article. Please find the article here Performance Tuning in Oracle – Part1 In this article we will be discussing the additional tools which are used in… Read More

Performance Tuning in Oracle – Part1

6 years ago by SUMAN MORAOracle DB Admin

Introduction of Performance Tuning: Performance tuning plays an important role in the DBA life. Performance tuning refers in the following areas: 1. Instance tuning 2. SQL tuning 3. Memory, Storage (I/O) 4. Network tuning 5. OS tuning For OS Tuning and Network tuning DBAs… Read More

Oracle : Delete duplicate records from table

8 years ago by Vinod SiramOracle Adhoc SQL Scripts

Delete duplicate records from table Dear Readers, In this article we will see ”Delete duplicate records from table” Script if one column having duplicate records:

DELETE from scott.test1 a0
WHERE a0.rowid NOT IN (
SELECT max(a1.rowid)
FROM scott.test1 a1
GROUP BY a1.a
HAVING count(*) > 1)
AND (a0.a) IN
(SELECT a2.a FROM scott.test1 a2
GROUP BY a2.a HAVING count(*) > 1);

DELETE from scott.test1 a0

WHERE a0.rowid NOT IN (

SELECT max(a1.rowid)

FROM scott.test1 a1

GROUP BY a1.a

HAVING count(*) > 1)

AND (a0.a) IN

(SELECT a2.a FROM scott.test1 a2

GROUP BY a2.a HAVING count(*) > 1);

Script if two columns having duplicate records:

DELETE from scott.test2 a0
WHERE a0.rowid NOT IN (
SELECT max(a1.rowid)
FROM scott.test2 a1
GROUP BY a1.NUM, a1.NAME
HAVING count(*) > 1)
AND (a0.NUM, a0.name) IN
(SELECT
a2.NUM,
a2.name
FROM scott.test2 a2
GROUP BY a2.NUM, a2.NAME
HAVING count(*) > 1);

DELETE from scott.test2 a0

WHERE a0.rowid NOT IN (

SELECT max(a1.rowid)

FROM scott.test2 a1

GROUP BY a1.NUM, a1.NAME

HAVING count(*) > 1)

AND (a0.NUM, a0.name) IN

(SELECT

a2.NUM,

a2.name

FROM scott.test2 a2

GROUP BY a2.NUM, a2.NAME

HAVING count(*) > 1);

Deleting duplicate records involving multiple columns :

DELETE from EMP a0
WHERE a0.rowid NOT IN (
SELECT max(a1.rowid)
FROM EMP a1
GROUP BY a1.COL1, a1.COL2, a1.COL3, a1.COL4
HAVING count(*) > 1)
AND (a0.COL1, a0.COL2, a0.COL3, a0.COL4) IN
(SELECT
a2.COL1, a2.COL2, a2.COL3, a2.COL4
FROM EMP a2
GROUP BY a2.COL1, a2.COL2, a2.COL3, a2.COL4
HAVING count(*) > 1);