오라클 DB SQL 교육 정리 (초급)

1일차

D:\app\ADMIN\product\11.2.0\dbhome_1\NETWORK\ADMIN
tnsnames.ora


내컴퓨터> 관리> 서비스에서
OracleOraDb11g_home1TNSListner 수동
OracleServiceORCL 수동

D:\app\ADMIN\product\11.2.0\dbhome_1\database
cmd창 sqlplus "/as sysdba"
sys: 가장 강력한 계정으로 접속됨.

SQL> show sga

startup

shutdown
4가지옵션

D:\app\ADMIN\product\11.2.0\dbhome_1\database
처음: SPFILEORCL.ORA 파일존재 / INITorcl.ORA 파일 미존재
1.create pfile from spfile;
2.create spfile from pfile;
startup 상태에서는 안됨 -> shutdown후 -> create spfile from pfile; -> startup

sqlplus "/as sysdba"
alter user scott account unlock;
conn scott/tiger

exit

sqlplus "/as sysdba"
alter user sys identified by manager; sys로 변경

테이블스페이스생성
create tablespace test_data
datafile 'D:\test_data01.dbf' size 50M
default storage(initial 15M
next 5M)
online;

이어붙이기
alter tablespace test_data
add datafile 'D:\test_data02.dbf' size 5M;

데이터파일 사이즈변경
alter database datafile
'D:\test_data02.dbf' resize 10M;

temp파일 사이즈변경
alter database tempfile
'D:\APP\ADMIN\ORADATA\ORCL\TEMP01.DBF' resize 200M;
temp파일 경로 확인: desc v$tempfile; select name from v$tempfile;
desc v$datafile; select name from v$datafile;

create table dept2
(dept number(7) primary key,
dname varchar2(30) not null,
location number(3) default 0)
pctfree 20
pctused 70
storage(pctincrease 0
maxextents 99)
tablespace users;

alter table dept2
add (cr_date date not null);

modify는 여러개하면 잘안됨. 컬럼하나씩.
alter table dept2
modify(dname varchar2(40));

<CTAS 테이블복제>
create table dept3
as select * from dept2 nologging;
nologging: 속도빠르게

<시퀀스생성>
create sequence seq_bid
start with 1
increment by 1
nomaxvalue
nocycle 9iew
cache 500;

select seq_bid.nextval from dual;


===================================
<종합실습>
sqlplus "/as sysdba" 시스템유저가
1.테이블스페이스 생성
1)테이블용
create tablespace gunjun
datafile 'd:\gunjun.dbf' size 20m
minimum extent 500k
default storage(initial 500k next 500k);
2)인덱스용
create tablespace gunjun_idx
datafile 'd:\gunjunidx.dbf' size 20m
minimum extent 500k
default storage(initial 500k next 500k);

2.User생성
1)DBA용
create user ammis identified by ammis
default tablespace gunjun
temporary tablespace temp;

2)일반
create user ammisdp identified by ammisdp
default tablespace gunjun
temporary tablespace temp;

3.권한부여
grant dba to ammis;
grant create session to ammisdp; --DB세션만붙게 최소권한 +리소스

#확인
dba > all > user
dba_tables: DB에 속한 모든
all_tables: 자신이 생성한 + 권한있는
user_tables: 자신이 생성한

4.테이블생성
create table db_test(
code varchar2(01),
gubun varchar2(01),
name varchar2(50),
inputdate date,
updatedate date,
constraint pk_errc primary key(code)
using index tablespace GUNJUN_IDX
storage (initial 1m next 1m pctincrease 0)
)
pctfree 10 pctused 70
tablespace GUNJUN
storage (initial 1m next 1m pctincrease 0);

*pk이름 unique하게 부여하여야 인덱스생성시 알아볼수있다.

create table db_fftest(
type varchar2(01),
code varchar2(01),
name varchar2(40),
id varchar2(32),
plandate varchar2(08),
startdate varchar2(08),
constraint pk_fftest primary key(type)
using index tablespace GUNJUN_IDX
storage (initial 10k next 20k),
constraint fk_fftest foreign key(code)
references db_test(code)
on delete cascade
)
pctfree 10 pctused 60
tablespace GUNJUN
storage (initial 30k next 30k pctincrease 0);

