HighLight in your life~

아래의 내용은 일반적으로 Oralce을 공부할 때 사용하는 기초적인 테이블들입니다.

많은 기본 교육에서 아래의 환경에서 수업 진행을 합니다.

 오라클을 설치하면 해당 컴퓨터 어디에 Demobld.sql을 비롯하여 예제 테이블들이 위치할 것입니다. 그런데, 그 위치가 약간씩 상이한 것 같습니다. 그래서, 아래의 자료를 Upload하였습니다. 아래의 자료들은 제가 나중에 또 참고하게 될지 모르기 때문에 올린 자료입니다. 감사합니다. ^^

오라클 12c 이상의 버전에서 DB를 멀티테넌트 유형으로 생성한 경우에는 컨테이너 DB(CDB)와 플러거블DB(PDB)라는 구분이 있는데, CDB에 CONNECT하여 예제를 실행하면 'ORA-65096: 공통 사용자 또는 롤 이름이 부적합합니다.' 라는 에러가 발생한다. 이를 피하려면 CDB가 아닌 PDB에 접속하여 사용해야 한다.

create user scott identified by tiger;
alter user scott DEFAULT TABLESPACE USERS;
alter user scott QUOTA UNLIMITED ON USERS;
GRANT RESOURCE, CONNECT TO scott;

DROP TABLE EMP;
DROP TABLE DEPT;
DROP TABLE BONUS;
DROP TABLE SALGRADE;
DROP TABLE DUMMY;

CREATE TABLE EMP
       (EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7, 2),
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2));

INSERT INTO EMP VALUES
        (7369, 'SMITH',  'CLERK',     7902,
        TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20);
INSERT INTO EMP VALUES
        (7499, 'ALLEN',  'SALESMAN',  7698,
        TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30);
INSERT INTO EMP VALUES
        (7521, 'WARD',   'SALESMAN',  7698,
        TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250,  500, 30);
INSERT INTO EMP VALUES
        (7566, 'JONES',  'MANAGER',   7839,
        TO_DATE('2-APR-1981', 'DD-MON-YYYY'),  2975, NULL, 20);
INSERT INTO EMP VALUES
        (7654, 'MARTIN', 'SALESMAN',  7698,
        TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES
        (7698, 'BLAKE',  'MANAGER',   7839,
        TO_DATE('1-MAY-1981', 'DD-MON-YYYY'),  2850, NULL, 30);
INSERT INTO EMP VALUES
        (7782, 'CLARK',  'MANAGER',   7839,
        TO_DATE('9-JUN-1981', 'DD-MON-YYYY'),  2450, NULL, 10);
INSERT INTO EMP VALUES
        (7788, 'SCOTT',  'ANALYST',   7566,
        TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL,
        TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES
        (7844, 'TURNER', 'SALESMAN',  7698,
        TO_DATE('8-SEP-1981', 'DD-MON-YYYY'),  1500,    0, 30);
INSERT INTO EMP VALUES
        (7876, 'ADAMS',  'CLERK',     7788,
        TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES
        (7900, 'JAMES',  'CLERK',     7698,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),   950, NULL, 30);
INSERT INTO EMP VALUES
        (7902, 'FORD',   'ANALYST',   7566,
        TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),  3000, NULL, 20);
INSERT INTO EMP VALUES
        (7934, 'MILLER', 'CLERK',     7782,
        TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

오류 시에 아래와 같이 진행하면 됩니다.

INSERT INTO EMP VALUES
        (7369, 'SMITH',  'CLERK',     7902,
        DATE'1980-12-17',  800, NULL, 20);
INSERT INTO EMP VALUES
        (7499, 'ALLEN',  'SALESMAN',  7698,
        DATE'1981-02-20', 1600,  300, 30);
INSERT INTO EMP VALUES
        (7521, 'WARD',   'SALESMAN',  7698,
        DATE'1981-02-22', 1250,  500, 30);
INSERT INTO EMP VALUES
        (7566, 'JONES',  'MANAGER',   7839,
        DATE'1981-04-02',  2975, NULL, 20);
INSERT INTO EMP VALUES
        (7654, 'MARTIN', 'SALESMAN',  7698,
        DATE'1981-09-28', 1250, 1400, 30);
INSERT INTO EMP VALUES
        (7698, 'BLAKE',  'MANAGER',   7839,
        DATE'1981-05-01',  2850, NULL, 30);
INSERT INTO EMP VALUES
        (7782, 'CLARK',  'MANAGER',   7839,
        DATE'1981-06-09',  2450, NULL, 10);
INSERT INTO EMP VALUES
        (7788, 'SCOTT',  'ANALYST',   7566,
        DATE'1987-04-19', 3000, NULL, 20);
INSERT INTO EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL,
        DATE'1981-11-17', 5000, NULL, 10);
INSERT INTO EMP VALUES
        (7844, 'TURNER', 'SALESMAN',  7698,
        DATE'1981-09-08',  1500,    0, 30);
INSERT INTO EMP VALUES
        (7876, 'ADAMS',  'CLERK',     7788,
        DATE'1987-05-23', 1100, NULL, 20);
INSERT INTO EMP VALUES
        (7900, 'JAMES',  'CLERK',     7698,
        DATE'1981-12-03',   950, NULL, 30);
INSERT INTO EMP VALUES
        (7902, 'FORD',   'ANALYST',   7566,
        DATE'1981-12-03',  3000, NULL, 20);
INSERT INTO EMP VALUES
        (7934, 'MILLER', 'CLERK',     7782,
        DATE'1982-01-23', 1300, NULL, 10);

CREATE TABLE DEPT
       (DEPTNO NUMBER(2),
        DNAME VARCHAR2(14),
        LOC VARCHAR2(13) );

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');

CREATE TABLE BONUS
        (ENAME VARCHAR2(10),
         JOB   VARCHAR2(9),
         SAL   NUMBER,
         COMM  NUMBER);

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);

