Oracle 51일차

[gap 분석]

데이터 이관 시 양쪽 DB 현황을 조사하여 모든 이관 대상이 빠짐없이 이관됐는지 확인하는 과정.
대상 : table, index, constraints, view, synonym, sequence, privilege 등, 테이블 내 컬럼순서, 컬럼명, 데이터타입, 컬럼길이, default값 등.

사전 준비

db1에서 table 생성

create table scott.test_db1(no number, name varchar2(10));

copydb에서 table 생성

create table scott.test_copydb(no number, name varchar2(10));

1. table gap 분석

-- db1에만 있는 테이블 조회
select owner, table_name
  from dba_tables
 where owner in ('HR','SCOTT')
 minus
select owner, table_name
  from dba_tables@dblink_copydb
 where owner in ('HR','SCOTT');

-- copydb에만 있는 테이블 조회
select owner, table_name
  from dba_tables@dblink_copydb
 where owner in ('HR','SCOTT')
 minus
select owner, table_name
  from dba_tables
 where owner in ('HR','SCOTT');

-- 위 두 쿼리 결합 1)
select owner_db1, table_name_db1,null as owner_copydb, null as table_name_copydb
  from (select owner as owner_db1, table_name as table_name_db1
          from dba_tables
         where owner in ('HR','SCOTT')
         minus
        select owner, table_name
          from dba_tables@dblink_copydb
         where owner in ('HR','SCOTT'))
 union all
select *
  from (select '' as owner_db1,'' as table_name_db1,owner as owner_copydb, table_name as table_name_copydb
          from dba_tables@dblink_copydb
         where owner in ('HR','SCOTT')
         minus
        select '','',owner, table_name
          from dba_tables
         where owner in ('HR','SCOTT'));

-- 위 두 쿼리 결합 2)
with 
db1_tab as 
(select owner, table_name
   from dba_tables
  where owner in ('HR','SCOTT')),
copydb_tab as 
(select owner, table_name
   from dba_tables@copy_dblink
  where owner in ('HR','SCOTT'))
select a.owner as owner_db1, a.table_name as table_name_db1, b.owner as owner_copydb, b.table_name as table_name_copydb
  from db1_tab a, copydb_tab b
 where a.owner = b.owner(+)
   and a.table_name = b.table_name(+)
   and b.table_name is null
 union all
select a.owner as owner_db1, a.table_name as table_name_db1, b.owner as owner_copydb, b.table_name as table_name_copydb
  from db1_tab a, copydb_tab b
 where a.owner(+) = b.owner
   and a.table_name(+) = b.table_name
   and a.table_name is null;

2. index gap 분석

with 
db1_ind as 
(select owner, index_name
   from dba_indexes
  where owner in ('HR','SCOTT')),
copydb_ind as 
(select owner, index_name
   from dba_indexes@dblink_copydb
  where owner in ('HR','SCOTT'))
select a.owner as owner_db1, a.index_name as index_name_db1, b.owner as owner_copydb, b.index_name as index_name_copydb
  from db1_ind a, copydb_ind b
 where a.owner = b.owner(+)
   and a.index_name = b.index_name(+)
   and b.index_name is null
 union all
select a.owner as owner_db1, a.index_name as index_name_db1, b.owner as owner_copydb, b.index_name as index_name_copydb
  from db1_ind a, copydb_ind b
 where a.owner(+) = b.owner
   and a.index_name(+) = b.index_name
   and a.index_name is null;

3. 제약조건 gap 분석

with 
db1_cons as 
(select owner, table_name, constraint_name
   from dba_constraints
  where owner in ('HR','SCOTT')),
copydb_cons as 
(select owner, table_name, constraint_name
   from dba_constraints@dblink_copydb
  where owner in ('HR','SCOTT'))
select a.owner as owner_db1, a.table_name, a.constraint_name as constraint_name_db1, b.owner as owner_copydb, b.table_name, b.constraint_name as constraint_name_copydb
  from db1_cons a, copydb_cons b
 where a.owner = b.owner(+)
   and a.constraint_name = b.constraint_name(+)
   and b.constraint_name is null
 union all
select a.owner as owner_db1, a.table_name, a.constraint_name as constraint_name_db1, b.owner as owner_copydb, b.table_name, b.constraint_name as constraint_name_copydb
  from db1_cons a, copydb_cons b
 where a.owner(+) = b.owner
   and a.constraint_name(+) = b.constraint_name
   and a.constraint_name is null;

[DDL 추출]

테이블, 제약조건, 인덱스 생성 스크립트 추출 가능.
이관 시 후속작업으로 나머지 제약조건, 인덱스 등의 GAP을 맞출 때 주로 사용.
DBMS_METADATA.GET_DDL() 사용.

