오라클 구조

# DB

1. 데이터베이스 특성

        실시간처리

        계속적변화

        동시공유

        내용에 의한 참조

2. DBMS( DataBase Management System )

        DB는 데이터 저장소

        DBMS는 DB를 관리하기 위한 시스템의 집합

# 관계형 데이터 모델

1. DB개발과정

            업무정보화요구 --> 개념 데이터모델링(분석, 전략수립) --> DB설계 --> DB생성(개발)

# 오라클소개

1. 오라클 버전 

    i ( internet : 인터넷환경을지원 )

    g ( grid computing : 여러컴터를 연결)

    c ( cloud computing : 응용프로그램+개발플랫폼+인프라서비스를 통합된 it솔루션집합에

            접근할수 있는 서비스 )

Oracle 9i    -    서버를 정지 및 재부팅과정없이 메모리풀의크기를 조절, RAC( 장애대비 서버2대이상설치 )

Oracle 10g    -    기업내 중소형 서버를 연결시켜 하나의 커다란 서버로 활용할 수 있음

Oracle 11g    -    10g 업그레이드버전 ( 자동 sql튜닝기능, 장애처리 등등 )

Oracle 12c    -    클라우드....

10 11 grid   /  12 13 cloud 등 버전은 단지 오라클을 더욱 많이 팔기위한 마케팅적인 용어다라는 말도많음

2. 자료형

    char, varchar2, nchar, nvarchar2, date, number, long, raw, long raw, blob, clob, nclob, bfile, rowid

3. SQL / PL-SQL

    SQL은 비절차적

    PL-SQL은 절차적(procedural)으로 접근( 루프나 함수등을 이용가능 )

# 오라클설치

1. jre가 먼저 설치되어야함

<먼저 대부분이 오래된 오라클 8 ~9 일때의 정보이다. 그냥 개념만 이해하고 상세한건 

해당 버전에 맞는 DB구조를 봐야함>

# DB구조

1. 인스턴스 ( 메모리 + 프로세서 )

    사용자가 DB 계속 요청시 DISC IO무리가 많이감 => 메모리를 이용함!

    ( DB요청에의해 한번 DISC읽으면 메모리에 올려두고,  똑같은 내용에 대해서는 메모리에서 처리 )

1. DB 구조 

2. DB 아키텍쳐 ( 프로세스+공유메모리영역+디스크(물리적파일)영역 )

        프로세스영역( 백그라운드프로세스(DBWR,CKPT,LGWR,PMON,SMON) , 사용자프로세스, 서버프로세스 )

        메모리영역( SHARED POOL, DATA BUFFER CACHE, LOG BUFFER, LARGE POOL )

        파일영역( 컨트롤파일, 파라미터파일, 데이터파일 , 리두로그파일 )

