[오라클]INDEX REBUILD, 지연제약조건, 사용자/PROFILE/PRIVILEGE/ROLE 관리, SQLLDR

[INDEX REBUILD]

1. 테이블 소유자 조회

SELECT OWNER,TABLE_NAME, TABLESPACE_NAME FROM DBA_TABLES

WHERE OWNER='HR' AND TABLE_NAME='EMPLOYEES';

 

 

2. 블럭이 분할을 했을때 사용하지 못한 빈공간을 분석하는 명령어

ANALYZE TABLE HR.EMPLOYEES COMPUTE STATISTICS;

 

3. 레코드의 수 & 단편화 현상이 일어난 데이터의 수 조회

SELECT NUM_ROWS,CHAIN_CNT FROM DBA_TABLES

WHERE TABLE_NAME='EMPLOYEES';

 

- NUM_ROWS < CHAIN_CNT 라면 REBUILD 작업 진행해줘야 한다.

 

 

4. HR에 USERS 테이블스페이스의 1메가 메모리를 할당해줌. 고로 두 곳 모두에서 테이블 생성 가능.

HR의 디폴트 테이블스페이스는 EXAMPLE.

ALTER USER HR QUOTA 1M ON USERS;

 

5. HR에 로그인하기 위해서 계정 잠금 풀고 로그인

ALTER USER HR IDENTIFIED BY HR ACCOUNT UNLOCK;

CONN HR/HR

 

6. 테이블스페이스를 USERS로 이동 ★

ALTER TABLE EMPLOYEES MOVE TABLESPACE USERS;

 

 

7. SYS 로 접속하여 재조회시 테이블스페이스 변경된 내용 확인 가능

 

8. REBUILD ★

EMPLOYEES 테이블에 존재하는 인덱스를 조회

 

9. 리빌드 작업 진행. (인덱스를 읽어서 재정의 하는 것)

인덱스 독립적 컬럼으로 개별적으로 모두 진행해줘야 한다.

ALTER INDEX HR.EMP_EMAIL_UK REBUILD;

ALTER INDEX HR.EMP_EMP_ID_PK REBUILD;

ALTER INDEX HR.EMP_DEPARTMENT_IX REBUILD;

ALTER INDEX HR.EMP_JOB_IX REBUILD;

ALTER INDEX HR.EMP_MANAGER_IX REBUILD;

ALTER INDEX HR.EMP_NAME_IX REBUILD;

 

10. 다시 인덱스를 조회하면 VALID(검증됨)으로 STATUS가 변경 되어 있다.

 

 

---

 

 

[제약조건 -  지연]

1. (예를 들어, 수많은 데이터를 입력할 때) 100만개의 데이터를 일단 삽입 후 이상이 있으면 에러를 발생시켜서 ROLLBACK 후 수정.

관리자가 테이블에 이런 기능을 집어 넣을 수 있다.

내가 지금 연결한 세션에만 해당되도록 변경. (제약 조건을 지연시켜라.)

제약조건이 실행되는 때는 COMMIT 입력시. 확인 후 입력전 COMMT상태로 ROLLBACK 진행

 

2. 세션에 CONSTRAINT = DEFERRED 제약 조건 설정.

NO에 1입력 COMMIT, 2,3,4,1 입력 후 COMMIT 시 1만 남고 전부 ROLLBACK 됨

3. 테이블 생성시 제약조건에 지연처리 삽입.

 

 

---

 

 

[USER 관리]

1. 사용자 생성

CREATE USER BOB

IDENTIFIED BY BOB

DEFAULT TABLESPACE USERS

TEMPORARY TABLESPACE TMP

QUOTA 1M ON USERS;

 

CREATE USER KAY

IDENTIFIED BY KAY

DEFAULT TABLESPACE USERS

 

TEMPORARY TABLESPACE TMP;

 

 

2. 권한 부여

ROLL을 쓰지않고 시스템권한을 부여함 ( CREATE SESSION -> CONNECT, CREATE TABLE-> RESOURCE)

GRANT CREATE SESSION, CREATE TABLE TO BOB, KAY;

 

3. 조회

 

4. 사용자에게 주어진 테이블스페이스의 할당량을 조회할 수 있는 딕셔너리 ( DBA_TS_QUOTAS)

- BYTES 수가 0 이니까 BLOCK도 0

 

5. 계정 삭제

DROP USER KAY;

 

6. 비밀번호 변경

CONN BOB/BOB 로그인

ALTER USER BOB PASSWORD EXPIRE; 패스워드변경 새 비밀번호 설정 가능

 

 

---

 

 

[PROFILE 관리] 

- 우리가 사용하는 대표적인 ROLE : CONNECT, RESOURCE, DBA

- PROFILE은 환경설정 개념과 같다.

- EX) A라는 사용자가 접속하려는데 비밀번호 3번 틀릴 경우 계정 잠금과 같은 정보를 저장

 

1. 프로파일 조회, 프로파일명, 리소스명,

SELECT * FROM DBA_PROFILES;

 

2. 프로파일의 종류 조회

 

3. 사용자가 사용하는 프로파일 조회

 