** 사용법

exec dbms_metadata.get_ddl('table', table_name, owner);
exec dbms_metadata.get_ddl('index', index_name, owner);

** 사전 설정

exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',false);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',false);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',false);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'TABLESPACE',false);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'CONSTRAINTS',true);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'REF_CONSTRAINTS',false);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'CONSTRAINTS_AS_ALTER',true);

— SQLTERMINATOR : create 바로 뒤에 세미콜론 빼는 명령어. 왜냐면 tbs를 이관하면서 변경할 때 직접 tbs를 지정해주고 그때 ;을 붙이기 때문이다.

— TABLESPACE : 보통은 이관하면서 tbs 구조?구성을 바꾼다. 만약 안 바꾼다면 그때 키면 됨.

— CONSTRAINTS_AS_ALTER : 제약조건문을 alter로 뽑을 건지 create table 시에 같이 만들어버릴 건지. 근데 제약조건이 있다면 데이터 전송 시에 시간이 오래 걸린다. 따라서 처음에는 create table만 한 뒤 데이터를 다 옮기고 그 다음에 alter문을 써서 제약조건을 추가해준다.

1. table ddl 추출

select /*+ parallel(a 4) */
       owner, table_name, tablespace_name,
       dbms_metadata.get_ddl('TABLE',table_name,owner) as table_ddl
from dba_tables a
where owner in ('SCOTT','HR');

2. index ddl 추출

select /*+ parallel(a 4) */
       owner, index_name, tablespace_name,
       dbms_metadata.get_ddl('INDEX',index_name,owner) as index_ddl
from dba_indexes a
where owner in ('SCOTT','HR');

[user 관리]

1. 생성된 유저 확인

select *
from dba_users
where username='HR';

2. 유저 관리

1) 생성

create user itwill10 identified by oracle
default tablespace users
temporary tablespace temp
quota unlimited on users
profile default
account lock;
-- profile : 해당 유저의 관리 정책

2) 관리

alter user itwill10 identified by "oracle10*";  -- 비밀번호 변경
alter user itwill10 account unlock;  -- 락 해제
alter user itwill10 default tablespace test01;  -- tbs 변경
alter user itwill10 temporary tablespace temp;  -- temp tbs 변경
alter user itwill10 quota 100m on test01;  -- quota 변경 (근데 이건 그냥 unlimited로 놓음)
alter user itwill10 profile default;  -- default라는 이름의 profile 설정

[profile]

user 관리 정책.
패스워드 관리 / 리소스 관리에 대한 정책을 만들고 특정 유저에게 해당 정책을 적용함.
– ex) 로그인 실패 횟수 제한(5회), 패스워드 유효기간.

1. 조회

select *
from dba_profiles
order by profile, resource_type;

2. user별 profile 확인

select username, profile
from dba_users;

** profile resourece 종류

1) kernel
— IDLE_TIME : 세션 유효 기간(이 기간 동안 아무 동작 없을 경우 세션이 자동 만료됨) (초 단위)
— LOGICAL_READS_PER_CALL : 한 요청 당 LOGICAL_READS 제한
— LOGICAL_READS_PER_SESSION : 한 세션 당 LOGICAL_READS 제한
— CPU_PER_CALL : 요청별 CPU 사용량 제한
— CPU_PER_SESSION : 세션별 CPU 사용량 제한
— PRIVATE_SGA : 세션별 SGA 사용량 제한
— SESSION_PER_USER : 사용자별 최대 접속 제한
— COMPOSITE_LIMIT : 세션별 총 자원 사용량 제한(cpu time, session time 등)
— CONNECT_TIME : 세션별 총 접속시간 제한

2) password
— PASSWORD_GRACE_TIME : 비밀번호 만료 경고를 받고 비밀번호를 변경할 수 있는 기간 (일 단위)
— PASSWORD_LOCK_TIME : 비밀번호 입력 오류로 계정이 잠긴 경우 잠겨있는 시간 (일 단위)
— PASSWORD_VERIFY_FUNCTION : 비밀번호 규칙 제한 함수
— PASSWORD_REUSE_MAX : 비밀번호 재사용 제한 횟수
— PASSWORD_REUSE_TIME : 비밀번호 재사용을 위해 지나야 하는 시간
— PASSWORD_LIFE_TIME : 비밀번호 유효기간(이 기간동안은 패스워드 변경 필요없음) (일 단위)
— FAILED_LOGIN_ATTEMPTS : 비밀번호 입력오류 최대 횟수(이 횟수 이상으로 틀리면 lock)
— INACTIVE_ACCOUNT_TIME : 해당 기간동안 로그인을 하지 않을 때 계정 lock

