In sweet memories of my ever loving brother "kutty thambi " ARUN KUMAR

Tuesday, August 11, 2009

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,


Saturday, August 8, 2009

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,

Thursday, August 6, 2009

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.

Tuesday, August 4, 2009

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

Monday, August 3, 2009

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.

 
Share/Bookmark