Oracle PDB 인스턴스에 연습 데이터용 샘플 스키마 “SCOTT” 작성하기

“SCOTT” 스키마가 디폴트로 설치된 마지막 오라클 버전은 11g(11.2.0.x) 입니다. 11g 버전 이후 Oracle 12c(12.1.0.2) 버전부터 “SCOTT” 스키마는 기본적으로 설치되지 않도록 변경되었습니다. 하지만, 개발 목적으로 사용할 수 있도록 설치 파일은 포함하고 있습니다.

그리고 Oracle 12c(12.1.0.2) 버전 부터는 인스턴스 종류도 다음 두가지 구조중 하나를 선택해서 설치 할 수 있도록 변경되었습니다.

일반 데이터베이스 인스턴스(General Purpose Database Instance)
- 하나의 데이터베이스 인스턴스에 하나의 데이터베이스를 갖는 기존 구조
콘테이너 데이터베이스 인스턴스(Container Database Instance)
- 하나의 데이터베이스 인스턴스에 여러 개의 데이터베이스를 갖는 구조
- 하나 이상의 사용자 정의 데이터베이스 개체 (PDB Instance)를 포함
- PDB Instance는 독립적인 데이터베이스 개체
- CDB Instance와 공유되는 메타데이터와 리소스를 사용

이 글에서는 콘테이너 데이터베이스 인스턴스(Container Database Instance)안에 포함된 사용자 정의 데이터베이스 개체 (PDB Instance)에 연습용으로 “SCOTT” 스키마를 추가하는 방법을 소개합니다.

참고로 일반 데이터베이스 인스턴스(General Purpose Database Instance)에 연습용으로 “SCOTT” 스키마를 추가하고자 한다면 “Oracle일반 데이터베이스 인스턴스에 연습 데이터용 샘플 스키마 “SCOTT” 작성하기” 글을 참조하십시오.

“SCOTT” 스키마 존재 확인하기
“SCOTT” 스키마 작성하기
“SCOTT” 스키마 접속하기
“SCOTT” 스키마의 테이블 확인하기

“SCOTT” 스키마 존재 확인하기

12c 버전 이후 컨테이너 데이터베이스 인스턴스(CDB)를 사용한다면 플러그인 데이터베이스(PDB)에 연결해서 확인합니다.

C:\> sqlplus / as sysdba

show con_name 명령으로 현재 컨테이너 이름을 표시합니다. 실행 결과, 컨테이너 데이터베이스 인스턴스(CDB) 의 CDB$ROOT 접속을 확인할 수 있습니다.

SQL> show con_name
CON_NAME
------------------------------
CDB$ROOT

show pdbs 명령으로 모든 플러그인할 수 있는 데이터베이스(PDB)를 표시합니다.

SQL> show pdbs
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 XEPDB1                         READ WRITE NO

“SCOTT” 스키마 존재를 확인할 플러그인 데이터베이스(PDB) XEPDB1를 현재 컨테이너로 선택합니다.

SQL> ALTER SESSION SET CONTAINER=XEPDB1; 
세션이 변경되었습니다.

show con_name 명령으로 현재 컨테이너 이름을 표시합니다. 실행 결과, XEPDB1 가 현재 컨테이너로 변경되었음을 확인할 수 있습니다.

SQL> show con_name
CON_NAME
------------------------------
XEPDB1

다음 SQL문을 사용하여 “SCOTT” 스키마가 존재하는지 확인합니다.

SQL> select username from all_users where username = 'SCOTT';
선택된 레코드가 없습니다.

확인한 결과, Oracle 12c(12.1.0.2) 버전 이전에는 기본으로 샘플 스키마 “SCOTT” 가 만들어져 있었습니다 만 최신 버전에서는 다음 SQL문을 실행해서 만들어야 합니다.

“SCOTT” 스키마 작성하기

현재 컨테이너 선택하기

“SCOTT” 스키마를 작성할 플러그인 데이터베이스(PDB) XEPDB1를 현재 컨테이너로 선택합니다.

C:\> sqlplus / as sysdba
SQL> ALTER SESSION SET CONTAINER=XEPDB1; 
세션이 변경되었습니다.

show con_name 명령으로 현재 컨테이너 이름을 표시합니다. 실행 결과, XEPDB1 가 현재 컨테이너로 변경되었음을 확인할 수 있습니다.

