출처 : http://blog.naver.com/minis24/80115393571

오라클의 인스턴스로 접속을 가능하게 해주는 다음과 같은 세가지 영역이 존재한다.

 

 

  • 사용자 프로세스
  • 오라클 리스너 (Oracle Listener)
  • 오라클 넷클라이언트(Oracle Net Client)

 

 

사용자 프로세스는 데이터 베이스로의 접속을 시도하는 일종의 소프트웨어로서,

데이터 베이스와의 통신에 오라클 넷 서비스 를 이용한다.


    

   ※ 오라클 넷 서비스

 

       다양한 네트워크 와이어-레벨 프로토콜을 통한 통신을 지원하는 컴포넌트의 집합이며, 개발자나 데이터베이스 운영자가

       여러가지   다른 하드웨어 플랫폼에서의 환경을 설정하는 복잡한 작업을 대신한다.

       예를 들어 윈도우 2000서버에서 레지스트리를 편집하거나  리눅스 서버에서 환경설정 파일을 편집하는 대신에

       오라클에서는 몇가지 간단한 설정파일(오라클의 설치된 경로에 특정영역에 있는)을 이용해서 오라클 넷을 관리한다.

 

      오라클은 오라클 넷 서비스를 설정하는 작업을 위해 Oracle Net Manager 와 Oracle Net Configuration Assistant 와 같은

      툴을 제공한다.  

 

 

 

오라클 리스너 (Oracle Listener)는 오라클 데이터베이스 서버에서 실행되는 프로세스이며, 클라이언트 어플리케이션에서 보내는 접속 요청을 청취하는 역할을 한다.

 

클라이언트는 청취자로 보내는 초기 접속요청에 서비스의 이름을 지정해야 한다.

이 서비스의 이름은 클라이언트가 접속하려는 데이터베이스의 인스턴스를 구별해주는 식별자이다.

 

 

※ 오라클 리스너 설정

 

오라클 리스너의 설정을 수동으로 변경하고자 한다면 listener.ora 파일을 찾아서 설정정보를 변경하면 된다.

Unix 의 경우 $ORACLE_HOME/network/admin 에서 찾을 수 있고,

윈도우의 경우 %ORACLE_HOME%\network\admin 에서 찾을 수 있다.

 

두 운영체제에서 모두 TNS_ADMIN 이란 환경변수를 만들고 오라클 넷 서비스 파일이 위치한 디렉토리를 가리키도록 할 수 있다.


  === >> 리눅스에서의 listener.ora 예 << ===


 TCP/IP 프로토콜로 slaphappy.us.oracle.com 의 1521 포트를 청취할 리스너 프로세스 이다.

 LISTENER 는 데이터베이스를 설치할 때 지정되는 오라클 리스너의 표준 이름이지만, 다른 포트들을 청취하는 

 여러개의 리스너를 다른 이름으로 만들 수 있다.

 LISTENER =

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=tcp)(HOST=slaphappy.us.oracle.com)(PORT=1521))

)

 SID_LIST_LISTENER는 LISTENER 에 연결하는 클라이언트에 제공할 수 있는 서비스들을 지정한다.

 SID는 시스템 식별자 이며,

 SLAPDB는 전역 데이터 베이스 이름,

 US.ORACLE.COM은 설치시에 데이터베이스에 할당된 전역 데이터베이스 도메인,

 SLAPDB는 설치시에 데이터베이스에 할당된 인스턴스 이름,

 ORACLE_HOME은 오라클 데이터베이스가 설치된 경로 이다.

   SID_LIST_LISTENER=

(SID_LIST=

(SID_DESC=

(GLOBAL_DBNAME=slapdb.us.oracle.com)

(SID_NAME=slapdb)

(ORACLE_HOME=/u01/app/oracle/oracle10g)

)

)

   lsnrctl라는 유틸을 이용하면 리스너가 실행되는 동안 리스너의 설정을 변경할 수 있는데

   STOP,START,RELOAD,STATUS,SHOW(파라미터),SET(파라미터)등과 같은 작업을 제공하는 명령어라인 어플리케이션이다.


   리스너의 세팅을 listener.ora에 기록할지를 오라클 넷 서비스에게 알려주는 역할을 한다.

   SAVE_CONFIG_ON_STOP_LISTENER = ON


   리스너의 로그파일의 위치를 지정한다.

   LOG_FILE_LISTENER=lsnr.log

   LOG_DIRECTORY_LISTENER = /u01/app/oracle/oracle 10g/network/log


   오라클의 넷 컴포넌트들에 대한 부가적인 정보를 제공한다.

   TRACE_FILE_LISTENER = lsnr

   TRACE_DIRECTORY_LISTENER = /u01/app/oracle/oracle 10g/network/log


   이 항목이 OFF이면 리스너에 어떤 일이 일어나도 추적파일에 정보를 기록하지 않는다.

   USER - 사용자 접속에 의해 발생한 오류에 대한 정보를 기록한다.

   ADMIN - 리스너의 설치와 설정에 관련된 문제만을 보여줄 수 있도록 추적정보를 기록한다.

   SUPPORT - 이척적은 오라클 서비스지원(OSS:ORACLE SERVICE SUPPORT)을 호출할 때 이용된다.

                    이때 생성된 추적정보는 OSS로 전달되어 문제 해결에 이용될 수 있다.

   TRACE_LEVEL_LISTENER = OFF 

 

 


 



