Update records in one table based on values of another table in oracle database

drop table emp_halim;

CREATE TABLE emp_halim AS SELECT * FROM emp;

SET DEFINE OFF;
Insert into EMP_HALIM
(EMPNO, ENAME, JOB, MGR, HIREDATE,
SAL, COMM, DEPTNO)
Values
(7999, 'halim', 'CLERK', 7782, TO_DATE('01/23/1982 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
1300, NULL, 70);
Insert into EMP_HALIM
(EMPNO, ENAME, JOB, MGR, HIREDATE,
SAL, COMM, DEPTNO)
Values
(7989, 'halim', 'CLERK', 7782, TO_DATE('01/23/1982 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
1300, NULL, 60);
Insert into EMP_HALIM
(EMPNO, ENAME, JOB, MGR, HIREDATE,
SAL, COMM, DEPTNO)
Values
(7979, 'halim', 'CLERK', 7782, TO_DATE('01/23/1982 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
1300, NULL, 50);
COMMIT;

SELECT *
FROM emp_halim;

----------------------------------------------

drop table dept_halim;

CREATE TABLE dept_halim AS SELECT * FROM dept;

SELECT *
FROM dept_halim;

UPDATE emp_halim a
SET ename = (SELECT dname
FROM dept_halim b
WHERE a.deptno = b.deptno)

rollback ;

UPDATE emp_halim a
SET ename = (SELECT dname
FROM dept_halim b
WHERE a.deptno = b.deptno)
WHERE EXISTS (SELECT dname
FROM dept_halim b
WHERE a.deptno = b.deptno);

rollback;





Live examples:--


SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 12 18:27:34 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn scott@test107
Enter password:
Connected.
SQL>
SQL>
SQL> set linesize 1000
SQL> set pagesize 1000
SQL>
SQL> UPDATE emp_halim a
2 SET ename = (SELECT dname
3 FROM dept_halim b
4 /
FROM dept_halim b
*
ERROR at line 3:
ORA-00907: missing right parenthesis

SQL>
SQL> drop table emp_halim;

Table dropped.

SQL>
SQL>
SQL> CREATE TABLE emp_halim AS SELECT * FROM emp;

Table created.

SQL>
SQL>
SQL> SET DEFINE OFF;
SQL> Insert into EMP_HALIM
2 (EMPNO, ENAME, JOB, MGR, HIREDATE,
3 SAL, COMM, DEPTNO)
4 Values
5 (7999, 'halim', 'CLERK', 7782, TO_DATE('01/23/1982 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
6 1300, NULL, 70);

1 row created.

SQL> Insert into EMP_HALIM
2 (EMPNO, ENAME, JOB, MGR, HIREDATE,
3 SAL, COMM, DEPTNO)
4 Values
5 (7989, 'halim', 'CLERK', 7782, TO_DATE('01/23/1982 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
6 1300, NULL, 60);

1 row created.

SQL> Insert into EMP_HALIM
2 (EMPNO, ENAME, JOB, MGR, HIREDATE,
3 SAL, COMM, DEPTNO)
4 Values
5 (7979, 'halim', 'CLERK', 7782, TO_DATE('01/23/1982 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
6 1300, NULL, 50);

1 row created.

SQL> COMMIT;

Commit complete.

SQL>
SQL>
SQL> SELECT *
2 FROM emp_halim;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 1000 20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7999 halim CLERK 7782 23-JAN-82 1300 70
7989 halim CLERK 7782 23-JAN-82 1300 60
7979 halim CLERK 7782 23-JAN-82 1300 50

16 rows selected.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> drop table dept_halim;

Table dropped.

SQL>
SQL>
SQL> CREATE TABLE dept_halim AS SELECT * FROM dept;

Table created.

SQL>
SQL> SELECT *
2 FROM dept_halim;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL>
SQL>
SQL>
SQL> UPDATE emp_halim a
2 SET ename = (SELECT dname
3 FROM dept_halim b
4 WHERE a.deptno = b.deptno);

16 rows updated.

SQL>
SQL> rollback ;

Rollback complete.

SQL>
SQL>
SQL>
SQL> UPDATE emp_halim a
2 SET ename = (SELECT dname
3 FROM dept_halim b
4 WHERE a.deptno = b.deptno)
5 WHERE EXISTS (SELECT dname
6 FROM dept_halim b
7 WHERE a.deptno = b.deptno);

13 rows updated.

SQL>
SQL>
SQL>
SQL>
SQL>

ALTER SYSTEM SET service_names='SYS$SYS.KUPC$S.....SCOPE=MEMORY SID ='' in alert log file

In my alert log, it shows like below

ALTER SYSTEM SET service_names='SYS$SYS.KUPC$S_1_254872100321.halimdb.halimdba.COM','halimdbservice' SCOPE=MEMORY SID='halimdb1';

why it shows this command, i am sure nobody can execute this command in this Database.

so how it appear in alert log.

Answer:-

You must have run expdp or impdp recently. These tools change the service name.

Datapump doesn't tamper with existing services, it just creates

new services for its own queue operation.

You can also find similar statement in the alert log file after running the expdp command.

just test it .

ORA-1461 encountered when generating server alert SMG-3500 in oracle 10.2.0.3 RAC.

this is a bug of 10.2.0.3 version

After applying the 10.2.0.3 patchset the following errors may be reported:

ORA-1461 ENCOUNTERED WHEN GENERATING SERVER ALERT SMG-3500

ORA-01461: can bind a LONG value only for insert into a LONG column

Solutions :-

simply you can avoid it.

or

see metalink note 461911.1

oracle database alert log shows one of ASM Diskgroup Gets Mounted/Dismounted, is it normal behavior ?

such as alert log contents follow............

-------------------------------------------------------------------------------------

--------------------------------------------------------------------------------------

SUCCESS: diskgroup ASMDISKG4 was mounted

SUCCESS: diskgroup ASMDISKG4 was dismounted

SUCCESS: diskgroup ASMDISKG4 was mounted

SUCCESS: diskgroup ASMDISKG4 was dismounted

SUCCESS: diskgroup ASMDISKG4 was mounted

Fri Jun 10 05:15:40 2011

SUCCESS: diskgroup ASMDISKG4 was dismounted

Fri Jun 10 05:23:45 2011

Thread 1 advanced to log sequence 985477

Current log# 1 seq# 985477 mem# 0: +ASMDISKG1/halimdb/redo01.log

Current log# 1 seq# 985477 mem# 1: +ASMDISKG2/halimdb/redo01db.log

Fri Jun 10 05:23:46 2011

SUCCESS: diskgroup ASMDISKG4 was mounted

SUCCESS: diskgroup ASMDISKG4 was dismounted

SUCCESS: diskgroup ASMDISKG4 was mounted

SUCCESS: diskgroup ASMDISKG4 was dismounted

SUCCESS: diskgroup ASMDISKG4 was mounted

----------------------------------------------------------------------------------

----------------------------------------------------------------------------------

Answer:-

This is expected behavior. The messages you see are not errors (that is why they are prefixed with "SUCCESS"). The database instance is creating and then closing archive logs one at a time in the "ASMDISKG4" disk group. Whenever a database closes its last file in a disk group, it dismounts the disk group. Whenever a database accesses a disk group when it does not have any other files open in the disk group, it mounts the disk group.

If you do not want to see the log messages frequently, you can put a mirrored control-file, or mirrored online redo, or dummy online tablespace data file on the diskgroup. This message will not be output frequently in this case. Because CKPT keeps opening a control-file, LGWR keeps opening an online redo, DBW keeps opening an online tablespace data file. So, the diskgroup is not dismounted until the database shutdown.

ORA-00020: maximum number of processes () exceeded ORA-15055: unable to connect to ASM instance ORA-17503: ksfdopn:

If you face following type of error in alert log file and after that database is going to be hang...
and this problem is happen after some days or time

Errors in file /DB01/application1/oracle/admin/halimdb/bdump/halimdb_arc0_69854.trc:
ORA-00313: open failed for members of log group 4 of thread 1
ORA-00312: online log 4 thread 1: '+ASMDISK1/halimdb/redo04.log'
ORA-17503: ksfdopn:2 Failed to open file +ASMDISK1/halimdb/redo04.log
ORA-15055: unable to connect to ASM instance
ORA-00020: maximum number of processes () exceeded
ORA-15055: unable to connect to ASM instance
ORA-00020: maximum number of processes () exceeded
ORA-00312: online log 4 thread 1: '+ASMDISK1/halimdb/redo04.log'
ORA-17503: ksfdopn:2 Failed to open file +ASMDISK1/halimdb/redo04.log
ORA-15055: unable to connect to ASM instance
ORA-00020: maximum number of processes () exceeded
ORA-15055: unable to connect to ASM instance
ORA-00020: maximum number of processes () exceeded

cause is :-
========
The Oracle parameter PROCESSES has been exceeded as a result of multiple database instances connecting to the ASM instance. The default value (often 40) is insufficient to support more than one database connecting to the ASM instance


Then solution is .....
==============
[note:-sometime we do mistake only just checking database processes parameter value,
in this case we have to check ALL ASM instance processes parameter value ]


1) connect to the ASM instance one (if more than one instance and do it for all ASM instance)

2) check the processes parameter value of ASM instance

SQL> show parameter processes

3) check the resource limit of processes

sql> select * from v$resource_limit
where RESOURCE_NAME = 'processes';

if v$resource_limit.MAX_UTILIZATION is reach the processes parameter value then

4) increase the processes parameter value, like

SQL> alter system set processes=300 scope=spfile sid=’+ASM1';

[NOTE : as oracle says......
Processes = 25 + 15n, where n is the number of instances on the box using ASM for their storage.
This formula is for a basic instance and does not accomodate for
* Multiple ARCH processes
* Multiple LGWR processes
Should the ORA-20 occur even after implementing this formula ... add additional for any multiples of these background processes ]


5) restart (bounce) the database and ASM instance as well .