INSERT문에 대한 UNDO
-
INSERT DML 문이 실행될 때, 일반적으로는 기존 데이터를
변경하지 않기 때문에 UNDO 세그먼트에는 "새 데이터가 추가되기 전의 상태"를 저장할 필요가 없다. -
그러나 Oracle Database는 트랜잭션의 무결성을 보장하기 위해 일부 메타데이터와 관리 정보를 저장한다.
INSERT시 UNDO에 저장되는 정보
- 블록의 메타데이터
- 새로운 행이 추가될 블록의 메타데이터를 업데이트한다. 이 메타데이터에는 블록의 공간 정보, 행의 위치 정보 등이 포함된다.
- 이러한 메타데이터 변경 사항을 원래 상태로 복구하기 위해 UNDO 세그먼트에 저장된다.
- 트랜잭션 정보
- 트랜잭션 테이블의 상태를 업데이트한다. 트랜잭션이 시작되고 커밋되기 전까지의 상태 정보를 UNDO 세그먼트에 저장된다.
- 이는 트랜잭션의 롤백이나 재시작 시 필요한 정보를 제공하기 위함이다.
UNDO_RETENTION
- 일관성 있는 읽기를 위해 제공되는 언두 데이터의 보유시간을 결정한다.
- undo_retetion 매개변수는 초단위로 설정한다.
- long query 문장이 수행되는 세션을 위해서 트랜잭션을 commit을 완료하더라도 undo_retention까지는 이전값을 가지고 있다.
- 만약에 long query 문장에서 이전값을 못찾으면 ORA-01555 snapshot too old 오류가 발생한다.
- undo retention 값을 보장하려면 undo 공간이 여유가 있어야 한다.
show parameter undo_retention
-
undo tablespace의 기본값은 no guarantee이다.
SELECT tablespace_name, contents, retention FROM dba_tablespaces;
-
undo retention을 undo segment 공간이 부족해도 보장하기 위해서는 guarantee 설정을 해줘야 한다.
ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;
-
하지만 guarantee를 보장하기 위해 다른 세션이 해당 undo segment를 공유 받지 못하는 경우 해당 세션의 DML 작업이 막힐 수 있다.
UNDO 발생량 확인
- v$undostat 뷰 확인
- 최대 4일 정도로 확인 가능
- 데일리 업무로 처리해야한다.
- 10분 단위로 확인
SELECT to_char(begin_time,'yyyy-mm-dd hh24:mi:ss'),
to_char(end_time,'yyyy-mm-dd hh24:mi:ss'),
undoblks -- undo시 사용 블록 수
FROM v$undostat;
- 일 단위로 확인
SELECT to_char(begin_time,'yyyy-mm-dd'),sum(undoblks) as block_count, sum(undoblks)*8 as size_BYTE
FROM v$undostat
GROUP BY to_char(begin_time,'yyyy-mm-dd');
Flashback query
- 특정 시간에 query를 수행할 수 있다.
- retention 시간안에 수행 가능
- SELECT문의 AS OF 절을 사용하여 데이터를 확인할 시간 기록을 지정할 수 있다.
- 현재 scn 확인
SELECT current_scn, scn_to_timestamp(current_scn)
FROM v$database;
- update 수행
UPDATE hr.emp_20
SET salary = 1000
WHERE employee_id = 201;
commit;- 이전 시간대 query 수행
SELECT employee_id, salary
FROM hr.emp_20 AS OF TIMESTAMP TO_TIMESTAMP('2024-08-08 12:12:50','yyyy-mm-dd hh24:mi:ss');
3-1 interval 사용 query 수행
select employee_id,salary from hr.emp_30 AS OF TIMESTAMP systimestamp - interval '5' minute;3-2 scn 사용 (단 이전 scn 번호를 알아야 사용 가능)
select employee_id,salary from hr.emp_30 AS OF SCN 3250000;Flashback version query
- version절을 사용하여 과거의 값과 현재의 값을 같이 볼 수 있다.
- SCN 이용
SELECT versions_xid, employee_id,salary
FROM hr.emp_30 VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;- versions_xid는 트랜잭션 id
- MINVALUE, MAXVALUE는 SCN의 최소 최대값
- TIMESTAMP 이용
SELECT versions_xid, employee_id,salary
FROM hr.emp_30 VERSIONS BETWEEN TIMESTAMP SYSTIMESTAMP - INTERVAL '15' MINUTE AND SYSTIMESTAMP;- 변경 DML 타입과 변경시간까지 같이 확인
SELECT versions_xid,versions_operation, versions_starttime, employee_id,salary
FROM hr.emp_30 VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;
USER 관리
- 9i 버전까지는 유저 생성시 default tablespace를 지정하지 않으면 system tablespace가 지정이 되었다.
이러한 문제점을 10g 버전부터는 데이터베이스 생성시 데이터베이스 레벨에서 default tablespace가 생성되어 있다.
데이터베이스 레벨에서 기본값 설정 확인
SELECT * FROM database_properties;
default tablespace, temporary tablespace 변경
- 변경할 테이블스페이스, temporary 테이블스페이스 생성
CREATE TABLESPACE user_tbs
DATAFILE '/u01/app/oracle/oradata/ORA19C/user_tbs.dbf' SIZE 10M
AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
SEGMENT SPACE MANAGEMENT AUTO;
CREATE TEMPORARY TABLESPACE user_temp
TEMPFILE '/u01/app/oracle/oradata/ORA19C/uesr_temp.dbf' SIZE 10M
AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT MANUAL;- TEMPORARY 테이블스페이스는 세그먼트 관리를 MANUAL로 해야한다.
- 기본 테이블스페이스, temporary 테이블스페이스 설정
ALTER DATABASE DEFAULT TABLESPACE user_tbs;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE user_temp;- database_properties 뷰 확인
SELECT * FROM database_properties ORDER BY 1;
- CREATE USER 생성시 DEFAULT TABLESPACE를 지정해주지 않으면 데이터베이스 레벨에 설정되어있는 테이블스페이스로 등록된다.
- DEFAULT TABLESPACE와 DEFAULT TEMPORARY TABLESPACE 를 삭제하려고 하면 오류가 발생한다.
권한(pivilege)
- 특정한 SQL문을 실행하거나 다른 유저가 소유한 객체를 액세스할 수 있는 권한
시스템권한
-
데이터베이스에 영향을 줄 수 있는 권한
-
권한 관리는 SYS가 한다.
-
권한 조회
SELECT * FROM dba_sys_privs; -
권한부여
GRANT create session TO insa;
다른 유저에게 시스템 권한 부여 옵션
WITH ADMIN OPTION으로 받은 시스템 권한에 대해서는 다른 유저에게 그 시스템 권한을 부여할 수 있다.
GRANT create sequence TO insa WITH ADMIN OPTION; -- DBA 대신 INSA 유저가 권한 부여 가능
SELECT * FROM dba_sys_privs WHERE grantee='INSA';⚠️ 주의할 점은 with admin option 으로 시스템 권한 부여 받은 1번 유저가 2번 유저에게 시스템 권한 부여 후 DBA가 1번 유저의 시스템 권한을 회수 한다고 해서 2번 유저의 시스템 권한이 같이 회수되지 않는다. DBA가 따로 회수를 진행해야 한다.
객체권한
-
다른 유저가 소유한 객체를 액세스 할 수 있는 권한
-
권한 관리는 객체 소유자, SYS가 한다.
-
권한 조회
SELECT * FROM dba_tab_privs;
다른 유저가 생성한 객체에 대한 접근 권한 종류
-
TABLE : SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX, REFERENCES
-
VIEW : SELECT, INSERT, UPDATE, DELETE
-
SEQUENCE : SELECT, ALTER
-
PROCEDURE, FUNCTION, PACKAGE : EXECUTE
-
트리거는 이벤트성 이기때문에 권한이 따로 없다.
다른유저에게 객체 권한 부여 옵션
WITH GRANT OPTION으로 받은 객체 권한에 대해서는 다른 유저에게 그 객체권한을 부여할 수 있다.
GRANT SELECT ON hr.employees TO insa WITH GRANT OPTION;
SELECT * FROM dba_tab_privs WHERE grantee = 'INSA';⚠️ 주의할 점은 with grant option으로 객체 권한 부여 받은 1번 유저가 2번 유저에게 객체 권한 부여 후 DBA가 1번 유저의 객체 권환을 회수하면 2번 유저의 객체 권한도 같이 회수된다.