서버와 마찬가지로 오라클 클라이언트 툴 역시 네트워크 상의 데이터 베이스와 통신 할 수 있도록 하는 설정이 필요하다.

클라이언트를 위한 설정파일은 tnsnames.ora 이다.

 

이 설정 파일의 내부에는 오라클의 툴이 데이터베이스에 접속하는 데 이용되는 접속 설정 리스트가 포함되며, 접속 설정은

서버의 호스트 이름과 서버와의 통신에 이용된 프로토콜 리스너와의 통신에 이용된 포트 등을 지정한다.

 

 

 === >> tnsnames.ora << ===

 

 SLAPDB.US.ORACLE.COM =

(DESCRIPTION =

(ADDRESS_LIST =

( ADDRESS = (PROTOCOL = TCP)

                    (HOST = slaphappy.us.oracle.com)

                    (PORT = 121)

)

)

)

 

 

2010/12/09 13:59 2010/12/09 13:59

원문 : http://zepinos.springnote.com/pages/1595048

오라클 시작과 종료


기본적인 오라클 시작과 종료는 SQL*Plus 로 접속한 뒤 SQL 명령창에서 아래와 같은 명령으로 진행한다.


  • 시작 : startup;
  • 종료 : shutdown immediate;

시작과 종료 모두 몇 가지 옵션을 가지고 있다. 하지만 일반적인 경우에는 종료시 immediate 옵션을 사용하는 것만 알고 있어도 된다.


오라클 시작 옵션


시작시 사용할 수 있는 옵션은 아래와 같다.


startup [force] [restrict] [pfile=파일명] [open [recover] [데이터베이스명] | mount | nomount];


force : 오라클 DB가 시작된 상태에서 다시 재시작할 때만 사용
restrict : DBA 권한을 가진 사용자만이 접속하여 이용 가능하도록 함
pfile : 오라클이 기본으로 제공하는 파라미터 파일인 init.ora 파일이 아닌 관리자가 생성한 파라미터 파일을 사용하여 오라클 서버를 시작할 때 사용
nomount : 인스턴스만 시작된 상태, 주로 오라클 DB 를 생성할 때 사용
mount : 인스턴스에 대한 Control File 을 열기
open : 인스턴스에 대한 모든 파일을 오픈하고 모든 유저가 데이터 베이스 사용 가능
recover : 오라클 복구시 사용


오라클 종료 옵션


종료시 사용할 수 있는 옵션은 아래와 같다.


shutdown [normal | transactional | immediate | aboart];


normal : 기본값,새 연결을 허용하지 않고 현재 사용자들이 접속 종료할 때까지 대기하며, 처리중인 트랜젝션이 종료할 때까지 대기한 후 종료, 다음 startup 시 인스턴스 복구가 필요하지 않음

transactional : 모든 사용자가 특정 인스턴스에서 새로운 트랜잭션을 시작 할 수 없고, 처리중인 트랜젝션이 모두 종료할 때까지 대기한 후 종료, 다음 startup 시 인스턴스 복구가 필요하지 않음

immediate : 현재 처리중인 SQL 을 모두 정지하고 모든 진행중인 트랜젝션을 롤백시킴, 모든 사용자의 DB 닫기 및 인스턴스 연결 종료

aboart : -현재 처리중인 SQL 을 모두 취소하고 모든 진행중인 트랜젝션을 강제 종료시킴(롤백 하지 않음), 모든 사용자의 연결을 종료하고 DB 닫기 및 DB 인스턴스와의 연결을 강제종료, 다음 startup 시 인스턴스 자동 복구


리스너 시작과 종료


리스너는 오라클의 접속을 관리하는 서비스이다. 리스너가 실행되어 있지 않으면 외부에서 오라클을 접속할 수 없다.

