오라클
: 회사 이름인 동시에 DBMS의 이름이기도 하다.
*grid computing : 하나의 커다란 업무를 여러 개의 작은 단위로 나눈 후 네트워크로 연결되어 있는 여러 컴퓨터에 나누어 처리하는 기술. 처리 속도가 월등히 빠르다.
오라클 특징
- 네트워크 환경 지원
- 디양한 운영체제 지원
- 대용량 데이터 처리 지원 : 1PB (=1024TB)크기의 데이터 처리 가능
- 여러 사용자의 동시 접속 지원
- 신뢰성 높은 보안 기능 제공
- 오류 및 장애에 대한 대비책 지원
오라클 설치
오라클 설치
학교 수업에서 Oracle Database 11g Express Edition을 사용하기 때문에 이 버전으로 깔았다.
(https://www.oracle.com/database/technologies/xe-prior-releases.html)
-
압축파일에서 setup.exe 파일을 찾아 실행하기.
-
next를 눌러 기본적인 것들을 세팅 (폴더설정, 라이센스 동의등)
-
시스템 관리자 암호 설정이 나오는데 암호는... (책에 적어놓음)
+) 관리 비밀번호 가이드라인
- 길이는 8자 이상 30자 이하의 길이로
- 특수문자는 -, $, # 만 가능
- 숫자로 시작하지 말 것
- 사용자 계정과 동일하게 하지 않을 것
- 오라클에서 사용되는 예약어를 사용하지 말 것
- 사전에 등재되어 있는 단어를 사용하지 말 것 -
그 후로 계속 진행하면 설치 완료
설치가 완료된 모습이다. 프로그램이 몇 개 부족하게 설치되었다면 다시 설치하기를 권장 (나도 그랬음)
-
Windows Service 등록확인
: OracleServiceXE, OracleXETNSListener가 실행상태여야 함.
-
scott 예제 계정 생성
: "Run SQL Command Line" 선택 후 창이 뜨면 다음과 같이 입력한다.
SQL> connect system/dongduk
SQL> @ ..\RDBMS\ADMIN\scott.sql
SQL> connect scott/TIGER (접속 가능 여부 테스트)
SQL> exit
//우리 학교 예시다...- 예제 계정 및 학사 데이터베이스 생성
1) 첨부된 파일들을 적당한 폴더(예: C:\temp)에 다운로드 함
2) Oracle SQL Developer를 실행하고 PC에 설치된 Oracle XE 데이터베이스에 시스템 관리자(system)로 접속함 (강의노트-3 16페이지 참조; 사용자 이름에 system, 비밀번호에는 Oracle을 설치할 때 입력했던 비밀번호 입력)
?? : Name을 그냥 system이라고 해도 되는지 모르겠다. (수업 놓쳐서 영원히 모르게 생김)
3) 메뉴에서 파일>>열기를 선택하여 scott.sql 파일을 화면에 연 후, 스크립트 실행 버튼 (또는 F5 키)를 이용하여 실행
--> scott 계정이 생성됨 (화면에 출력된 로그 확인)
??: system에서 scott.sql을 열는 것까진 성공했는데 스크립트를 실행하니 scott 계정이 생성된건지 모르겠다. 로그의 마지막 줄에는 <<커밋 완료.
CONNECT 스크립트 명령으로 생성된 접속이 해제되었습니다.>> 이런 말만 뜨고... 미래의 내가 알았으면 좋겠다.
4) 다시 새 접속을 생성하여 scott 계정으로 접속 (password: TIGER)
5) 위 3번과 동일한 방법으로 example.sql 파일을 열어 실행
--> scott 계정 내에 학사 데이터베이스 테이블 생성됨 (왼쪽 상단 목록에서 생성된 테이블들을 선택하여 확인)
기타 참고사항
-
오라클 인스턴스(instance)
: 오라클이 데이터베이스를 관리하는 데 필요한 각종 정보들과 구체적인 관리 작업을 수행하는 여러 프로세스들을 통틀어서 일컫는 말. -
scott, system, sys 계정
: system과 sys는 관리자(DBA) 계정이고 scott은 오라클을 설치할 때 자동으로 생성되는 사용자 계정이다.
system과 sys의 비밀번호는 앞서 설정한 관리자 비밀번호이고,
scott의 비밀번호는 tiget로 설정되어 있다. (기본) -
관련 폴더
-
Oracle Server : C:\oreaclexe\app\oracle\product\11.2.0\server
-
Default database :
(Database System ID(SID)가 폴더 이름이 된다)
-
– CONTROL.DBF, UNDOTBS1.DBF:시스템 관리와 관련된 데이터 저장
– SYSTEM.DBF, SYSAUX.DBF: 데이터사전이 저장됨
– TEMP.DBF: SQL문을 처리하는 과정에 생성되는 임시테이블과 인덱스들이 저장
– USERS.DBF: 사용자가 생성한 테이블들이 저장됨
SQL*Plus를 이용한 오라클 테스트
-
SQL(Strutured Query Language)
: 테이블(표)과 테이블에 저장되는 데이터를 다루기 위한 표준 언어.
생성/수정/삭제/저장/검색 등이 가능하다. -
SQL*Plus
: Oracle DBMS에서 사용자가 SQL질의를 작성 및 실행할 수 있도록 인터페이스르 제공하는 클라이언트 도구.
; Oracle Server에 접속하여 대화식으로 이용. (local뿐만 아니라 remote에 있는 서버에도 접근 가능(뭔 소리야))
SQL*Plus의 기능
; cmd와 비슷한 모양을 하고 있는 프로그램이다.
- SQL*Plus 자체 명령어 실행
- DB 접속 및 종료
- 테이블 생성, 편집, 저장 및 검색
- SQL문 또는 PL/SQL프로그램 코드를 작성 및 실행
- 질의결과를 정렬하고 지정된 형식으로 출력 (보고서 작성)
- DB간의 데이터 복사 등의 DB관리 작업 수행
SQL*Plus 실행 및 로그인
- [Run SQL Command Line] 실행 or [cmd]에서 sqlplus/nolog 입력
- connect(또는 conn) (username)/(password) 입력
ex. conn scott/TIGER
+)scott 예제 계정 생성
: 11gR2에는 scott계정이 기본적으로 생성되어있으나 Express Edition에는 없으므로 생성 필요.
SQL script파일을 이용하여 생성 가능하다. (scott.sql 파일을 미리 Temp파일에 넣어놓음)
SQL> connect system
Enter password: (관리자 암호 입력)
SQL> Connected.
SQL> @C:\Temp\scott.sql
SQL> connect scott/TIGER
SQL> Connected.
SQL> @C:\temp\example.sql
~~//실행 예의 list하고 아무것도 없다는 건 실수니까 그냥 스루. ~~
SQL문 실행
SQL*Plus는 SQL질의문을 작성하기 위한 행 편집기를 제공한다.
- 하나의 질의를 여러 행에 걸쳐 입력 가능
- 입력 내용은 내부 버퍼에 저장됨
- 행을 지정하여 행 단위 편집 가능
- SQL문 입력 후 바로 실행 하려면 마지막 행의 끝에 ; 입력후 Enter
- 버퍼에 저장된 SQL문을 다시 실행하려면 / or Run 명령어 실행
SQL*Plus 명령어 실행
- 종류
- 편집 명령어
- 파일 명령어
- 실행 명령어
- 환경 명령어
- 형식 명령어
+) 명령어는 대소문자를 구분하지 않는다.
편집 명령어
: SQL 버퍼에 저장된 현재의 SQL문을 화면에 출력하고 편집할 수 있는 명령어들을 포함한다.
- LIST(L)
: 버퍼에 저장된 내용(바로 직전에 실행시켰던 명령)을 화면에 출력
: SQL 버퍼에 있는 모든 행 표시
-
EDIT(ED)
: 버퍼에 저장된 내용을 메모장 편집기를 통해 편집 가능
ㅡ 버퍼의 내용을 임시 파일에 저장 (afiedt.buf)
ㅡ SQL문의 종결은 / (; X)
ㅡ 메모장 종료시 임시 파일의 내용이 SQL*Plus의 버퍼에 저장됨 -
APPEND text(A text)
: 현재 행 끝에 텍스트 추가 -
CHANGE/old/new(C/old/new)
: 현재 행의 이전 텍스트를 새로운 텍스트로 변경 -
CLEAR BUFFER (CL BUFF)
: SQL 버퍼 내의 모든 행 삭제 -
DEL
: 현재 행 삭제 -
INPUT
: 한 행 이상 추가
이 외에도 더 많다.
파일 명령어
: SQL 버퍼에 저장된 SQL문을 파일로 저장하거나, 파일에 저장된 SQL문을 불러오거나, SQL*Plus의 작업 화면을 파일에 기록하는 등의 작업을 수행하기 위한 명령어
- SAVE (파일이름)
: 버퍼에 있는 내용을 파일에 저장
; 확장자는 자동으로 .sql
//그냥 파일이름만 쓰면 BIN파일에 저장되어 찾기 힘드니 절대경로를 쓰는 것 추천
-
GET (파일이름)
: 파일의 내용을 버퍼로 불러옴
-
SPOOL (파일이름)
: 이후의 작업 내용을 파일에 저장
-
SPOOL OFF
: spool을 해제 (기록 종료)
-
HOST
: 운영체제로 나가거나 운영체제 명령어를 직접 실행
//more(또는 type) 을 붙이면 해당 파일의 내용을 출력한다. -
EXIT
: HOST명령 후에 복귀하거나 SQL*Plus를 종료 -
CONNECT (사용자이름)/(암호)
: 다른 사용자로 접속
실행 명령어
: SQL*Plus에서 작성된 SQL문을 작성할 때 사용하는 명령어
- START (파일이름)
- @ (파일이름)
: 파일에 저장된 SQL 스크립트를 실행
- RUN(r)
: 버퍼에 있는 SQL문을 실행
; 직전에 실행했던 명령문을 다시 입력하지 않고 반복해서 실행시키고 싶을 때
( / 과의 차이점은 버퍼에 저장된 SQL문을 먼저 출력한 후 실행결과를 출력한다는 점이다. (/는 실행결과만 출력))
환경 명령어
: SQL*Plus의 환경을 설정하기 위한 명령어
-
SET LINESIZE(가로)
: 질의 결과를 출력하는 페이지의 폭을 문자 수로 설정한다. -
SET PAGESIZE(세로)
: 페이지 당 행 수를 설정한다.
형식 명령어
: SQL문의 결과를 보고서 형식으로 출력하기 위해 사용하는 명령어
-
컬럼 헤딩 변경
: column명령어에 heading절을 사용하여 컬럼의 헤딩을 정의
ex. column empno heading '사원번호' -
컬럼 형식 설정
: column명령어에 format절을 사용하여 데이터의 출력 형식을 정의
ex. column dname format a20
-
컬럼 헤딩 아래 밑줄 표시 변경
: set명령어를 사용하여 underline을 설정
ex. SET UNDERLINE = -
컬럼 형식 복사
: 하나 이상이 컬럼에 같은 형식을 지정하고자 하는 경우에는 LIKE절을 사용
ex. COLUMN (컬럼이름) LIKE (컬럼이름)
; 헤딩을 복사하지 않도록 하려면 따로 설정해야함
ex. COLUMN (컬럼이름) LIKE (컬럼이름) HEADING (컬럼헤딩)
기타 명령어
- describe(desc) <테이블이름>
: 해당하는 테이블의 구조 정보를 출력
; 테이블에 어떤 필드들이 정의되어 있는지 알아보고 싶을 때.
-
SELECT * FROM tab;
: 사용자가 참조할 수 있는 모든 테이블에 대한 이름 및 유형 정보 출력 -
SELECT table_name FROM user_tables;
: 사용자가 생성한 모든 테이블 이름을 출력
//내가 뭘 만들었는지 기억이 안날 때..
PL/SQL 실행
*PL/SQL(Procedural Language extension to SQL)
: C, JAVA같은 고급 프로그래밍 언어의 특성을 SQL과 결합하여 더 많은 작업을 처리할 수 있도록 고안된 오라클에서만 사용되는 특별한 프로그래밍 언어.
=> SQL의 데이터 검색, 삽입 등 기능 + 고급 언어의 조건문, 반복문등의 기능
(간략하게 함)
오라클 데이터 저장 구조
오라클에서는 데이터 관리를 위해 데이터의 구조를 나누어 관리한다.
논리적 구조는 데이터의 효과적인 사용 및 관리를 위한 방법을 제공하기 위해,
물리적 구조는 데이터가 실제 디스크에 저장 및 관리되는 방법을 제공하기 위함.
논리적 구조
데이터의 효과적인 관리와 사용을 위해 논리적 구성요소를 사용한다.
*논리적 구성요소 : 데이터 블록, 익스텐트, 세그먼트, 테이블 스페이스
이를 통해 실제 데이터가 저장되는 디스크의 공간을 효율적으로 사용할 수 있도록 한다.
데이터 블록(data block)
: 데이터의 최소 저장 단위로 오라클의 모든 데이터들을 데이터블록에 저장된다.
; 데이터가 늘어나면 데이터 블록의 배수로 저장 공간을 확보해야 한다.
; 데이터 블록 표준 크기는 db_block_size에 저장되어 있다.
; data block 표준 크기는 8192bytes(8KB)로 모든 데이터는 8KB 단위로 저장된다.
- 표준 크기 확인 방법
오라클 관리자인 system으로 접속하여
- SQL*Plus의 show명령 사용
show parameter db_block_size- 설정 정보를 담고 있는 테이블인 v$parameter의 내용 확인
SQL> desc v$parameter
SQL> select name, value from v$parameter where name='db_block_size';
익스텐트(extent)
: 연속적인 여러 개의 데이터 블록이 모여서 하나의 익스텐트를 구성한다.
; 데이터 블록 다음 단계.
; 익스텐트가 모여 세그먼트를 구성한다.
세그먼트(segment)
: 하나의 세그먼트에는 같은 종류의 데이터가 저장되는데
테이블이 저장되면 데이터 세그먼트,
인덱스 정보가 저장되면 인덱스 세그먼트.
+) Temporary segment : SQL질의 처리 과정에서 임시 데이터를 저장하기 위한 공간
Rollback segment : 트랜잭션에 의해 변경되는 데이터의 변경 전 값을 저장하기 위한 공간.
; 하나의 세그먼트는 테이블 스페이스에 저장된다.
; 하나의 세그먼트에 할당된 공간이 모두 사용되면 새로운 익스텐트를 만들어 그 세그먼트에 할당해준다.
따라서 하나의 세그먼트를 구성하는 익스텐트들을 디스크상에 연속적으로 저장되지 않을 수도 있다.
*출처 : 데이터베이스의 이해(이한미디어)
테이블스페이스(tablespace)
: table, index, view등 스키마 객체들을 저장한다.
; 하나의 데이터베이스는 여러 개의 테이블스페이스들로 구성된다.
; 하나의 테이블스페이스는 하나 이상의 세그먼트가 포함된다.
; 하나의 테이블스페이스에 포함되는 데이터들은 하나 이상의 데이터파일로 저장된다. (물리적구조)
- 용도에 따른 테이블스페이스의 구분
- System Tablespace (관리자)
- SYSTEM : Dictionary 데이터 유지, 'System' 사용자의 default tablespace, 롤백 세그먼트 포함 가능
- SYSAUX : SYSTEM과 더불어 추가적인 시스템 데이터 유지
- Non-system Tablespace
- USERS : 일반 사용자가 생성한 데이터를 저장, 일반 사용자들의 default tablespace
- TEMP : SQL질의 처리 과정에서 임시 저장이 필요한 정보를 유지
- UNDO : 변경되는 데이터의 변경 전 값을 저장하기 위한 용도.
- System Tablespace (관리자)
물리적 구조
구성 : 데이터파일, 컨트롤파일, redo 로그 파일, 매개변수 파일, alert/trace 로그 파일, 백업 파일
데이터 파일
: 오라클에서 관리하는 데이터가 실제로 저장되는 디스크상의 파일.
*출처 : 데이터베이스의 이해(이한미디어)
// 디스크는 주로 원기둥으로 표현된다.
; 테이블은 논리적으로 테이블스페이스에 저장되는 것이기 때문에 실제 디스크에 있는 데이터 파일로 저장될 때는 하나의 테이블이 두 개의 데이터 파일로 나뉘어 저장될 수도 있다.
SYSAUX.DBF, SYSTEM.DBF : 오라클 시스템 관리를 위해 만들어진 파일.
TEMP.DBF : 임시 데이터들을 저장하기 위한 파일.
USER.DBF : 사용자 계정을 위해 만들어진 파일.
UNDOTBS1.DBF : 데이터에 문제가 발생했을 때 복구를 위한 정보 저장.
EXAMPLE.DBF : 예제 테이블들을 저장하고 있는 파일.
컨트롤 파일
: DB의 물리적 구조, 이름, 생성 시간, redo 로그 파일들의 위치정보, 현재 로그 번호, 체크포인트 정보 등을 저장.
redo 로그 파일
: 데이터베이스 변경 내역을 저장.
; 데이터 변경 과정에서 장애가 발생하여 변경 내용이 데이터베이스에 반영되지 못했을 경우 온라인 redo 로그 파일을 이용하여 복구 할 수 있다.
설정 파일(parameter file)
: 데이터베이스와 데이터베이스 서버에 관련된 설정 정보들이 저장된다.
alert/trace 로그 파일
: 오라클 서버 내부에서 오류가 발생할 경우 그 오류에 대한 정보나 메시지를 저장한다.
테이블스페이스 관리
생성
: 오라클 관리자만이 생성할 수 있으며 생성할 때 데이터 파일을 지정할 수 있다.
생성명령어는
create tablespace <TS 이름> datafile '<DF 경로명>' size <DF 크기>
ex.
1. 관리자 계정인 system으로 접속하여
2. 이름이 my_space인 테이블 스페이스 생성
SQL> conn system/(암호)
SQL> create tablespace my_space datafile 'c:\my_data01.dbf' size 20M;변경
-
이미 만들어져 있는 테이블스페이스에 새로운 데이터 파일 추가
alter tablespace <TS 이름> add datafile '<DF 경로명>' size <DF 크기>
-
기존 테이블스페이스 삭제
drop tablespace <삭제할 TS이름>
테이블스페이스를 삭제하더라도 연결되어 있는 데이터파일은 삭제되지 않고, 새로 만드는 테이블스페이스에서 다시 사용할 수 있다.
조회
: 생성된 테이블 스페이스에 대한 정보는 dba_data_files 테이블에 저장된다. 이는 관리자 계정으로만 접속해야 볼 수 있다.
select tablespace_name, file_name from dba_data files;테이블스페이스 사용
생성된 테이블스페이스는 오라클에서 새로운 사용자 계정을 만들거나 새로운 테이블을 만들 때 사용될 수 있다.
사용자 계정 생성
create user <사용자 계정>
identified by <비밀번호>
default tablespace <사용할 TS이름>
quota <용량> on <사용할 TS이름>
계정을 생성했다고 해서 바로 사용가능한 것은 아니다.
관리자가 이 계정에 권한을 부여해야 한다.
grant connect, resource to <사용자 계정>
//새로운 사용자 계정을 생성하여 기본 테이블스페이스를 지정.
//용량은 200KB면 200K, 3MB면 3M, 무제한이면 unlimited 지정
테이블 생성
테이블을 생성할 때 테이블스페이스를 지정하지 않으면 사용자 계정을 생성할 때 지정된 테이블스페이스를 기본으로 사용하게 설정된다.
//그러니까 여기서 굳이 tablespace를 my_space로 지정해주지 않아도 my_space로 설정된다는 것이다.
