2024.12.11 11일차 수업

# 객체 권한 부여 (sys, 객체 소유자가 grant 해줄 수 있다.)

grant select on hr.employees to insa;
grant select on hr.departments to insa;

출력

select * from dba_tab_privs where grantee = 'INSA';

출력

 

※ SYS와 SYSTEM 계정의 차이

   - 큰 차이는 없지만, SYS는 Oracle 시스템을 유지, 관리, 생성하기 위한 모든 권한을 갖는 계정이고, SYSTEM은 생성된 DB를 운영, 관리하기 위한 관리자 계정 정도로 이해하면 된다.

 

# insa SQL

select * from user_tab_privs;
select * from hr.employees;
select * from hr.departments;

▶ 위에 2개는 실행이 되지만 맨 밑에는 오류가 뜬다. 그 이유는 insa 유저는 departments라는 테이블 권한을 받지 않았기 때문이다.

출력

 

※ 항상 테이블 앞에 소유자를 적는 습관을 가지는 게 좋다.

 

# 객체 권한 회수

revoke select on hr.employees from insa;  --  employees 테이블 회수
revoke select on hr.departments from insa; -- departments 테이블 회수

select * from dba_tab_privs where grantee = 'INSA';

출력

 

■ 유저 수정 (DBA)

alter user insa
identified by oracle
default tablespace users
temporary tablespace temp
quota 10m on users;

▶ default tablespace users : 이걸 적지 않으면 기본값으로 system으로 들어가기 때문에 일반 유저는 꼭 이걸 작성해줘야 된다.

▶ 유저 생성 및 수정에는 맨 밑에 account lock(계정 잠금)이 기본값으로 들어가 있다.

 

# 비밀번호 수정

alter user insa
identified by oracle;

 

# quota 수정

alter user insa
quota 20m on users;

 

# 계정 잠금

alter user insa
account lock;

select * from dba_users;

출력

▶ account_status에 insa 테이블이 lock이 걸린 걸 볼 수 있다.

 

# 어느 시간대에 lock이 걸렸는지 확인할 때

select username,
	   account_status,
       to_char(lock_date, 'yyyy-mm-dd hh24:mi:ss')
from dba_users;

출력

 

# 계정 잠금 해지

alter user insa
account unlock;

select * from dba_users;

출력

 

■ 테이블 생성

create table insa.emp(
				id number(4),
                name varchar2(30),
                day date default sysdate);

▶ 테이블엔 2가지 조건이 있다

▶ 시스템 권한(create table)과 quota값이 둘 다 있어야 실행이 된다.

 

 # 시스템 권한 주는 방법

grant create table to insa;

select * from dba_sys_privs where grantee = 'INSA';

출력

 

■ 테이블 삭제하는 법

drop table insa.emp purge;

출력

 

# 테이블 생성 시 주의할 점

  - 테이블 생성 시 tablespace절을 생략하게 되면 default tablespace에 생성된다.

create table insa.emp(
 				id number(4),
				name varchar2(30),
				day date default sysdate);

 

# 위에 거보다 밑에 거처럼 쓰는 습관을 가지자!!

 create table insa.emp(
 				id number(4),
				name varchar2(30),
				day date default sysdate)
tablespace users;

 

# 유저 생성 시 필수 항목

alter user insa
identified by oracle;

 

# SYS SESSION (DBA)

create user scott
identified by oracle;

출력

▶ defalut_tablespace 항목에 일반 유저는 system으로 되어있으면 절대 안 된다.

 

# scott한테 권한 부여

grant 
	create session,
	create table,
	unlimited tablespace
    to scott;

▶  unlimited tablespace : 이거는 특정 극 일부분한테만 권한을 주기때문에 따로 관리해야 한다.(엑셀에 대부분 함)

select * from dba_sys_privs where grantee = 'SCOTT';

출력

 

■ DML (Data Manipulation Language)

  - insert

  - update

  - delete

  - merge

 

■ TCL (Transaction Control Language) - 면접 때 많이 물어봄!

   - commit : DML 작업을 영구히 저장

   - rollback : DML 작업을 영구히 취소

   - savepoint : rollback 기능을 도와주는 표시자

 

# Transaction : 논리적으로 DML을 하나로 묶어서 처리하는 작업 단위

 

■ insert문

  - 테이블에 새로운 행을 입력하는 SQL문

INSERT INTO 소유자.테이블(컬럼, 컬럼, 컬럼,...)
VALUES(데이터, 데이터, 데이터,...);

insert into insa.emp(id, name, day)
values(1, '홍길동', to_date('2024-12-11', 'yyyy-mm-dd'));

▶ insert문이 시작되는 순간 transaction 시작된다.

출력

 

# 하지만 sqlplus에서 실행하면 오류가 뜬다.

  - commit : insert 작업에 대해서 영구히 저장, transaction 종료된다.

 

