자수성가한 부자

[ora19c@datasvc ~]$ sqlplus

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Aug 31 08:28:25 2021
Version 19.8.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.

Enter user-name: /as sysdba

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.8.0.0.0


SQL> create user test_0831 identified by test_0831 default tablespace users;

User created.

SQL> grant connect, resource to test_0831;

Grant succeeded.

SQL> conn test_0831/test_0831
Connected.
SQL> create table test (col1 number, col2 varchar2(10)) tablespace system;

Table created.

SQL> insert into test values (1,'test');
insert into test values (1,'test')
            *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'SYSTEM'


SQL> conn / as sysdba
Connected.
SQL> grant unlimted tablespace to test_0831;
grant unlimted tablespace to test_0831
      *
ERROR at line 1:
ORA-00990: missing or invalid privilege


SQL> grant unlimited tablespace to test_0831;

Grant succeeded.

SQL> conn test_0831/test_0831
Connected.

SQL> insert into test values (1,'test');

1 row created.

SQL> commit;

Commit complete.

19c upgrade 시에 password_version 관련 사항

최초 작성일 : 2021년 8월 19일(목)

최종 수정일 : 2021년 8월 19일(목)

#########################
## 1. 파라미터 변경
#########################

패스워드의 파라미터 변경

alter system set sec_case_sensitive_logon=false SCOPE=both;


#########################
## 2. 패스워드 버전 확인
#########################

select username, account_status, password_versions 
  from dba_users
 order by username;


#########################
## 3. 패스워드 버전 변경
#########################

vi $ORACLE_HOME/network/sqlnet.ora

SQLNET.ALLOWED_LOGON_VERSION_SERVER=12a

alter user <계정> identified by <패스워드>;

select username, account_status, password_versions 
  from dba_users
 order by username;

패스워드 방식

10G 
11G - SHA1
12C - SHA-2에 기본을 둔 SHA-512

join이란?

읽고자 하는 데이터가 하나의 테이블이 아닌 두개 이상의 테이블에 나눠있다면 어떻게 해야할까?

그러면 두개의 테이블로부터 데이터를 읽어와야할 것이다

from emp

from dept

=>

from emp, dept

이것이 join이다.

join은 간단히 얘기해서 from 절에 두개 이상의 테이블이 오는 것이다.

예를 들어 emp 테이블에 아래와 같이 데이터가 저장되어있다고 하자.

emp_id    emp_name    dept_no

-------------------------------------

1           홍길동           10

2           세종대왕        10

3           유재석           20

그리고 dept 테이블에는 아래와 같이 데이터가 있다고 가정하자.

dept_no       dept_name

---------------------------

10              경영관리실

20              기술지원실

아래의 결과는 어떻게 나올까?

select emp.*, dept.*

from emp, dept;

결과는 아래와 같다.  총 row의 수는 3*2 = 6개이다.

이것을 cartesian product (카티션 프로덕트)라고 한다.

emp_id  emp_name  dept_no    dept_no       dept_name

----------------------------------------------------------------

1          홍길동        10           10              경영관리실

1          홍길동        10           20              기술지원실

2          세종대왕     10           10              경영관리실

2          세종대왕     10           20              기술지원실

3          유재석        20           10              경영관리실

3          유재석        20           20              기술지원실

여기에 where 조건을 추가하게 되면

select emp.*, dept.*

from emp, dept

where emp.dept_no = dept.dept_no;

조건에 맞는 row들만 남게되는 것이다. 총 3건이 된다.

emp_id  emp_name  dept_no    dept_no       dept_name

----------------------------------------------------------------

1          홍길동        10           10              경영관리실

1          홍길동        10           20              기술지원실

2          세종대왕     10           10              경영관리실

2          세종대왕     10           20              기술지원실

3          유재석        20           10              경영관리실

3          유재석        20           20              기술지원실

이렇게 순차적으로 이해를 하면 join을 이해하기가 쉽다.

연산자는 WHERE 조건절에서 자료의 검색시 조건을 주기 위해 사용한다.

= : 같은지를 묻는다.

WHERE EMP_ID = 19970824

EMP_ID의 컬럼의 값이 19970824인 ROW를 검색

< : 작은지를 묻는다.

WHERE EMP_ID < 19970824

EMP_ID의 컬럼의 값이 19970824보다 작은 ROW를 검색

<= : 작거나 같은지를 묻는다.

WHERE EMP_ID <= 19970824

EMP_ID의 컬럼의 값이 19970824보다 작거나 같은 ROW를 검색

> : 큰지를 묻는다.

