[Oracle] Oracle XE에서 사용자 생성 오류, ORA-65096: 공통 사용자 또는 롤 이름이 부적합합니다. invalid common user or role name
by breezyday 2022. 8. 2.
Oracle에서 개발 및 테스트를 위해 Oracle XE를 무료로 사용할 수 있도록 배포하고 있습니다. 그러나 몇 가지 제약사항들이 있는 데 그중에 하나가 사용자 생성 시 사용자 이름에 대한 제약이 있습니다.

1. ORA-65096 : 공통 사용자 또는 롤 이름이 부적합합니다.
CREATE USER C##myuser IDENTIFIED BY pwd123;
 

Oracle XE에서는 system 계정으로 로그인해서 사용자를 만들 때, C##을 붙인 사용자는 만들 수 있지만 일반 이름은 사용할 수 없는 제약이 있습니다. 그래서 위의 SQL은 문제가 없지만 아래와 같은 일반적인 SQL문은 오류가 발생합니다.

 

CREATE USER my_user IDENTIFIED BY pwd123
DEFAULT TABLESPACE myts
 

위의 SQL을 실행하면 오류가 발생하고, 오류 메시지는 아래와 같습니다.

 

명령의 28 행에서 시작하는 중 오류 발생 -
CREATE USER my_user IDENTIFIED BY pwd123
DEFAULT TABLESPACE myts
오류 보고 -
ORA-65096: 공통 사용자 또는 롤 이름이 부적합합니다.
65096. 00000 -  "invalid common user or role name"
*Cause:    An attempt was made to create a common user or role with a name
           that was not valid for common users or roles. In addition to the
           usual rules for user and role names, common user and role names
           must consist only of ASCII characters, and must contain the prefix
           specified in common_user_prefix parameter.
*Action:   Specify a valid common user or role name.
 

2. 해결 방법
Oracle XE에서 제약을 걸어두기 위해 system 계정으로 로그인할 경우 SESSION이 잠겨진 SESSION으로 접속하게 됩니다. 그래서 아래 명령을 사용하여 SESSION 설정을 변경하면 기존의 SQL 구문들을 제약 없이 사용할 수 있습니다.

 

ALTER SESSION SET "_ORACLE_SCRIPT"=true;
 

Oracle XE에서 개발 및 테스트를 하고 있는 개발자라면 system으로 로그인해서 DDL을 다룰 경우 위 명령을 먼저 실행해주면 다른 SQL문 작업 중에도 혼돈 없이 작업을 진행할 수 있겠습니다.

참고 : http://www.gurubee.net/lecture/1160

* EXPORT 옵션

  - userid : EXPORT를 실행시키고 있는 username/password 명

  - file : 생성되는 EXPORT덤프 파일명

  - full : 전체 데이터베이스를 EXPORT할것인가의 여부 (Full Level EXPORT) (Y/N 플래그)

  - owner : EXPORT 될 데이터베이스의 소유자 명 (User Level EXPORT)[owner=user]

  - tables : EXPORT될 테이블의 리스트(Table Level EXPORT) [tables=(table1, table2, ...)]

  - log : EXPORT 실행 과정을 지정된 로그 파일에 저장

1. Full Level EXPORT

  전체 데이터베이스가 엑스포트 된다. 모든 테이블스페이스, 모든 사용자, 또한 모든 객체, 데이터들이 포함 된다.

  exp  userid=system/manager file='C:\full.dmp' full=y

2. User Level EXPORT

  사용자 자신이 만든 모든 오브젝트를 그 user가 EXPORT하는 방법

  exp userid=scott/tiger  file='C:\scott.dmp'

  혹시 비밀번호 특수문자 있을시 :   exp userid='scott'/'tiger!^'  file='C:\scott.dmp'

  SYSTEM계정으로 특정 user소유의 오브젝트들을 EXPORT 하는 방법

  exp userid=system/manager owner=scott  file='C:\scottuser.dmp' 