4. 파라미터 파일에 resource_limit=true 삽입

- 변경된 파라미터 파일로 실행해야 되므로 SHUTDOWN과 STARTUP 진행해주어야 한다.

SHUTDOWN IMMEDIATE

STARTUP

 

 

 

5. 사용자가 접속 후 5분동안 사용이 없으면 강제 종료하는 프로파일 생성

CREATE PROFILE TEST_PROFILE LIMIT

SESSIONS_PER_USER 3

DLE_TIME 5;

 

 

6. 생성된 프로파일 조회

SELECT * FROM DBA_PROFILES

WHERE PROFILE='TEST_PROFILE'

ORDER BY RESOURCE_TYPE;

 

 

 

7. 패스워드 30일 지날경우 변경 & 비밀번호 시도 3번 실패시 제한 프로파일

ALTER PROFILE TEST_PROFILE LIMIT

FAILED_LOGIN_ATTEMPTS 3

PASSWORD_LIFE_TIME 30

 

 

8. 계정이 잠겼을 때 풀어줘야 함

 

9. 재접속

 

10. KIM, LEE가 사용하는 프로파일 조회

 

11. 프로파일 삭제는 CASCADE로 진행해야 함.

 

12. TEST_PROFILE이 삭제되어 KIM의 프로파일 재 조회시 DEFAULT로 변경 됨

 

 

 

---

 

[PRIVILEGE 관리]

1. SCOTT계정에게 계정을 만들 수 있는 권한을 SYS가 부여.

WITH ADMIN OPTION; SCOTT이 또 다른 사용자에게 해당 권한 부여 가능

 

2. SCOTT이 BOB에게 권한 부여

 

3. SCOTT과 BOB의 권한 조회

 

4. SCOTT의 권한 취소, BOB은 그대로 가지고 있음

 

5. SCOTT이 BOB에게 SELECT 권한 부여

 

6. BOB가 SCOTT의 테이블이 SELECT 되는 지 확인 후 ROSE에게 권한부여

 

7. 권한 부여 내역 조회

SCOTT -> BOB 권한 부여 , BOB -> ROSE 권한 부여

 

8. SCOTT이 BOB의 권한 삭제

조회시 ROSE의 권한도 삭제됨. SYSTEM 권한은 안뺏기지만 OBJECT권한은 같이 뺏김

 

 

---

 

[ROLE 관리] 

1. RESOURCE ROLE의 권한 종류 조회

 

2. ROLE생성

 

3. ROLE에 권한 부여

 

4. PREVILEGE 조회

 

5. DEFAULT 제한

 

6. BOB으로 연결 후 소유한 권한 조회

 

6. BOB 소유한 권한 재조회

 

 

 

---

 

[ SQL*LOADER : 이기종간의 데이터 전송( MS-SQL, MY-SQL 에서의 데이터를 가져오는 것만 지원함) ]

‘tab’ 과 ‘,’으로 구분된 데이터를 받아내는 방법이 존재.  

일반적으로 csv 파일. 데이터가 완벽하게 넘어가지 않음. 데이터 변형 필요. 귀찮은 작업…

 

1. 테이블 컬럼 생성

 

2. SQL LOADER를 사용해서 데이터 입력. CTL파일에 데이터 입력되어 있음

 

3. 기본경로에 해당 CTL파일이 존재하므로 파일명만 작성해줘도 문제 없음

 

4. 입력된 데이터 조회

 

 

---

 

 

[ SQLLDR - 데이터가 CTL파일이 아닌 다른 파일에 있을 때]

 

1. EMP 테이블 생성

 

2. CASE2.CTL 생성

 

3. CASE2.DAT 생성 (데이터가 들어있는 파일. 일반적으로 CSV파일)

 

4. SQLLDR 실행

 

5. 데이터가 테이블에 저장되었는지 조회

 

 

---

 

[ SQLLDR - CTL 파일에는 4가지 입력 가능]

- INSERT 입력

- REPLACE 바꿔치기

- TRUNCATE 전부 지우고 입력

- APPEND 누적 입력

 

1. 테이블 생성

 

2. CTL 파일 생성

 

 

3. 로더 실행

 

4. 테이블 조회

 

5. 테이블 생성

 

 

---

 

[SQLLDR - 시퀀스 사용]

1. CTL파일 생성

 

2. SQLLDR 실행

 

3. 테이블 조회

 

---

 

 

[SQLLDR - TRAILING NULLCOLS : 레코드의 마지막 값에 NULL이 있을 때 처리하는 명령어]

중간값이 NULL일 경우는 , , 로 인식이 되나 마지막 값은 인식이 안되므로 존재하는 명령어.

1. CTL 파일 생성

 

2. SQLLDR 실행 후 재조회

 

 

---

 

[SQLLDR  - NVL 함수 사용]

1. CTL 파일 생성

 

2. SQLLDR 실행

 

3. 결과 조회

 

 

---

 

[SQLLDR - LOWER, UPPER함수 사용]

1. CTL 파일 생성

 

2. 결과 조회

 

 

---

 

[ SQLLDR - TO_CHAR, CASE문 사용]

1. CTL 파일 생성

 

2. 결과 조회