리스너의 시작과 종료는 쉘 혹은 도스 프롬프트에서 아래의 명령을 실행한다.


  • 시작 : lsnctl start
  • 종료 : lsnctl stop

엔터프라이즈 매니져 시작과 종료


오라클 10g 부터는 기존의 엔터프라이즈 매니져(Enterprise Manager, 이하 EM)가 웹서비스 형태로 변경되었다.

새로운 EM 을 이용하기 위해서는 서비스를 실행해야 한다.

EM 의 시작과 종료는 쉘 혹은 도스 프롬프트에서 아래의 명령을 실행한다.


  • 시작 : emctl start dbconsole
  • 종료 : emctl stop dbconsole

EM  을 이용하기 위해서는 1158 포트로 접속하면 된다.


iSQL*Plus 시작과 종료


오라클 10g 부터는 EM 뿐만 아니라 SQL Query Tool 인 SQL*Plus 마져 iSQL*Plus 라는 이름의 웹서비스로 제공한다.

EM 과 마찬가지로 iSQL*Plus 을 이용하기 위해서는 서비스를 실행해야 한다.

iSQL*Plus 의 시작과 종료는 쉘 혹은 도스 프롬프트에서 아래의 명령을 실행한다.


  • 시작 : isqlplusctl start
  • 종료 : isqlplusctl stop
2010/10/22 11:47 2010/10/22 11:47

원본 : http://elflord.egloos.com/3388504
PHP등에서 오라클DB 접속 시도시 아파치 프로세스가 KILL 되는 경우가 있습니다. 이경우 프로세스 자체가 KILL 되어버리면 PHP나 DB의 에러 로그가 전혀 남지 않음은 물론, Apache의 errorlog에서도 단지 Segmentation Fault가 발생했다는 내용만 남을뿐 그밖에 어떤 흔적도 남지 않기에 원인을 추적하기가 무척 곤란한 경우가 많습니다.
사실 Segmentation Fault 의 원인은 너무나 다양하기에 특정하기가 무척 힘듭니다만, 오라클 관련 커맨드를 실행중에 이런 문제가 생겼다면, 많은 경우 퍼미션에 관련되었을 가능성이 높습니다.

이경우 확인할 사항은 다음과 같습니다.

1. 아파치 실행시 오라클 관련 파라메터들이 제대로 설정되어 있는가.
2. 아파치 유저에게 오라클 관련 파일에의 접근 퍼미션이 주어져 있는가.

특히 2번의 경우, 가령 예를 들어 오라클 클라이언트에서 DB 서버에 접근시에는
$ORACLE_HOME/network/admin/tnsnames.ora을 통해 서버 리스너 정보를 확보합니다만 여기까지 접근하는 parent 디렉토리, 혹은 tnsnames.ora자체가 other그룹에 read권한이 주어져 있지 않을 경우 기본설정의 Apache는 리스너 정보에의 접근에 실패하고 Segmentation Fault 를 일으킵니다.

이러한 퍼미션 관련의 문제를 해결하기 위해서 관련 디렉토리들에는 755, 각 파일들에 644 이상의 퍼미션을 부여함으로써 other그룹의 유저도 접근이 가능하도록 수정하는 방법이 있습니다만 많은 관련 디렉토리 및 파일들을 전부 수정하는 것은 효율이 나쁘고 other그룹의 타유저에게도 접근을 허용하는 세큐리티 홀을 발생시키게 됩니다. 그러므로 일반적으로 이 문제를 해결하기 위한 솔루션으로써는 Apache의 설정에서 유저 그룹을 오라클 유저그룹에 접근 가능한 권한의 그룹으로 설정하는 방법이 있습니다. 이경우 Apache의 유저 자체를 오라클 유저로 설정하는 것은 심각한 세큐리티 홀을 발생시킬 가능성이 있으므로 권장되지 않습니다.
2010/10/22 09:51 2010/10/22 09:51

 파일을 다운 받아

# rpm -Uvh rlwrap-0.30-1.el4.i386.rpm

설치 후

오라클 접속 계정 홈디렉토리의 .bashrc 파일을 수정

alias sqlplus='rlwrap sqlplus'

수정 후

$ . .bashrc

$ sqlplus / as sysdba

sqlplus 접속하여 사용하면 잘 사용이 된다.

2010/08/06 16:47 2010/08/06 16:47

7.12

Oracle/PLSQL 2010/08/06 14:12

 begin
        execute immediate 'create table xce8(x date)';
