Duplicate values in a table

Create table sql :

 CREATE TABLE ABC (  ID NUMBER , NAME VARCHAR2(20 BYTE) ) ;

Lets insert duplicate values into it:

INSERT INTO ABC (ID, NAME) VALUES ('15', 'f');
INSERT INTO ABC (ID, NAME) VALUES ('15', 'f');
INSERT INTO ABC (ID, NAME) VALUES ('15', 'f');
INSERT INTO ABC (ID, NAME) VALUES ('11', 'B');
INSERT INTO ABC (ID, NAME) VALUES ('11', 'B');
INSERT INTO ABC (ID, NAME) VALUES ('13', 'G');
INSERT INTO ABC (ID, NAME) VALUES ('13', 'G');

Now table looks like this:

                  

 Now we have a duplicate combination of 2 columns.
sql to find duplicate values combination in two columns:
 
 select ID,NAME, count(ID) from ABC group by ID,NAME having count(ID) > 1;

sample output:


 


Find the duplicate ID's in a column :

 select ID, count(ID) from ABC group by ID having count(ID) > 1;


Sample output:



 Retrieving duplicate values in database :

SELECT a.*
FROM   tablename a
 INNER
  JOIN 
  (
  SELECT column1
       , column2
  FROM   table1 
  GROUP
      BY column1
       , column2
  HAVING Count(*) >1
  ) b
    ON a.column1 = b.column1
   AND a.column2 = b.column2

If you have combination of multiple columns that you want to check duplicates. 

For example : Check duplicates for combination of multiple columns check this link.

http://arvindasdba.blogspot.com/2016/02/check-duplicates-for-combination-of.html

grant permissions on all tables to a user

Grant select  permission on all tables in logged in schema to a user/role:



declare
cursor c1 is select table_name from user_tables;
cmd varchar2(200);
begin
for c in c1 loop
cmd := 'GRANT SELECT ON '||c.table_name||' TO &&YOURUSERNAME';
execute immediate cmd;
end loop;
end;



Note :
you change the select to what ever permissions you want to grant. This will be very helpful in case of huge number of tables.

adding a primary/unique key to existing table and updating values

lets take a simple example here. create a table and leave id column empty and add values to other column name.

Sample create table :

create table abc (id number, name varchar2(20));

add values to name column and leave the id column empty

INSERT INTO ABC (NAME) VALUES ('a');

INSERT INTO ABC (NAME) VALUES ('b');

INSERT INTO ABC (NAME) VALUES ('c');

INSERT INTO ABC (NAME) VALUES ('d');

INSERT INTO ABC (NAME) VALUES ('e');

INSERT INTO ABC (NAME) VALUES ('f');

INSERT INTO ABC (NAME) VALUES ('g');

INSERT INTO ABC (NAME) VALUES ('h');

Now we have a situation where we have a table with only values in the name column and id column is empty (in case if u want to add id's to already existing table  just add a column for the new unique ID's ). 

Now we will create a sequence to get the sequential values to update with.

create a sequence :

CREATE SEQUENCE SEQ_abc

START WITH 1

MAXVALUE 99999

MINVALUE 1

NOCYCLE

NOCACHE

NOORDER;

This cursor will update the ID's columns with the new unique ID's from above sequence sequentially. execute below block and all set.

Update cursor:

DECLARE

Cursor store_id

IS

SELECT id FROM abc FOR UPDATE;

BEGIN

FOR c_store_id IN store_id LOOP

UPDATE abc

SET id = SEQ_abc.nextval

WHERE CURRENT OF store_id;

END LOOP;

commit;

END;

/

Note: This is a one time update, use a trigger  to keep column updated everytime.