delete a duplicate rows in a oracle table

for example here, user HR is having table emp,

HR@prod 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

6 rows selected.

to 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 example,

HR@prod SQL>insert into emp
values(10005,'palani',54544,10);

1 row created.

HR@prod 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

6 rows selected.

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

1 row deleted.

HR@prod 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


thanks,

important linux commands

1. Command to Check the RAM Size

ORACLE:/home/oracle>free -m
total used free shared buffers cached
Mem: 998 976 22 0 56 540
-/+ buffers/cache: 379 619
Swap: 4502 77 4425

ORACLE:/home/oracle>dmesg | grep RAM
BIOS-provided physical RAM map:
RAMDISK driver initialized: 16 RAM disks of 16384K size 1024 blocksize

ORACLE:/home/oracle>vmstat
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
0 0 78976 27396 57228 547856 0 0 16 32 575 359 3 0 97 0

ORACLE:/home/oracle>vmstat 2 10
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
0 0 78976 30396 58040 543516 0 0 16 32 575 357 3 0 97 0
0 0 78976 30252 58040 543516 0 0 0 0 1014 218 0 0 100 0
0 0 78976 30252 58040 543516 0 0 0 24 1016 171 0 0 100 0
0 0 78976 30252 58048 543508 0 0 0 166 1038 200 0 0 100 0
0 0 78976 30252 58048 543508 0 0 0 0 1012 184 0 0 99 0
0 0 78976 30252 58048 543508 0 0 0 24 1014 201 0 0 100 0
0 0 78976 30252 58052 543504 0 0 0 30 1015 194 0 0 100 0
0 0 78976 30316 58052 543764 0 0 0 48 1021 181 0 0 100 0
0 0 78976 30252 58060 543756 0 0 0 32 1015 198 1 0 100 0
0 0 78976 30252 58068 543748 0 0 0 32 1015 213 0 0 100 0

and top command also shows up free space

2.to check free disk space
ORACLE:/home/oracle>df
Filesystem 1K-blocks Used Available Use% Mounted on
/dev/sda1 20161172 7177644 11959388 38% /
/dev/sda3 50394996 4415128 43419912 10% /d01
/dev/sda2 50394996 1476324 46358716 4% /d02
/dev/sda6 28344028 77888 26826316 1% /d03
none 511344 0 511344 0% /dev/shm

ORACLE:/home/oracle>df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda1 20G 6.9G 12G 38% /
/dev/sda3 49G 4.3G 42G 10% /d01
/dev/sda2 49G 1.5G 45G 4% /d02
/dev/sda6 28G 77M 26G 1% /d03
none 500M 0 500M 0% /dev/shm

3.uptime - Tell how long the system has been running.

ORACLE:/home/oracle>uptime
18:38:23 up 7:40, 9 users, load average: 0.28, 0.13, 0.10

4.Display Number of CPUs in Linux
To see the number of CPUs on a Linux server, you can cat the /proc/cpuinfo file.
cat /proc/cpuinfo grep processor

ORACLE:/home/oracle>cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 7
model name : Intel(R) Core(TM)2 Duo CPU E8200 @ 2.66GHz
stepping : 6
cpu MHz : 2666.830
cache size : 6144 KB
physical id : 0
siblings : 2
core id : 0
cpu cores : 2
fdiv_bug : no
hlt_bug : no
f00f_bug : no
coma_bug : no
fpu : yes
fpu_exception : yes
cpuid level : 10
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm pni monitor ds_cpl est tm2 xtpr
bogomips : 5336.09

scp secure copy

ORACLE:/home/oracle>scp $ORACLE_HOME/dbs/initstby.ora oracle@10.11.12.7:/db/oracle/product/10.2.0/dbs/initstby.ora
oracle@10.11.12.7's password:
initstby.ora 100% 1120 1.1KB/s 00:00

SQL*Loader

The SQL*Loader environment




SQL*Loader (sqlldr) is an Oracle-supplied utility that allows you to load data from a flat file(external file) into one or more database tables.

To view the available command in sqlldr using help command