exception
        when others then
                if sqlcode=-955 then
                        dbms_output.put_line('----------');
                        dbms_output.put_line(sqlerrm);
                        dbms_output.put_line('----------');
                end if;
end;
/


--user-defined exceptions : 논리적 error에 대한 처리
--ex) sal < 0 논리적 오류

--에러 지정 방법
declare
        min exception;
 begin
    for c in (select * from emp where deptno=30) loop
        if c.sal < 0 then
                RAISE min;
        else
            dbms_output.put_line(c.sal);
        end if;
    end loop;
exception
        when min then
                dbms_output.put_line('min error');
end;

--RAISE_APPLICATION_ERROR Procedure : error번호까지 만들어서 error발생 -> 프로그램단에서 처리하기가 용이.
-- error번호 기존 error번호와 겹칠 수 있음 그래서 특정 대역을 사용 –20000 ~ –20999
declare
        min exception;
begin
    for c in (select * from emp where deptno=30) loop
        if c.sal < 0 then
                raise min;
        else
            dbms_output.put_line(c.sal);
        end if;
    end loop;
exception
        when min then
                dbms_output.put_line('min');
                RAISE_APPLICATION_ERROR(-20111,'min error 2');
end;
/

-- 둘다 파라미터가 있음
/*
1.procedures : 리턴값이 없는 function, 재사용 가능
 subprograme : function, trigger

*/
-- functions : 리턴값이 있음

--객체생성
create procedure ce8
as
begin
        dbms_output.put_line('------------');
end;
/
--실행방법 exec ce8


--객체 확인
select object_name, object_type from user_objects where object_name='CE8'

--파마리터값 받아서 평균 출력(user defined function)
create or replace function avgsal(deptnox number)
     return number
  as
     sumx number(10);
 begin
     select avg(sal) into sumx from emp where deptno=deptnox;
  return sumx;
end;
 /

--실행 select ename from emp where sal < avgsal(10);

--EM(Enterprise Manager=(GUI관리툴))
--http://192.168.186.3:1158/em/
-- Performance(튜닝) Administration(셋팅) Maintenance(유지보수)

--구구단
create or replace procedure gugu(dan number)
as
begin
        for i in 2..9 loop
                dbms_output.put_line(dan || 'x' || i || ' = ' || dan*i);
        end loop;
end;

--실행방법 ( exec gugu(8) )

create or replace procedure tp2(su number, su2 out number) as
begin
        su2 := su+1;
end;
/

declare
        x number := 9;
begin
        tp2(1,x);
        dbms_output.put_line(x);
end;
/

--x 결과값은 2

--out의 용도 : 프로시저 내부에서 바꾼 값을 invoke해준 블록에서 적용이 되게 함
create or replace procedure tp3(su in out number) as
begin
        su := su+1;
end;

--in방식에서만 숫자 가능, out방식은 변수만 가능

--procedure 생성(파라미터를 in으로 받아서 out으로 보내는 것으로 이해)
CREATE OR REPLACE PROCEDURE getdl(pdeptno number, pdname out dept.dname%type, ploc out dept.loc%type) as
begin
        select dname, loc into pdname, ploc from dept where deptno = pdeptno;
end;

--생성된 프로시저 실행하기
declare
        dname dept.dname%type;
        loc dept.loc%type;
begin
        getdl(10, dname,loc);
        dbms_output.put_line(dname || ',' || loc);
end;

2010/08/06 14:12 2010/08/06 14:12

7.7 ~7.9

Oracle/PLSQL 2010/08/06 14:11
-- 1.출력 프로시저
exec dbms_output.put_line(3*4);
2. 출력안될때 실행해야 하는 명령
set serveroutput ON

--기본 문
DECLARE
        event VARCHAR2(15); -- 변수 선언
BEGIN
 --변수 정의
        event := q'!Father's day!';
 --출력
        DBMS_OUTPUT.PUT_LINE('3rd Sunday in June is :'||event);
        event := q'[Mother's day]';
        DBMS_OUTPUT.PUT_LINE('2nd Sunday in May is :'||event);
END;

--대입
:=
x NUMBER DEFAULT 2;
--비교
=

--%type : 테이블의 타입정의를 가지고 옴
declare
        v_id txx.id%type := &id;
        v_data txx.data%type := '&data';
begin
        insert into txx values(v_id,v_data);
end;

var x number
EXEC :x := 30
SELECT * FROM dept WHERE deptno=:x;

print x

--암시적 커서 예
begin
        for c in(select * from emp) loop
         dbms_output.put_line(c.ename);
        end loop;
