Ⅰ. Oracle SQL Developer 환경설정 하기
1. 인코딩 UTF-8로 변경
-- 도구 - 환경설정
-- 인코딩 MS949(예전에 사용했던 한글 지원) 말고 UTF-8(UTF8 아님)로 변경
2. 기본 주석 색상, 글꼴, 행 여백 변경
-- 도구 - 환경설정
-- 기본 주석 색상 변경
-- 행 여백(행 번호 표시)
-- 체크하면 옆 여백에 번호가 표시된다.
-- 글꼴은 취향대로 변경하면 된다.
Ⅱ. 계정 새로 만들기
접속이름 local_sys : 내 오라클 서버에 sys라는 계정으로 붙어서 쓰겠다
local(내 pc)
사용자이름 sys
비밀번호 eclass
롤 기본값 말고 sysdba(시스만큼은)
호스트이름 localhost(내 pc ip)
포트 1521
sid는 기본값 xe(express version
테스트 (상태: 성공) - 저장 (저장하면 왼쪽에 접속이름과 접속 세부정보가 뜬다)
접속이름을 잡고 접속
save-C-하위디렉토리생성(오라클수업)-파일이름: sys에서작업한것.sql (저장)
새접속- 접속이름(local_system) - 사용자이름(system) - 비밀번호 (eclass) - 비밀번호 저장 - 롤: 기본값 -
테스트(성공) - 저장 - 시스템 잡고 접속
showuser; 저장 - 오라클수업 - system에서작업한것.sql - 저장
-- 명령문 실행 단축키 : 블록 잡고 Ctrl + Enter
-- SYSTEM : 오라클 데이터베이스의 관리자 계정(부)
-- 오라클 서버를 구동/중지를 할 수 없다.
SYSTEM
show user;
▷ 결과
USER이(가) "SYSTEM"입니다.
-- SYS : 오라클 데이터베이스의 관리자 계정(정)
-- 오라클 서버를 구동/중지를 할 수 있다.
SYS
show user;
▷ 결과
USER이(가) "SYS"입니다.
현재 생성되어있는 오라클 사용자 정보 조회하기
SYS
select *
from dba_users;
-- ACCOUNT_STATUS : OPEN → 사용할 수 있다.
-- HR(Human Resource) : 인사 관리에 대한 샘플
-- EXPIRED & LOCKED : 비밀번호 유효기간이 끝났고, 잠겨 있다.
1) 잠겨진(locked) HR계정을 사용 가능하도록 풀어주도록 한다.
alter user 계정명 account unlock;
SYS
alter user HR account unlock;
2) HR 계정에게 새로운 암호를 부여하도록 한다.
alter user 계정명 identified by 새로운 암호;
SYS
alter user HR identified by eclass;
-- HR의 ACCOUNT_STATUS의 값이 OPEN 으로 되었다.
-- OPEN 으로 되어야만 사용가능한 오라클 계정이다.
새 접속
접속이름 (local_hr) 사용자이름 (hr) 비밀번호(eclass) 비밀번호 저장 롤: 기본값 - 테스트(성공) - 저장 - hr 선택하여 접속
-- 오라클 데이터베이스의 일반 사용자 계정
-- 오라클 데이터베이스를 관리할 수 없다.
HR
show user;
▷ 결과
USER이(가) "HR"입니다.
저장 - C 오라클수업 - hr에서작업한것 - 저장
HR
select *
from dba_users;
-- sys와 system은 결과가 출력되지만 hr은 권한이 없으므로 출력되지 않는다.
sys 계정으로 구동/중지 할 수 있는지 알아보기
-- Oracle SQL Developer 말고 CMD창에서 진행한다.
CMD
sqlplus /nolog ==> 오라클을 설치하였기 때문에 가능
show user ==> USER is ""
conn sys/eclass as sysdba (==> Connected.) ==> 오라클 서버가 정상적으로 구동인 상태에서 접속을 한 것
- show user (==>USER is "SYS")
shutdown immediate ==> 오라클 서버를 구동중지 시키는 것
==> Database closed.
Database dismounted.
ORACLE instance shut down.
-- hr에서작업한것 / system에서작업한것 에 show user;를 실행해도 아무 결과 출력되지 않는다. → 오라클 서버 중지
CMD
cmd - conn system/eclass
==> ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Process ID: 0
Session ID: 0 Serial number: 0
Warning: You are no longer connected to ORACLE.
-- 현재 오라클 서버가 구동이 중지 되었으므로 오라클 서버에 접속이 불가하다.
SQL> conn sys/eclass as sysdba
Connected to an idle instance. --> 오라클 서버가 구동이 중지된 상태에서 접속을 한 것
SQL> startup --> 오라클 서버를 구동시작 해 주는 것.
ORACLE instance started.
Total System Global Area 1068937216 bytes
Fixed Size 2260048 bytes
Variable Size 616563632 bytes
Database Buffers 444596224 bytes
Redo Buffers 5517312 bytes
Database mounted.
Database opened.
SQL> conn system/eclass
Connected.
SQL> show user
USER is "SYSTEM"
SQL> shutdown immediate
ORA-01031: insufficient privileges --> system 계정은 오라클 서버를 구동중지 할 수 있는 권한이 없다.
SQL> exit
Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
현재 오라클 서버에 접속한 사용자(지금은 HR)가 만든(사용자 소유의) 테이블과 뷰의 이름 조회하기
HR
select *
from tab;
▶▶ 참고
-- 파일 이름과 상관없이 무엇과 연결되어 있는지가 중요하다. → 꼭 주의!
▷ 대륙정보를 보여주는 테이블
HR
select *
from REGIONS;
▷ 국가정보를 보여주는 테이블 조회
HR
select *
from COUNTRIES;
-- REGION도 나타내 준다.
▷ 부서가 위치한 위치(주소)정보를 보여주는 테이블
HR
select *
from LOCATIONS;
-- COUNTRY도 나타내 준다.
▷ 부서정보를 보여주는 테이블
HR
select *
from DEPARTMENTS;
▷ 사원정보를 보여주는 테이블
HR
select *
from EMPLOYEES;
★★★ SELECT 문의 처리 순서 ★★★ → 꼭 외워둘 것!
| 부서번호 | 부서명 | 관리자번호 | 부서위치코드 |
| 10 | 관리부 | 1001 | 100 |
| 20 | 생산부 | 2001 | 200 |
| 30 | 연구부 | 3001 | 300 |
-- 부서번호, 부서명, 관리자번호, 부서위치코드들을 column(== field, 열)이라고 한다.
-- 10, 관리부, 1001, 100과 같이 어떤 하나의 부서정보를 row(== record, 행)이라고 한다.
-- row(== record, 행)들이 모여있는 것을 table(== 테이블)이라고 한다.
employees 테이블에 있는 컬럼에 대한 정보 조회
HR
describe employees;
또는
HR
desc employees;
-- 데이터 타입은 정수형, 실수형 구분 없이 숫자는 NUMBER로 통일
| 이름(컬럼명) | 널? | 유형(데이터타입) |
|
EMPLOYEE_ID (사원번호) |
NOT NULL(필수입력사항) |
NUMBER(6) -999999 ~ 999999 NUMBER(자리수) |
|
FIRST_NAME (이름) |
VARCHAR2(20) 문자열 VARCHAR2(byte수) VARCHAR2(20)은 문자열 최대 20byte까지만 들어올 수 있다. 한글 1글자는 2byte이므로 한글은 최대 10글자까지만 들어올 수 있다. NVARCHAR2(20)은 문자열 최대 20글자까지만 들어올 수 있다. 한글이든 영문이든 오로지 글자갯수가 최대 20개까지 들어올 수 있다. |
|
|
LAST_NAME (성) |
NOT NULL(필수입력사항) | VARCHAR2(25) |
|
(이메일) |
NOT NULL(필수입력사항) | VARCHAR2(25) |
|
PHONE_NUMBER (전화번호) |
VARCHAR2(20) | |
|
HIRE_DATE (입사일자) |
NOT NULL(필수입력사항) | DATE |
|
JOB_ID (직종아이디) |
NOT NULL(필수입력사항) | VARCHAR2(10) |
|
SALARY (기본급여) |
NUMBER(8,2) NUMBER(전체자리수, 소수부자리수) NUMBER(8,2)는 -999999.99 ~ 999999.99 |
|
|
COMMISSION_PCT (커미션(수당)퍼센티지) |
NUMBER(2,2) NUMBER(2,2)는 -0.99 ~ 0.99 |
|
|
MANAGER_ID (직속상관(사수)의 사원번호) |
NUMBER(6) | |
|
DEPARTMENT_ID (사원본인이 근무하는 부서번호) |
NUMBER(4) |
연습으로 테이블을 생성해 본다.
HR
create table test_products
(productCode varchar2(10) not null
,productName Nvarchar2(10)
,price number(5) -- -99999 ~ 99999
,discountPct number(2,2) -- -0.99 ~ 0.99
,registerDate date
);
select *
from tab;
HR
desc test_products;
HR
insert into test_products(productCode, productName, price, discountPct, registerDate)
values('swk', '새우깡', 3000, 0.05, sysdate);
test_products 테이블 속에 들어있는 모든 컬럼 및 모든 행 조회
select productCode, productName, price, discountPct, registerDate
from test_products;
또는
select *
from test_products;
HR
insert into test_products(productCode, price, productName, discountPct, registerDate)
values('kjk', '감자깡', 5000, 0.15, sysdate);
-- insert into test_products ( ) 안의 순서와 valuse ( ) 안의 순서가 같아야 한다.
HR
insert into test_products(productCode, price, productName, discountPct, registerDate)
values('kjk', 5000, '감자깡', 0.15, sysdate);
HR
insert into test_products
values('ypr', 7000, '양파링', 0.20, sysdate);
-- 괄호가 있을 땐 순서가 바뀌더라도 값을 넣을 때 순서에 맞추어서 넣으면 되지만, 괄호가 없을 땐 정확한 순서를 지켜서 넣어야 한다.
HR
insert into test_products
values('ypr', '양파링', 7000, 0.20, sysdate);
HR
insert into test_products(productCode, productName, price, discountPct, registerDate)
values('맛좋은새우깡', '새우깡', 3000, 0.05, sysdate);
-- productCode는 varchar2(10)이기 때문에 10byte까지만 올 수 있다.
-- 글자는 6글자지만 byte는 18byte이다.
-- 인코딩 UTF-8을 할 경우 한글 3byte, MS949로 할 경우 2byte이다.
HR
insert into test_products(productCode, productName, price, discountPct, registerDate)
values('delicious', 'hotdogsnack', 4000, 0.07, sysdate);
-- productName은 Nvarchar2(10)으로 10글자까지만 나타낼 수 있다.
HR
insert into test_products(productCode, productName, price, discountPct, registerDate)
values('delicious', '따끈따끈한스낵', 4000, 0.07, sysdate);
-- productCode 9byte, 따끈따끈한스낵 7글자로 행이 삽입된다.
HR
insert into test_products(productCode, productName, price, discountPct, registerDate)
values('computer', '컴퓨터', 100000, 0.07, sysdate);
-- price는 number(5)이므로 -99999 부터 99999 까지만 나타낼 수 있다.
HR
insert into test_products(productCode, productName, price, discountPct, registerDate)
values('computer', '컴퓨터', 99999, 0.07, sysdate);
HR
insert into test_products(productCode, productName, price, discountPct, registerDate)
values('book', '셜록홈즈', 99999, 0.078, sysdate);
-- discountPct는 number(2,2)이므로 -0.99 부터 0.99 까지 나타낼 수 있다.
-- insert가 되어지지만 0.078로 입력되는 것이 아니라 반올림되어 0.08로 입력된다.
HR
insert into test_products(productCode, productName, price, discountPct, registerDate)
values('drink', '박카스디', 1234.567, 0.05, sysdate);
-- price는 number(5)이므로 -99999 부터 99999 까지 나타낼 수 있다.
-- insert가 되어지지만 1234.567로 입력되는 것이 아니라 반올림되어 1235로 입력된다.
HR
insert into test_products(productCode, price, registerDate)
values('cloth', 98765, sysdate);
HR
insert into test_products(productName, price, discountPct, registerDate)
values('코카콜라', 3456, 0.05, sysdate);
Ⅲ. 행 삭제하기 (Delete)
HR
delete from test_products;
▷ 결과
8개 행 이(가) 삭제되었습니다.
HR
rollback;
-- commit한 이후로 insert(행 입력), delete(행 삭제), update(컬럼값을 변경하기) 내용들을 디스크에 적용하지 않고 commit한 시점으로 (원상복구) 돌려주는 것이다.
HR
insert into test_products(productCode, productName, price, discountPct, registerDate)
values('keybord', '기계식키보드', 12345, 0.07, sysdate);
commit;
insert into test_products(productCode, productName, price, discountPct, registerDate)
values('snack1', '오징어스낵', 1000, 0.07, sysdate);
insert into test_products(productCode, productName, price, discountPct, registerDate)
values('snack2', '고구마스낵', 2000, 0.07, sysdate);
select *
from test_products;
HR
delete from test_products;
rollback;
select *
from test_products;
commit을 하면 rollback을 하더라도 복구되지 않는다.
조건에 만족하는 특정한 행 삭제하기
HR
delete from test_products
where productcode = 'computer';
컬럼의 데이터 값 변경하기
HR
update test_products set productname = '라면';
HR
update test_products set productcode = "abcd"
,productname = "짜장면"
,price = 1300;
HR
update test_products set productname = 'onionRing'
,price = 9800
where productcode = 'ypr';
HR
select productname, discountpct
from test_products
where productcode = 'book';
HR
select *
from test_products
where productcode = 'book';
-- data 값은 대소문자 구분이 있다.
문제 ▷▷
employess 테이블에서 부서번호(department_id)가 80번에 근무하는 사원들만 사원번호, 사원 성, 사원 이름, 이메일, 기본급여, 부서번호를 추출하세요.
HR
select employee_id, first_name, last_name, email, salary, department_id
from employees
where department_id = 80;
Ⅳ. 오라클 데이터베이스 사용자 계정 생성하기
SYS
create user testuser01 identified by eclass;
create user 유저명 identfied by 암호
-- 유저가 존재하지만 create session 권한이 없기 때문에 접속이 거부된다.
-- 유저를 만들고 권한을 주어야 오라클 서버에 붙일 수 있다.
-- 생성되어진 testuser01 오라클 데이터베이스 사용자에게 오라클서버에 접속이 가능하도록 권한을 부여해 준다.
-- 오라클서버에 접속이 가능하도록 하는 권한은 create session이다.
-- 권한을 부여해 주는 명령어는 grant 권한의종류 to 오라클사용자명
SYS
grant create session to testuser01;
-- 저장하고 접속
local_testuser01
show user;
▷ 결과
USER이(가) "TESTUSER01"입니다.
TESTUSER01
create table testmember01
(userid varchar2(10)
,passwd varchar2(20)
,name Nvarchar2(20)
,address Nvarchar2(300)
);
-- 테이블을 만들 수 있는 권한이 없다.
SYS
grant create table to testuser01;
-- 생성되어진 testuser01 오라클 데이터베이스 사용자에게 테이블을 생성할 수 있는 권한을 부여하겠다.
TESTUSER01
create table testmember01
(userid varchar2(10)
,passwd varchar2(20)
,name Nvarchar2(20)
,address Nvarchar2(300)
);
오류 보고 -
ORA-01950: no privileges on tablespace 'SYSTEM'
01950. 00000 - "no privileges on tablespace '%s'"
*Cause: User does not have privileges to allocate an extent in the
specified tablespace.
*Action: Grant the user the appropriate system privileges or grant the user
space resource on the tablespace.
-- 테이블은 만들 수 있으나, 테이블이 저장되어질 파일(table space)을 쓸 수 있는 공간이 없다.
-- 생성되어진 testuser01 오라클 데이터베이스 사용자에게 테이블이 저장되어질 디스크공간(할당량, quota)크기를 부여하겠다.
-- 먼저, 테이블이 저장되어질 디스크공간의 종류와 크기를 조회해 본다.
SYS
select *
from dba_tablespaces;
SYS
select *
from dba_data_files;
-- alter user 오라클데이터베이스사용자명 quota 크기k(또는 크기m) on 특정테이블스페이스명;
alter user testuser01
quota 64m on system;
TESTUSER01
create table testmember01
(userid varchar2(10)
,passwd varchar2(20)
,name Nvarchar2(20)
,address Nvarchar2(300)
);
TESTUSER01
insert into testmember01(userid, passwd, name, address)
values('leess','1234','이순신','서울시 강남구');
commit;
select *
from testmember01;
Ⅴ. Tablespace
-- 데이터가 저장되어질 파일(즉, tablespace)를 만들어 본다.
-- C - 새폴더 - myoradata
SYS
create tablespace tbs_mytesta
datafile 'c:\myoradata\tbs_mytesta_01.dbf' size 10m
extent management local
segment space management auto;
-- TBS_MYTESTA 테이블스페이스는 파일크기가 10mb인 테이블스페이스로서 extent와 segment의 관리는 오라클서버가 알아서 자동적으로 관리하고 처리해 주는 테이블스페이스로 만든다.
SYS
select *
from dba_tablespaces;
SYS
select *
from dba_data_files;
-- create table testmember02 만들어 tbs_mytesta_01.dbf에 저장하려고 한다.
TESTUSER01
create table testmember02
(userid varchar2(10)
,passwd varchar2(20)
,name Nvarchar2(20)
,address Nvarchar2(300)
) tablespace tbs_mytesta;
오류 보고 -
ORA-01950: no privileges on tablespace 'TBS_MYTESTA'
01950. 00000 - "no privileges on tablespace '%s'"
*Cause: User does not have privileges to allocate an extent in the
specified tablespace.
*Action: Grant the user the appropriate system privileges or grant the user
space resource on the tablespace.
-- 테이블이 저장되어질 파일(table space)을 쓸 수 있는 공간이 없다.
SYS
alter user testuser02
quota 4m on tbs_mytesta;
TESTUSER01
insert into testmember02(userid, passwd, name, address)
values('hongkd','1234','홍길동','서울시 강북구');
commit;
select *
from testmember02;
-- testmember01을 만들 때에는 tablespace를 지정하지 않았는데 왜 system에 공간이 없다고 나온 것일까?
'수업내용' 카테고리의 다른 글
| [Day18][Oracle] 제약조건 / 테이블에 대한 주석 달기 (0) | 2019.09.16 |
|---|---|
| [Day17][Oracle] Tablespace / Tablespace 삭제 / 시스템권한(System Privilege) / 테이블 삭제 / 복원 / Table 삭제 (0) | 2019.09.11 |
| [Day14][Java] Exception(예외) / 제품 주문 관리 프로그램 (0) | 2019.09.06 |
| [Day13][Java] Overloading(오버로딩) / final(예약어) / interface(인터페이스) (0) | 2019.09.05 |
| [Day12][Java] 다형성 / 추상 메소드 / 추상 클래스 (0) | 2019.09.04 |
