OS 환경 : Oracle Linux 9.6 (64bit)
DB 환경 : Oracle AI Database 23.26.1.0.0 ai
방법 : 오라클 26ai 신기능 행간 제약사항 Assertion
오라클 26ai부터 행간 제약사항을 설정할 수 있게
Assertion이라는 새로운 스키마 레벨 오브젝트로 설정이 가능함.
이 기능은 Boolean 표현식 기반으로 서브쿼리를 포함한 제약 조건을 정의하여 트랜잭션 수행 및 커밋 과정에서도 해당 조건이 항상 참이 되도록 데이터 무결성을 강제하는 기능임
또한 Assertion은 단일 행이나 단일 테이블 수준을 넘어 여러 행 또는 여러 테이블을 조합한 결과 집합까지 제약 조건으로 정의할 수 있는 범용 데이터 무결성 제약 객체임
필요 시 CHECK, PRIMARY KEY, UNIQUE, FOREIGN KEY 같은 기존 제약 조건을 논리적으로 대체할 수도 있음
본문에서는 assertion을 생성해보고 제약을 위반했을때 어떤 에러가 발생하는지 확인해봄
이외에도 lock을 유발해 새로 나온 이벤트 이름을 확인해보고 관련 뷰도 확인해봄
테스트
1. 일반 테스트
2. deferred 테스트
3. lock 테스트
4. 에러 발생 케이스 확인
5. 관련 뷰 확인
테스트
1. 일반 테스트
샘플 테이블 생성
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
SQL>
-- 1. 직급 테이블 (직급별 급여 상한선 정의)
create table job_limits (
job_id varchar2(10) primary key,
job_title varchar2(50),
max_salary number
);
-- 2. 직원 테이블 (직급과 급여 정보 포함)
create table emp_salaries (
emp_id number primary key,
emp_name varchar2(50),
job_id varchar2(10) not null,
salary number
);
-- 초기 기준 데이터 입력
insert into job_limits values ('DEV', 'Developer', 5000);
insert into job_limits values ('MGR', 'Manager', 8000);
commit;
|
assertion 생성
NOT EXISTS와 JOIN만을 사용하여 규칙을 정의함
"직급 테이블의 상한선(max_salary)보다 많은 급여를 받는 직원이 존재해서는 안 된다(NOT EXISTS)"라는 제약을 걸어줌
|
1
2
3
4
5
6
7
8
9
|
SQL>
create assertion check_salary_limit check
(not exists (
select 1
from emp_salaries e, job_limits j
where e.job_id = j.job_id
and e.salary > j.max_salary));
Assertion created.
|
제약조건 위반 확인
정상 데이터 입력(개발자 상한 5000 이하)
|
1
2
3
|
SQL> insert into emp_salaries values (101, 'Alice', 'DEV', 4500);
1 row created.
|
정상 입력됨
비정상 데이터 입력(개발자 상한 5000 초과)
|
1
2
3
4
5
6
|
SQL> insert into emp_salaries values (102, 'Bob', 'DEV', 5500);
insert into emp_salaries values (102, 'Bob', 'DEV', 5500)
*
ERROR at line 1:
ORA-08601: SQL assertion (IMSI.CHECK_SALARY_LIMIT) violated.
Help: https://docs.oracle.com/error-help/db/ora-08601/
|
에러 발생함
emp_salaries 테이블에는 max_salary 컬럼이 없지만 오라클이 job_limits 테이블까지 확인하여 입력(5500)을 차단함
2. deferred 테스트
deferred 선언시
|
1
|
SQL> set constraints all deferred;
|
비정상 데이터 입력(개발자 상한 5000 초과)
|
1
2
3
|
SQL> insert into emp_salaries values (1003, 'Kim', 'DEV', 6000);
1 row created.
|
에러 미발생
커밋 시도
|
1
2
3
4
5
6
7
|
SQL> commit;
commit
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-08601: SQL assertion (IMSI.CHECK_SALARY_LIMIT) violated.
Help: https://docs.oracle.com/error-help/db/ora-02091/
|
에러 발생함, deferred 옵션을 활성화하면 commit 시점에 제약조건 에러가 발생하며 트랜잭션이 롤백됨
3. lock 테스트
job_limits 테이블에 새로운 데이터 삽입
세션1
|
1
2
3
|
SQL> insert into job_limits values ('HR', 'HumanResource', 6000);
1 row created.
|
아직 commit 하지 않은 상태
emp_salaries 테이블에 해당 테이블에 들어가는 데이터(HR) 삽입 시도
세션2
|
1
2
|
SQL> insert into emp_salaries values (9999, 'KIM', 'HR', 5000);
(대기중)
|
ash 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
SQL>
set lines 200 pages 1000
col wait_class for a15
col event for a50
select * from (
select
wait_class ,
event,
count(sample_time) as est_secs_in_wait
from v$active_session_history
where sample_time between sysdate - interval '30' second and sysdate
group by wait_class,event
order by count(sample_time) desc
)
where rownum <6;
WAIT_CLASS EVENT EST_SECS_IN_WAIT
--------------- -------------------------------------------------- ----------------
Other enq: AN - SQL assertion DDL/DML 5
|
새로운 "enq: AN - SQL assertion DDL/DML" 이벤트가 확인됨
4. 에러 발생 케이스 확인
assertion 생성시 not exists 안에 집계 함수 쓰면 에러 발생함
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
SQL>
create assertion limit_mentees_per_mentor
check (
not exists (
select mentor_id
from mentees
group by mentor_id
having count(*) > 3
)
);
FROM mentees
*
ERROR at line 5:
ORA-08689: CREATE ASSERTION failed
ORA-08661: Aggregates are not supported.
Help: https://docs.oracle.com/error-help/db/ora-08689/
|
sys 유저가 소유한 테이블로 assertion 생성시 에러 발생함
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
SQL>
conn sys/oracle@localhost:1521/ORACLE26PDB1 as sysdba
(본문 샘플 테이블 및 데이터 생성)
CREATE ASSERTION check_salary_limit CHECK
(NOT EXISTS (
SELECT 1
FROM emp_salaries e, job_limits j
WHERE e.job_id = j.job_id
AND e.salary > j.max_salary));
FROM emp_salaries e, job_limits j
*
ERROR at line 4:
ORA-08710: SQL assertion not supported in SYS schema.
Help: https://docs.oracle.com/error-help/db/ora-08710/
|
일반 유저여도 오라클 조인 방식이 아닌 ansi join 구문 적으면 에러 발생함
SYS 오너 테이블로 조인한것처럼 에러가 발생함
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL>
create assertion if not exists check_salary_limit
check (
not exists (
select 1
from emp_salaries e
join job_limits j on e.job_id = j.job_id
where e.salary > j.max_salary
)
);
*
ERROR at line 1:
ORA-08689: CREATE ASSERTION failed
ORA-08697: SYS owned tables are not supported.
Help: https://docs.oracle.com/error-help/db/ora-08689/
|
job_id 에 NOT NULL 제약 없는경우 에러 발생함
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
create assertion check_salary_limit check
(not exists (
select 1
from emp_salaries e, job_limits j
where e.job_id = j.job_id
and e.salary > j.max_salary));
*
ERROR at line 4:
ORA-08689: CREATE ASSERTION failed
ORA-08673: Equijoin "E"."JOB_ID"="J"."JOB_ID" found does not meet the criteria to do a FAST validation.
Help: https://docs.oracle.com/error-help/db/ora-08689/
|
5. 관련 뷰 확인
dba_assertions 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
SQL>
set lines 200 pages 1000 long 999999
col owner for a10
col assertion_name for a20
col definition_sql for a60
select * from dba_assertions
where assertion_name = 'CHECK_SALARY_LIMIT';
OWNER ASSERTION_NAME OBJECT_ID STATUS DEFERRABLE DEFERRED VALIDATED INVALID DEFINITION_SQL
---------- -------------------- ---------- -------- -------------- --------- ------------- ------- ------------------------------------------------------------
IMSI CHECK_SALARY_LIMIT 31619 ENABLED NOT DEFERRABLE IMMEDIATE VALIDATED CREATE ASSERTION "IMSI"."CHECK_SALARY_LIMIT" CHECK
(NOT EXISTS (
SELECT 1
FROM emp_salaries e, job_limits j
WHERE e.job_id = j.job_id
AND e.salary > j.max_salary))
ENABLE
NOT DEFERRABLE
INITIALLY IMMEDIATE
VALIDATE
|
dba_assertion_dependencies 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
|
SQL>
set lines 200 pages 1000 long 999999
col owner for a10
col referenced_owner for a10
col assertion_name for a20
col referenced_name for a30
select * from dba_assertion_dependencies
where assertion_name = 'CHECK_SALARY_LIMIT';
OWNER ASSERTION_NAME REFERENCED REFERENCED_NAME REFERENCED_TYPE REFERENCED_ID VALIDATI VALIDATION_EVENT
---------- -------------------- ---------- ------------------------------ ----------------------- ------------- -------- ------------------------
IMSI CHECK_SALARY_LIMIT IMSI JOB_LIMITS TABLE 0 FAST ROWS INSERTED OR UPDATED
IMSI CHECK_SALARY_LIMIT IMSI EMP_SALARIES TABLE 1 FAST ROWS INSERTED OR UPDATED
|
dba_assertion_lock_matrix 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
|
SQL>
set lines 200 pages 1000 long 999999
col owner for a10
col table_owner for a10
col table_name for a15
col ADJ_TABLE_OWNER for a15
col ADJ_TABLE_NAME for a15
col JOIN_COLUMNS for a15
col assertion_name for a20
col lock_text for a15
select * from dba_assertion_lock_matrix
where assertion_name = 'CHECK_SALARY_LIMIT';
OWNER ASSERTION_NAME TABLE_ID TABLE_OWNE TABLE_NAME ADJ_TABLE_ID ADJ_TABLE_OWNER ADJ_TABLE_NAME LOCK_SCOPE JOIN_COLUMNS LOCK_MOD LOCK_TEXT
---------- -------------------- ---------- ---------- --------------- ------------ --------------- --------------- ------------------ --------------- -------- ---------------
IMSI CHECK_SALARY_LIMIT 0 IMSI JOB_LIMITS 0 IMSI JOB_LIMITS JOIN_COLUMN_VALUES JOB_ID SX T#:0 C#:1:1
IMSI CHECK_SALARY_LIMIT 1 IMSI EMP_SALARIES 0 IMSI JOB_LIMITS JOIN_COLUMN_VALUES EMP_ID S T#:0 C#:1:1
IMSI CHECK_SALARY_LIMIT 0 IMSI JOB_LIMITS 1 IMSI EMP_SALARIES JOIN_COLUMN_VALUES MAX_SALARY S T#:1 C#:1:3
IMSI CHECK_SALARY_LIMIT 1 IMSI EMP_SALARIES 1 IMSI EMP_SALARIES JOIN_COLUMN_VALUES JOB_ID SX T#:1 C#:1:3
|
결론 :
26ai 신기능인 assertion을 이용해 효과적으로 데이터의 무결성을 지켜줄 수 있게됨
참조 :
https://docs.oracle.com/en/database/oracle/oracle-database/26/cncpt/data-integrity.html#GUID-F87D5640-EACA-43EA-9ACA-8BFAA5C6E8B0
https://www.linkedin.com/posts/saxonchris_sql-ugcPost-7421962814245883904-L9KK/?utm_source=social_share_send&utm_medium=android_app&rcm=ACoAACnc0-YB637ikCAA_0oqCOEB07kHI_OAIrw&utm_campaign=copy_link
https://docs.oracle.com/en/database/oracle/oracle-database/26/admin/managing-schema-objects.html#GUID-83B6F464-B8AB-4BA7-997A-D74C81051F52
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 26ai cdb 기동시 pdb 자동 기동 방법 (0) | 2026.02.05 |
|---|---|
| 오라클 26ai On-Premise 리눅스 버전 출시 (2) | 2026.01.29 |
| 오라클 26ai 관련 MOS 문서 번호 (0) | 2026.01.23 |
| 오라클 19c to 티베로7 db 링크 설정 가이드 on AIX (0) | 2025.12.17 |
| 오라클 19c 디스크 그룹 이름 변경 (0) | 2025.12.14 |