# Transaction 시작

   - 여러 데이터 추가

insert into insa.emp(id, name, day)
values(2, '박찬호', to_date('2020-10-01','yyyy-mm-dd'));

insert into insa.emp(id, name, day)
values(3, '윤건', to_date('2021-01-01','yyyy-mm-dd'));

출력

 - 여기서 rollback을 주게 되면

rollback;

출력

▶ transaction 시작 시점까지 한꺼번에 영구히 취소 된다.

 

# insert 수행 시 default 값을 입력하는 방법 - 편한 거 사용하면 된다.

 

 1) 

insert into insa.emp(id, name)
values(2, '나얼');

출력

▶ 테이블 생성할 때 day date default sysdate라고 적었기 때문에 day를 안 적어도 값이 나온다.

 

  2) default 키워드를 사용해서 기본값을 입력하는 방법

insert into insa.emp(id, name, day)
values(3, '이문세', default);

출력

 

  3) name 컬럼에 default 값이 선언 되어 있지 않은데 default 키워드를 사용 시

insert into insa.emp(id, name, day)
values(4, default, default);

출력

▶ null로 나온다.

 

  4) day 컬럼에 default 값이 선언되어 있더라도 null값을 입력하는 방법

insert into insa.emp(id, name, day)
values(5, '제임스', null);

출력

 

■ update

  - 특정한 필드값을 수정하는 SQL문

  - where(조건절)을 안 쓰면 다 수정된다.

update 소유자.테이블
set 컬럼 = 새로운값, 컬럼 = 새로운값,...
where 조건;

 

ex)

update insa.emp
set name = 'itwill'; -- transaction 시작

출력

▶ 조건절을 안 썼기 때문에 다 바뀌어서 출력이 되는 걸 볼 수 있다.

▶ 이전으로 돌리고 싶으면 rollback을 하면 된다.

rollback;

출력

 

ex)

update insa.emp
set name = 'itwill'
where id = 4;

update insa.emp
set name = '비타민', day = to_date('2023-12-01', 'yyyy-mm-dd')
where id = 5;

출력

 

 # day 컬럼의 값을 default 값으로 수정

update insa.emp
set day = default
where id = 5;

출력

▶ id = 5인 비타민에 day 값이 default로 sysdate로 넣어놨기 때문에 금일 날짜가 출력이 된다.

 

  # day 컬럼의 값을 null 값으로 수정

update insa.emp
set day = null
where id = 5;

출력

 

■ delete문

  - 행을 삭제하는 SQL문

delete from 소유자.테이블; -- 테이블 전체 행을 삭제
delete from 소유자.테이블 where 조건; -- 테이블 조건절에 해당하는 행만 삭제

 

ex)

delete from insa.emp;

출력

# 하지만 sqlplus에서 확인해 보면

출력

▶ 삭제가 안 되어있다. 그 이유는 sql developer에서 커밋을 안 했기 때문이다.

 

ex)

delete from insa.emp where id = 5;

commit;

출력1

출력2

---

■ savepoint

  - DML 작업 시에 rollback을 도와주는 표시자

  - savepoint 표시자;

  - rollback to 표시자; : 표시자 밑에 있는 transaction에 대해서 취소 

 

insert into insa.emp(id, name, day)
values(6, '이영산', sysdate);

savepoint a;

update insa.emp
set name = 'oracle'
where id = 4;

savepoint b;

delete from insa.emp where id = 3;

rollback to b;

출력

▶ rollback to b : b 표시자 밑에 있는 transaction만 영구히 취소된다. 표시자 위는 다 살아있고, commit을 하게 되면 b 표시자 위에만 영구히 저장된다.

출력

 

■ 자동 commit 발생할 때 (매우매우 주의할 것!!)

  - DDL (create, alter, drop, rename, truncate, comment)

  - DCL (grant, revoke)

  - sqlplus에서 exit를 수행해서 종료하면 자동 commit

  - sqlplus에서 connect (conn) 접속할 때 자동 commit

 

ex)

delete from insa.emp where id = 3;

select * from insa.emp;

create table insa.test(id number);

▶ 이렇게 transaction 실행되고 있을 때 DDL(create)를 쓰게 되면 commit을 쓰지는 않았지만 create 안에 내부적으로 자동 commit을 가지고 있기 때문에 실행하는 순간 delete from insa.emp where id = 3  영구히 삭제되고, rollback해도 소용이 없으니 매우 매우 주의해야 한다.

 

■ 자동 rollback 발생할 때

  - DML 작업을 수행하고 있는 컴퓨터가 비정상적인 종료

  - sqlplue를 비정상적인(창닫기 'x'로 닫기 누를 때) 종료

  - client-sever 환경에서 네트워크 장애가 발생하는 경우