3. Table Level EXPORT

  SYSTEM계정으로 특정 유저의 table을 EXPORT하는 예제 

  다른 계정으로 EXPORT시 table의 user명까지 지정해야 EXPORT가 성공한다.

  exp userid=system/manager file='C:exp.dmp' tables=(scott.EMP, scott.DEPT)

  scott user로 table을 몇 개만EXPORT하는 예제

  자신의 table을 EXPORT할 때에는 user명을 지정할 필요가 없다. 

  exp userid=scott/tiger file='C:\exp.dmp' tables=(EMP, DEPT) log=exp.log

참고 : http://www.gurubee.net/lecture/1161

* IMPORT 옵션

  - userid : IMPORT를 실생시키는 계정의 username/password 명

  - file : IMPORT될 EXPORT 덤프 파일명

  - show : 파일 내용이 화면에 표시되어야 할 것인가를 나타냄(Y/N 플래그)

  - ignore : IMPORT중 CREATE명령을 실행할 때 만나게 되는 에러들을 무시할 것인지 결정(Y/N 플래그)

  - full : FULL엑스포트 덤프 파일이 IMPORT 할때 사용한다.

  - tables : IMPORT될 테이블 리스트

  - commit : 배열(배열의 크기는 BUFFER에 의해 설정됩니다) 단위로 COMMIT을 할것인가 결정 기본적으로는 테이블 단위로 COMMIT을 한다.

  - fromuser : EXPORT덤프 파일로 부터 읽혀져야 하는 객체들을 갖고 있는 테이터베이스 계정

  - touser : EXPORT덤프 안에 있는 객체들이 IMPORT될 데이터베이스 계정

1. IMPORT 예제

   전체 데이터베이스가 IMPORT(Full Level Export file을 Import)

   imp userid=system/manager file='C:\full.dmp'  full=y

   User Level Export file을 Import

   imp userid=scott/tiger file='C:\scott.dmp'

   User Level Export file을 다른 계정으로 IMPORT

   scott 유저의 데이터를 EXPORT받아 test 유저에게 IMPORT하는 예제 

   exp userid=system/manager file='C:\scott.dmp' owner=scott

   imp userid=system/manager file='C:\scott.dmp' fromuser=scott touser=test   

출처: https://godlvkhj.tistory.com/215 [하은양 믿음군 효실맘 호홍홍집s:티스토리]

1. Docker

우선 도커가 설치되어있어야 합니다.
도커 설치는 기존 포스팅을 참고 해주시기 바랍니다!

$ docker -v
Docker version 20.10.17, build 100c701

도커 버전을 확인합니다.
도커가 정상 설치되었으므로 본격적으로 테스트에 사용할 Alpine 리눅스를 설치해 보겠습니다.

docker pull alpine

이미지 부터 다운로드 해줍니다.

docker run -d -it --name alpine-container alpine:latest sh

그다음 테스트에 사용할 알파인 리눅스 컨테이너를 생성해줍니다.

2. Docker 컨테이너로 파일 복사하기

호스트에 있는 파일을 도커 컨테이너의 특정 경로로 복사하는 명령어는 다음과 같다.

docker cp <복사할 파일 경로> <컨테이너 이름>:<컨테이너 내부 파일 경로>

만일 호스트에 있는 test.txt 파일을 컨테이너의 /test 경로로 복사한다면, 다음과 같이 사용한다.

docker cp test.txt alpine-container:/test

만일 디렉토리를 옮기고 싶을때는 어떻게 해야할까?
디렉토리를 복사하는 경우도 별다른 옵션없이 복사가 가능하다.

3. Docker 컨테이너에서 파일 가져오기

그럼 반대로 도커 컨테이너에 있는 파일을 호스트로 가져오려면 어떻게 해야할까?
매우 단순하다. 파일 경로와 컨테이너의 경로를 바꿔주면 된다.

docker cp <컨테이너 이름>:<컨테이너 내부 파일 경로> <복사할 파일 경로> 

만일 도커 컨테이너의 test.txt 파일을 호스트의 경로인 /test 으로 복사를 원한다면, 다음과 같이 사용한다.

docker cp alpine-container:/test.txt /test/