3. 프로세스는 크게 서버프로세스와 백그라운드프로세서로 나뉨

        @서버프로세스 : 사용자프로세스(클라이언트)와 통신하면서 사용자의 각종 명령어를 처리하는 프로세스

                            SQL을 실행하면서 블록을 읽고, 읽은 데이터를 정렬해서 클라이언트가 요청한 결과를

                            만들어 네트워크에 전송하는 일련의 작업을 처리

                            또한, 백그라운드 프로세스가 할일을 백그라운드 프로세스에게 위임시키기 위한

                            시스템 콜 요청을 하는 프로세스이다.

                

                            서버프로세스는 클라이언트와 연결하는 방식이 두가지 있다.

                                -    전용서버(Dedicated Server)방식 : 요청올때마다 서버프로세스 새로연결/제거

                                                                                부담이 많이 가므로 커넥팅 풀링 기법 사용해야함

                                -    공유서버(Shared Server)방식 : 하나의 서버프로세스를 여러사용자 세션이공유

                                                                                    ( 미리 여러개 프로세스 띄워놓고 이를 공유 )  

      @백그라운드 프로세스

        DB의 현재상태를 모니터링하거나, 사용자가 실행한 SQL문의 작업을 처리해주는 프로세스

        내부적으로 DB시스템이 잘 돌아가게 해주는 역활을 함

        DB시작시 꼭 필요한 DBWR, LGWR, SMON, PMON, CKPT가 있음

        만약 5개 백그라운드프로세스중 하나라도 존재하지 않는다면 DB는 더이상 사용불가

        그래서 이 5개 백그라운드프로세스는 필수 백그라운드 프로세스 라고함

        ( 그말은 백그라운드 프로세스가 5개이상이라는 소리?  )

    -    DBWR프로세스( DataBase Writer )

            사용자가 실행한 SQL문에 의해 데이터의 변경내역을 테이블에 저장하는 작업을 수행

            DB시작시 자동생성, DB종료시 자동 종료 

    -    LGWR프로세스( Log Writer )

            사용자가 실행한 SQL문을 커밋시 커밋했던 모든 작업내용을 리두 로그파일에 백업하는 프로세스

            ( 시스템 복구하기위해 백업 )

    -    PMON프로세스( Process Monitor )

            사용자들의 DB접속시 한번의 접속요구마다 사용자 프로세스가 하나식 생성됨

            이런 사용자 프로세스의 상태를 감시함( 강제종료시 롤백 등 )

    -    SMON프로세스( System Monitor )

            백그라운드 프로세스와 DB메모리영역의 상태를 감시함

            DB다운시 다시 시작될때 자동복구작업 수행

    -    CKPT프로세스( CheckPoint )

            LGWR프로세스에 의해 활동하며 커밋문을 실행할때마다 오라클 서버가 관리하는

            시스템 변경번호 및 DB상태정보를 컨트롤 파일과 데이터파일에 저장하는 작업함

    -    기타 백그라운드 프로세스

4. 메모리 영역 ( 시스템공유메모리영역SGA + 프로세스 전용 메모리 영역PGA )

        @SGA ( System Global Area )

        시스템메모리영역으로부터 DB가 전용으로 사용하기위해 할당받게되는 영역 

        사용자가 실행한 SQL문에 의해 조회,변경되는 테이블 데이터를 임시로 저장하는 메모리영역

        오라클DB에서 가장중요한영역이며, DB의 크기를 결정하는 척도이며, 속도향상을 위한 튜닝의대상

        또한 DB에 접속하는 모든 사용자들이 공유하는 영역이며 

        기본적으로 오라클DB서버는 하나의 SGA영역으로 구성되어 있음

        -    공유풀영역( SHARED POOL AREA )

            DB접속시  1 사용자  프로세스 / 1 서버 프로세스 할당되는데

            서버프로세스는 SQL문법확인, 사용된 테이블의 DB존재여부, SQL의 실행가능한 실행코드와 실행계획

            을 수립하고, 그내용을 저장하는곳이 공유풀 영역임

            라이브러리캐쉬 영역(실행코드,실행계획저장) 과 

            딕셔너리 캐쉬영역(사용자가 읽게될 자료사전테이블과 뷰정보가 저장 )으로 나뉨 

                라이브러리 캐시 : 사용자가 수행한 SQL문과 실행계획, 저장프로시저를 저장해두는 캐시

                딕셔너리캐쉬 : 오브젝트(테이블,인덱스..) + tablespace, 데이터파일, 세그먼ㅌ, 익스텐트, 사용자

                                        , 제약에 관한 메타 데이터를 저장하는곳(테이블 메타데이터정보같은것들.. )

    -    데이터 버퍼캐쉬영역( DATA BUFFER CACHE AREA )

            서버프로세스는 사용자가 실행한 SQL문에 정의된 테이블이 존재하는 디스크의 데이터 파일로부터

            테이블을 읽어서 여기 데이터 버퍼캐쉬에 저장한다.( 모든 읽기는 DB버퍼캐시를 통해 이루어지며

                DB버퍼캐시에 없을경우 disc에서 읽어 여기에 저장한후 여기에 있는걸 읽는다 )

            

    -    로그버퍼 영역( LOG BUFFER AREA )

            사용자가 실행한 DML문을 커밋하면 화면에 '커밋성공' 메시지 보여줌( 또는 롤백시 롤백성공 등 )

            이때 커밋했던 모든 작업 내용을 메모리의 로그버퍼영역에 저장함 

            모든 작업내용을 리두로그버퍼에 저장해서 시스템 에러시 복구를 위해 사용됨 

            

    -    라지 풀 영역

            사용자가 DB접속하는 방법중에 공유서버 프로세스라는 환경이 있는데 이러한 환경을 구성할때

            라지 풀 영역을 사용

    @PGA( Process Global Area) 

            서버프로세스가 가진 자신만의 메모리 영역

            데이터정렬, 세션과 커서에 대한 상태 정보를 저장하는 용도

