PARTITIONING PARTITIONS : Dividing the large tables into small tables known as partitions Partition of single table can go in two different tablespaces Sorting can be done by partitioning thus we not require temporary tablespace Partition can be independently managed Backup and recovery… 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
AUDITING Auditing is for verification purpose Types of auditing: Privilege level Statement level Object level To enable the auditing audit_trail=true or db or os To disable the auditing audit_trail=false or none If the value is true it will enable the auditing and audit files… 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
To do a selective data export in a table using expdp, query option can be used, provided if the where condition doesn’t have any subqueries.For any Query Export Dumps, having Sub query should be using a parfile instead of the direct expdp command.All the complex… Read More