end;

--명시적 커서 예
declare
  cursor c is select ename from emp;
  e emp.ename%type;
begin
  open c;
  loop
        fetch c into e;
        exit when c%notfound;
         dbms_output.put_line(e);
  end loop;
  dbms_output.put_line(c%rowcount);
end;

--IF문
declare
        v_dept varchar(10);
begin
        for c in(select * from emp) loop
          if c.deptno = 10 then
                v_dept := 'acc';
          elsif c.deptno = 20 then
                v_dept := 'res';
          elsif c.deptno = 30 then
                v_dept := 'sal';
          else
                v_dept :='n/a';
          end if;
                dbms_output.put_line(c.ename || ',' || v_dept);
        end loop;
end;

--case 문
declare
        v_dept varchar(10);
begin
        for c in(select * from emp) loop
        dbms_output.put_line(c.ename || ',' ||
        case c.deptno
                when 10 then 'acc'
                when 20 then 'res'
                when 30 then 'sal'
          end
        );
        end loop;
end;

--for문
begin
   for dan in 2..9 loop
        exit when dan >= 2;
        for i in 1..9 loop
                dbms_output.put_line(dan ||'X'||i||'='||dan*i);
        end loop;
   end loop;
end;

-- scalar types( 값 <> vector)
-- records 변수인데 값이 여러개 C언어의 struct
-- collections record를 묶어 놓은 c언어에서의 struct array

--rocord
DECLARE
 --일부 컬럼도 가능
 type dept_type is record(DEPTNO NUMBER(2), DNAME VARCHAR2(14), LOC VARCHAR2(14));
 dept_row dept_type;
 --dept_row dept%rowtype; -- 전체 컬럼만 가능
begin
 select * into dept_row from dept where deptno=10;
 insert into dept values(dept_row.deptno+1, dept_row.dname, dept_row.loc);
end;

--레코드를 이용한 출력 방법
-- 변수 r은 암시적으로  emp%rowtype이 됨.
begin
        for r in (select * from emp) loop
                if r.deptno=10 and r.sal+nvl(r.comm,0) > 2000 then
                        dbms_output.put_line(r.ename || ',' || r.sal);
                end if;
        end loop;
end;


declare
 type t is record(deptno number(2),dname varchar2(14),loc varchar2(13));
 type tt is table of t index by pls_integer;
 r t;
 rs tt;
 i number(2) :=0;
begin
 select * into r from dept where deptno=10;
 dbms_output.put_line(r.dname);
 for r in(select * from dept) loop
  rs(i) :=r;
  -- dbms_output.put_line(r.dname||i);
  i:=i+1;
 end loop;
 for j in 0..i-1 loop
  dbms_output.put_line(rs(j).dname);
 end loop;
end;
/


--Explicit cursors (명시적 커서)

--명시적
begin
 FOR i IN (SELECT * FROM emp) loop
  dbms_output.put_line(i.ename);
        end loop;
end;
/

--명시적 커서
declare
        cursor c is select ename from emp; --선언
        ename emp.ename%type;
begin
        open c; --open
        loop
                fetch c into ename; -- fetch
                exit when c%notfound; --속성이용 나오기
                dbms_output.put_line(ename);
        end loop;
 CLOSE c; --close
end;
/

-- 타입 지정과 명시적 커서 사용
--
declare
        type t is record(ENAME emp.ename%type, SAL emp.sal%type);
        deptnox emp.deptno%type :=10;
        cursor c is select ENAME, SAL from emp
                     where deptno=deptnox;
        r t;
begin
        deptnox:=20;
        open c;
        loop
                fetch c into r;
                exit when c%notfound;
                dbms_output.put_line(r.ename || ',' || r.sal);
        end loop;
        close c;
end;

--- 커서 카운트
declare
        deptnox emp.deptno%type;
        rcount number(2);
        cursor c is select * from emp
                     where deptno=deptnox;
begin
        deptnox:=20;
        for r in c loop
                dbms_output.put_line(r.ename||','||r.sal);
                rcount := c%rowcount;
        end loop;
        dbms_output.put_line(rcount);
end;

----------------중요 코드(함수형식으로 콜) 파라미터
declare
           cursor c(deptnox number) is select ename,sal from emp
                        where deptno=deptnox;
           ename emp.ename%type;
        rcount number(2);
begin
      for r in c(10) loop
              dbms_output.put_line(r.ename||','||r.sal);
                rcount := c%rowcount;
      end loop;
        dbms_output.put_line(rcount);
 end;
/


--select update 동시