5. 파일 영역

        DB가 생성되면서 만들어졌던 자료사전 테이블과 뷰 그리고 사용자가 직접 생성한 테이블,인덱스

        ,뷰, 시퀀스, 시노늄등이 저장되어 있음

        또한 DB의 모든 상태정보가 저장되어있음( 파일영역을 DB라고 부르기도 함 )

    -    컨트롤 파일

            DB시작할때 항상 참조되는 파일

            이유는 DB에서 사용할 모든 파일들의 절대경로와 파일크기 등의 정보를 저장 하고 있기 때문

            오라클 DB가 시작되기 위해서는 최소한 하나의 컨트롤 파일이 존재해야함

            ( 오라클 DB설치시 원본1, 복사본2 가 생성되는데 매우 중요한파일이라 복구용으로 사용하기위해)

    -    파라미터 파일

            SGA영역이 시스템으로부터 얼마만큼의 메모리크기를 할당받게되는지

            컨트롤파일의 경로, DB의 환경설정등 관련된 모든 정보를 포함

            DB튜닝시 사용되는 매우 중요한 파일중 하나

            DB설치시 INIT<DB명>.ORA 이나 INIT.ORA 이라는 파일 이름으로 존재함

            

# TABLESPACE

 

DB파일구조 -- 논리영역 -- tablespace -- segment -- extent -- block

           -- 물리영역 -- 여러 데이터파일들로 구성

                         각각 데이터파일을 여러개의 블록으로 구성

1. Storage구조 

          DB > TABLESPACE > SEGEMENT > EXTENT > DB BLOCK

tablespace(땅) - segment(건물) - extent(건물의 어느한층) - block(건물어느한층의 사무실)

1) 블록 : 오라클의 최소 입출력단위

          데이터가 바로 여기 블록에 저장됨    

          먼저 블록의 크기는  파라미터파일에서 DB_BLOCK_SIZE에서 설정

         하나의 블록안에 여러레코드(데이터)들이 들어있다.

         DB에서는 I/O를 블록단위로한다. 따라서 원하는 레코드가 하나여도 블록째로 읽어 들이기때문에

        성능을 좌우하는것은 읽어올 블록의 개수를 최소로 하는것이다.

header            -  85~100byte정도 / 데이터위치저장

table directory

row directory

free space

row data

    header          1. 데이터위치정보를 저장( 블록주소,세그먼트타입(테이블인지뷰인지 등 ))

                        2.  크기는 85~100 byte 정도 

                            ( 파라미터파일에서 DB_BLOCK_SIZE에서 설정한 크기가 2k라면 

                            2k중 85~100바이트는 블록헤더가 잡고있음 )

    테이블 디렉토리    1. 클러스터로 합쳐진 테이블에 대한정보

    행 디렉토리     1. 블록내에서 실제 행에 대한 정보를 저장 ( 2byte )

    프리스페이스    1. 

    행 데이터    1. 실제로 물리적인 테이블/index 의 데이터를 저장하는 공간

2) 익스텐트

        여러개의 연속된 블록의 집합( 블록의 묶음 )

        I/O의 단위는 블록이지만 테이블 스페이스로부터 공간을 할당하는 단위는 익스텐트다.