ORACLE:/home/oracle>sqlldr help=y
userid          ORACLE username/password
control         Control file name
log             Log file name
bad             Bad file name
data            Data file name
discard         Discard file name
discardmax              Number of discards to allow
skip            Number of logical records to skip
load            Number of logical records to load
errors          Number of errors to allow
rows            Number of rows in conventional path bind array or between direct path data saves
bindsize                Size of conventional path bind array in bytes
silent          Suppress messages during run (header,feedback,errors,discards,partitions)
direct          use direct path
_synchro                internal testing
parfile         parameter file: name of file that contains parameter specifications
parallel                do parallel load
file            File to allocate extents from
skip_unusable_indexes           disallow/allow unusable indexes or index partitions
skip_index_maintenance          do not maintain indexes, mark affected indexes as unusable
commit_discontinued             commit loaded rows when load is discontinued
_display_exitcode               Display exit code for SQL*Loader execution
readsize                Size of Read buffer
external_table          use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE
columnarrayrows         Number of rows for direct path column array
streamsize              Size of direct path stream buffer in bytes
multithreading          use multithreading in direct path
resumable               enable or disable resumable for current session
resumable_name          text string to help identify resumable statement
resumable_timeout               wait time (in seconds) for RESUMABLE
date_cache              size (in entries) of date conversion cache

FINDING PID PROCESS ID USING VIEWS

through SQL*Plus via the following query :

SYS@prod SQL>select b.name bkpr , s.Username spid, p.pid
2 from V$BGPROCESS b, V$SESSION s, V$PROCESS p
3 where p.Addr = b.Paddr(+)
4 and p.Addr = s.Paddr
5 /

BKPR  SPID                                  PID
----- ------------------------------ ----------
PMON                                          2
PSP0                                             3
MMAN                                         4
DBW0                                           5
ARC0                                          17
ARC1                                          18
ARC2                                          19
LGWR                                          6
CKPT                                           7
RVWR                                        16
SMON                                           8

BKPR  SPID                                  PID
----- ------------------------------ ----------
RECO                                          9
CJQ0                                         10
QMNC                                       20
MMON                                      11
MMNL                                       12
                                            28
                                            26
                                            21
                                            27
                                            23
                                            25

BKPR  SPID                                  PID
----- ------------------------------ ----------
                                           22
SYS                                    15
                                           24
                                           29

26 rows selected.

using os linux command in SQL*Plus
(if it is linux os)
ps -ef | grep oracle
or
ps -ef | grep database_name

