Delete duplicate records from table

Summary 

To select all duplicate records from a table and delete them use the queries below:

SELECT * FROM table_with_duplicates WHERE ROWID NOT IN
(SELECT MIN (ROWID) FROM table_with_duplicates 
GROUP BY col1, col2, col3);

DELETE FROM table_with_duplicates WHERE ROWID NOT IN
(SELECT MIN (ROWID) FROM table_with_duplicates  
GROUP BY col1, col2, col3);

Comments

Post a Comment

Popular posts from this blog

Change Protection Mode for Active Dataguard (11GR2)

Change Protection Mode for Active Dataguard (11GR2) Primary Database :  TESTDB Standby Database :  TESTDBDR Database version   :  11gR2  The database is running under Maximum Performance mode. SQL> select status,instance_name,database_role,protection_mode from v$database,v$instance; STATUS INSTANCE_NAME DATABASE_ROLE PROTECTION_MODE ------ ------------- ------------- -------------------- OPEN    TESTDB        PRIMARY       MAXIMUM PERFORMANCE In order to change the protection mode to either MAXIMUM AVAILABILITY/MAXIMUM PROTECTION, we need to have the standby redo logs configured on the standby database. Also, the redo shippment parameter (log_archive_dest_2) on the primary database should be configured to use SYNCHRONOUS (“SYNC”) mode. Let’s check the number of online redo logs and it’s size on primary database ...
Read more

Reboot Exadata Machine

Image
1) Log in to the first database server as root. 2) Change to the OneCommand directory # cd /opt/oracle.SupportTools/onecommand 3) Note whether the Grid Infrastructure is currently enabled for autostart, so that this state can be restored later: # dcli -g dbs_group -l root /u01/app/11.2.0/grid/bin/crsctl config crs 4) Disable the Grid Infrastructure for autostart on the database servers if the previous step indicated it is currently enabled for autostart. # dcli -g dbs_group -l root /u01/app/11.2.0/grid/bin/crsctl disable crs Note: This is step is [Optional] and it can required during maintenance operation like “firmware patches” which requires to reboot the Compute Node several times. 5) Stop the Grid Infrastructure stack on the database servers (compute nodes): # dcli -g dbs_group -l root /u01/app/11.2.0/grid/bin/crsctl stop crs 6) Verify that the Grid Infrastructure stack has shutdown successfully on the database servers. The follow...
Read more

STEPS TO troubleshoot long running concurrent request in R12.2.x

USUALLY we are getting concurrent program running longer and concurrent request not picking jobs, etc. those are very frequent issue we expected from development team. Collect basis level of information to development team A. Oracle seeded program/Custom program? B. How much time it used earlier? C. Is there any recent code change done in concurrent program? D. Is this program fetching higher data compare to last run? E. Does this job running any specific time/ It can be run any time? F. Does this job fetching data using DB link? G. Does the problem happen on both the test and production instance? Once asked above questions to development team in meanwhile we need to start basic sanity check in our environment. A. Verify the status of the concurrent program select REQUEST_ID,phase_code,status_code,ORACLE_SESSION_ID from apps.fnd_concurrent_requests where request_id=’23432345’; B. Find which concurrent manger ran a specific concurrent request select b.USER_CONCURRENT_QUEUE_NAME from fnd...
Read more