역시 동일하게 디렉토리도 별다른 옵션없이 복사가 가능하다.

docker ps 
실행중이 도커 보기

docker ps -a
전체 도커 보기

docker rm [컨테이너 id]
도커에서 컨테이너 삭제 하기

docker images

docker rmi[이미지id]
도커 이미지 삭제 하기

docker rmi -f [이미지id]

일단 docker 라는 것을 처음 접하고, container를 실행해 본 후 가장 당황 스러웠던 것은 

"container에 어떻게 접속하지?"

였다. ( 필자는 그랬다.. ) 

일단 container 자체가 하나의 Proccess 였기에 container가 daemon으로 실행하고 나면 여기에 어떻게 접속해야할지 난감한 상황이..

[ Status가 Up 상태인거 보니 뭔가 실행 중이다. 접속은? ]

접속하는 방법은 매우 간단하다. 

docker exec 명령을 이용하면 된다.  docker exec 는 container에 특정 명령을 실행할 수 있는 것인데 이때 명령을 /bin/bash 라고 하면 된다. 

우리가 "접속" 하고 싶다는 의미는 해당 container 의 shell 에 접속하겠다는 의미이다. 

주의해야할 것은 docker exec 명령을 할때 옵션으로 -it 라고 덧붙여 주어야 한다. 이는 STDIN 표준 입출력을 열고 가상 tty (pseudo-TTY) 를 통해 접속하겠다는 의미이다. 

위와 같이 hostname이 해당 container id로 바뀐 것을 볼 수 있다. 즉, container 내부에 접속한 상태라는 것이다. 

마지막으로 접속을 종료할때는 간단히 exit 명령을 통해 가능하다. 

출처: https://bluese05.tistory.com/21 [ㅍㅍㅋㄷ:티스토리]

0. 서론

데이터베이스전문가(SQLP) 자격증 취득을 위해 공부하던 중, mac M1에 오라클을 띄워서 실습 공부를 해야할 필요가 있었다. 그러나, 맥북 Apple Silicon(M1)에서는 로컬로 오라클을 띄울 수가 없다.

그래도, 아래 블로그를 찾아 Docker/Colima를 활용해서 Oracle을 띄울 수 있게 되었다.

참고 블로그

1. 설치

1.1 colima

docker desktop은 매우 무거운 프로그램이어서, 가벼운 CLI 환경에서 도커를 실행할 수 있는 오픈 소스인 colima를 사용한다.

brew install colima 

1.2 docker

docker desktop은 docker 공식 웹 홈페이지에서 설치할 수 있다.
Docker 웹 홈페이지

아니면, brew로 설치해도 된다.

# Docker Desktop
brew install --cask docker 

# Docker Engine
brew install docker

2. 실행

colima와 docker 모두 설치 후, colime를 x86_64 환경으로 띄워준다.

colima start --memory 4 --arch x86_64 

정상적으로 실행되고 있는지 확인하려면, docker ps나 images로 확인한다.

docker ps
docker images

모든 명령어를 포함해서 docker run
아직 미숙한 사람을 위해 컨테이너명까지 명시

# 컨테이너명 = oracle으로 명시
docker run --restart unless-stopped --name oracle -e ORACLE_PASSWORD=pass -p 1521:1521 -d gvenzl/oracle-xe 


#-d 를 넣으며 백단에서 계속 실행되...
docker run --name oracle -d -p 1521:1521 jaspeen/oracle-xe-11g

3. 활용

docker log 확인

# docker logs -f (컨테이너명)
docker logs -f oracle

4. DB접속

접속정보

• host: localhost
• database: xe (orcl로 되어 있으면 바꿔줘야 함)
• port: 1521
• user: system
• password: XXXX

5. 샘플계정

도커에는 샘플계정이 포함되어 있지 않기 때문에 만들어줘야 한다.

# 한 줄씩 생성
docker exec -it oracle sqlplus
CREATE USER scott identified by tiger;
GRANT CONNECT, resource, dba to scott;

생성 후에는 아래 쿼리로 유저가 정상적으로 생성 된 것을 확인