WHERE EMP_ID > 19970824

EMP_ID의 컬럼의 값이 19970824보다 큰 ROW를 검색

>= : 크거나 같은지를 묻는다.

WHERE EMP_ID >= 19970824

EMP_ID의 컬럼의 값이 19970824보다 크거나 같은 ROW를 검색

<> : 다른지를 묻는다.

WHERE EMP_ID <> 19970824

EMP_ID의 컬럼의 값이 19970824와 다른 ROW를 검색

!= : 다른지를 묻는다.

WHERE EMP_ID != 19970824

EMP_ID의 컬럼의 값이 19970824와 다른 ROW를 검색

NOT : IN, BETWEEN, LIKE, ANY, ALL, EXISTS 등과 함께 쓰여 연산의 결과를 부정할 때 사용한다.

WHERE EMP_ID NOT IN ( 19970824, 19970825)

EMP_ID의 컬럼의 값이 19970824, 19970825 중 어디에도 해당되지 않는 ROW를 검색

LIKE 연산자

: 값의 일부를 주어주고 검색할 때 사용한다.

예1)

부서코드가 A로 시작되는 ROW를 검색한다.

SELECT EMP_ID, EMP_NAME, DEPT_CODE

FROM TEMP

WHERE DEPT_CODE LIKE 'A%';

예2)

부서코드 중에 A가 들어가는 ROW를 검색한다.

SELECT EMP_ID, EMP_NAME, DEPT_CODE

FROM TEMP

WHERE DEPT_CODE LIKE '%A%';

예3)

총 6자리 부서코드 중 2번째 자리에 A가 들어가는 ROW를 검색한다.

SELECT EMP_ID, EMP_NAME, DEPT_CODE

FROM TEMP

WHERE DEPT_CODE LIKE '_A____';

BETWEEN 연산자

: 범위를 지정하여 검색할 때 사용하는 연산자

예1)

사번이 1997로 시작하는 사원의 사번과 성명을 검색할 때 BETWEEN 연산자를 쓴다면 다음과 같이 된다.

SELECT EMP_ID, EMP_NAME

FROM TEMP

WHERE EMP_ID BETWEEN 19970101 AND 19979999;

예2)

BETWEEN 연산자를 사용하여 성명(EMP_NAME)이 'ㄱ'으로 시작되는 사람의 EMP_ID와 EMP_NAME를 조회해보자

SELECT EMP_ID, EMP_NAME

FROM TEMP

WJERE EMP_NAME BETWEEN '가' AND '나';

IN 연산자

: OR 조건으로 연결될 조건을 한번에 기술해 줄 수 있는 기능을 제공해주는 역할을 한다.

예)

SELECT EMP_ID, EMP_NAME

FROM TEMP

WHERE EMP_NAME IN ('홍길동','김길동');

참고 : 전문가로 가는 지름길 오라클 실습 P.50

ORDER BY

RECORD가 테이블에 저장될 때는 순서가 정해져 있지 않다.

입력한 순서대로 저장이 된다고 생각하는 경우도 있지만 항상 그런 것도 아니다.

우리가 만든 TEMP에 들어있는 자료를 조건절 없이 SELECT 해보면 INSERT 문장이 실행된 순서대로 불려 나오게 된다.

이런 경우는 자료의 양이 적어서 DB 상의 동일 BLOCK 상에 차곡차곡 쌓이거나,

삭제나 변경이 없어 자료가 운 좋게 입력 순서대로 쌓여있는 경우이다.

이런 상태에서 자료의 양이 많아지고 삭제가 일어나고

삭제된 자료가 다시 들어가고 하는 경우가 발생하다 보면 정말 입력한 순서도 아니고

그렇다고 특정 컬럼 순서도 아닌 말 그대로 무작위로 자료가 저장되게 된다.

참고로 자료가 DB의 BLOCK 상에 쌓이는 원리는 테이블 CREATE 시 지정하는

PCTFREE와 PCTUSED에 의해 결정된다.

이렇게 무순서로 저장된 자료를 특정한 순서에 맞게 보고자 할 때 사용하는 것이 ORDER BY이다.

예1)

SELECT EMP_ID, EMP_NAME

FROM TEMP

ORDER BY EMP_ID;

=> EMP_ID 컬럼을 기준으로 오름차순(ASCENDING) 정렬하여 보여준다. 

     만약 내림차순으로 결과를 보고 싶다면 

     ORDER BY EMP_ID DESC 라고 표기하면 된다.

예2)

SELECT LEV, EMP_ID, EMP_NAME

FROM TEMP

