HighLight in your life~

  dump 파일 분석

 인도네시아 자카르타에서 돌아 온 후에 ORACLE DATABASE SERVER에 대해 알기 위해 OCP WDP를 강남 중앙정보처리학원에서 약 한달간 방형욱 선생님 수업을 들었다.

방형욱 선생님께서 말씀해주신 간단한 분류의 Recovery Case 분석이다.

 -----------------
 > Recovery Case <
 -----------------

 - complete recovery -

 [0] parameter file 손상 1 : 파일이 삭제된 상황                            --> 재생성(alert 파일을 활용할 경우 편리하다.)
 [0] parameter file 손상 2 : spfile에 오타 입력된 상황                     --> pfile 생성 ▷ pfile 편집 ▷ spfile 재생성
 [0] password file 손상                                                    --> 재생성

 [1] control file 1개 손상                                                 --> 복사, 붙여넣기
 [2] control file 몽땅 손상                                                --> create controlfile 명령 수행

 [3] redo log file 멤버 1개 손상                                           --> 복사, 붙여넣기
 [4] redo log file 그룹 손상 : Inactive                                    --> 삭제 or 복사, 붙여넣기 or Clear logfile  --> 반드시!! 백업
 [5] datafile 손상 : temporary       Tablespace의 datafile                 --> restartup 또는 "추가 뒤 삭제"
 [6] datafile 손상 : 일반            Tablespace의 datafile                 --> open   recover(open에서)
 [7] datafile 손상 : 시스템(및 Undo) Tablespace의 datafile                 --> closed recover(mount에서)
 [8] datafile 손상 : 백업하지 않은 datafile                                --> create datafile + redo
 [9] datafile 손상 : 디스크 손상으로 인해 datafile을 다른 위치로 restore   --> rename file + redo

 - incomplete recovery -

   [10] table drop purge : flashback으로 복구 불가능한 경우                --> time-based   불완전 복구

 - Clone Database -

   [11] Database의 백업 datafile을 이용해서 복제 DB 생성하기 

 - Clone Database를 이용한 불완전 복구 -

   [12] Clone Database를 이용한 복구 (user-managed 방식)     

 - incomplete recovery -

   [13] current(또는 active) group 손상                                    --> cancel-based 불완전 복구

Oracle Database 10gR2, 11gR2 Installation On Enterprise Linux 4.0 (오라클 설치) [메인 참조 - http://gseducation.blog.me/20093164977]

윈도우 7에 VMWARE를 설치하고 리눅스를 INSTALL하였다. 문제는 인터넷이 안된다는 것이 없다.

현재 나의 상황은 아래와 같다.

  1. 상황 분석

  1) LG+ 티비와 인터넷을 함께 사용하고 있다.

  2) 위와 같이 공유 설정이 되어 있다. (DHCP)

  3) VMWARE를 설치하고 리눅스를 설치하였다. IP 설정작업은 아래와 같이 진행하였다.

2. 리눅스 상황을 아래와 같이 맞추어 주었다.

1) Telnet과 FTP를 사용할 수 있도록 아래와 같이 파일의 내용을 수정하고, xinetd을 재시작한다.

    [root@ora10gr2 ~]# su - root
    [root@ora10gr2 ~]# vi /etc/xinetd.d/telnet : disable = no로 수정

    [root@ora10gr2 xinetd.d]# vi /etc/xinetd.d/krb5-telnet  : disable = no로 수정 : 혹시 telnet이 없다면 이렇게 하세요.
    [root@ora10gr2 xinetd.d]# vi /etc/xinetd.d/gssftp         : disable = no로 수정
                                                               server_args = -l -a 부분의 -a를 제거

    [root@ora10gr2 xinetd.d]# cd /etc/rc.d/init.d
    [root@ora10gr2 init.d]# ./xinetd restart
    (혹은 [root@ora10gr2 init.d]# service xinetd restart)
    [root@ora10gr2 ~]# cd
 
2) 호스트명 확인 및 설정

(가) /etc/hosts 파일과 /etc/sysconfig/network 파일의 내용을 다음과 같이 설정한다.
 
[root@ora10gr2 ~]# vi /etc/hosts
#127.0.0.1         localhost
192.168.0.10    ora10gr2.gsedu.com      ora10gr2        localhost.localdomain localhost

[root@ora10gr2 ~]# vi /etc/sysconfig/network

(나) 변경
os] hostname [호스트 이름]