select username from dba_users where username = 'SCOTT';

SCOTT 접속

docker exec -it oracle sqlplus

https://velog.io/@gpg/setting-docker-oracle

docker ps 
실행중이 도커 보기

docker ps -a
전체 도커 보기

docker rm [컨테이너 id]
도커에서 컨테이너 삭제 하기

docker images

docker rmi[이미지id]
도커 이미지 삭제 하기

docker rmi -f [이미지id]

저 중에 하나를 docker pull 명령을 이용해서 받으면 된다.

오라클 도커 컨테이너 실행

이제 받은 오라클 도커 이미지를 이용해서 오라클 인스턴스를 띄워보자.

이제 sqlplus 를 실행해서 오라클 인스턴스에 붙어보자

다른 테이블의 SELECT 한 결과물을 참조하여 현재 테이블을 UPDATE 해야 하는 상황이 있습니다.

Oracle, MS-SQL, Maria DB, Mysql DBMS마다 다른 문법을 지원하므로 방법도 각자 다릅니다.

저는 Oracle을 사용 중인데 다른 DBMS 보다 조금 문법이 어려운 것 같네요 이 방법에 한해서는요 ㅎㅎ

table master, table user 두 개의 테이블이 있다고 가정하겠습니다.

master 테이블의 Status을 user 테이블의 Status의 값을 참조하여 변경하는 쿼리를 작성해보겠습니다.

MS-SQL

UPDATE
    master
SET
    master.status = cust.status
FROM
    master m
INNER JOIN
    cust c
ON 
    m.id = c.id;

MySQL and MariaDB

UPDATE
    master m,
    cust c
SET
    m.status = c.status
WHERE
    m.id = c.id;

Oracle

단순 업데이트만 사용하는 방법

EXISTS를 사용하지 않으면 첫 번째 서브 쿼리의 id 키 값이 매칭 되지 않는 값들은 master의 status 값이 null로 변경됩니다. 

UPDATE master m
SET    m.status = (select c.status
                  from cust c 
                  where m.id = c.id)
WHERE EXISTS (select 1
              from cust c
              where m.id = c.id)

merge into를 구문을 사용하는 방법

MERGE INTO master m
USING cust c
ON (m.id = c.id)
WHEN MATCHED THEN UPDATE SET m.status = c.status;

Oracle 실습

master table

cust table

이 쿼리를 실행해보겠습니다.

UPDATE master m
SET    m.status = (select c.status
                  from cust c 
                  where m.id = c.id)
WHERE EXISTS (select 1
              from cust c
              where m.id = c.id)

우리가 기대하는 결과는 master 테이블의 test1, test3, test5의 status값이 FALSE로 변환되는 결과입니다.

update 결과

올바르게 변환됩니다!

그런데 만약 아래와 같이 EXIST 구문을 빼면 어떻게 될까요?

UPDATE master m
SET    m.status = (select c.status
                  from cust c 
                  where m.id = c.id)

update 결과

test2와 test4는 id 키 값이 매칭되는 것이 없으므로 status의 값이 null로 변경됩니다. 기본적으로 update 구문에서 where 조건을 설정하지 않으면 전체 테이블을 update하게 되어서 나타나는 결과입니다.

merger into 쿼리의 결과 역시 제대로 업데이트 되는 것을 볼 수 있습니다!

MERGE INTO master m
USING cust c
ON (m.id = c.id)
WHEN MATCHED THEN UPDATE SET m.status = c.status;

merge into 결과

오라클에서 테이블의 값을 수정하기 위해서는 UPDATE 문을 사용하면 된다. WHERE 절에 해당하는 행이 여러 개인 경우 한 번에 여러 건의 행이 SET 절의 값으로 변경된다. UPDATE 문은 시스템 운영 시 자주 사용하므로 다양한 방법을 익혀두는 것이 좋다.

오라클 UPDATE 문

기본 사용법

UPDATE emp
   SET job = 'MANAGER'
     , deptno = 20
 WHERE empno = 7566