declare
 r emp%rowtype;
 cursor e is select * from emp where ename='BLAKE' for update of sal nowait;
begin
 open e;

 fetch e into r;
 dbms_output.put_line(r.ename || ',' || r.sal);
 update emp set sal=sal+1 where current of e;

 close e;
end;


--error handling
begin
        dbms_output.put_line(1/(1-1));
exception
        when ZERO_DIVIDE then
                 dbms_output.put_line('su /0 <--err');
end;


declare
        ename emp.ename%type;
        cursor c is select ename from emp where deptno=40;
begin
        open c;
        loop
                fetch c into ename;
                exit when c%notfound;
                dbms_output.put_line(ename);
        end loop;
end;

--2개의 에러 처리
declare
        r emp%rowtype;
begin
        select * into r from emp where deptno=30;
        dbms_output.put_line(r.ename);
exception
        when NO_DATA_FOUND then
                dbms_output.put_line(' no data');
        when TOO_MANY_ROWS then
                dbms_output.put_line('many row');
end;

2010/08/06 14:11 2010/08/06 14:11

7.2 / DCL

Oracle/SQL 2010/08/06 14:09
with read only 읽기 전용
select view_name from user_views;--뷰 확인
sequences
일련번호 생성기, PK 기본값
next.val : transaction의 영향 안받음., 다음 숫자
currentval.val : transcation의 영향 받음., 세션에서의 마지막 리턴 숫자
cache 메모리에서 미리 읽어 드림.
cycle : maxvlaue에 도착하면 minvalue로 바꿔라

create sequence ss
increment by 10
start with 5
maxvalue 20
minvalue 3
cycle
nocache

select sequence_name from user_sequences;
시퀀스 확인

index
select index_name from user_indexes
인덱스 확인
index은 primary key로 설정하면 인덱스가 자동으로 생성
primary key로 생성된 인덱스 삭제 방법
alter table ttt drop constraint sys_C005440;
수동으로 생성된 인덱스 삭제 방법
drop index index_name;

물리적인 rowid 확인
select comm, rowid from emp where comm is not null order by comm

built-in view
 performance view - from 메모리(V$~~시작 : v$fixed_table)
 data dictionary view - from 파일 [dictionary view] : (DBA_~~ : 전체, ALL_~~ : 접근가능한, USER_~~ : 내가소유한)

 scott 소유한 테이블
 select count(*) from user_tables;
 scott 접근 가능한 테이블
 select count(*) from all_tables;
 db전체의 테이블 수(sysdba)
 select count(*) from dba_tables;
 전체 view(sysdba)
 select count(*) from dba_views;
 현재 접속자(session) 리스트(sysdba)
 select username from v$session where username is not null

세션관련 테이블
select name from v$fixed_table where name like '%SESSION%';

--built-in view검색하기(save fv)
select view_name from dba_views where view_name like upper('%&key%')

select CONSTRAINT_NAME, CONSTRAINT_TYPE from USER_CONSTRAINTS where TABLE_NAME='EMP'

07.02 오후 dcl

grant select on emp to hr;
revoke select on emp from hr;
rol 권한의 집합, 권한 받을 때는 유저처럼 권한 줄때는 system권한처럼...

ddl -- alter문
 add - defualt
 modify - defualt
 drop

flashback 기능 -> 과거의 변화를 추적 혹은 복원
external 테이블 -> text파일을 select할 수 있음.

flashback

external tables -> text파일을 select할수 있음

insert all
insert first

dss 의사결정지원 시스템
dw-dss 분석
1.자료수집 - external table
2.분류작업 -
3.분석
4.레포팅
rollup : 부분합
cube : cross tabulation질의 (차원질의,타블릿질의)

5장
date
timestamp : 소주점 이하 초까지
timestamp with timezone : 위에꺼 플러스 시간대 정보
timestamp with local timezone : 시간대 정보가 자동으로 변경
interval day to second (3 12:13:5) : 3일 12시간 13분 5초
interval year to month 3-4 : 3년 4개월

6장
서브쿼리

7장
트리구조 구현
self조인응용.

8장
Regular Expression(정규표현식)

기존    10g정규표현식 응용(4개 함수로)
컬럼명 like 키문자열  regexp_like(컬럼명,표현식)
replace    regexp_replace
substr    regxp_substr
instr    regxp_instr

2010/08/06 14:09 2010/08/06 14:09

statment-level rollback
ex)ddl 실패시 해당 문장만 rollback되고 자동으로 commit됨
read consistency commit하기 전까지는 다른 유저가 못 봄