(다) 확인
os] hostname

os] service network restart

[참조] http://cafe.naver.com/ticenter/541

[if it still not work, then iptables is blocking that, you need to flush iptables or turn off service like]

os] iptables -F
os] iptables -X

os] /etc/init.d/xinetd stop

[참조] http://www.webhostingtalk.com/showthread.php?t=897301

export ORACLE_SID=ardb

-----------------------------------------------------------------
select * from v$resource where type in ('TM', 'TX');
select * from v$lock where type in ('TM', 'TX');
-----------------------------------------------------------------

update t1
set sal = sal+1000
where rownum = 1;

-----------------------------------------------------------------
select * from v$resource where type in ('TM', 'TX');
select * from v$lock where type in ('TM', 'TX');
-----------------------------------------------------------------

update t1
set sal = 2000
where empno=7788;

-----------------------------------------------------------------
select * from v$resource where type in ('TM', 'TX');
select * from v$lock where type in ('TM', 'TX');
-----------------------------------------------------------------

update t1
set sal = sal+1000
where rownum = 1;

-----------------------------------------------------------------
select * from v$resource where type in ('TM', 'TX');
select * from v$lock where type in ('TM', 'TX');
-----------------------------------------------------------------

alter table t1 drop(sal);

-----------------------------------------------------------------
select * from v$resource where type in ('TM', 'TX');
select * from v$lock where type in ('TM', 'TX');
-----------------------------------------------------------------

1. EM(Enterprise Manager) management
1)EM 시작과 종료

os] export ORACLE_SID=인스턴스이름
os] emctl start dbconsole
os] emctl status dbconsole
os] emctl stop dbconsole

Note: You may need to navigate to your $ORACLE_HOME/bin directory

         if this directory is not in your operating system(OS) path.
         Database Control uses a server-side agent process.

         This agent process automatically starts and stops
         when the dbconsole process is started or stopped.

2)EM 접속하기
http://ip_address:1158/em
or
http://host_name:1158/em

3)로그인 후 사용

4) EM 재설정

OS] emca -config dbcontrol db -repos drop

OS] sqlplus / as sysdba

SQL> drop user sysman cascade;
SQL> drop role MGMT_USER;
SQL> drop user MGMT_VIEW cascade;
SQL> drop public synonym MGMT_TARGET_BLACKOUTS;
SQL> drop public synonym SETEMVIEWUSERCONTEXT;

OS] emca -config dbcontrol db -repos create

2. iSQP*PLus management

1)iSQP*PLus 시작과 종료

os]isqlplusctl start

2)EM 접속하기
http://ip_address:5560/isqlplus
or
http://host_name:5560/isqlplus

3)로그인 후 사용

Note: The port number that is used by iSQL*Plus is usually 5560

        unless Oracle Universal Installer(OUI) detects that
        something is already using that port.
        Check $ORACLE_HOME/install/portlist.ini to find the port used by iSQL*Plus

os] more $ORACLE_HOME/install/portlist.ini

  SQL> show parameter    --> http://docs.oracle.com/cd/B19306_01/server.102/b14237/toc.htm

  SQL> ed param.sql

       set linesize 300
       set pagesize 50

       col name format a50
       col value format a20
       col description format a50

       select ksppinm name, ksppstvl value,
              decode(bitand(ksppiflg/256,1),1,'true','false') ses_modifiable,
              decode(bitand(ksppiflg/65536,3),1,'immediate',2,'deferred',3,'immediate','false') sys_modifiable,
              ksppdesc description
       from sys.x$ksppi i, sys.x$ksppcv v
       where i.indx = v.indx;

  SQL> @param.sql
  SQL> exit

http://blog.naver.com/kmymk?Redirect=Log&logNo=110081009687

http://www.cyworld.com/BC83/6987448

http://blog.naver.com/ladygrey10g?Redirect=Log&logNo=140136042849

http://blog.naver.com/ladygrey10g?Redirect=Log&logNo=140136042849

http://blog.naver.com/fntlove?Redirect=Log&logNo=110127037163

http://blog.naver.com/astrolena?Redirect=Log&logNo=70027469566

*SQL Functions(10g) -  Aggregate Functions

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm#sthref912

1. AVG(N) - N의 평균을 구하여 반환한다. 단, NULL인 값은 제외하고 계산한다.

SQL> SELECT EMPNO, SAL, COMM FROM EMP;

