oracle – Page 7 – KTEXPERTS

Oracle : Oracle Server Architecture

8 years ago by SoundharyaOracle Architecture

Oracle Server Architecture An Oracle server includes an Oracle Instance and an Oracle database. Oracle instance: An Oracle Instance is a combination of two different sets of components. The first component includes the Memory components. That is nothing but SGA (shared global area or system global area) The second… Read More

Oracle : Steps for Update Statement Process in SGA

9 years ago by DayakerOracle Architecture

Steps for Update Statement Process in SGA: update processes >update emp set sal=5000 where empno=7903; step 1 when user fires update statement first it will start user processes step 2 user processes to server processes with the help of user processe interface step 3… Read More

Oracle : Steps for Select Statement Process in SGA

9 years ago by DayakerOracle Architecture

Steps for Select Statement Process in SGA : >SELECT * FROM EMP; step 1 when user fires update statement first it will start user processes step 2 user processes to server processes with the help of user processe interface step 3 server processes to… Read More

Oracle : Delete duplicate records from table

9 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);

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);

Thank you for… Read More

Oracle : USER MANAGEMENT

9 years ago by DayakerOracle DB Admin

USER MANAGEMENT How to create user? Syntax sys>> create user <username> identified by <password>; Create user u1 identified by u1; How to assign the privileges to user? Grant connect,resource to u1; How to assign the tablespace to user? Alter user u1 default tablespace userdata; How… Read More