위는 WHERE 절의 empno에 해당하는 데이터의 job, deptno 컬럼을 수정하는 쿼리이다.  empno는 PK이기 때문에 한건의 데이터만 수정되지만 PK가 아니고 여러 건이 조회되면 여러 건의 데이터가 한 번에 수정된다.

SET 절에 서브쿼리 사용법

UPDATE emp a
   SET a.sal  = (SELECT MAX(aa.sal) FROM emp aa WHERE aa.job = a.job)
     , a.comm = (SELECT MAX(aa.comm) FROM emp aa WHERE aa.job = a.job)
 WHERE a.job = 'ANALYST'

다른 테이블에서 데이터를 조회하여 UPDATE를 해야 하는 경우가 있다. 조인을 사용하면 편리할 거 같지만 UPDATE문에서 조인은 제약사항이 많기 때문에 서브 쿼리를 활용하면 쉽게 해결되는 경우가 많다.

UPDATE emp a
   SET (a.sal, a.comm)  = (SELECT MAX(aa.sal), MAX(aa.comm)
                             FROM emp aa 
                            WHERE aa.job = a.job)
 WHERE a.job = 'ANALYST'

동일한 서브쿼리를 반복해서 사용해야 하는 경우 컬럼을 묶어서 한 번의 서브 쿼리로 업데이트 값을 입력할 수 있다.

WHERE 절에 IN 사용법

UPDATE emp
   SET sal = 3000
 WHERE empno IN (SELECT aa.empno
                   FROM emp aa
                      , dept bb
                  WHERE aa.job = 'ANALYST'
                    and aa.deptno = bb.deptno)

업데이트 범위를 정할 때 다른 테이블을 참조해야 할 경우 IN을 사용하여 데이터를 포함시키거나 NOT IN을 사용하여 제외시킬 수 있다.

UPDATE emp
   SET comm = 500
 WHERE (job, deptno) IN (SELECT aa.job, aa.deptno
                           FROM emp aa
                          WHERE aa.deptno = 30)

IN의 서브 쿼리에서 키값이 여러 개인 경우 컬럼을 묶어서 조건을 맵핑할 수 있다.

UPDATE emp a
   SET a.comm = 500
 WHERE a.ROWID IN (SELECT aa.ROWID
                     FROM emp aa
                    WHERE aa.deptno = 30)

동일한 데이블을 사용한다면 ROWID를 키로 사용할 수 있다.

WHERE 절에 EXISTS 사용법

UPDATE emp a
   SET a.comm = 500
 WHERE EXISTS (SELECT 1
                 FROM emp aa
                WHERE aa.empno = a.empno
                  AND aa.deptno = 30)

EXISTS를 사용하면 쉽게 데이터를 제외시키거나 포함시킬 수 있다. IN과 비슷한 역할을 하지만 성능면에서 조금 더 나을 수 있다.

UPDATE emp a
   SET a.comm = 500
 WHERE NOT EXISTS (SELECT 1
                     FROM emp aa
                    WHERE aa.empno = a.empno
                      AND aa.deptno = 30)

조인하여 UPDATE 하는 방법

UPDATE (SELECT a.sal
             , a.comm
             , b.sal AS sal_2
             , b.comm AS comm_2
          FROM emp a
             , emp_man b
         WHERE a.empno = b.empno)
   SET sal = sal_2 
     , comm = comm_2

두 개의 테이블을 조인해서 업데이트해야 할 경우 사용하는 방법이다. 위의 쿼리는 오라클 11.2 이하의 버전에서는 오류가 발생하며 아래의 MERGE 문을 사용해야 한다.

MERGE 
 INTO emp a
USING emp_man b
   ON (a.empno = b.empno)
 WHEN MATCHED THEN
      UPDATE 
         SET a.sal = b.sal
           , a.comm = b.comm;

MERGE 문은 오라클 9i부터 사용할 수 있다. BYPASS_UJVC 힌트를 사용하는 방법이 있으나 11g부터 공식적으로 사용을 중단하였기 때문에 특별한 경우가 아니면 MERGE 문을 사용할 것을 권장한다.