[DB] 4장. 오라클 실습

 

설치시에 사용자는 DBA 계정을 위한 암호를 직접 입력하여야 한다. 오라클 시스템의 대표적인 DBA 계정은 두 개(system, sys)이다. 

• system : 거의 모든 DBA 역할(시스템의 booting 및 shutdown 관련 작업 제외)을 수행하는 계정
• sys : DBA 자격을 가지는 계정(SYSDBA privileage)이며, 데이터 사전(dictionary)을 구성하는 테이블 및 뷰를 소유한다. 이는 변경되지 않아야 하므로 일반적으로 사용하지 않는 계정이다

 

SQL Developer는 클라이언트 프로그램이다.  데이터베이스 서버를 접근하기 위해서는 데이터 서버에 connect 해야 한다. 여기에는 지역(local) 연결과 원격 서버 연결로 나뉜다. 지역 연결은 Id와 pw만 명시하지만, 원격 서버 연결은 사용자 계정 및 암호 이외에도 호스트 이름과 포트번호를 명기하여야 한다. 포크 번호 default 값은 1521이다.

• 지역 연결 : Connect sys/mypasswd;  
• 원격 서버 연결: Connect sys/mypasswd@dbserver.ssu.ac.kr:1522;

원격 호스트에는 Oracle Net Listener(in short, listener)가 특정 포트를 listen하고 있으며, 정당한 연결 요청을 받으면 이를 오라클 서버에 전달한다. 그 이후에는 클라이언트와 서버가 직접 통신한다.

 

사용자 생성

오라클 시스템을 사용하기 위해서 첫 번쨰로 해야 하는 것은 DBA로 시스템에 접속하여 사용자를 생성하고 또한 사용자에게 필요한 권한을 부여하는 것이다. 

Create user C##hodori identified by tooshytotell
default TABLESPACE users
temporary TABLESPACE temp;

Grant connect, resource to C##hodori;

 

사용자 C##hodori(사용자 계정은 항상 'C##' 으로 시작함)을 암호 tooshytotell로 생성한다. 

다음으로, C##hodori 에게 connect와 resource 권한을 부여한다. 여기서 connect와 resource는 권한 다수 개를 묶어 놓은 롤(role)이다.

 

 

테이블 생성과 터플 삽입

Create table firstTable
    (id		char(13) primary key,
    name	varchar(30),
    height	numeric(4,1));

 

위의 테이블은 3개의 attribute(id, name, height)를 가지며 그 중 id 속성은 주 키 역할을 한다.

 

Insert into firstTable('11', 'Hong Gildong', 165.3);
Insert into firstTable('22', 'Lee Chulsoo', 175.4);
Insert into firstTable('33', 'Kim Younghee', 167.5);

 

생성된 테이블에 위의 Insert 문을 통해 터플을 삽입할 수 있다. 문자열 속성은 값 입력 시에 단일 인용부호를 사용한다.

 

SQL 문장을 간단한 editor에 사용하여 작성하고, 이를 cut-and-paste로 SQL Developer 실행창에서 실행하는 것이 편리하다. SQL script는 추후 사용을 위하여 sql 확장자(extension)를 가지는 화일(file)에 저장하기로 한다.

 

아래는 오라클 시스템 사용시에 도움을 주는 몇 가지 SQL이다.

Create table myFirstTemp as select * from firstTable;
// Create myfirstTemp table
// exactly the same as 'firstTable'(schema and instances)

Describe user_constraint;
Describe table_name;	//to see the schema

Select * from tab;	//to see all tables available
Show user;		// to see who am I

 

외래키와 데이터 입력

Create table temp1(
    deptName	char(10) primary key,
    address	char(20));
    
Create table temp2(
    name	char(10) primary key,
    dept	char(10)
    constraint 	c1 foreign key (dept) references temp1);

 

위에서 c1는 외래키 제약의 이름이다. 만약 사용자가 제약 이름을 명시하지 않으면 시스템이 자동적으로 이름을 부여하여, 사용자는 데이터 사전을 검색하여 이를 확인할 수 있다.

 