3) 세그먼트

        여러개의 익스텐트를 가지고 있는 객체이다.

        테이블, 인덱스 Undo처럼 저장공간이 필요로 하는 객체를 말한다.

4) 테이블 스페이스

        세그먼트를 담는 콘테이너로 여러 데이터 파일로 구성됨

        보통 MySQL같은 DBMS는 tablespace안에다가 테이블 만듬( 오라클은 다른듯... )

        

        system tablespace    :    데이터딕셔너리정보, 저장프로시져의정의, 패키지, 트리거

                                        시스템롤백세그먼트, 사용자데이터 포함

        non-system tablespace : system tablespace이외의것들

                                            임시세그먼트, 인덱스세그먼트, 유저데이터세그먼트,

                                            롤백세그먼트등 

<기타>

클러스터 : 서로다른테이블의 공통컬럼을 공유하거나 자주 함께 사용할 경우 

                물리적으로는 하나로 저장되는것을 말함( 나중에 조인시 성능 향상 )

인덱스 세그먼트 : 큰 테이블이나 클러스터에 대한 검색을 빠르게 수행하기위해 

                                만들어지는 인덱스데이터를 저장하는곳..

                               ( 즉, 테이블이나 클러스터에 대한 인덱스 생성시 각각의 인덱스에 

                                 대한 세그먼트가 생성됨 ) 

인덱스 : 전체테이블 검색을 대신하므로 disc i/o를 줄여줌

            인덱스된 컬럼만을 참조하는 쿼리는 인덱스만으로 참조가능

            인덱스는 최적화를 위해 테이블,클러스터로 부터 분리된 스토리지에저장

                            ( 물리적으로 다른 테이블스페이스에 저장 )

임시 세그먼트 : 정렬작업 또는 조인연산에 사용되는 데이터를 임시로 보관하기위해 사용

                        임시데이터를 다루는곳이므로 리두로그에 기록안됨

                        만약 임시세그먼트가 존재하지않으면 SYSTEM  테이블스페이스내에 임시세그먼트생성

                            하게되므로 I/O마찰을 일으켜 시스템성능 저하

                        CREATE TABLESPACE문에서 TEMPORARY 옵션의 지정으로 만듬

롤백 세그먼트 : 트랜잭션에 의해 변경되기 전의 데이터를 저장하는 순환적 세그먼트

                        DB생성시 시스템 롤백 세그먼트라는 특정 롤백 세그먼트를 tablespace에 생성하며 

                        적어도 하나이상의 롤백 세그먼트가 필요하다.

                        

# TABLESPACE 관리

1. TABLESPACE 생성

CREATE TABLESPACE tablespace DATAFILE filespec DEFAULT STORAGE storage_clause;

CREATE TABLESPACE test_data   DATAFILE  'f:/oracle/oradate/orcl/test_data01.dbf' SIZE 10M 

                    DEFAULT STORAGE (   ...   );

                                                                                             <   C:\oraclexe\oradata\XE 에있는 .dbf파일이 테이블스페이스파일들이다!     >

스캇계정은 dba권한이없는 일반사용자임... SYSTEM/MANAGER는 DBA권한을 가지고있는 계정임....이거로접속후 테이블스페이스생성!

    

2. TABLESPACE 변경, 삭제     

3. Temporary Tablespace  :  테이블스페이스생성시 temporary키워드를 사용해서 생성

4. Read Only Tablespace   :

5. Data File Size 조정하는법

6. Tablespace관련 Dictionary 조회 ( select * from 밑에꺼 ) 

          DBA_TABLESPACES     :      모든 테이블스페이스의 Storage정보및 상태정보를 갖고있다

          DBA_DATA_FILES        :      테이블스페이스를 구성하고있는 각 DATA FILE의 정보를 갖고있다

          DBA_FREE_SPACE       :      테이블스페이스의 공간사용에 관한 정보를 갖고있다.

          DBA_FREE_SPACE_ COALESCED   :   테이블스페이스의 수집가능한 EXTENT에 대한 

                                                                통계정보를 갖고있다. 