SQL> SELECT AVG(COMM)         AS AVG1,  --  커미션이 있는 사원들의 평균 커미션
                 AVG(NVL(COMM, 0))  AS AVG2   --  사원 1인당 평균 커미션
         FROM EMP;

Analytic Example

SQL> SELECT manager_id, last_name, hire_date, salary,
   AVG(salary) OVER (PARTITION BY manager_id ORDER BY hire_date
   ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS c_mavg
   FROM employees;

2. COLLECT(N) -

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions024.htm#i1271564

  SQL> create or replace procedure proc1 (d EMP.DEPTNO%TYPE)
  is
    TYPE emp_sal_table_type IS TABLE OF emp.sal%type
      INDEX BY PLS_INTEGER;

    emp_sal_tab emp_sal_table_type;
  begin
    select sal BULK COLLECT INTO emp_sal_tab
    from emp
    where deptno = d
    order by sal desc;

    p.p(emp_sal_tab.first);
    p.p(emp_sal_tab.last);

    for i in emp_sal_tab.first .. emp_sal_tab.last loop
      p.p(emp_sal_tab(i));
    end loop;
  end;
  /

  SQL> show errors

  SQL> exec proc1(10)
  SQL> exec proc1(30)

3. CORR(M, N) -

CORR

returns the coefficient of correlation of a set of number pairs. You can use it as an aggregate or analytic function.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions028.htm#i82637

Analytic Example

SQL> SELECT employee_id, job_id,
TO_CHAR((SYSDATE - hire_date) YEAR TO MONTH ) "Yrs-Mns", salary,
CORR(SYSDATE-hire_date, salary)
OVER(PARTITION BY job_id) AS "Correlation"
FROM employees
WHERE department_id in (50, 80)
ORDER BY job_id, employee_id;

4. COUNT(N) - N의 총 개수를 계산한다. 단, NULL은 제외한다.

SQL>  drop table t1 purge;

SQL> create table t1 (col1 number);
SQL>  insert into t1 values (1000);SQL>
SQL> insert into t1 values (1000);
SQL> insert into t1 values (2000);
SQL> insert into t1 values (2000);
SQL> insert into t1 values (null);
SQL> insert into t1 values (null);

SQL> commit;

SQL> select col1, col1, col1 from t1;

SQL> select count(*), count(col1), count(distinct col1) from t1;

5. COVAR_POP(P1, P2) - COVAR_POP returns the population covariance of a set of number pairs. You can use it as an aggregate or analytic function.

COVAR_POP(P1, P2) = (SUM(P2*P1)- SUM(P2) * SUM(P1) / N) /N

population : 집단

covariance : 공분산

 - http://terms.naver.com/entry.nhn?cid=1839&docId=75608&mobile&categoryId=1841

 - http://terms.naver.com/entry.nhn?cid=1839&docId=977687&mobile&categoryId=1841

SQL> SELECT job_id,
   COVAR_POP(SYSDATE-hire_date, salary) AS covar_pop,
   COVAR_SAMP(SYSDATE-hire_date, salary) AS covar_samp
   FROM employees
   WHERE department_id in (50, 80)
   GROUP BY job_id;

6. COVAR_SAMP(P1, P2) = (SUM(P1*P2) - SUM(P1) * SUM(P2) / N)/(N-1) 

COVAR_SAMP returns the sample covariance of a set of number pairs. You can use it as an aggregate or analytic function.

7. CUME_DEST - CUME_DIST calculates the cumulative distribution of a value in a group of values. The range of values returned by CUME_DIST is >0 to <=1. Tie values always evaluate to the same cumulative distribution value.

SQL> SELECT CUME_DIST(15500, .05) WITHIN GROUP
   (ORDER BY salary, commission_pct) "Cume-Dist of 15500"
   FROM employees;

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions035.htm#i82886

8. DENSE_RANK(M, N) - DENSE_RANK computes the rank of a row in an ordered group of rows and returns the rank as a NUMBER. The ranks are consecutive integers beginning with 1. The largest rank value is the number of unique values returned by the query. Rank values are not skipped in the event of ties. Rows with equal values for the ranking criteria receive the same rank. This function is useful for top-N and bottom-N reporting.

This function accepts as arguments any numeric datatype and returns NUMBER.

1) RANK() VS DENSE_RANK()