SYS@prod SQL>!ps -ef | grep prod
oracle    7636     1  0 12:26 ?        00:00:00 ora_pmon_prod
oracle    7638     1  0 12:26 ?        00:00:00 ora_psp0_prod
oracle    7640     1  0 12:26 ?        00:00:00 ora_mman_prod
oracle    7642     1  0 12:26 ?        00:00:00 ora_dbw0_prod
oracle    7644     1  0 12:26 ?        00:00:00 ora_lgwr_prod
oracle    7646     1  0 12:26 ?        00:00:00 ora_ckpt_prod
oracle    7648     1  0 12:26 ?        00:00:00 ora_smon_prod
oracle    7650     1  0 12:26 ?        00:00:00 ora_reco_prod
oracle    7652     1  0 12:26 ?        00:00:00 ora_cjq0_prod
oracle    7654     1  0 12:26 ?        00:00:00 ora_mmon_prod
oracle    7656     1  0 12:26 ?        00:00:00 ora_mmnl_prod
oracle    7658     1  0 12:26 ?        00:00:00 ora_d000_prod
oracle    7660     1  0 12:26 ?        00:00:00 ora_s000_prod
oracle    7663     1  0 12:26 ?        00:00:00 ora_rvwr_prod
oracle    7664  7633  0 12:26 ?        00:00:00 oracleprod (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle    7666     1  0 12:26 ?        00:00:00 ora_arc0_prod
oracle    7668     1  0 12:26 ?        00:00:00 ora_arc1_prod
oracle    7672     1  0 12:26 ?        00:00:00 ora_qmnc_prod
oracle    7688     1  0 12:26 ?        00:00:00 ora_q000_prod
oracle    7690     1  0 12:26 ?        00:00:00 ora_q001_prod
oracle    8049     1  3 13:42 ?        00:00:00 ora_j000_prod
oracle    8050  7633  1 13:42 pts/1    00:00:00 -bin/tcsh -c ps -ef | grep prod
oracle    8063  8050  0 13:42 pts/1    00:00:00 grep prod

Thank you,

How to know process ID or PID on windows

Process Identifier or Process ID or PID is the unique ID of a process running on the system. With PID we can uniquely identify a process and can know about their characteristics.

On unix based system like linux, solaris, HP-UX with "ps" command we can easily identify each type of process and their PID. We can also grep the output for filtering. But for Windows operating system such as Windows 95, Windows 98, Windows 2000, Windows XP, Windows Vista, Windows Server 2003, Windows Server 2008 and Windows 7 there is no such utility or command.

In windows through GUI we can still see the process ID or PID of a process. This is sometime very essential to track a culprit process. It can be seen from Task Manager. To get Task Manager right click on empty area in the task bar and then you can select Task Manger. You can also get Task Manager window by clicking CTRL+ALT+DEL.

After opening task manager you will see there is no process ID or PID there by default. But you can see Processes tab there. Whenever you click on processes tab, process information is shown. By default option is Image Name(Process Name), User Name(the user under whom process is running), CPU(percentage of CPU usage) and Mem Usage(Total how many KB is allocated to the process).

To get Process ID,
- Click Processes tab.
- Click on View menu, then click on Select Columns.
- In the "Select Columns" or "Select Process Page Columns" dialog, check the checkbox for PID (Process Identifier), and click OK.

You will see PID is shown and you can easily track each process identifier.

thank you,

Database Created as 32bit Or 64bit Platform

We can check the Database Created as 32bit Or 64bit Platform through below sql query

SQL> select metadata from sys.kopm$ ;
METADATA

-----------------------------------------------------------0000006001240F050B0C030C0C0504050D0609070805050505050F05050505050A05050505050405
0607080823472323081123081141B023008300B207D0030000000000000000000000000000000000
0000000000000000000000000000000000000000

If above Colored Character Is "B023" means 32bit

Or

If it is "B047" means 64bit.

Thanks.

more ways to check oracle is 32 bit or 64 bit by using the following methods

1. You can retrieve this information from the data dictionary view v$version. Banner will tell you 64-bit if the 64-bit version is installed.If it does not specify 64-bit then it is 32-bit (although it does not explicitly say so). Same information you get when login into SQL*PLUS.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

2. On Unix, the file command is also available . Example:

ORACLE:/home/oracle>file `which oracle`
/d01/app/oracle/product/10.2.0/db_1/bin/oracle: setuid setgid ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), for GNU/Linux 2.2.5, dynamically linked (uses shared libs), not stripped

3.by quering v$process view

SQL> select length(addr)*4 || '-bits' word_length
2 from v$process
3 where rownum=1;

WORD_LENGTH
---------------------------------------------
32-bits

4.Check for lib, lib32

1) $ORACLE_HOME/lib32
2) $ORACLE_HOME/lib

If the two directories $ORACLE_HOME/lib32 and $ORACLE_HOME/lib are existing then it is 64 bit.

If there is only an ORACLE_HOME/lib directory then it is 32 bit client.

Performing DML operation in join views(INSERT)

How to insert a join view ?

You may need to set up the following data structures for this example to work:

CREATE TABLE Project_tab (
   Prj_level NUMBER,
   Projno    NUMBER,
   Resp_dept NUMBER);
CREATE TABLE Emp_tab (
   Empno     NUMBER NOT NULL,
   Ename     VARCHAR2(10),
   Job       VARCHAR2(9),
   Mgr       NUMBER(4),
   Hiredate  DATE,
   Sal       NUMBER(7,2),
   Comm      NUMBER(7,2),
   Deptno    NUMBER(2) NOT NULL);

CREATE TABLE Dept_tab (
   Deptno    NUMBER(2) NOT NULL,
   Dname     VARCHAR2(14),
   Loc       VARCHAR2(13),
   Mgr_no    NUMBER,
   Dept_type NUMBER);

---

The following example shows an INSTEAD OF trigger for inserting rows into the MANAGER_INFO view.

CREATE OR REPLACE VIEW manager_info AS
SELECT e.ename, e.empno, d.dept_type, d.deptno,
p.prj_level,p.projno
FROM   Emp_tab e, Dept_tab d, Project_tab p
 WHERE E.DEPTNO= D.DEPTNo
AND D.DEPT_TYPE=P.PROJNO;

View created.

CREATE OR REPLACE TRIGGER manager_info_insert
INSTEAD OF INSERT ON manager_info
REFERENCING NEW AS n  -- new manager information
FOR EACH ROW
DECLARE
   rowcnt number;
