[Oracle]오라클 Sample Schema 생성

오라클 샘플 스키마 생성 방법에 대해 알아보겠습니다. 

오라클 설치 시  오라클 스키마는 생성되지 않습니다. 

테스트하기 위한 Sample Schema 생성 방법에 대해 알아보겠습니다. 

 

Oracle Database Sample Schemas 다운로드

먼저 오라클에서 제공하는 샘플 스키마를 다운로드 받습니다. 

https://github.com/oracle/db-sample-schemas/tree/v19c

 

 

git clone https://github.com/oracle/db-sample-schemas.git

다운로드 파일 :db-sample-schemas

합계 128
drwxr-xr-x 11 oracle-docker oracle-docker  4096  3월 22 20:33 .
drwx------ 12 oracle-docker oracle-docker  4096  3월 22 20:33 ..
drwxr-xr-x  8 oracle-docker oracle-docker  4096  3월 22 20:33 .git
-rw-r--r--  1 oracle-docker oracle-docker   117  3월 22 20:33 CONTRIBUTING.md
-rw-r--r--  1 oracle-docker oracle-docker  1050  3월 22 20:33 LICENSE.md
-rw-r--r--  1 oracle-docker oracle-docker  5682  3월 22 20:33 README.md
-rw-r--r--  1 oracle-docker oracle-docker  5263  3월 22 20:33 README.txt
drwxr-xr-x  2 oracle-docker oracle-docker  4096  3월 22 20:33 bus_intelligence
drwxr-xr-x  2 oracle-docker oracle-docker  4096  3월 22 20:33 customer_orders
-rw-r--r--  1 oracle-docker oracle-docker  3633  3월 22 20:33 drop_sch.sql
drwxr-xr-x  2 oracle-docker oracle-docker  4096  3월 22 20:33 human_resources
drwxr-xr-x  2 oracle-docker oracle-docker  4096  3월 22 20:33 info_exchange
-rw-r--r--  1 oracle-docker oracle-docker  2740  3월 22 20:33 mk_dir.sql
-rw-r--r--  1 oracle-docker oracle-docker 27756  3월 22 20:33 mkplug.sql
-rw-r--r--  1 oracle-docker oracle-docker  7166  3월 22 20:33 mksample.sql
-rw-r--r--  1 oracle-docker oracle-docker  6592  3월 22 20:33 mkunplug.sql
-rw-r--r--  1 oracle-docker oracle-docker  6123  3월 22 20:33 mkverify.sql
drwxr-xr-x  3 oracle-docker oracle-docker  4096  3월 22 20:33 order_entry
drwxr-xr-x  2 oracle-docker oracle-docker  4096  3월 22 20:33 product_media
drwxr-xr-x  2 oracle-docker oracle-docker  4096  3월 22 20:33 sales_history
drwxr-xr-x  2 oracle-docker oracle-docker  4096  3월 22 20:33 shipping

sample-schemas scripts 수행 설명 (mksample.sql)

sqlplus sys/oracle@orclcdb as sysdba

@/opt/oracle/oradata/db-sample-schemas/mksample.sql oracle oracle hr oe pm ix sh bi users temp /opt/oracle/oradata/db-sample-schemas/logs/ ora19c

mksample.sql 에 서 입력 받는 인자 값들입니다. 

입력 변수는 볼드체로 표기했습니다. 

 sqlplus system/systempw@connect_string
@mksample systempw syspw hrpw oepw pmpw ixpw shpw bipw users temp /your/path/to/log/ connect_string

$ vi mksample.sql
Rem
Rem $Header: rdbms/demo/schema/mksample.sql.sbs /main/12 2015/03/19 10:23:26 smtaylor Exp $
Rem
Rem mksample.sql
......
......
......