SQL> SELECT EMPNO, SAL,
         RANK() OVER(ORDER BY SAL DESC) RANK_T,
         DENSE_RANK() OVER(ORDER BY SAL DESC) DENSE_RANK_T
         FROM EMP;

        -- RANK() 함수는 2등이 2명이면 다음 등수는 4등이 됩니다.

        -- DENSE_RANK() 함수는 2등이 2명이면 다음 등수는 3등이 됩니다.

        -- RANK() 함수는 중간에 빠지는 등수가 있지만 DENSE_RANK() 함수는 중간에 빠지는 등수가 없습니다.

2) 상위 5명 추출

SQL> SELECT * FROM
        (SELECT EMPNO, ENAME, SAL, RANK() OVER(ORDER BY SAL DESC) RANK FROM EMP)
        WHERE RANK < 6;

SQL> SELECT * FROM
        (SELECT EMPNO, ENAME, SAL, RANK() OVER(ORDER BY SAL DESC) RANK FROM EMP)
        WHERE ROWNUM < 6;

3)

SQL> SELECT DENSE_RANK(15500, .05) WITHIN GROUP
   (ORDER BY salary DESC, commission_pct) "Dense Rank"
   FROM employees;

9. FIRST & LAST - FIRST와 LAST 함수는 SORTING된 집합에서 첫번째 혹은 마지막 결과를 얻는데 사용된다.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions056.htm#i1000901

SQL> SELECT department_id,
         MAX(salary) KEEP (DENSE_RANK LAST ORDER BY SALARY) "최대값",
         MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY SALARY) "최소값"
         FROM employees
         GROUP BY department_id;

SQL> SELECT DEPARTMENT_ID, 
         MAX(EMPLOYEE_ID||' '||LAST_NAME) KEEP (DENSE_RANK FIRST ORDER BY SALARY DESC) 최대급여,
         MAX(SALARY) 최대값,
         MIN(EMPLOYEE_ID||' ' ||LAST_NAME) KEEP (DENSE_RANK LAST ORDER BY SALARY DESC) 최소급여,
         MIN(SALARY) 최소값
         FROM EMPLOYEES
         GROUP BY DEPARTMENT_ID;

10. GROUP_ID - GROUP_ID 함수는 지정된 GROUP BY 결과로 부터 중복된 그룹을 구별한다. 질의 결과에서 중복된 GROUPING을 필터링 하는 것에 유용하다. 유일한 중복 그룹을 식별하기 위해 ORACLE NUMBER를 반환한다. GROUP_ID는 GROUP BY clause를  포함한 SELECT statement에 적용 가능하다. 만약 N이 특정 GROUPING에서 중복이 존재하면, GROUP_ID는 0~(N-1) 범위의 값을 반환한다.

1) http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions061.htm#i1002035

2) http://www.oracle-base.com/articles/misc/rollup-cube-grouping-functions-and-grouping-sets.php

- ROLLUP, CUBE, GROUPING Functions and GROUPING SETS, GROUP_ID

3) http://etutorials.org/SQL/Mastering+Oracle+SQL/Chapter+13.+Advanced+Group+Operations/13.3+The+GROUPING_ID+and+GROUP_ID+Functions/

- GROUP BY CUBE, GROUP_ID

4) http://blog.naver.com/PostView.nhn?blogId=agoodman_jjk&logNo=130092652199&categoryNo=25&viewDate=&currentPage=1&listtype=0

5) http://neucareer.tistory.com/50

6) http://radiocom.kunsan.ac.kr/lecture/oracle/function/GROUP_ID.html

11. GROUPING(Expr) - Expr의 Value가 NULL이면 1을 NOT NULL이면 0을 반환한다.

                    SELECT 한 ROW에서 NULL 값을 유무를 확인 할 때 사용한다.

http://www.statwith.pe.kr/ORACLE/functions054.htm

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions062.htm#i77498

SQL> SELECT DEPTNO, 
                        JOB, 
                        SUM(SAL), 
                        GROUPING(DEPTNO) AS DEPT_G,
                        GROUPING(JOB) AS JOB_G
          FROM EMP
          GROUP BY ROLLUP(DEPTNO, JOB)
          ;

SQL> SELECT DECODE(GROUPING(department_name), 1, 'All Departments',
   department_name) AS department,
   DECODE(GROUPING(job_id), 1, 'All Jobs', job_id) AS job,
   COUNT(*) "Total Empl", AVG(salary) * 12 "Average Sal"
   FROM employees e, departments d
   WHERE d.department_id = e.department_id
   GROUP BY ROLLUP (department_name, job_id);