# 테이블 관리 및 활용

1. 칼럼제거

               ALTER TABLE emp_family DROP COLUMN age;

2. 칼럼사용중지( 실제물리적으로 그칼럼의 데이터를 삭제하지는않고 사용금지만함! )

               트렌젝션이나 과부하가 많을경우 칼럼제거하면 오버헤드?걸리기때문에 이렇게 

                칼럼사용중지를 해놓고 나중에 한가할때 일괄삭제!

ALTER TABLE emp_family SET UNUSED COLUMN age;      :    일단 사용중지후

ALTER TABLE emp_family DROP UNUSED COLUMNS;        :    사용중지한것들 일괄삭제

3. Row Chaining      :      i/o퍼포먼스를떨어트림                          

                (한블럭에 해당row의크기가커지면 커진만큼은 다른블럭에 추가됨.. => 호출시 두개블럭호출됨! )

4. Row Migration     :      로우체이닝의단점을 없애기위한방법    

                     (그냥 커진 해당row전체를 다른블럭으로 옴김.. => 기존row있던곳이 빈곳이됨.)

5. PCTFREE            :      해당블럭의 업데이트나 추가등에의해 예비용 공간을 애초에 잡아두는법! 

                                   PCTFREE 10       <=     한블럭의 10%는 예비공간으로! 

6. PCTUSED            :      PCTUSED 40       <=     PCTFREE 10을 제외하고 row들이 삭제될시 바로 

                                                                        거기추가한다고 거기추가되는게아니고

                                                                        사용하고있는공간이 40%로 떨어지면 그 삭제된곳이

                                                                         드디어 사용가능 

CREATE TABLE emp_family (

          empno           NUMBER(4) NOT NULL,

          fam_name      VARCHAR2(10) NOT NULL,

          relation           VARCHAR2(10) NOT NULL,

          brithday           DATE NOT NULL,

          age                NUMBER,

          CONSTRAINT emp_family_pk PRIMARY KEY ( empno, fam_name )  --테이블레벨의 제약조건

)

--위까지만해주면 디폴트값은 SYSTEM테이블스페이스--

TABLESPACE test_data          

PCTFREE 10            --10

PCTUSED 50            --40

STORAGE (            --시스템테이블스페이스의 디폴트값으로 먹음! ( 이러면 데이터 관리가 잘안됨!  )

      INITIAL    1M

      NEXT       1M

      MINEXTENTS   1

      MAXEXTENTS   UNLIMITED

      PCTINCREASE   0

);

# 사용자 관리

    사용자계정은 물리적인 구조가 아닌 논리적인 구조

    DB처음생성시 SYS, SYSTEM, SCOTT 사용자 계정을 제공

        SYS : DB내의 모든권한을 갖고있는 가장 최상위 레벨( 디폴트비번.. )

        SYSTEM : SYS사용자로부터 DBA권한을 받은 사용자( 디폴트비번 manager )

        scott : 테스트를 위해 제공되는 일반 사용자

    

    사용자별 공간할당,리소스제한, 패스워드관리, 세션관리등을 할 수 있다.

    table, view, trigger등 DB객체는 사용자별(사용자스키마) 로 생성되어지며

    해당 객체의 소유주는 해당사용자이다. 

    사용자 생성후 권한 부여하지않으면 db작업을 아무것도 할 수 없다.

    사용자스키마내에 object가 존재하면 사용자를 cascade절을 사용하여 drop하여야 한다.

    all, dba, user_users딕셔너리는 사용자 테이블 스페이스공간할당을 모니터링하기위한 정보제공

    v$session 딕셔너리에서 사용자 섹션에 대한 정보를 얻을 수 있음   

 

# 사용자 모니터링

          SELECT * FROM user_users;      

          SELECT * FROM dba_users;