SQL> show con_name
CON_NAME
------------------------------
XEPDB1

“SCOTT” 스키마 접속 정보 변경

플러그인 데이터베이스(PDB)에서는 ?/rdbms/admin/utlsampl.sql 을 실행하기 전에 의 파일을 열어 접속 정보를 수정합니다.

수정전	CONNECT SCOTT/tiger
수정후	CONNECT SCOTT/tiger@localhost/XEPDB1

수정전 접속 정보는 root 컨테이너 데이터베이스(CDB$ROOT) 에 접속합니다. 그러나 “SCOTT” 스키마는 플러그인 데이터베이스(XEPDB1) 에서 만들었기 때문에 에러 종료합니다.

‘@?/rdbms/admin/utlsampl.sql‘ 에서 물음표 (?) 는 ORACLE_HOME을 나타냅니다. 이 스크립트가 실행될 때, 실제 ORACLE_HOME 경로를 가지고 물음표 (?)가 대체되게 됩니다. 예를 들어, ORACLE_HOME 경로가 /app/oracle/product/12.2.0/dbhome_1이면, 스크립트는 실제로 @/app/oracle/product/12.2.0/dbhome_1/rdbms/admin/utlsampl.sql 파일을 실행하게 됩니다. 이처럼 물음표 (?)는 경로 지시자의 역할을 하고 있어, 스크립트 작성 시 유용하게 사용될 수 있습니다.

“SCOTT” 스키마 작성 스크립트 실행하기

다음과 같이 ?/rdbms/admin/utlsampl.sql 파일을실행합니다.

SQL> @?/rdbms/admin/utlsampl.sql
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0에서 분리되었습니다.

다음과 같이 “SCOTT” 스키마가 생성되었는지 확인합니다.

C:\> sqlplus / as sysdba
SQL> ALTER SESSION SET CONTAINER=XEPDB1; 
세션이 변경되었습니다.
SQL> select username from all_users where username = 'SCOTT';
USERNAME
----------------
SCOTT

주의 사항

?/rdbms/admin/scott.sql 파일도 “SCOTT” 스키마를 만드는 sql스크립트 입니다만 패스워드가 대문자이고 to_date포맷이 지정되어 있지 않습니다. 수정 이력도 1995년이 마지막 입니다. XE 18c 버전에서 는 오류가 발생해서 사용할 수 없습니다.

“SCOTT” 스키마 접속하기

다음과 같이 “SCOTT” 스키마에 접속하여 작성된 테이블을 확인합니다. 주의) 스키마명과 패스워드는 작성 할때 대문자로 입력했으므로 대소문자 구분 설정이 되어있는 데이터베이스에서는 대문자로 입력합니다.

C:\>sqlplus SCOTT/tiger@localhost/xepdb1
SQL> select table_name from user_tables;
TABLE_NAME
----------------
DEPT
EMP
BONUS
SALGRADE

“SCOTT” 스키마의 테이블 확인하기

BONUS 테이블

select * from bonus;

ENAME	JOB	SAL	COMM

(데이터 없음)

DEPT 테이블

select * from dept;

DEPTNO	DNAME	LOC
10	ACCOUNTING	NEW YORK
20	RESEARCH	DALLAS
30	SALES	CHICAGO
40	OPERATIONS	BOSTON

EMP 테이블

select * from emp;

EMPNO	ENAME	JOB	MGR	HIREDATE	SAL	COMM	DEPTNO
7369	SMITH	CLERK	7902	1980/12/17	800		20
7499	ALLEN	SALESMAN	7698	1981/2/20	1600	300	30
7521	WARD	SALESMAN	7698	1981/2/22	1250	500	30
7566	JONES	MANAGER	7839	1981/4/2	2975		20
7654	MARTIN	SALESMAN	7698	1981/9/28	1250	1400	30
7698	BLAKE	MANAGER	7839	1981/5/1	2850		30
7782	CLARK	MANAGER	7839	1981/6/9	2450		10
7839	KING	PRESIDENT		1981/11/17	5000		10
7844	TURNER	SALESMAN	7698	1981/9/8	1500	0	30
7900	JAMES	CLERK	7698	1981/12/3	950		30
7902	FORD	ANALYST	7566	1981/12/3	3000		20
7934	MILLER	CLERK	7782	1982/1/23	1300		10