[Oracle] sample schema 및 insert 하는 법 (HR , OE , PM ,SH ERD 확인 및 샘플 파일 다운 / 기본 제공 스크립트 적용)

1. Oracle 공식 홈페이지 참조

Sample Models and Scripts | Oracle 대한민국

Sample Models and Scripts | Oracle 대한민국

 

 

 

 

제공하는 sample  모델들이 총 4개가 있다 

4개의 스키마를 잘 정리해준 블로그가 있다 

Oracle Datatbase 스키마 HR, OE, SH의 ERD (taewan.kim)

Oracle Datatbase 스키마 HR, OE, SH의 ERD

 

 

HR ERD

 

 

 

SH

 

OE

 

 

2. Oralce 기본 제공 ($ORACLE_HOME 밑에 있는 스키마 적용)

 

script 위치

HR(Human Resource) 부분만 제공하고 있다 

[oracle@sua-oracle schema]$ pwd
/opt/oracle/product/19c/dbhome_1/demo/schema

[oracle@sua-oracle schema]$ ls -l
total 60
-rw-r--r--. 1 oracle oinstall  2322 Apr  3  2009 drop_sch.sql
drwxr-xr-x. 2 oracle oinstall   205 Apr 11 11:24 human_resources
drwxr-xr-x. 2 oracle oinstall     6 Apr 17  2019 log
-rw-r--r--. 1 oracle oinstall  1821 Apr 18  2019 mk_dir.sql
-rw-r-----. 1 oracle oinstall  1685 Nov  5  2015 mk_dir.sql.sbs
-rw-r--r--. 1 oracle oinstall 27570 Jul  1  2014 mkplug.sql
-rw-r--r--. 1 oracle oinstall 16894 Jul  1  2014 sted_mkplug.sql.dbl

 

 

 

hr_main.sql 실행하기 

권한 부여를 해야되기에 dba 권한이 필요하다 

[oracle@sua-oracle human_resources]$ sqlplus sua/sua as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jul 24 07:37:15 2024
Version 19.3.0.0.0

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


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

07:37:15 SYS@ORCL>
07:37:17 SYS@ORCL> @@hr_main.sql

 

 

아래 실행 내용은 터미널에 출력도 되면서 해당 폴더의 hr_main.log 에 기록이 된다 

07:37:17 SYS@ORCL> @@hr_main.sql

specify password for HR as parameter 1:
Enter value for 1: hr

specify default tablespeace for HR as parameter 2:
Enter value for 2: SUA_DAT

specify temporary tablespace for HR as parameter 3:
Enter value for 3: TEMP

specify log path as parameter 4:
Enter value for 4: ./


PL/SQL procedure successfully completed.


Elapsed: 00:00:00.00





User created.

Elapsed: 00:00:00.10

User altered.

Elapsed: 00:00:00.03

...

******  Creating REGIONS table ....

Table created.

Elapsed: 00:00:00.02

Index created.

Elapsed: 00:00:00.01

Table altered.

Elapsed: 00:00:00.04
******  Creating COUNTRIES table ....

Table created.

Elapsed: 00:00:00.02

Table altered.

Elapsed: 00:00:00.02
******  Creating LOCATIONS table ....

Table created.

Elapsed: 00:00:00.01

Index created.

Elapsed: 00:00:00.00

Table altered.

Elapsed: 00:00:00.01

Sequence created.

Elapsed: 00:00:00.01
******  Creating DEPARTMENTS table ....

Table created.

Elapsed: 00:00:00.00

Index created.

Elapsed: 00:00:00.01

Table altered.

Elapsed: 00:00:00.01

Sequence created.

Elapsed: 00:00:00.00
******  Creating JOBS table ....

Table created.

Elapsed: 00:00:00.01

Index created.

Elapsed: 00:00:00.02

Table altered.

Elapsed: 00:00:00.01
******  Creating EMPLOYEES table ....

Table created.

Elapsed: 00:00:00.03

Index created.

Elapsed: 00:00:00.01

Table altered.

Elapsed: 00:00:00.02

Table altered.

Elapsed: 00:00:00.01

Sequence created.

Elapsed: 00:00:00.00
******  Creating JOB_HISTORY table ....

Table created.

Elapsed: 00:00:00.01

Index created.

Elapsed: 00:00:00.00

Table altered.

Elapsed: 00:00:00.05
******  Creating EMP_DETAILS_VIEW view ...

View created.

Elapsed: 00:00:00.09

Commit complete.

Elapsed: 00:00:00.00

Session altered.

Elapsed: 00:00:00.00
******  Populating REGIONS table ....

1 row created.

Elapsed: 00:00:00.04

1 row created.

Elapsed: 00:00:00.01

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00
******  Populating COUNTIRES table ....

1 row created.

Elapsed: 00:00:00.01

1 row created.

Elapsed: 00:00:00.00

1 row created.

Elapsed: 00:00:00.00


....


Elapsed: 00:00:00.00

PL/SQL procedure successfully completed.

 

 

 

 

잘들어갔는지 확인을 해본다 

 

USER 체크

07:57:15 SUA@ORCL> connect hr/hr
Connected.
07:57:29 HR@ORCL>


07:57:29 HR@ORCL> select * from all_users where username = 'HR';
USERNAME                    USER_ID CREATED   COM O INH DEFAULT_COLLATION                                     IMP ALL
-------------------------------------------------------------------------------------------------------------------------------- ---------- --------- --- - --- ---------------------------------------------------------------------------------------------------- --- ---
HR                        110 24-JUL-24 NO  N NO  USING_NLS_COMP                                     NO  NO

 

 

Table 체크

07:59:39 HR@ORCL> select table_name from user_tables;

TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
COUNTRIES
REGIONS
LOCATIONS
DEPARTMENTS
JOBS
EMPLOYEES
JOB_HISTORY

7 rows selected.