3. profile 생성

create profile my_profile
limit
FAILED_LOGIN_ATTEMPTS 5
PASSWORD_LOCK_TIME 1/24/60
PASSWORD_LIFE_TIME 10
PASSWORD_GRACE_TIME 1
IDLE_TIME 900;

4. user에 profile 적용

alter user scott profile my_profile;

select username, profile
from dba_users
where username = 'SCOTT';

5. profile 수정

alter profile 프로파일명 limit 리소스명 값;

alter profile default limit FAILED_LOGIN_ATTEMPTS 5;

select *
from dba_profiles
where profile='DEFAULT'
  and resource_type='PASSWORD';

[password verify function]

패스워드 생성 규칙을 제한하는 함수 (PL/SQL 구문으로 생성).
보통 8자 이상, 대소문자 하나씩 포함, 특수기호 포함 등으로 복잡성을 띄는 패스워드로 설정 권고.

** password verify function

> CREATE OR REPLACE FUNCTION verify_function1(
    username IN VARCHAR2,
    password IN VARCHAR2,
    old_password IN VARCHAR2
) RETURN BOOLEAN IS
    -- 패스워드 길이 확인
    password_length INTEGER := LENGTH(password);
    has_upper BOOLEAN := FALSE;
    has_lower BOOLEAN := FALSE;
    has_digit BOOLEAN := FALSE;
    has_special BOOLEAN := FALSE;
    special_characters CONSTANT VARCHAR2(50) := '!@#$%^&*()_+|~-=`{}[]:";\<>?,./';
BEGIN
    -- 패스워드 길이 확인
    IF password_length < 8 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Password must be at least 8 characters long.');
    END IF;

    -- 대소문자, 숫자, 특수기호 확인
    FOR i IN 1..password_length LOOP
        IF SUBSTR(password, i, 1) BETWEEN 'A' AND 'Z' THEN
            has_upper := TRUE;
        ELSIF SUBSTR(password, i, 1) BETWEEN 'a' AND 'z' THEN
            has_lower := TRUE;
        ELSIF SUBSTR(password, i, 1) BETWEEN '0' AND '9' THEN
            has_digit := TRUE;
        ELSIF INSTR(special_characters, SUBSTR(password, i, 1)) > 0 THEN
            has_special := TRUE;
        END IF;
    END LOOP;

    -- 모든 조건을 만족하는지 확인
    IF NOT has_upper THEN
        RAISE_APPLICATION_ERROR(-20002, 'Password must contain at least one uppercase letter.');
    ELSIF NOT has_lower THEN
        RAISE_APPLICATION_ERROR(-20003, 'Password must contain at least one lowercase letter.');
    ELSIF NOT has_digit THEN
        RAISE_APPLICATION_ERROR(-20004, 'Password must contain at least one digit.');
    ELSIF NOT has_special THEN
        RAISE_APPLICATION_ERROR(-20005, 'Password must contain at least one special character.');
    END IF;

    RETURN TRUE;  -- 모든 검사를 통과한 경우
END;
/

[실습]

전체 유저의 패스워드 관리 정책 변경 (패스워드 복잡도 설정 -> password verify function 생성 및 변경)

0. 기존 함수 조회

select *
from dba_objects
where object_name like '%VERIFY_FUNCTION%'
order by 1,2;

1. default profile에 password 정책 적용하기

-- 아까 함수 만드는 것도 ss써서 sys계정으로 리눅스 쪽에서 수행하기. orange에서 하면 안되더라.

> alter profile default limit PASSWORD_VERIFY_FUNCTION verify_function1;

2. 확인

SELECT PROFILE, RESOURCE_NAME, LIMIT
FROM DBA_PROFILES
WHERE PROFILE = 'DEFAULT' AND RESOURCE_NAME = 'PASSWORD_VERIFY_FUNCTION';

3. 패스워드 변경 시도

alter user itwill identified by "oracle1234";  -- error

4. 다시 원복시키기

alter profile default limit PASSWORD_VERIFY_FUNCTION null;

[권한 관리]

** 권한 종류
1) 오브젝트 권한 : 테이블 조회, 수정 권한
2) 시스템 권한 : 테이블 생성, 삭제, 변경 권한

** 권한 부여 방법
1) 직접 부여

grant 권한 to 유저;

2) 롤을 통해 부여

create role 롤이름;
grant 권한 to 롤이름;
grant 롤이름 to 유저;

** 권한 조회

1. 직접 부여된 권한

1) 오브젝트 권한
select grantee, grantor, owner, table_name, privilege
from dba_tab_privs
where grantee='HR';

