라이프온룸

퇴근 후 일상

Oracle Database 사용

by · Published · Updated

  • 관련 카테고리 : [taxonomy category field=link]
    •

    Oracle 

    Ubuntu 20 에서 설치 및 사용함 설치는 아래 URL 참조 했고 조금 어려웠던 점은 shared memory 가 900mb 로 잡혀있었는데 이게 작으면 Oracle이 안 살아 났다.(df -h 로 확인 가능)  그래서 늘려주는 작업을 아래 와 같이 진행 했다. 

    https://a292run.tistory.com/entry/Oracle-11gR2-on-Ubuntu-2004

    sudo -s
umount /dev/shm
sudo rm -rf /dev/shm 
sudo mkdir /dev/shm
mount --move /run/shm /dev/shm
sudo mount -t tmpfs shmfs -o size=2048m /dev/shm

    https://stackoverflow.com/questions/51998745/oracle-xe-11-2-0-database-configuration-failed-on-ubunto-14-and-16

    • Booting 시 shm 설정 

    /etc/fstab 열고 아래 내용 추가 

    tmpfs /dev/shm tmpfs defaults,size=2g 0 0

    DB 연결 

    1. sqlplus sys as sysdba > conn system

    scott 계정 Enable

    테스트 계정인 듯 하다 

    1. sqlplus / as sysdba
    2. @$ORACLE_HOME/rdbms/admin/utlsampl.sql
    3. sqlplus scott/tiger

     

    Oracle DB 쿼리 

    1. ORDER BY – ASC, DESC order by 절은 제일 마지막에 수행 된다. From 절을 먼저수행 하고 Select를 수행한 다음 Order By
    2. Where – Where 절은 From 절을 수행한 다음에 수행된다. 그래서 Select 절에 별칭을 지정했고 이를 Where절에서 사용하면 에러가 발생한다. 
      • nls_session_parameters 는 현제 접속한 Session 의 국가, 날짜 형식 에 대한 정보를 출력해 준다. 
      • ALTER SESSION SET NLS_DATE_FORMATE=’YY/MM/DD’ 로 형식 변경이 가능하다. 
      • 세션은 데이터베이스로 로그인 해서 로그아웃 할때 까지 부여되는 것 세션을 종료하면 세션 변경사항은 폐기된다. 
    3. NVL – if null NVL(comm, 0 ) = 0, NULL 과의 산술연산은 NULL이다. 
    4. <>, ^= 비교연산자로 같지 않다를 의미한다. 
    5. NOT 
    6. LIKE – wildcard 사용가능
      • ‘S%’ – 처음 알파벳이 S 인 데이터 
      • ‘_M%’ – 두번째 철자가 M인 데이터
    7. IS NULL – NULL 인 데이터는 IS NULL 로 검색 
    8. DISTINCT – Select 절에 사용되며 중복을 없애줌 
    9. IN – where job in (‘SALESMAN’, ‘ANALYST’)
    10. TRUE AND NULL – 은 NULL 이다. NULL은 알수 없는 값임으로 TRUE 인지 FALSE인지 알 수 없다고 생각하면 편할것 같다. 
    11. INITCAP – 첫번째 철자만 대문자 함수이다. 
    12. SUBSTR – 특정 철자 추출 
    13. LENGTH – 문자열 길이 LENGTHB 는 바이트 단위로 
    14. INSTR – 특정 철자의 INDEX 출력 
    15. DUAL – 더미 문법 시험에 좋다. 
    16. RPLACE – 특정 철자를 다른 문자로 바꾼다. REPLACE(sal, 0, “#”), REGEXP_REPLACE(sal, ‘[0-3]’, ‘*’) as SAL
    17. LPAD, RPAD – 고정된 자리수를 지정하고 데이터가 자리수보다 작으면 지정한 문자를 체운다. LPAD(sal, 10, ‘*’)
    18. ROUND 반올림, TRUNC 버림, MOD 나머지값, FLOOR 몫
    19. MONTHS_BETWEEN – 날짜간 개월수 출력 MONTHS_BETWEEN(sysdate, hiredate) TO_DATE를 써서 산술연산해도 된다. 
    20. ADD_MONTHS – 개월수를 더한 날짜 select ADD_MONTHS(TO_DATE(‘2019-05-01’, ‘RRRR-MM-DD’), 100) from dual
      • select TO_DATE(‘2019-05-01’, ‘RRRR-MM-DD’) + interval ‘100’ month from dual이렇게 해도 된다. 
    21. NEXT_DAY – 특정 날짜 뒤에 오는 요일의 날짜 출력 NEXT_DAY(‘2019/05/06’, ‘월요일’)
    22. LAST_DAY – 특정 날짜가 있는 달의 마지막 날짜 LAST_DAY(‘2019/05/22’)
    23. EXTRACT – 날짜에서 특정 년, 달 같은 요소를 추출 
    24. TO_DATE – TO_DATE(’81/11/17′, ‘RR/MM/DD’)
    25. SET AUTOT ON 실행계획 출력
    26. NVL2 – if null else NVL2(comm, sal + comm, sal) – comm 이 NULL이면 sal을 출력 Null이 아니면 sal+comm 출력 
    27. DECODE – If 문을 SQL로 구현한것 DECODE(deptno, 10, 300, 20, 400,0) if deptno = 10 300, else if deptno = 20 400 else 0 else에 해당하는 default 값은 생략 가능 
    28. case – decode 와 마찬가지로 if 문을 SQL로 표현 
      • CASE WHEN sal >= 3000 then 500
      • When sal >= 5000 then 300
      • ELSE 0 END 
    29. MAX, MIN, AVG(Null 무시)
    30. GROUP BY –  데이터를 그룹핑함 
      • select job, MAX(sal) from emp where job=’SALESMAN’; — error 발생 Job 은 행이 여러 개고  MAX는 한 개 임으로 
      • select job, MAX(sal) from emp where job=’SALESMAN’ GROUP BY job; — 정상동작 
      • select job, MAX(sal) from emp GROUP BY job; — 직업별 최고 연봉
      • 실행 순서는 From, Where, Group By, Select 순으로 실행 
    31. 함수는 Where절이 False라도 항상 출력된다. 예를 들어 select min(sal) from emp where 1=2; 는 no row selected 가 아니라 NULL이다. 
    32. HAVING – WHERE 절과 비슷하지만 Group 함수(MIN, MAX, AVG, SUM 등등)를 허용한다. 
      • select job, sum(sal) from emp where job != ‘SALESMAN’ group by job HAVING sum(sal) >= 4000;
      • 여기서 Having 절은 4번째로 수행된다. 
    33. COUNT – NULL은 무시된다. 
    34. RANK – 순위를 출력 select ename, job, sal, RANK() over (ORDER BY sal DESC) 순위 from emp where job in (‘ANALYST’, ‘MANAGER’); 이와 같은 하면 동등한 순위는 같은 Rank로 표시 되고 동순위 갯수 만큼 증가된 숫자로 Rank 가 출력 되는데 순차적으로 출력하고 싶다면 DENSE_RANK를 이용한다.
      • select ename, sal, job, RANK() over (PARTITION BY job ORDER BY sal DESC) as 순위 — 직업별 월급 순위 
      • select DENSE_RANK(2975) within group(ORDER BY sal DESC) from emp; — 월급이 2975인 사원의 월급 순위
    35. NTILE – 등급을 정해놓고 그에 맞게 출력 
      • SELECT ename, job, sal,
        NTILE(4) over (order by sal desc nulls last) 등급
        from emp
        where job in (‘ANALYST’, ‘MANAGER’, ‘CLERK’)
      • nulls last 는 null은 가장 마지막에 출력됨 
    36. CUME_DIST – 순위의 비율 총 14명중에 1등인 사람 수
    37. LISTAGG – 데이터를 가로로 출력 
      • select deptno, LISTAGG(ename, ‘/’) within group(order by ename asc) from emp group by deptno;
      • Deptno에 대한 사원들을 가로로 출력한다. 
      • select deptno, LISTAGG(ename || ‘(‘ || sal || ‘)’,  ‘/’) within group(order by ename asc) from emp group by deptno;
      •  
    38. within group – LISTAGG, RANK, DENSE_RANK 등에 사용되면 집계 그룹의 정렬을 지정한다. 
    39. LAG, LEAD – LAG 는 전행, LEAD는 다음행 
      • LAG(sal, 1) over (order by sal asc)
    40. PIVOT – 가로 출력 UNPIVOT – 세로 출력 
      • select * from (select deptno, sal from emp)
        pivot (sum(sal) for deptno in (10, 20, 30));
    41. SUM Over – 집계함수 
    42. ROW_NUMBER – 번호를 매겨준다. OVER (Order by sal asc) 등의 내용이 필요 하다. 
    43. ROWNUM – 출력행 제한 Where 절에서 
    44. FETCH FIRST 5 ROWS ONLY – where 절에서 실행되지 않고 행을 제한한다 
      • select empno, ename, job, sal from emp
        order by sal DESC
        FETCH FIRST 5 ROWS ONLY;
      • FETCH FIRST 20PERCENT ROWS ONLY;
      • 2 ROWS WITH TIES – 값이 동일하면 출력을 추가로 해준다. 
      • OFFSET 2 rows – 두번째 오프셋 부터 출력
    45. EQUI JOIN – From 절에서 콤마로 두개의 테이블을 가져온다  
    46. Create Table 
      • CHAR – MAX 2000 바이트
      • VARCHAR2 – MAX 4000바이트
      • LONG – 가변길이 문자 데이터 MAX 2GB
      • CLOB – 문자데이터  MAX 4GB
      • BLOB – 바이너리 데이터 4GB
      • NUMBER – 최대 32자리 
      • DATE – 날짜  
    47. Index – create index emp_sal on emp(sal) 데이터 검색을 빠르게 하기 위해 사용한다. 
      • Index과정과장점
        • Index로 지정된 데이터를 Sorting 하고 해당 데이터가 있는 물리 주소를 인덱스로 지정된 데이터에 매핑한다. 
        • Sorting이 되어 있음으로 Index 테이블에서 검색이 매우 빠르고 실제 데이터의 물리 번지를 알고 있으니 빠르게 접근이 가능하다.
      • Index 단점
        • 데이터 추가시 Index Table은 Sorting 되어야 함으로 추가, 삭제, 수정 시 성능 저하가 올 수 있다. 
    48. Sequence – 중복 번호 없이 데이터를 증가 시키려 할때 

     

    Oracle Object

    1. VIEW – 사용자에게 필요한 정보만 보여줄 목정으로 생성된 가상 테이블
    2. Index – create index emp_sal on emp(sal) 데이터 검색을 빠르게 하기 위해 사용한다. 
      • Index과정과장점
        • Index로 지정된 데이터를 Sorting 하고 해당 데이터가 있는 물리 주소를 인덱스로 지정된 데이터에 매핑한다. 
        • Sorting이 되어 있음으로 Index 테이블에서 검색이 매우 빠르고 실제 데이터의 물리 번지를 알고 있으니 빠르게 접근이 가능하다.
      • Index 단점
        • 데이터 추가시 Index Table은 Sorting 되어야 함으로 추가, 삭제, 수정 시 성능 저하가 올 수 있다. 
    3. Sequence – 중복 번호 없이 데이터를 증가 시키려 할 때 
    4. Trigger – 데이터 변화 이벤트시 자동적으로 수행되는 프로시져
    5. Package – 프로시저 변수 등의 모음 

     

     

    명령어 모음

    systemctl status oracle // oracle 상태 조회
    systemctl start oracle
    systemctl stop oracle

    // oracle Listener 정지 및 재실행 
    lsnrctl start
    lsnrctl stop


    블로그 구독하기 !!

    You may also like...