BEGIN
   SELECT COUNT(*) INTO rowcnt
  FROM Emp_tab
  WHERE empno = :n.empno;
   IF rowcnt = 0  THEN
       INSERT INTO Emp_tab (empno,ename)
       VALUES (:n.empno, :n.ename);
   ELSE
      UPDATE Emp_tab
    SET Emp_tab.ename = :n.ename
     WHERE Emp_tab.empno = :n.empno;
   END IF;
   SELECT COUNT(*) INTO rowcnt
  FROM Dept_tab
  WHERE deptno = :n.deptno;
   IF rowcnt = 0 THEN
      INSERT INTO Dept_tab
     (deptno, dept_type)
       VALUES(:n.deptno, :n.dept_type);
   ELSE
      UPDATE Dept_tab
     SET Dept_tab.dept_type = :n.dept_type
      WHERE Dept_tab.deptno = :n.deptno;
   END IF;
   SELECT COUNT(*) INTO rowcnt
  FROM Project_tab
   WHERE Project_tab.projno = :n.projno;
   IF rowcnt = 0 THEN
      INSERT INTO Project_tab
         (projno, prj_level)
         VALUES(:n.projno, :n.prj_level);
   ELSE
       UPDATE Project_tab
  SET Project_tab.prj_level = :n.prj_level
  WHERE Project_tab.projno = :n.projno;
END IF;
END;
/

 Trigger created.

SQL> select * from user_updatable_columns
2  where table_name='MANAGER_INFO';

OWNER TABLE_NAME      COLUMN_NAME     UPD INS DEL
----- --------------- --------------- --- --- ---
HR    MANAGER_INFO    ENAME           NO  YES NO
HR    MANAGER_INFO    EMPNO           NO  YES NO
HR    MANAGER_INFO    DEPT_TYPE       NO  YES NO
HR    MANAGER_INFO    DEPTNO          NO  YES NO
HR    MANAGER_INFO    PRJ_LEVEL       NO  YES NO
HR    MANAGER_INFO    PROJNO          NO  YES NO


SQL> SELECT * FROM EMP_TAB;

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM DEPTNO
----- ---------- --------- ---------- --------- ---------- ---------- ------
101 antony     manager          100 03-JAN-98      12000        100     10
111 john                                                                10
112 RAJ                                                                 11
12 anto                                                                12
13 palani                                                              13

SQL> insert into manager_info values('ramesh',14,14,14,14,14);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from manager_info;

ENAME      EMPNO  DEPT_TYPE DEPTNO  PRJ_LEVEL     PROJNO
---------- ----- ---------- ------ ---------- ----------
antony       101         10     10          1         10
john         111         10     10          1         10
RAJ          112         11     11          1         11
anto          12         12     12         12         12
palani        13         13     13         13         13
ramesh        14         14     14         14         14

6 rows selected.

SQL> select * from emp_tab;

EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM DEPTNO
----- ---------- --------- ---------- --------- ---------- ---------- ------
101 antony     manager          100 03-JAN-98      12000        100     10
111 john                                                                10
112 RAJ                                                                 11
12 anto                                                                12
13 palani                                                              13
14 ramesh                                                              14

6 rows selected.

SQL> select * from dept_tab;

DEPTNO DNAME           LOC               MGR_NO  DEPT_TYPE
------ --------------- ------------- ---------- ----------
 10 software        london               100         10
 11 hdware                                           11
 12                                                  12
 13                                                  13
 14                                                  14

SQL> select * from project_tab;

PRJ_LEVEL     PROJNO  RESP_DEPT
---------- ---------- ----------
      1         10
      1         11
     12         12
     13         13
     14         14

worked it out and send ur comments

Performing DML operation in join views(UPDATE)

How to update a join view ?

In order to be able to update a view which joins two or more tables, you need to use an instead of trigger.
The following example shows a view being created over the EMPLOYEES and DEPARTMENTS tables in the HR schema.

SQL> CREATE OR REPLACE VIEW empdept_view
2  (empno,ename,deptno,dname,loc)
3  AS SELECT e.employee_id,e.last_name, d.department_id,
4            d.department_name,d.location_id
5  FROM employees e, departments d
6  WHERE e.department_id=d.department_id;

View created.

We can see which columns we are able to update by looking at USER_UPDATABALE_COLUMNS for this view :

SQL> col owner format a5
SQL> col table_name format a15
SQL> col column_name format a15