table : 데이터를 저장하는 객체
view : select문 저장 객체(table처럼 사용됨)
sequence : 일련번호 생성기
 create sequence t2s;
 t2s.nextval;
 select t2s.currval from dual;

index : 검색속도 향상위해 미리 만든 객체
create index

synonym : 동의어

질의 실행 시간 측정 (set timing on)
질의의 실행 계획 확인 set autot on exp
truncate 공간까지 비워짐(delete은 undo 테이블에 복사하기때문에 느리고 공간도 안비워짐)

select emp.ename from scott.emp; 테이블 명과 유저명 생략(당연한 거니까 ㅋㅋㅋ)
다른 유저의 테이블을 볼러면 유저명을 써서 사용한다.

data types
varchar2(size) 가변길이-> 글자영릐 넓이값까지 저장, 공간은 덜차지
char(size) 고정길이: 글자1이 들어가도 넓이값만큼 공간차지
number(p,s) p: 전체넓이, s : 그중에서도 소주점으로 사용할 자리수.
datetime data types
timestamp : 소주점이하까지
interval year to month
interval day to second
timestamp with local time zone : 자동으로 변환해서 저장.

not null 널을 허용 안함
unique 중복을 허용 안함, 에러위치 모름
primary key not null + unique
foreign key 다른(특정) primary key값만 허용.
check 특정 조건만 허용

talbe level로 하는 것을 권장
inline방식 constraint 지정
create table tc2(xx number unique, x2 number); column level
outline방식 constraint 지정
create table tc2a(xx number, yy number, constraint tc2a_uk unique(xx)) table level

create table tc4( id number, yy number, deptno number, constraint tc4_fk foreign key(deptno) references dept(deptno));
constraint tc5_ck check(xx>0);

not null talbe level constraint을 지원하지 않음

crating table as : constraint는 복사가 안됨

alter table
alter table d2 add(xxx number);
alter table d2 add constraint d2_pk primary key(deptno);
alter table d2 modify(xxx number(4) default 0);
alter table d2 drop column xxx;
alter table d2 drop(loc);

테이블이 이미 있다고 하면 찾아보기 (시퀀스나 인덱스에 있을수 있음)
select object_name, object_type from user_objects where object_name like '%&key%'

simple view는 dml이 모두 가능

2010/08/06 14:08 2010/08/06 14:08

6.26 / Type of Join

Oracle/SQL 2010/08/06 14:08
  1.  natural join
     이름 같은 컬럼 1개. -join 1개
     join되는 컬러이름 지정부 생략
  2. inner join(기본 조인)
     using 사용가능(table alias 생략 가능)
     on절에서는 table alias 생략 불가
  3.  outer join
     using 사용가능
  4.  cross join

on clause
 : 모든 경우 가능, table alias 필수

self-joins
 : inner joind의 응용, where 대신 and 가능

non-equijoins
 : on절에서 = 대신 between문 사용. ( 등급 표시를 구현이 목적)

92버전
(+)족이 덤.(+)안한 쪽이 다 나옴.

inner versus outer joins (join되는 컬럼의 값이 널이라도)

left outer join 왼쪽이 다 나오게
right outer join 오른쪽이 다 나오게
full outer join 양쪽 다 나오게

cartesian products 모든 경우의 수 다나오게

cross join 카르티잔 곱 (on, using 쓰면 에러)
natual join (on, using 쓰면 에러)

subqueries
row 1개 리턴(값) : single row subquery
row n개 리턴(값) : multi row subquery
뷰형태(2차원) : view style의 subquery

 () singlerow일때는 상관없음 multirow일때 중의
multirow (in,any,all)


select rownum, rowid, deptno, dname from dept;

    ROWNUM ROWID                  DEPTNO DNAME
---------- ------------------ ---------- --------------
         1 AAAMfKAAEAAAAAQAAA         10 ACCOUNTING
         2 AAAMfKAAEAAAAAQAAB         20 RESEARCH
         3 AAAMfKAAEAAAAAQAAC         30 SALES
         4 AAAMfKAAEAAAAAQAAD         40 OPERATIONS

rownum은 order by이 하기전의 출력 순서

multirow ex)
select ename, sal, rownum
  2  from (select ename, sal from emp order by sal desc)
  3  where rownum <= 5;

ENAME             SAL     ROWNUM
---------- ---------- ----------
KING             5000          1
SCOTT            3000          2
FORD             3000          3
JONES            2975          4
BLAKE            2850          5

any 하나의 조건이라도 만족하면 됨
all 모든 조건을 만족해야 됨

union (합집합)