12. GROUPING_ID(Expr) - Expr의 GROUPING 비트 벡터에 대응하는 수치를 반환한다. GROUPING_ID는 ROLLUP, CUBE, GROUPING 함수와 같은 GROUP BY 확장 기능을 포함한 SELECT 문장에서 적용할 수 있다. 많은 GROUP BY 표현을 가지는 쿼리에서, 많은 GROUPING 함수를 필요로 하는 특정한 행의 GROUP BY 레벨을 지정하기 위해서는 복잡한 쿼리가 요구된다. GROUPING_ID는 이러한 경우 유용한다.

 GROUPING_ID 함수는 다수의 GROUPING 함수의 결과와 비트 벡터(0과 1의 문자열)를 연결하는 것과 동일핟. GROUPING_ID를 이용하여서 다수의 GROUPING 함수를 사용하지 않고서, 표한하기 위한 행의 필터 조건이 간단해 진다. 행 필터링은 원하는 행잉 GROUPING_ID=N의 단일 조건으로 정의 될 수 있어서 더 쉬워진다. 단일 테이블에서 다수의 집계의 레벨을 저장할 때 유용하다.

http://www.statwith.pe.kr/ORACLE/functions055.htm

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions063.htm#i1001964

SQL> SELECT  DEPTNO,
    DECODE(GROUPING_ID(DEPTNO,JOB),3,'총합계',1,'부서합계',JOB) AS JOB,
    DECODE(GROUPING_ID(DEPTNO,JOB,EMPNO),1,'직급소계',EMPNO) AS EMPNO,
    DECODE(GROUPING_ID(DEPTNO,JOB,EMPNO),0,ENAME,COUNT(*)||'명') AS ENAME,
    MGR,
    SUM(SAL) AS SAL,
    SUM(COMM) AS COMM
FROM EMP
GROUP BY ROLLUP (DEPTNO,JOB,(EMPNO,ENAME,MGR))

;

13. VARIANCE(Expr) - Exprt의 분산(VARIANCE) 값을 반환한다.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions212.htm#i89144

1) Aggregate Example

SQL> SELECT VARIANCE(SALARY)  AS "Variance" FROM EMPLOYEES;

2) Analytic Example

SQL> SELECT LAST_NAME, SALARY, VARIANCE(SALARY) OVER (ORDER BY HIRE_DATE)  AS "Variance" FROM EMPLOYEES;

14. MAX(N) - N의 최대값을 반환한다.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions085.htm#i89072

1) Aggregate Example
SQL> SELECT MAX(salary) AS "최대" FROM employees;

2) Analytic Example
SQL> SELECT manager_id, last_name, salary,
   MAX(salary) OVER (PARTITION BY manager_id) AS "메니져별 최대"
   FROM employees;

15. MIN(N) -  N의 최소값을 반환한다.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions087.htm#i1280029

SQL> SELECT MIN(hire_date) AS  "Earliest" FROM employees;

SQL> SELECT manager_id, last_name, hire_date, salary,
   MIN(salary) OVER(PARTITION BY manager_id ORDER BY hire_date
   RANGE UNBOUNDED PRECEDING) AS p_cmin
   FROM employees;

16. SUM(N)- N의 합계을 반환한다.

1) Aggregate Example
SQL> SELECT SUM(salary) "Total"
         FROM employees;

2) Analytic Example

SQL> SELECT manager_id, last_name, salary,
         SUM(salary) OVER (PARTITION BY manager_id ORDER BY salary
         RANGE UNBOUNDED PRECEDING) l_csum
         FROM employees;

17. MEDIAN(Expr) - Expr의 중앙값을 반환한다. 데이터를 크기 순서로 나열할 때 가장 중앙에 위치하게 되는 데이터 값. 자료의 개수 n이 홀수이면 (n+1)/2번째 값이 중앙값이고, 짝수이면 n/2번째 값과 (n/2)+1번째 값의 평균값이 중앙값이 된다.(네이버 IT용어사진)

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions086.htm#i1279886

http://terms.naver.com/entry.nhn?cid=80&docId=512281&mobile&categoryId=80

http://terms.naver.com/entry.nhn?cid=2906&docId=1619660&mobile&categoryId=2906

SQL> SELECT department_id, MEDIAN(salary)
         FROM employees
         GROUP BY department_id;