*foreign key는 child테이블에서 생성하여야한다.


#확인
select table_name from user_tables;

5. 껍데기롤
create role ammis_role;

6. 껍데기롤에 권한 부여
grant select, update, insert, delete on db_test to ammis_role;
grant select, update, insert, delete on db_fftest to ammis_role;

7. synonym 부여
create public synonym db_test for ammis.db_test;
create public synonym db_fftest for ammis.db_fftest;

8. ammisdp에게 롤부여
grant ammis_role to ammisdp;

9. 확인
ammisdp user -> all_tables
select table_name from all_tables;

*********************************************************************
2일차

* 뷰생성
grant create view to scott; --권한부여

create view emp_view as
select ename, sal*12 annual_salary
from emp where ename = 'FORD';

* Decode -> Case
select job
,sum(case deptno when 10 then 1 else 0 end) as dept10
,sum(case deptno when 10 then 1 else 0 end) as dept20
,sum(case deptno when 10 then 1 else 0 end) as dept30
,count(*) as total
from scott.emp
group by job;

select job
,sum(case deptno when 10 then 1 else 0 end) as dept10
,sum(case deptno when 20 then 1 else 0 end) as dept20
,sum(case deptno when 30 then 1 else 0 end) as dept30
,count(*) as total
from scott.emp
group by rollup(job);

*
select round(avg(sal)) as "average"
from emp;

select empno, count(*)
from emp
group by empno;

select deptno, count(*)
from emp
group by deptno;

*
select count(mgr) from emp; 13 (컬럼값이 null인 경우 제외)
select count(distinct(mgr)) from emp; 6
select count(*) from emp; 14
select count(1) from emp; 14
select count(nvl(comm,0)) from emp; 14

*안티조인
select empno, ename, job, sal
from emp
where deptno not in(
select deptno
from dept
where loc = 'NEW YORK')
order by ename;

*세미조인
select *
from dept
where exists(
select*
from emp
where dept.deptno = emp.deptno
and emp.sal > 2500)
order by dname;

*아우터조인
select e1.ename, e1.mgr, e2.ename as "manager", e2.empno
from emp e1, emp e2
where e1.mgr = e2.empno(+);

*풀아우터조인 (허용안됨)
select e1.ename, e1.mgr, e2.ename as "manager", e2.empno
from emp e1, emp e2
where e1.mgr(+) = e2.empno(+);

*풀아우터조인 예제
select a.id,
b.id,
a.name,
b.name
from fullouter1 a full outer join fullouter2 b000
on a.id = b.id;

*풀아우터조인합친것(올바른예)
select (case when a.id is not null then a.id else b.id end),
a.name,
b.name
from fullouter1 a full outer join fullouter2 b
on a.id = b.id;

select a.id, (select b.name from fullouter2 b where a.id=b.id) name
from fullouter1 a;

*서브쿼리
select deptno, empno, ename, sal
from emp
where sal in
(select min(sal)
from emp
group by deptno);

*
select empno, ename
from emp a,
(select deptno, dname, loc
from dept b
where loc = 'CHICAGO' and a.deptno = b.deptno) dept_bo
where emp.deptno = dept_bo.deptno;

select empno, ename
from emp a,
(select deptno, dname, loc
from dept b
where loc = 'CHICAGO') dept_bo
where emp.deptno = dept_bo.deptno;

*계층형 검색
select level, empno, ename, job
from emp
where deptno = 20
start with empno = 7369
connect by prior mgr = empno;

select level, empno, ename, job
from emp
where deptno = 20
start with empno = 7566
connect by prior empno = mgr;

select level, empno, ename, mgr
from emp
start with empno = 7839
connect by prior empno = mgr;

*
select level, empno, ename, job
from emp
where deptno = 20
and empno != 7902
start with empno = 7566
connect by prior empno = mgr;

select level, empno, ename, job
from emp
where deptno = 20
start with empno = 7566
connect by prior empno = mgr
and empno != 7902;

*
select lpad(' ', 4*(level-1)) || ename ename, empno, mgr, job
from emp
start with job='PRESIDENT'
connect by prior empno=mgr;

3일차
D:\app\SKCCADMIN\diag\rdbms\orcl\orcl\trace
alert_orcl.log

DB의 로그파일 볼 수 있다.