SQL> select ename from emp
  2  union
  3   select dname from dept;

ENAME
--------------
ACCOUNTING
ADAMS
ALLEN
BLAKE
CLARK
FORD
JAMES
JONES
KING
MARTIN
MILLER

ENAME
--------------
OPERATIONS
RESEARCH
SALES
SCOTT
SMITH
TURNER
WARD

18 rows selected.


union all(정렬을 안해서 빠르다)
교집합
차집합 minus 순서조심(결과 다름)
union 합집합 (중복제거, 집합이기때문에 내부적 정렬)

col "1" noprint 컬럼 리스트 안나옴


읽기 일관성 : commit하기 전까지 다른 유저는 보지 못함.

2010/08/06 14:08 2010/08/06 14:08
  1. 단위

     10^3 k
    10^6 m
    10^9 g
    10^12 t
    10^15 peta
    10^18 exa

  2. date function
  3. months_between 월과 월차이
    add_months 몇개월후
    next_day 돌아오는 해당일
    last_day 해당월일의 마지막날
    round 반올림
    trunc 내림
  4.  implicit(암시적,자동)
    select last_day(sysdate-30) from dual;
  5. explicit(명시적, 수동)
    select to_char(1)||to_char(1), to_number('1')+to_number('1') from dual;
  6. 문자->날짜
    select sysdate-to_date('1986.09.30','yyyy.mm.dd') from dual
  7. 날짜-문자
    select to_char(sysdate,'yyyy')||'년 ' || to_char(sysdate,'MM')||'월' from dual

    hh:hour,hh24,mi:분,ss : 초,ff6: 소수점 이하 초(6자리

    yy(95을 2095년으로 인식) rr(1950~2049년으로 자동 mapping)

  8. nesting functions(중첩 가능한 이유는 리터값이 1개라서)
  9. nvl 널이면 특정값으로 변환
  10. nvl2 널이면 두번째값 리턴 아니면 세번째값 리턴
  11.  SQL> select nvl2(null,1,2), nvl2(123,1,2) from dual;

    NVL2(NULL,1,2) NVL2(123,1,2)
    -------------- -------------
                 2             1

  12. nullif 같으면 널 값리턴, 다르면 첫번째 값

    select nullif(1,1), nullif(1,2) from dual;
    NULLIF(1,1) NULLIF(1,2)
    ----------- -----------
                          1

  13.  coalesce : nul중첩 (null이 아닌 첫번째 파라미터 리턴)
    select coalesce(null,null,null,1) from dual

    COALESCE(NULL,NULL,NULL,1)
    --------------------------
                             1

  14.  성능 때문에
    case expression(where절에서 많이 사용)
    select ename,
        case deptno
         when 10 then 'ACCOUNTING'
        when 20 then 'RESEARCH'
        when 30 then 'SALES'
        when 40 then 'OPERATIONS'
        else 'OPERATIONS'
       end dname
        from emp;
  15.  decode function(select와 from사이에 사용)

    select ename,
    decode( deptno
    , 10 , 'ACCOUNTING'
    , 20 , 'RESEARCH'
    , 30 , 'SALES'
    , 40 , 'OPERATIONS'
    , 'OPERATIONS'
    ) dname
    from emp


     버퍼 사용시 중간 강제 종료시 해결 방법
    ed을 수정하는 중 강제 종료 되면 swp 파일이 남아 있다
    그럼 sqlplus로 접속후 ed 편집하면 에러 메시지 발생
    그럴경우 rm .*.swp 실행

  16.  메뉴얼 보는 법

     otn.oracle.com -> documentation
    키워드 이용 : 단어는 아는데 뜻을 모르는 경우
    ex)는 oe/oe or hr/hr 계정에서
    분류체게 이용. 뜻은 아는데 단어를 모르는 경우
    books->

  17.  락 풀기
    alter user oe identified by oe account unlock;
  18.  4장

    aggregated (group multi-row에 대해 연산하는...)
    select job, count(*) from emp group by job having count(*)>1
    job별로 1명이상인 job의 명수를 구하기.
    stddev 편준편찬(값자체를 이용할때), variance 분산(단순비교형)
    count(컬럼)널이 아닌 값의 갯수
    count(*) row의 갯수
    select avg(nvl(comm,0)) from emp;

    group by 묶은 컬럼명만 사용 가능
    select deptno, avg(nvl(sal,0)), sum(sal) from emp group by deptno;

  19.  5장
    multiple tables (join -92, 99)
    sql 92를 통한 조인
2010/08/06 14:08 2010/08/06 14:08