SQL> SELECT manager_id, employee_id, salary,
         MEDIAN(salary) OVER (PARTITION BY manager_id) "Median by Mgr"
         FROM employees;

18. RANK(Expr) - Expr의 순위를 반환한다.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions123.htm#i1269223

1) Aggregate Example
SQL> SELECT RANK(15500, 0.5) WITHIN GROUP
            (ORDER BY SALARY, COMMISSION_PCT) "Rank"
            FROM EMPLOYEES

SQL> SELECT RANK(15500) WITHIN GROUP
            (ORDER BY SALARY DESC) "Rank of 155500"
            FROM EMPLOYEES

2) Analytic Example

SQL> SELECT department_id, last_name, salary, commission_pct,
           RANK() OVER (PARTITION BY department_id
           ORDER BY salary DESC, commission_pct) "Rank"
           FROM employees WHERE department_id = 90;

19. PERCNET_RANK(Expr) - PERCENT_RANK 함수는 CUME_DIST(cumulative distribution) 함수와 비슷하다. PERCENT_RANK의 반환값은 0과 1사이다. 첫번째 집합의 PERCENT_RANK의 값은 0이다.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions109.htm#i1043951

1) Aggregate Example
SQL> SELECT PERCENT_RANK(15000, .05) WITHIN GROUP
   (ORDER BY salary, commission_pct) "Percent-Rank"
   FROM employees;

2) Analytic Example

SQL> SELECT department_id, last_name, salary,
   PERCENT_RANK()
      OVER (PARTITION BY department_id ORDER BY salary DESC) AS pr
   FROM employees
   ORDER BY pr, salary;

20. STDDEV(Expr) - Exprt의 표본표준편차(the sample standard deviation)을 반환한다. STTDEV는 1건의 데이타를 갖고 있으면 0을 반환하고 STDDEV_SAMP은 NULL을 반환한다.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions159.htm#i89108

1) Aggregate Example
SQL> SELECT STDDEV(salary) "Deviation"
   FROM employees;

2) Analytic Example

SELECT last_name, salary,
   STDDEV(salary) OVER (ORDER BY hire_date) "StdDev"
   FROM employees 
   WHERE department_id = 30;

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

21. GROUPING SETS

http://radiocom.kunsan.ac.kr/lecture/oracle/function/GROUPING_SETS.html

22. GROUPING(), ROLLUP, CUBE Study 정리

http://fly32.net/252

*********************************
ANALYTIC FUNCTION 정리해야 함
*********************************

*SQL Functions(10g) - NUL-Related Functions

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm#sthref912

1. COALESCE(Expr) - Expr List에서 첫번째 NULL이 아닌 값을 반환한다. Expr List의 값이 모두 NULL이면 NULL을 반환한다.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions023.htm#i1001341

* 아래의 3개는 모두 유사하다.

1) CASE WHEN expr1 IS NOT NULL THEN expr1 ELSE expr2 END

2) COALESCE(expr1, expr2, ..., exprn), for n >= 3

3) CASE WHEN expr1 IS NOT NULL THEN exp1

SQL> SELECT coalesce('','', 'Expr1', 'Expr2') FROM DUAL;

SQL> SELECT ename, comm, sal, coalesce(comm, sal, 0) AS COAL_TEST
FROM emp;

SQL> SELECT last_name, manager_id, commission_pct,
COALESCE(manager_id,commission_pct, -1) comm
FROM employees
ORDER BY commission_pct;

2. LNNVL(Expr) - It provides a concise way to evaluate a condition when one or both operands of the condition may be null. The function can be used only in the WHERE clause of a query. It takes as an argument a condition and returns TRUE if the condition is FALSE or NUKNOWN and FALSE if the condition is TRUE. Oracle Database sometimes uses the LNNVL function internally in this way to rewrite NOT IN conditions as NOT EXISTS conditions.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions078.htm#i1479295

아래의 1)번과 2)번 결과는 동일하다.

1)

SQL> SELECT ENAME, SAL, COMM, SAL+COMM
FROM EMP
WHERE LNNVL((SAL+COMM) >= 1800)

;

2)

SQL> SELECT ENAME, SAL, COMM, SAL+COMM
FROM EMP
WHERE (SAL+COMM) < 1800 OR (SAL+COMM) IS NULL
;

SQL> SELECT COUNT(*) FROM employees WHERE commission_pct < .2;

SQL> SELECT COUNT(*) FROM employees WHERE LNNVL(commission_pct >= .2);