2) 시스템 권한
select grantee, privilege
from  dba_sys_privs
where grantee='HR';

2. 롤을 통해 부여된 권한

[실습1 – object 권한]

itwill, itwill2, itwill3 유저가 있을 때,
scott소유의 모든 테이블에 대한 조회 권한을 role_sel_scott 롤에 부여.
scott소유의 모든 테이블에 대한 CUD 권한을 role_cud_scott 롤에 부여
ROLE_SEL_SCOTT -> itwill
ROLE_SEL_SCOTT, ROLE_CUD_SCOTT -> itwill2, itwill3

0. 유저 있는지 확인

SELECT USERNAME FROM ALL_USERS;

1. 롤 생성

CREATE ROLE role_sel_scott;
CREATE ROLE role_cud_scott;

2. 롤에 권한 부여

SELECT 'grant select on '||OWNER||'.'||TABLE_NAME||' to role_sel_scott;'
FROM DBA_TABLES
WHERE OWNER = 'SCOTT';

SELECT 'grant insert, update, delete on '||OWNER||'.'||TABLE_NAME||' to role_cud_scott;'
FROM DBA_TABLES
WHERE OWNER = 'SCOTT';

3. 각 유저에 롤 부여

GRANT role_sel_scott TO itwill, ITWILL2, ITWILL3;
GRANT role_cud_scott TO ITWILL2, ITWILL3;

4. 확인

** 참고 – 롤에 포함된 권한을 확인할 수 있는 뷰는 아래와 같다.
– ROLE_TAB_PRIVS, ROLE_SYS_PRIVS
– DBA_TAB_PRIVS, DBA_SYS_PRIVS
이들 중 ROLE_TAB_PRIVS, ROLE_SYS_PRIVS는 USER_XXXX 뷰처럼 유저가 직접 부여한 권한에 대해서만 조회가능.
DBA_TAB_PRIVS, DBA_SYS_PRIVS 뷰가 더 정확한 결과 리턴

-- 1. 유저에게 부여된 롤 조회
SELECT GRANTEE, GRANTED_ROLE 
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = 'ITWILL';

SELECT GRANTEE, GRANTED_ROLE 
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = 'ITWILL2';

SELECT GRANTEE, GRANTED_ROLE 
FROM DBA_ROLE_PRIVS
WHERE GRANTEE = 'ITWILL3';

-- 2. 롤에 부여된 권한 조회
select *
from role_tab_privs
where role='ROLE_SEL_SCOTT';
또는
select *
from dba_tab_privs
where grantee='ROLE_CUD_SCOTT';

-- 3. 종합 뷰
select owner, table_name, privilege
from role_tab_privs
where role in (select granted_role
               from dba_role_privs
               where grantee='ITWILL');

[실습2 – system 권한]

작업을 위한 서브DBA 롤 생성, 해당 롤에 create any table, alter any table, drop any table 권한 부여. 해당 롤을 itwill 부여.

1. 롤 생성

create role subdba;

2. 롤에 권한 부여

grant create any table, alter any table, drop any table to subdba;

3. 유저에 롤 부여

grant subdba to itwill;

4. 확인

select *
from role_sys_privs
where role in (select granted_role
               from dba_role_privs
               where grantee='ITWILL');
또는
select *
from dba_sys_privs
where grantee in (select granted_role
               from dba_role_privs
               where grantee='ITWILL');

[ORACLE 저장 구조]

1. 물리적 저장구조
– os block : 2k, 4k, 8k, …
– data file :

2. 논리적 저장구조
– db block : 8k, 16k, 32k, …
– extent :
– segment :
– tablespace :

[extent 할당 과정]

1. 테이블 생성

create table scott.extent_test1(no number, name varchar2(20), addr varchar2(20));

2. 대용량 insert

begin
for i in 1..500000 loop
    insert into scott.extent_test1 values(i, dbms_random.string('a',19), dbms_random.string('q',19));
end loop;
commit;
end;
/

3. 현재 할당된 block수 확인

select table_name,
       num_rows,
       blocks,
       empty_blocks
from dba_tables
where table_name='EXTENT_TEST1';
-- 통계 정보 수집 전이라 조회되지 않음.

4. 통계 정보 수집 및 block수 재확인

analyze table scott.extent_test1 compute statistics;

select table_name,
       num_rows,
       blocks,
       empty_blocks
from dba_tables
where table_name='EXTENT_TEST1';
-- num_rows에 500000 출력됨.

5. 테이블 사이즈 조회

select segment_name,
       bytes,
       blocks,
       extents
from dba_segments
where segment_name='EXTENT_TEST1';