CREATE TABLE DUMMY
        (DUMMY NUMBER);

INSERT INTO DUMMY VALUES (0);

COMMIT;

CREATE UNIQUE INDEX dept_pk ON dept(deptno);
ALTER TABLE dept ADD CONSTRAINT dept_pk PRIMARY KEY(deptno);
CREATE UNIQUE INDEX emp_pk ON emp(empno);
ALTER TABLE emp ADD CONSTRAINT emp_pk PRIMARY KEY(empno);
CREATE UNIQUE INDEX emp_ename_uk ON emp(ename);
ALTER TABLE emp ADD CONSTRAINT emp_ename_uk UNIQUE(ename);
CREATE UNIQUE INDEX bonus_pk ON bonus(ename);
ALTER TABLE bonus ADD CONSTRAINT bonus_pk PRIMARY KEY(ename);
CREATE UNIQUE INDEX salgrade_pk ON salgrade(grade);
ALTER TABLE salgrade ADD CONSTRAINT salgrade_pk PRIMARY KEY(grade);

 Oracle 홈페이지에서 Oracle 10g를 다운로드 받아 설치하였다면 다음은 Database Configuration Assistant를 실행해야 한다.

Database 객체를 만들어야 한다.

 그 다음에는 예제 소스를 실행해야 한다. 예제 소스의 실행 방법은 다음과 같다.
@ C:\oracle\product\10.2.0\db_1\odp.net\samples\DataSet\RelationalData\setup\Demobld.sql

예제 테이블을 만들고 그것을 가지고 공부를 할 수 있다. 대부분 예제 테이블을 기준으로 교재들이 구성되었기 때문에 예제 테이블을 만들고 공부를 해야 한다. 위는 @ 에 Demobld.sql 경로를 추가해 준 것이다. 위의 '@ 경로'를 sqlplus에 접속하여 실행하면 예제 테이블이 만들어 진다.

 처음 Database Configuration Assistant를 실행하고 sql*plus 로 접속하여 DML을 하면 잘 작동한다. 그러나 컴퓨터를 다시 부팅하면 실행이 안된다. 그것은 DB가 shutdown 된 상태이기 때문이다.


ORA-01034: ORACLE not available
ORA-27101: shared memory realm deos not exist

 DB를 startup 시켜 주어야 한다.

cmd 창에서 sqlplus /nolong 혹은 sqlplus "/as sysdba"로 접속을 한다.
 그 뒤에 SQL> startup 한 뒤에 다시 원하는 user로 접속을 시도한다.
SQL> connect scott/tiger

한 가지 문제점이 발생할 수도 있다.
select * from emp; 를 했는데, 아무 값이 나오지 않을 수도 있다.

그것은 현재 윈도우의 날짜가 한글 형식으로 되어 있기 때문이다. 그래서 오라클 10g도 날짜가 한글형식으로 되어 있기 때문에, 만약,

INSERT INTO EMP VALUES
        (7369, 'SMITH',  'CLERK',     7902,
        TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20);

를 한다면 Error가 발생할 수도 있다. 이것은 3가지 방법으로 해결할 수 있다.

1. NLS_DATE_FORMAT 변경
SQL> select sysdate from dual;
SQL> alter session set nls_date_format ='DD-MON-YY';
SQL> select sysdate from dual;

2. 시스템 언어 변경
SQL> alter session set nls_language = 'AMERICAN';
SQL> select sysdate from dual;

3. 아예 입력 구문을 바꾸기
SQL> alter session set nls_date_format = 'DD-MON-YY';
SQL> INSERT INTO EMP VALUES
        (7369, 'SMITH',  'CLERK',     7902,
        '17-12월-80',  800, NULL, 20);

오늘 처음으로 혼자 공부를 해보았는데, 하루에 1시간 아니 30분씩이라도 꾸준히 공부해야 할 것 같다.

퇴근 하고 나서 앞으로 DB 공부를 해야 할 것 같다.