3. NULLIF(Expr1, Expr2) - Expr1과 Expr2가 같으면 NULL 아니면 Expr1을 반환한다. Expr1에는 NULL이 할당될 수 없다.

CASE WHEN expr1 = expr2 THEN NULL ELSE expr1 END

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions102.htm#i1001340

SQL> SELECT e.last_name, NULLIF(e.job_id, j.job_id) "Old Job ID"
   FROM employees e, job_history j
   WHERE e.employee_id = j.employee_id
   ORDER BY last_name;

4. NVL(Expr1, Expr2) - Expr1이 NULL이면 Expr2를 반환하고, Expr1이 NOT NULL이면 Expr1을 반환한다. 

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions105.htm#i91798

SQL> SELECT last_name, NVL(TO_CHAR(commission_pct), 'Not Applicable')
   "COMMISSION" FROM employees;

5. NVL2(Expr1, Expr2, Expr3) - Expr1이 NOT NULL이면 Expr2를 반환한다. 만약 Expr1이 NULL이면 Expr3를 반환한다.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions106.htm#i91806

SQL> SELECT last_name, salary, commission_pct, NVL2(commission_pct,
   salary + (salary * commission_pct), salary) income
   FROM employees;

*SQL Functions(10g) -  Environment and Identifier Functions

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm#sthref912

1. SYS_CONTEXT  - 환경에 관련된 정보들을 보여준다.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions165.htm#i1038176

SQL> SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL;

ACTION, AUDITED_CURSORID, AUTHENTICATED_IDENTITY, AUTHENTICATION_DATA, AUTHENTICATION_METHOD, BG_JOB_ID, CLIENT_IDENTIFIER, CLIENT_INFO, CURRENT_BIND, CURRENT_SCHEMA, CURRENT_SCHEMAID, CURRENT_SQL, CURRENT_SQLn, CURRENT_SQL_LENGTH, DB_DOMAIN, DB_UNIQUE_NAME, ENTRYID, ENTERPRISE_IDENTITY, FG_JOB_ID, GLOBAL_CONTEXT_MEMORY, GLOBAL_UID, HOST, IDENTIFICATION_TYPE, INSTANCE, INSTANCE_NAME, IP_ADDRESS, ISDBA, LANG, LANGUAGE, MODULE, NETWORK_PROTOCOL, NLS_CALENDAR, NLS_CURRENCY, NLS_DATE_FORMAT, NLS_DATE_LANGUAGE, NLS_SORT, NLS_TERRITORY, OS_USER, POLICY_INVOKER, PROXY_ENTERPRISE_IDENTITY, PROXY_GLOBAL_UID, PROXY_USERID, SERVER_HOST, SERVICE_NAME, SESSION_USER, SESSION_USERID, SESSIONID, SID, STATEMENTID, TERMINAL

2. SYS_GUID - SYS_GUID generates and returns a globally unique identifier (RAW value) made up of 16 bytes. On most platforms, the generated identifier consists of a host identifier, a process or thread identifier of the process or thread invoking the function, and a nonrepeating value (sequence of bytes) for that process or thread.

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions168.htm#i79194

3. SYS_TYPEID

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions169.htm#i1044156

4. UID - UID는 세센 사용자를 중복 없이 구분하기 위해 정수를 반환한다.

SQL> SELECT UID FROM DUAL;

5. USER - 세션 사용자(the user who logged on)의 이름을 반환한다.(데이터 타입은 VARCHAR2이다.)

SQL> SELECT USER, UID FROM DUAL; 

6. USERENV - 현재 세션의 정보를 반환한다. 이러한 정보들을 통해 application-specific audit trail table를 작성하거나 현재 세션의 determining the language-specific characters를 알 수 있다.

SQL> SELECT USERENV('LANGUAGE') AS "Lang",
--USERENV('ENTRYID') "ENTRYID",
--USERENV('ISDBA') "ISDBA",
USERENV('LANG') AS  "LANG",
USERENV('SESSIONID') "SESSIONID"
--USERENV('TERMINAL') "TERMINAL"
FROM DUAL;

*SQL Functions(10g) - Collection Functions

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm#sthref912

1. CARDINALITY
2. COLLECT
3. POWERMULTISET
4. POWERMULTISET BY CARDINALITY
5. SET

*SQL Functions(10g) - Hierarchical Functions

http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm#sthref912

1. SYS_CONNECT_BY_PAHT