Oracle 15일차
1. 인덱스(Index)
SELECT OWNER, TABLE_NAME, TABLESPACE_NAME FROM DBA_TABLES
WHERE OWNER='LEE' AND TABLE_NAME='CUSTOM';
(1) 블럭이 분할을 했을 때 쓰지 못한 공간을 관리
ANALYZE TABLE HR.EMPLOYEES COMPUTE STATISTICS;
SELECT NUM_ROWS, CHAIN_CNT FROM DBA_TABLES
WHERE TABLE_NAME='EMPLOYEES';
▶ CHANI_CNT > NUM_ROWS이면
(2) HR에 테이블을 만들면 기본적으로 EXAMPLE에 들어감
ALTER USER HR QUOTA 1M ON USERS;
ALTER USER HR IDENTIFIED BY HR ACCOUNT UNLOCK;
▶이제는 USERS에도 1M 사용 할 수 있도록 변경
(3)테이블 위치 변경
○ 현재 위치 확인 > EXAMPLE
SELECT OWNER, TABLE_NAME, TABLESPACE_NAME FROM DBA_TABLES
WHERE OWNER='HR' AND TABLE_NAME='EMPLOYEES';
○ 바뀐 위치 확인 > USERS
ALTER TABLE EMPLOYEES MOVE TABLESPACE USERS;
SELECT OWNER, TABLE_NAME, TABLESPACE_NAME FROM DBA_TABLES
WHERE OWNER='HR' AND TABLE_NAME='EMPLOYEES';
○ 다시 EXAMPLE로 변경
(4) REBUILD
○ EMPLOYEES에 있는 인덱스 확인
SELECT TABLE_NAME,INDEX_NAME,STATUS FROM DBA_INDEXES
WHERE OWNER='HR' AND TABLE_NAME='EMPLOYEES';
○인덱스를 재정의
ALTER INDEX HR.EMP_EMAIL_UK REBUILD;
인덱스를 재정의 : 인덱스의 단편화가 없어짐
EXAMPLE에 포함된 모든 인덱스를 REBUILD 해줘야 함
○ STATUS 가 UNUSABLE에서 VALID로 변경
SELECT TABLE_NAME,INDEX_NAME,STATUS FROM DBA_INDEXES
WHERE OWNER='HR' AND TABLE_NAME='EMPLOYEES';
2. 제약조건(지연)
적용을 지연 시킴 : 일단 데이터를 넣는게 목적일 때 사용
(1) 테이블 생성 후 제약 조건 지연
○ 테이블 생성후 데이터 입력.
CREATE TABLE CONS
(NO NUMBER CONSTRAINT CONS_PK_NO PRIMARY KEY DEFERRABLE);
○제약조건을 지연
ALTER SESSION SET CONSTRAINT=DEFERRED;
○ COMMIT
COMMIT할 때가 문제가 생김.
중복된 데이터가 있으면 롤백.
(2) 테이블 생성과 제약 조건 지연을 동시에
○테이블 생성 및 제약조건 지연, 데이터 입력
CREATE TABLE CONS2
(NO NUMBER,
CONSTRAINT CONS2_NO_PK PRIMARY KEY(NO)
DEFERRABLE
INITIALLY DEFERRED); ▶ ALTER SESSION SET CONSTRAINT=DEFERRED;
COMMIT명령을 하면 다 지워짐
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;
QUOTA 1M ON USERS : USERS에 오직 1M만 사용 가능
(2) 권한 부여
GRANT CREATE SESSION,CREATE TABLE TO BOB,KAY;
(3)
SELECT USERNAME,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE
FROM DBA_USERS
WHERE USERNAME IN('BOB','KAY');
(4) 테이블스페이스에 할당된 할당량 확인
SELECT * FROM DBA_TS_QUOTAS;
(5) 유저 삭제
DROP USER KAY;
4. PROFILE 관리
○ 자주 사용 하는 ROLL : CONNECT, DBA, RESOURCE
○ PROFILE : 각 개인의 환경 설정을 저장(비밀번호를 3번 틀리면 계정 잠금 등..)
(1) PROFILE 정보 확인
SELECT * FROM DBA_PROFILES;
○ PROFILE의 종류 확인
SELECT DISTINCT PROFILE FROM DBA_PROFILES;
○ 사용자가 사용하는 PROFILE확인
SELECT USERNAME, PROFILE FROM DBA_USERS;
DEFAULT : 환경설정이 다 0. 초기값이 없음
(2) PROFILE 생성
○ PATAMETER 추가
resource_limit=true;
○ PROFILE 생성
CREATE PROFILE TEST_PROFILE LIMIT
SESSIONS_PER_USER 3
IDLE_TIME 5;
SESSION_PER_USER 3 : 로그인 시 패스워드를 3번 틀리면 계정 잠금
IDLE_TIME 5 :키보드로 5분동안 입력작업이 없을 경우 종료
○ PROFILE 확인
SELECT * FROM DBA_PROFILES
WHERE PROFILE=’TEST_PROFILE’
ORDER BY RESOURCE_TYPE;
(3) 사용자 PROFILE 변경
ALTER USER KIM PROFILE TEST_PROFILE;
○ 변경 확인
SELECT USERNAME, PROFILE FROM DBA_USERS
WHERE USERNAME='KIM';
(4) PROFILE 변경
ALTER PROFILE TEST_PROFILE LIMIT
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 30;
FAILED_LOGIN_ATTEMPTS 3 : 로그인을 시도하다가 패스워드가 3번 틀리면 계정을 잠금
PASSWORD_LIFE_TIME 30 : 30일이 지나면 무조건 패스워드를 바꾸도록
○ KIM에서 확인
○ SYS에서 KIM에 걸린 LOCK 해제
ALTER USER KIM ACCOUNT UNLOCK;
(5) PROFILE 삭제
DROP PROFILE TEST_PROFILE;
이미 할당되어진 PROFILE은 CASCADE로 삭제
○ KIM의 PROFILE이 DEFAULT로 변경
SELECT USERNAME, PROFILE FROM DBA_USERS
WHERE USERNAME='KIM';
5. (PRIVILEGE)권한 관리
(1) SYSTEM 권한
○ SCOTT에게 계정을 만들 수 있는 권한을 줌
GRANT CREATE USER TO SCOTT WITH ADMIN OPTION;
WITH : 이 권한을 다른 사용자에게 줄 수 있음
▶ SCOTT에서 실행 : CREATE USER 권한을 BOB에게 줌
GRANT CREATE USER TO BOB;
▶ SYS에서 실행 : SCOTT와 BOB의 권한 확인
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE IN ('SCOTT','BOB');
○ SCOTT에 준 CREATE USER권한을 뺏음
REVOKE CREATE USER FROM SCOTT;
▶ SCOTT의 권한을 뺏겼지만, BOB의 권한은 그대로
(2) OBJECT 권한
○ 새로운 계정 생성
CREATE USER ROSE
IDENTIFIED BY ROSE
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TMP;
○ SCOTT이 BOB에게 권한
GRANT SELEC ON DEPT TO BOB WITH GRABT OPTION
○ BOB이 ROSE에게 권한 부여
GRANT SELECT ON SCOTT.DEPT TO ROSE;
○
SELECT * FROM ALL_TAB_PRIVS WHERE TABLE_NAME='DEPT';
SCOTT > BOB 권한 부여
BOB > ROSE 권한 부여
○ BOB에게서 SELECT 권한 회수
REVOKE SELECT ON DEPT FROM BOB;
▶ ROSE의 권한도 자동 회수
▶ SYSTEM권한은 자동 회수X, OBJECT권한은 자동 회수
(3) ROLE
○
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE=’RESOURCE’;
○ ROLE생성 및 권한 부여
CREATE ROLE DEV;
GRANT CREATE TABLE, CREATE VIEW TO DEV;
GRANT SELECT ON SCOTT.EMP TO DEV
○ BOB에게 권한 부여
GRANT DEV, CONNECT, RESOURCE TO BOB;
○ BOB에게 부여된 ROLE 중 CONNECT, RESOURCE만 DEFAULT값으로 사용 하도록 설정
ALTER USER BOB DEFAULT ROLE CONNECT, RESOURCE;
○ 나에게 적용된 ROLE 모두 가능하도록
SET ROLE ALL;
6. SQL*LODER
○ 이기종간의 데이터 전송
MS-SQL / MYSQL의 데이터를 ORACLE로 가져옴
ORACLE의 데이터를 이동하는 건 불가
○ TAB과 ,로 구분된 데이터를 받아냄
방법 1
① 테이블 구조만 복사하여 테이블 생성
CREATE TABLE DEPT2
AS
SELECT * FROM EMP WHERE 0=1;
② CASE1.CTL 생성
LOAD DATA
INFILE *
INTO TABLE DEPT2
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
(DEPTNO, DNAME, LOC)
BEGINDATA
12,RESEARCH,"SARATOGA"
10,"ACCOUNT",CLEVELAND
11,ART,SALEN
42,"INIT'L","SAN FRAM"
▶ FILEDS TERMINATED BY ',' > ,로 구분
▶ OPTIONALLY ENCLOSED BY '"' > “는 문자가 아닌 기호.. “ “ 안에 있는 문자만 인식
③ CMD창에서 CASE1.CTL 실행
SQLLDR scott/tiger CONTROL=’CASE1.CTL’
④ SCOTT에서 DEPT2 SELECT문 확인
SELECT * FROM DEPT2;
▶CASE1.CTL이 제대로 실행 되면 LOG파일이 생김
방법 2
① 테이블 구조만 복사하여 테이블 생성
CREATE TABLE EMP2
AS
SELECT * FROM EMP WHERE 0=1;
① CASE2.CTL / CASE2.DAT생성
○CASE2.CTL
○CASE2.DATLOAD DATA
INFILE CASE2.DAT
INTO TABLE EMP2
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
(EMPNO,ENAME,JOB,MGR,SAL,COMM,DEPTNO)
7777,SUZI,SINGER,6666,3000,100,10
5555,SHIN,SINGER,8888,5000,2000,20
2222,KIM,OFFICER,7777,2000,300,30
② CMD창에서 실행
SQLLDR scott/tiger CONTROL=’CASE2.CTL’
③ SCOTT에서 SELECT 확인
SELECT * FROM EMP2;
방법 3
① 테이블 생성
CREATE TABLE DEPT_TEST
(DID NUMBER(2),
DNAME VARCHAR2(20),
FAST_UPDATED DATE);
② CASE3.CTL파일 생성
CTL파일에는 INSERT, REPLACE, TRUNCATE, APPEND 사용 가능
LOAD DATA
INFILE *
APPEND
INTO TABLE DEPT_TEST
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
(DID,DNAME,FAST_UPDATED DATE 'YYYY-MM-DD')
BEGINDATA
11,SALES,2019-01-01
22,ACCOUNT,2019-10-10
33,FOOD,2010-11-11
③ CMD창에서 실행
SQLLDR scott/tiger CONTROL=’CASE3.CTL’
④ SCOTT에서 확인
SELECT * FROM DEPT_TEST;
방법 4
① 테이블 생성
CREATE TABLE STUDENT
(HAKBUN NUMBER(3),
NAME VARCHAR2(10),
DEPART VARCHAR2(20));
② CASE4.CTL파일 생성
LOAD DATA
INFILE *
REPLACE
INTO TABLE STUDENT
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
HAKBUN SEQUENCE(100,1),
NAME CHAR,
DEPART CHAR)
BEGINDATA
HONG,COMPUTER
KIM,MACHINE
LEE,FOOD
PARK,SALES
③ CMD창에서 실행
SQLLDR scott/tiger CONTROL=’CASE4.CTL’
④ SCOTT에서 확인
SELECT * FROM STUDENT;
⑤ CASE4.CTL 수정
LOAD DATA
INFILE *
REPLACE
INTO TABLE STUDENT
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(HAKBUN,
NAME CHAR,
DEPART CHAR)
BEGINDATA
1,HONG,COMPUTER
2,KIM,
3,LEE,FOOD
4,PARK,
TRAILING NULLCOLS
마지막값이 NULL일 때 인식을 못하지만 TRAILING NULLCOLS에 의해서 NULL값이 넣는다
⑥ CMD창에서 실행 및 SCOTT에서 확인
SQLLDR scott/tiger CONTROL=’CASE4.CTL’
방법 5
① CASE5.CTL 생성
LOAD DATA
INFILE *
REPLACE
INTO TABLE STUDENT
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
(HAKBUN,
NAME CHAR,
DEPART "NVL(:DEPART,'NONAME')")
BEGINDATA
1,HONG,COMPUTER
2,KIM,,
3,LEE,FOOD
4,PARK,,
② CMD창에서 실행
SQLLDR scott/tiger CONTROL=’CASE5.CTL’
③ SCOTT에서 확인
SELECT * FROM STUDENT;
방법 6
① CASE6.CTL 생성
LOAD DATA
INFILE *
REPLACE
INTO TABLE STUDENT
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
(HAKBUN,
NAME CHAR "LOWER(:NAME)",
DEPART "UPPER(:DEPART)")
BEGINDATA
1,SEO,computer
2,KIM,elect
3,LEE,food
4,PARK,sales
② CMD창에서 실행
SQLLDR scott/tiger CONTROL=’CASE6.CTL’
③ SCOTT에서 확인
SELECT * FROM STUDENT;
방법 7
① 테이블 생성
CREATE TABLE NEWSTUDENT
(HAKBUN NUMBER(3),
NAME VARCHAR2(10),
IBHAKDAY VARCHAR2(13),
JUBUN VARCHAR2(13),
GENDER VARCHAR2(2));
② CASE7.CTL 생성
LOAD DATA
INFILE *
REPLACE
INTO TABLE NEWSTUDENT
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
(HAKBUN,
NAME CHAR,
IBHAKDAY CHAR "TO_CHAR(SYSDATE,'YYYY-MM-DD')",
JUBUN CHAR,
GENDER
"CASE SUBSTR(:JUBUN,7,1)
WHEN '1' THEN 'M'
WHEN '3' THEN 'M'
ELSE 'F'
END")
BEGINDATA
1,SEO,,9405121234567,,
2,KIM,,9002222234567,,
3,LEE,,9206183234567,,
4,PARK,,9908254234567,,
③ CMD창에서 실행
SQLLDR scott/tiger CONTROL=’CASE7.CTL’
④ SCOTT에서 확인
SELECT * FROM NEWSTUDENT;