ORDER BY 1,2 DESC;

=> TEMP의 자료를 직급 명(LEV)에 ASCENDING하면서 결과 내에서 다시 사번 순으로 DESCENDING하게 하는 ORDER BY 하는 문장이다. 여기서 1이 의미하는 것은 첫번째 컬럼 즉 LEV이며, 2가 의미하는 것은 2번째 컬럼인 EMP_ID이다.

참고 : 전문가로 가는 지름길 오라클 실습 P.42

OPTIMIZER와 실행계획

1) OPTIMIZER

SELECT , DELETE, UPDATE, INSERT 등의 DML 을 수행할 때 OPTIMIZER 라는 것이 관여하게 된다.

OPTIMIZER는 수행하고자 하는 DML을 가장 효율적으로 처리할 수 있는 최적화 경로를 찾아주는 것이 주 임무이다.

OPTIMIZER는 최적화 경로를 찾아내기 위해서 내부적으로 많은 요인을 고려하게 된다.

어떤 테이블을 먼저 읽을 것인지?

테이블을 읽을 때 인덱스를 이용할 것인지?

인덱스를 이용한다면 어떤 인덱스를 사용할 것인지?

조인이 필요한 경우라면 어떤 방식으로 조인을 할 것인지?

등등의 여러 요인을 결정하여 실행계획을 작성한다.

이 실행계획에 의해 DML이 수행되고 우리는 OPTIMIZER가 선택한 최적화 경로인 수행 경로를 PLAN을 이용해 들여다 볼 수 있다.

2) 실행계획

데이터를 어떠한 경로로 통해 데이터베이스를 ACCESS 했는지를 보여주는 일종의 순서도이다.

오라클이 제공하는 UTLXPLAN.SQL을 실행시키면 PLAN_TABLE이라는 테이블이 생성된다.

이 상태에서 특정 SQL의 수행경로를 보고 싶다면 SQL 앞에 다음 명령을 붙여 실행하면 된다.

EXPLAIN PLAN SET STATEMENT_ID = '임의지정' FOR

수행이 완료되면 수행 경로가 PLAN_TABLE에 결과로 남게 된다.

이 때 STATEMENT_ID라는 컬럼에 위에서 지정한 값들이 들어간다.

참고 : 전문가로 가는 지름길 오라클 실습 P.39

조건절의 시작을 의미하는 것이 WHERE이다.

조건절은 질의문을 돌려 줘야할 결과값을 제한하는 역할을 한다.

여러 개의 RECORD 중 내가 찾고자 하는 RECORD 가 어떤 것인지에 대한 정보를

WHERE절을 통해 알려주고 그 조건에 맞는 결과만 돌려받고자 할 때 사용한다.

이러한 조건이 여러 개 연결 될 때는 AND 나 OR로 묶어서 계속 나열할 수 있다.

또한 두개 테이블 이상이 어떤 컬럼을 기준으로 조인이 걸린다면 그 조인 조건도 WHERE 절에 기술된다.

조건절이 없다면 FROM 절에 기술된 테이블의 모든 RECORD가 RETURN되어야 할 대상이 된다.

TEMP 테이블을 예로 들면 사원 중 취미가 '등산'인 직원만을 찾고자 할 때 라든지

생년월일이 언제 이후인 사람을 찾고자 할 때 WHERE를 사용한다.

뿐만 아니라 오라클의 RULE BASE OPTIMIZER MODE에서 인덱스를 이용하고자 할 때는

WHERE 절을 이용하여 INDEX의 사용을 유도하거나 제한할 수 있다.

예1)

SELECT EMP_NAME

FROM TEMP

WHERE HOBBY = '등산';

예2)

SELECT EMP_ID, EMP_NAME

FROM TEMP;

예3)

SELECT EMP_ID, EMP_NAME

FROM TEMP

WHERE EMP_ID > 0;

CONCATENATION 을 번역하면

연결이다.

함수의 일종으로 두개 이상의 문자열을 연결하여 

하나의 문자열을 만들어낼 때 사용한다.

방식은 CONCAT 함수를 사용하거나

합성연산자(||)를 이용한다.

예1)

SELECT EMP_NAME || '(' || LEV || ' )' 성명

FROM TEMP;

결과

성명

-----

김길동(부장)

홍길동(과장)

박문수(과장)

.

.

.

예2) 

SELECT EMP_NAME || '''' || LEV ||'''' 성명

FROM TEMP;

결과

성명

-----

김길동'부장'

홍길동'과장'

박문수'과장'

.

.

.

활용)