SQL> SELECT * FROM user_updatable_columns
2  WHERE table_name='EMPDEPT_VIEW'; 
OWNER TABLE_NAME      COLUMN_NAME     UPD INS DEL
----- --------------- --------------- --- --- ---
HR    EMPDEPT_VIEW    EMPNO           YES YES YES
HR    EMPDEPT_VIEW    ENAME           YES YES YES
HR    EMPDEPT_VIEW    DEPTNO          NO  NO  NO
HR    EMPDEPT_VIEW    DNAME           NO  NO  NO
HR    EMPDEPT_VIEW    LOC             NO  NO  NO

So this view will allow the EMPNO, ENAME columns to be updated, inserted or deleted, but not the DEPTNO, DNAME or LOC columns. The following shows an example of DML operation being carried out on the view :

First select the current data :

SQL> select * from empdept_view
2  where empno=206;
EMPNO ENAME      DEPTNO DNAME                  LOC
----- ---------- ------ --------------- ----------
206 Gietz         110 Accounting            1700

Then update the ENAME column :

SQL> UPDATE empdept_view
2  set ename='JOHN'
3  WHERE empno=206;
1 row updated.

SQL> select * from empdept_view
2  where empno=206;

EMPNO ENAME      DEPTNO DNAME                  LOC
----- ---------- ------ --------------- ----------
206 JOHN          110 Accounting            1700

So this view will allow the EMPNO, ENAME columns to be updated, inserted or deleted, but not the DEPTNO, DNAME or LOC columns. The following shows an example of DML operation being carried out on the view :

Then try to update the DNAME column :

SQL> update empdept_view
2  set dname = 'SOFTWARE'
3  WHERE empno=206;
set dname = 'SOFTWARE'
*
ERROR at line 2:
ORA-01779: cannot modify a column which maps to a non key-preserved table

So it is plain to see that we are not permitted to update the DNAME column even though we can update ENAME. We need to create a trigger.

SQL>CREATE OR REPLACE TRIGGER empdept_view_tr_up
2 INSTEAD OF UPDATE
3 ON empdept_view
4 FOR EACH ROW
5 BEGIN
6 IF (:NEW.deptno <> :OLD.deptno) THEN
7 RAISE_APPLICATION_ERROR(-20001, 'Updates are prohibited to the primary key of the DEPT table');
8 END IF;
9 IF (:NEW.dname <> :OLD.dname) OR
10 (:NEW.loc <> :OLD.loc) THEN
11 UPDATE departments
12 SET department_name = :NEW.dname,
13 location_id = :NEW.loc
14 WHERE department_id = :OLD.deptno;
15 END IF;
16 END;
17 /

Trigger created.

SQL> update empdept_view
2 set dname= 'SOFTWARE'
3 WHERE empno=206;

1 row updated.

SQL> select * from empdept_view
2 where empno=206;

EMPNO ENAME DEPTNO DNAME LOC
----- ---------- ------ --------------- ----------
206 JOHN 110 SOFTWARE 1700

And finally, having a instead of trigger for a specific DML statement on a view causes changes to USER_UPDATABLE_COLUMNS as can be seen below, however, note that it seems to imply that the DEPTNO column can be updated - you'll need to know what's inside your instead of triggers to believe everything you see in USER_UPDATABLE_COLUMNS

SQL> select * from user_updatable_columns
2  where table_name='EMPDEPT_VIEW';

OWNER TABLE_NAME      COLUMN_NAME     UPD INS DEL
----- --------------- --------------- --- --- ---
HR    EMPDEPT_VIEW    EMPNO           YES YES YES
HR    EMPDEPT_VIEW    ENAME           YES YES YES
HR    EMPDEPT_VIEW    DEPTNO          YES NO  NO
HR    EMPDEPT_VIEW    DNAME           YES NO  NO
HR    EMPDEPT_VIEW    LOC             YES NO  NO

the following final example demonstrates :

SQL> UPDATE empdept_view SET deptno=50
2 WHERE deptno=60;
UPDATE empdept_view SET deptno=50
*
ERROR at line 1:
ORA-20001: Updates are prohibited to the primary key of the DEPT table
ORA-06512: at "HR.EMPDEPT_VIEW_TR_UP", line 3
ORA-04088: error during execution of trigger 'HR.EMPDEPT_VIEW_TR_UP'

The error stack clearly shows the trigger's own error code ORA-20001 and message prohibiting you form changing the DEPARTMENTS table's primary key via the empdept_view view.