Setting up glogin.sql : cd $ORACLE_HOME/sqlplus/admin cp glogin.sql glogin.sql.bkp vi glogin.sql SET termout off DEFINE myv = ‘Not connected’ COLUMN myc new_value myv select user || ‘@QA@’ || global_name myc from global_name; SET sqlprompt ‘&myv:SQL> ‘ Execution : $ sqlplus scott/PW SQL*Plus: Release 12.1.0.2.0 Production… Read More
Checking Default_Temp_Tablespace : col property_name for a35 col property_value for a20 col description for a45 set linesize 300 SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME=’DEFAULT_TEMP_TABLESPACE’; Temp tablespace usage : set linesize 300 SELECT A.tablespace_name tablespace, D.mb_total, SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,… Read More
Follow below procedure to capture following information : 1) Tables row count. 2) Table Actual Size. 3) DBA_Tab_Modifications information. Pre requisite: This Procedure needs to be implemented if we are gathering stats daily. #############On any Dev server : (Create DB link to Standby DB from… Read More
********************************************************************* How to Purge CURSORS related to a single SQL_ID from library cache ********************************************************************* SQL> select ADDRESS, HASH_VALUE from V$SQL where SQL_ID like ‘8zdchyshg4d3g’; select ADDRESS, HASH_VALUE,plan_hash_value from V$SQL where SQL_ID like ‘8zdchyshg4d3g’; SQL> exec DBMS_SHARED_POOL.PURGE (‘000000180DC67320,552744047′,’C’); PL/SQL procedure successfully completed. SQL> select ADDRESS, HASH_VALUE… Read More
************************************************************ List all the hidden parameter and their values ************************************************************ SET PAUSE ON SET PAUSE ‘Press Return to Continue’ SET PAGESIZE 60 SET LINESIZE 300 COLUMN ksppinm FORMAT A50 COLUMN ksppstvl FORMAT A50 SELECT ksppinm, ksppstvl FROM x$ksppi a, x$ksppsv b WHERE a.indx=b.indx AND substr(ksppinm,1,1)… Read More
Oracle : HOW TO TRACE SESSIONS ========================================================== For TRACING your own session , use the following query to find details on your own session: ========================================================== set linesize 132 set pagesize 1000 set echo on col rbs format a6 col oracle_user format a12 heading “Oracle|Username” col… Read More
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:
|
1 2 3 4 5 6 7 8 9 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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 :
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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
DECLAREcount1 NUMBER := 0;total NUMBER := 0;CURSOR del_record_cur ISSELECT rowid FROM scott.emp WHERE empno<=20000;BEGINFOR rec IN del_record_cur LOOPDELETE FROM scott.emp WHERE rowid = rec.rowid;total := total + 1;count1 := count1 + 1;IF (count1 >= 1000) THEN COMMIT;count1 := 0;END IF;END LOOP;COMMIT;DBMS_OUTPUT.PUT_LINE(‘Deleted ‘ || total ||… Read More
Step 1 : Create Temporary Table : CREATE TABLE unpar_table ( a NUMBER, y number, name VARCHAR2(100), date_used date); Step 2 : load table with 1,000,000 rows begin for i in 1 .. 1000 loop for j in 1 .. 1000 loop insert into unpar_table values… Read More
cat g_stats_1_test.sh sqlplus / as sysdba <<EOF set head off conn / as sysdba set serveroutput on set timing on set echo on spool dict_stats_test.log exec DBMS_STATS.gather_table_stats (ownname => ‘SCOTT’,tabname => ‘EMP’,method_opt=>’FOR COLUMNS SIZE AUTO STATUS’); spool off set timing off set serveroutput off exit;… Read More