현재 유저에 존재하는 모든 OBJECTS를 DROP 시키는 명령문을 만들어 보자

SELECT 'DROP '|| OBJECT_TYPE || ' ' || OBJECT_NAME || ';'

FROM USER_OBJECTS;

결과

'DROP '|| OBJECT_TYPE || ' ' || OBJECT_NAME || ';'

------------------------------------------------------

DROP TABLE TDEPT;

DROP TABLE TEMP;

참고 : 전문가로 가는 지름길 오라클 실습 P.32 ~ P.33

ALIAS란 별명이다.

컬럼 명이 길거나 테이블 명이 길어서 

테이블이나 컬럼에 다른 이름을 부여하여 사용하는 것이 ALIAS이다.

ALIAS는 사용해서 편하기 때문에 사용하는 경우가 대부분 이지만 

반드시 사용을 해야만 하는 경우가 있다.

예제 1)

SELECT EMP_ID SABUN,

          EMP_NAME AS NAME

FROM TEMP;

EMPID SABUN : 컬러명 뒤에 한칸 이상을 띄우고 ALIAS를 주는 방법과

EMP_NAME AS NAME : 컬럼 이름과 ALIAS 사이에 AS라고 기술하는 방법이 있다.

예제 2)

SELECT EMP_ID,

          DEPT_CODE,

          DEPT_NAME

FROM TEMP,

         TDEPT

WHERE TDEPT.DEPT_CODE = TEMP.DEPT_CODE;

DEPT_CODE,

* 

ORA-00918 : 열의 정의가 애매합니다.

동일한 컬럼이 두개 테이블 이상에 존재할 때 어떤 테이블의 컬럼 인지를 명시하지 않으면

위와 같은 에러가 발생한다.

예제 3)

SELECT EMP_ID,

          TEMP.DEPT_CODE,

          DEPT_NAME

FROM TEMP,

         TDEPT

WHERE TDEPT.DEPT_CODE = TEMP.DEPT_CODE;

테이블명이 길어도 컬럼마다 테이블명을 명시하는 것이 쉽지만은 않다.

※ ALIAS를 반드시 사용할 때

SELF 조인일 경우 반드시 ALIAS를 사용해야 한다.

SELF 조인에서 자기 자신의 테이블과 조인이 일어나는 경우에는 모든 컬럼이 중복되게 된다.

예) 

사원과 자신이 속한 부서의 부서장을 읽어내는 SQL을 만들어내자.

SELECT A.EMP_ID,

             A.EMP_NAME,

             A.BOSS_ID,

             C.EMP_NAME

FROM TEMP A,

           TDEPT B,

           TEMP C

WHERE B.DEPT_CODE = A.DEPT_CODE

AND C.EMP_ID = B.BOSS_ID;

참고 : 전문가로 가는 지름길 오라클 실습 P.29 ~ P.30

NULL 이란 

컬럼의 값이 무엇인지 모른다는 뜻이다.

DML을 이용하여 자료를 다룰 때는 항상 NULL을 염두에 두고 다루어야 한다.

숫자(NUMBER)형 자료를 NULL과 연산(+ - * /)을 하면 결과는 항상 NULL 이 된다.

NULL이 포함될 우려가 있는 컬럼을 이용할 때는 항상 NVL 함수를 이용하여 값을 치환시켜 사용하는 습관을 들여야 한다.

숫자형 컬럼이나 변수에 NULL이 들어갈 우려가 있다면 0이나 1 등 다른 적절한 숫자로 치환을 한 후 연산에 사용한다.

문자형 컬럼이나 변수에 NULL이 들어갈 우려가 있다면 ' '(스페이스)나 다른 특정 문자값으로 치환하여 조건 절에 이용한다.

조건 절에서 비교 연산자를 사용할 때에도 NULL의비교는 일반 값과 다르게 비교한다.

A라는 문자형 컬럼의 값이 1과 같으나 또는 다르냐를 조건에서 비교할 때 다음과 같이 쓰게 된다.

같은지 비교 :  WHERE A = '1'

다른지 비교 : WHERE A <> '1'

하지만 A라는 문자형 컬럼의 값이 NULL 인지를 비교하고 싶을 때는 다음과 같이 사용하면 된다.

같은지 비교 : WHERE A IS NULL

다른지 비교 : WHERE A IS NOT NULL

절대로 A = NULL 또는 A <> NULL로 사용하면 안 된다.

에러가 발생하지는 않지만 의도한 바와 전혀 다른 결과가 나올 수 있기 때문이다.

참고 : 전문가로 가는 지름길  오라클 실습 P.24