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
SPFILE SPFILE IS SERVER PARAMETER FILE SPFILE IS A SEMI BINARY FILE. WE CANNOT EDIT MANUALLY WE CAN EDIT AS DYNAMICALLY BY USING ALTER SYSTEM ITS SPECIALY MADE FOR RAC (REAL APPLICATION CLUSTER) WE CAN CREATE SPFILE AT ANY STAGE(nomount,mount,open) IF YOUR DATABASE… Read More
USER MANAGEMENT How to create user? Syntax sys>> create user <username> identified by <password>; Create user u1 identified by u1; How to assign the privileges to user? Grant connect,resource to u1; How to assign the tablespace to user? Alter user u1 default tablespace userdata; How… Read More
TABLESPACE MANAGEMENT WHAT IS TABLESPACE ? IT’S A LOGICAL LINK BETWEEN USER AND DATAFILES.IT IS LOGICAL STORAGE OF DATA DICTIONARY: EXTENTS INFORMATION WILL GET FROM BASE TABLES LOCAL: EXTENTS INFORMATION WILL GET FROM DATAFILE HEADERS TABLESPACE MANAGEMENT HOW TO CREATE THE TABLESPACE SYS>>… 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
URL to access E-Business Suite : http://<web server host name>.<domain>:<port number> SSL enabled URL to access E-Business Suite : https://<web server host name>.<domain>:<port number> which is more secure How to find above URL in databasesqlplus apps/appsQUERY : SELECT home_url FROM icx_parameters; Usually data transmitted… Read More
What is Oracle Applications/Oracle E-Business Suite ?To facilitate big businesses, Oracle Corporation have created collection of software inthe category of ERP (Enterprise Resource Planning) known as modules, that areintegrated to talk to each other and known as Oracle Applications or E-BusinessSuite. Oracle E-Business Suite Releases:10.7… Read More