테이블 간에 외래 키가 선언되어 있으면 데이터 로딩이 자유롭지 않을 수 있다. 그래서 나중에 테이블을 생성한 이후에 나중에 선언할 수 있다. 이처럼 위에서 외래 키를 선언하지 않을 떄, 아래와 같이 터플을 삽입할 수 있다.

Alter table temp2 add constraint c1 foreign key (dept) references temp1;
Alter table temp2 disable constraint c1;	//disable it temporarily

 

 

아래는 사용할 수도 있는 몇 가지 SQL 예시이다.

Drop table table_name; //존재하지 않으면 error 발생

Truncate table_name; //모든 터플 제거

Alter table table_name disable/enable constraint constraint_name;

Alter table department add constraint constD foreign key 
(chairman) references professor on delete cascade;

 

 

데이터 사전

: 데이터베이스에 대한 데이터(메타 데이터)로서 데이터베이스에 대한 다양한 정보를 기록 관리한다. 사용자에게 주로 테이블(또는 뷰) 형식으로 정보를 공개한다. 사용자는 이에 접근하여 시스템 상황에 대한 여러 가지 정보를 접근할 수 있다.

 

오라클 DBMS는 데이터 사전을 SGA 영역의 dictionary cache에 보관하여 사용자에게 빠른 접근을 제공한다. 데이터 사전은 DBA가 소유하고 있으며, 일반적인 사용자는 read-only 연산(select statement)만 수행 가능하며, 데이터 변경 연산(insert/delete/update statements)는 권한이 없어 실행할 수 없다.

 

Dynamic performance table은 데이터베이스 시스템의 활동 현황을 기록하는 가상 테이블(즉, 뷰)이며, 그 중 일부는 일반 사용자가 'V$'로 시작하는 동의어를 사용하여 접근할 수 있다.

 

데이터 사전을 구성하는 테이블 또는 뷰 명칭은 'user', 'all', 'dba' 중의 하나의 접두사 뒤로 개체가 명시된다.

prefix	scope
USER	User's view(what is in the user's schema)
ALL	Expanded user's view (what the user can access)
DBA	DBA's view (what is in all users' schmas)

 

 

Sequence

:Oracle SQL Object 중의 하나이며 여러 사용자 간에서 유일한 값을 생성하여 사용하고자 할 때 유용하다.  이 값은 유일하지만 연속적이지 않다.

Create sequence sequence_name
[start with n]
[increment by n]
[maxvalue n | nomaxvalue]
[minvalue n | nominvalue]
[cycle | nocycle]
[cache | nocache];

 

nomaxvalue/nominvalue/nocycle이 default 값이다. cache는 빠른 접근을 위해서 메모리에 미리 할당하고자 하는 시퀀스 값의 개수를 의미한다.

 

아래는 sequence가 활용되는 예제이다.

Create sequence sequ1 start with 10 increment by 10;
Alter sequence sequ1 maxvalue 10000;
Select sequ1.nextval, sequ1.currval from dual;
//return 10,10

Create table dep1(
    deptno number(4) primary key,
    dname varchar2(30));

 

시퀀스는 생성문과 동시에 시퀀스 값이 생성되는 것이 아니고, 'nextval' 을 호출하여야 시퀀스 값이 생성된다. 10에서 시작해서 10씩 증가하므로 nextval은 10이며, currval(현재 값)도 10이다.

 

Insert into dep1 values(sequ1.nextvalue, 'Software');
Insert into dep1 values(sequ1.nextvalue, 'Hardware');

Drop sequence sequ1;

 

{<20, 'Software>, <30, 'Hardware'>} 가 입력된다.

(다른 사용자가 이전 또는 중간에 seql.nextvalue를 호출 하지 않았다면)

 

Null and Empty String

Oracle 시스템에서는 Insert 문장은 공스트링을 널 값으로 인식한다.

Create table myTable
    (ID		number,
    name	varchar2(100))'
    
Insert into myTable(ID, name) values(100, null);
Insert into myTable(ID, name) values(200, ");

 

 

ID	name
100	null
200	"  => null

 

따라서, 아래와 같이 name=null 조건으로 검색하면 2개가 나오고, name="으로 검색하면 0이 나온다.

Select count(*) from myTable where name=";		//return 0
Select count(*) from myTable where name is null;	//return 2