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
spool DEPT_NonPart_to_Part.logset timing onset time onset echo on conn SCOTT/PW –> Very Importantshow user –> Create DEPT_PART table (with partitions) EXEC DBMS_STATS.gather_table_stats(user, ‘DEPT’, cascade => TRUE);SELECT num_rows FROM user_tables WHERE table_name = ‘DEPT’; EXEC DBMS_STATS.gather_table_stats(user, ‘DEPT_PART’, cascade => TRUE);SELECT num_rows FROM user_tables WHERE table_name =… Read More
Please check my upcoming articles (Tomorrow and Day after Tomorrow) for detailed steps with examples.
********************************************************************* 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
1) Bkup Tnsnames: $ cp tnsnames.ora tnsnames.ora.date +”%Y%m%d%H%M” -rw-r—–. 1 oracle oinstall 564 Oct 20 11:57 tnsnames.ora -rw-r—–. 1 oracle oinstall 564 Nov 28 22:25 tnsnames.ora.201611282225 2) Crontab Backup : $ crontab -l > crontab_orig_date ‘+%m%d_%H%M’ $ ls -ltr crontab* -rw-r–r–. 1 oracle oinstall 2175… 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
set linesize 1000 pagesize 0 feedback off trimspool onwithhwm as ( — get highest block id from each datafiles ( from x$ktfbue as we don’t need all joins from dba_extents ) select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn),hwmts as… Read More
Rebalance status : column “Diskgroup” format A30 column “Imbalance” format 99.9 Heading “Percent|Imbalance” column “Variance” format 99.9 Heading “Percent|Disk Size|Variance” column “MinFree” format 999.99 heading “Minimum|Percent|Free” column “DiskCnt” format 9999 Heading “Disk|Count” column “Type” format A10 Heading “Diskgroup|Redundancy” SELECT g.name “Diskgroup”, 100*(max((d.total_mb-d.free_mb)/d.total_mb)-min((d.total_mb-d.free_mb)/d.total_mb))/max((d.total_mb-d.free_mb)/d.total_mb) “Imbalance”,100*(max(d.total_mb)-min(d.total_mb))/max(d.total_mb) “Variance”, 100*(min(d.free_mb/d.total_mb))… Read More