PROMPT specify password for SYSTEM as parameter 1:
DEFINE password_system     = &1
PROMPT 
PROMPT specify password for SYS as parameter 2:
DEFINE password_sys        = &2
PROMPT 
PROMPT specify password for HR as parameter 3:
DEFINE password_hr         = &3
PROMPT
PROMPT specify password for OE as parameter 4:
DEFINE password_oe         = &4
PROMPT
PROMPT specify password for PM as parameter 5:
DEFINE password_pm         = &5
PROMPT
PROMPT specify password for IX as parameter 6:
DEFINE password_ix         = &6
PROMPT
PROMPT specify password for  SH as parameter 7:
DEFINE password_sh         = &7
PROMPT 
PROMPT specify password for  BI as parameter 8:
DEFINE password_bi         = &8
PROMPT 
PROMPT specify default tablespace as parameter 9:
DEFINE default_ts          = &9
PROMPT
PROMPT specify temporary tablespace as parameter 10: 
DEFINE temp_ts             = &10 
PROMPT 
PROMPT specify log file directory (including trailing delimiter) as parameter 11: 
DEFINE logfile_dir         = &11 
PROMPT 
PROMPT specify connect string as parameter 12: 
DEFINE connect_string     = &12 
PROMPT

 

mksample.sql 파일 수정 

스크립트를 수행하면 샘플 유저를 생성하는 과정에서 ORA-65096: invalid common user or role name 에러가 발생합니다 .

에러 발생을 막기 위해 ALTER SESSION SET "_ORACLE_SCRIPT"=true; 적용하겠습니다. 

mksample.sql 스크립트중 CONNECT system/&&password_system@&&connect_string 구문 밑에 ALTER SESSION SET "_ORACLE_SCRIPT"=true; 를 추가합니다. 

SQL> create user hr identified by hr;
create user hr identified by hr
            *
ERROR at line 1:
ORA-65096: invalid common user or role name

mksample.sql 실행 

SQL> @/opt/oracle/oradata/db-sample-schemas/mksample.sql oracle oracle hr oe pm ix sh bi users temp /opt/oracle/oradata/db-sample-schemas/logs/ ora19c

specify password for SYSTEM as parameter 1:

specify password for SYS as parameter 2:

specify password for HR as parameter 3:

specify password for OE as parameter 4:

specify password for PM as parameter 5:

specify password for IX as parameter 6:

specify password for  SH as parameter 7:

specify password for  BI as parameter 8:

specify default tablespace as parameter 9:

specify temporary tablespace as parameter 10:

specify log file directory (including trailing delimiter) as parameter 11:

specify connect string as parameter 12:

Sample Schemas are being created ...

mkdir: cannot create directory '/opt/oracle/oradata/db-sample-schemas/logs/': File exists

정상적으로 수행이 완료되면 log 디렉토리에 수행 스크립트 별로 로그가 남게 되고 

마지막으로 검증 결과가 mkverify_v3.log 와 같은 형태로 남게 됩니다. 

 

db-sample-schemas/logs]$ ll
합계 172
drwxr-xr-x  2         54321         54321  4096  3월 23 19:53 .
drwxrwxrwx 12 oracle-docker oracle-docker  4096  3월 23 20:27 ..
-rw-r--r--  1         54321         54321  6284  3월 23 19:53 bi_v3.log
-rw-r--r--  1         54321         54321  2075  3월 23 19:53 chan_v3.log
-rw-r--r--  1         54321         54321  2318  3월 23 19:53 coun_v3.log
-rw-r--r--  1         54321         54321  3558  3월 23 19:53 cust1v3.log
-rw-r--r--  1         54321         54321  2751  3월 23 19:53 dem1v3.log
-rw-r--r--  1         54321         54321  2622  3월 23 19:53 dmsal_v3.log
-rw-r--r--  1         54321         54321  5508  3월 23 19:53 ext_1v3.log
-rw-r--r--  1         54321         54321  6199  3월 23 19:52 hr_main.log
-rw-r--r--  1         54321         54321  7038  3월 23 19:53 ix_v3.log
-rw-r--r--  1         54321         54321 59687  3월 23 19:53 mkverify_v3.log
-rw-r--r--  1         54321         54321  5985  3월 23 19:52 oe_oc_v3.log
-rw-r--r--  1         54321         54321   599  3월 23 19:53 pm_main.log
-rw-r--r--  1         54321         54321  5119  3월 23 19:53 pm_p_lob.log
-rw-r--r--  1         54321         54321  3616  3월 23 19:53 prod1v3.log
-rw-r--r--  1         54321         54321  2667  3월 23 19:53 prom1v3.log
-rw-r--r--  1         54321         54321  3104  3월 23 19:53 sale1v3.log
-rw-r--r--  1         54321         54321  6049  3월 23 19:53 sh_v3.log
-rw-r--r--  1         54321         54321  4743  3월 23 19:53 time_v3.log

 

