Tag: Tables

delete a duplicate rows in a oracle table

Posted on by Osama Mustafa in Scripts 
 
Check the below Steps to delete duplicate row : 
 
 

SQL>select * from emp;


 EMPID ENAME                       SAL     DEPTNO
---------- -------------------- ---------- ----------
 10005 palani                             54544         10
 10001 Scott Tiger                     1000           40
 10002 Frank Naude                 500             20
 10003 rajesh                            21000         140
 10004 ramesh                           3500          220
 10005 suresh                            34567        123
 
 

delete duplicate rows in empid column in emp table 

 
 

 SQL>delete from emp where rowid not in (select max(rowid) from emp group by empid);


1 row deleted.

SQL>select * from emp;


 EMPID ENAME                       SAL     DEPTNO
---------- -------------------- ---------- ----------
 10001 Scott Tiger                       1000         40
 10002 Frank Naude                   500           20
 10003 rajesh                             21000        140
 10004 ramesh                           3500           220
 10005 suresh                            34567        123
 
 
to delete the old duplicate row from the table instead of max(rowid) replace min(rowid) 
for exampl
 

SQL>insert into emp values(10005,’Osama’,54544,10);


1 row created.

SQL>select * from emp;


   EMPID ENAME                       SAL     DEPTNO
---------- -------------------- ---------- ----------
   10005 Osama                           54544        10
   10001 Scott Tiger                     1000          40
   10002 Frank Naude                 500            20
   10003 rajesh                            21000        140
   10004 ramesh                          3500          220
   10005 suresh                           34567        123

6 rows selected.

SQL>delete from emp where rowid not in (select min(rowid) from emp group by empid);


1 row deleted.
 

SQL>select * from emp;


   EMPID ENAME                       SAL     DEPTNO
---------- -------------------- ---------- ----------
   10005 Osama                            54544      10
   10001 Scott Tiger                      1000        40
   10002 Frank Naude                  500          20
   10003 rajesh                             21000      140
   10004 ramesh                           3500        220 
 
 
thank you 
Osama mustafa

Oracle Statistics Tables.

Posted on by Osama Mustafa in Database
Hi ,

I am going to talk about Some Oracle Statistics Table that will be useful for tuning your database , and gathering information about your database , these table are very useful

For example Dynamic Performance View  :
**The below query will gives you the most sql statement taking more time in the CPU .
1-Select Sql_text , executions from v$sqlstats where cpu_time > 1000 ;
**Below Query Gives you some Details about Specific Machine 
2-Select * from v$session where machine = ‘OSAMA_PC’ and where
    logon_time > sysdate -1 ;
**finally , Lock in your database 
3- select sid,ctime from v$lock where block > 0 ;

Statistics Tables are :
**Display System Wide Statistics :
V$Sysstat
V$Statname
V$Sesstat

**Display System Wide Statistics :

V$Sgastat
V$Event_name
V$system_event

 **Display Session-Related Statistics
V$statname
V$Session
V$Session _Event
V$Session_Wait

**Display Service-Related Statistics
V$Service_stats
V$Services
V$Service_event
V$Service_wait_Class


**Display Information About Wait Class :

V$Session_Wait_Class
V$System_Wait_Class
V$Service_Wait_Class

**Display Session Wait

V$Session_Event : Session Wait By event for each session had to wait .
V$Session_Wait : Session Wait By Event For current active session that are waiting .
V$System_Event : Total waits for an event (All Session Together).

Wait Event : Information About session that had to wait or must wait for Different Reasons (V$Event_name).

Some Other Useful Tables :
-V$SQL
-V$SQLAREA

Thank You
Osama Mustafa