mkverify_v3.log

The Customer Orders(CO) schema 스크립트 수행 

mksample 에서는 co 생성 스크립트가 포함되어 있지 않습니다. 

별도 스크립트로 수행해야 테스트 샘플 스키마가 생성 되게 됩니다. 

수행 파일은  db-sample-schemas/customer_orders/co_main.sql 입니다. 

 

SQL> @co_main
Enter value for 1: co
Enter value for 2: orclcdb
Enter value for 3: users
Enter value for 4: temp
Dropping user
drop user co
          *
ERROR at line 1:
ORA-01918: user 'CO' does not exist


Creating user

Grant succeeded.


User altered.


User altered.

Connected.
Running DDL
Creating tables, constraints and views for Customer Orders
Creating tables

Table created.


Table created.


Table created.

 

Scott 계정 생성 

오라클 12c? 이후 부터는 scott 계정이 기본 적으로 생성되지 않습니다. 

livesql 사이트에서 테이블 정보와 데이터 확인후 샘플데이터를 생성하면 됩니다.

 

https://livesql.oracle.com/apex/livesql/file/content_O5AEB2HE08PYEPTGCFLZU9YCV.html

 

Scott 계정 스크립트

create user scott identified by tiger;
grant dba to scott;

conn scott/tiger

DROP TABLE DEPT;
CREATE TABLE DEPT
       (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
	DNAME VARCHAR2(14) ,
	LOC VARCHAR2(13) ) ;
	
DROP TABLE EMP;
CREATE TABLE EMP
       (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
	ENAME VARCHAR2(10),
	JOB VARCHAR2(9),
	MGR NUMBER(4),
	HIREDATE DATE,
	SAL NUMBER(7,2),
	COMM NUMBER(7,2),
	DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
INSERT INTO DEPT VALUES
	(10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
	(30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
	(40,'OPERATIONS','BOSTON');
INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
INSERT INTO EMP VALUES
(7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('13-JUL-87')-85,3000,NULL,20);
INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
INSERT INTO EMP VALUES
(7876,'ADAMS','CLERK',7788,to_date('13-JUL-87')-51,1100,NULL,20);
INSERT INTO EMP VALUES
(7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
INSERT INTO EMP VALUES
(7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);

DROP TABLE BONUS;
CREATE TABLE BONUS
	(
	ENAME VARCHAR2(10)	,
	JOB VARCHAR2(9)  ,
	SAL NUMBER,
	COMM NUMBER
	) ;
	
DROP TABLE SALGRADE;
CREATE TABLE SALGRADE
      ( GRADE NUMBER,
	LOSAL NUMBER,
	HISAL NUMBER );
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
COMMIT;

샘플 스키마 ERD Diagrams 

자세한 자료는 오라클 자료를 참고하시기 바랍니다. 

https://docs.oracle.com/en/database/oracle/oracle-database/19/comsc/schema-diagrams.html#GUID-D268A4DE-BA8D-428E-B47F-80519DC6EE6E