오라클 기초 자료

  오라클 삭제...
   1. 서비스 중지
   2. OUI로 프로그램 제거
   3. regedit
       -\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Service\오라클 관련 삭제
       -\HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Service\Eventlog\Application\오라클 관련 삭제
       -\HKEY_LOCAL_MACHINE\SOFTWARE\오라클 관련 삭제
   4.폴더 삭제
   5.시작 메뉴 삭제
   6.재설치


서비스 관련

   SID    :    수동으로 변경 후 스타트
   TNSListener    : 수동으로 변경 후 스타트
   나머지는 모두 사용안함 일단.

사용자 패스워드 변경
   alter user sys identified by 패스워드 ;

레지스트리 변경
   \HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0\
   에 NLS_DATE_FORMAT 추가 하면서 값은 원하는대로
   ORA_sid_AUTOSTART : 서비스 가동시 DB startup 여부 컨트롤
   ORA_sid_SHUTDOWN : 서비스 중지시 DB shutdown 여부 컨트롤


테이블스페이스 생성

   create tablespace java
   datafile 'd:\DB\java01.dbf' size 10M;

사용자 생성
   create user 유저명
   identified by 패스
   default tablespace 명;

사용자에 대한 권한 설정
   grant connect,resource to java;
   grant privilege to user_name;

--사용자연결
conn happy/day
--사용자소유의 테이블 확인
select * from tab;
--현재 사용자 확인
show user;
conn system/test
select * from tab;
--연습용데이터 생성
conn happy/day
select * from tab;
@C:\oracle\ora92\sqlplus\demo\demobld.sql
sqlplus happy/day
select * from tab;

ftp://218.234.13.11:1101
==네트워크 환경설정
C:\oracle\ora92\network\admin
==오라클의 재가동
conn /as sysdba
shutdown immediate
startup
==사용자 암호변경
alter user 사용자명
   identified by 새암호;
==테이블스페이스 확인
conn /as sysdba
select tablespace_name,file_name from dba_data_files;
==사용자 확인
select username from dba_users;
==사용자 삭제
drop user 사용자명 cascade;
ex)drop user happy cascade;
==사용자 접속
conn happy/day
select * from tab;
==
conn scott/tiger
show user
select * from tab;
conn system/test
conn hr/hr
show user;
--잠긴 계정 풀기
conn /as sysdba
alter user hr
   account unlock;
--암호변경
alter user hr
   identified by hr;
conn hr/hr
select * from tab;
conn scott@oh/tiger
---
http://58.121.75.125/isqlplus
select * from tab;
==
conn happy/day
select * from tab;
--테이블의 구조확인
desc 테이블명
desc emp;
desc dept;
--테이블의 내용 조회
select * from 테이블명;
select * from emp;
==테이블의 내용조회
Select [distinct] {*|컬럼명1,컬럼명2,...} From 테이블명
       [ Where 조건]
       [ Group by 그룹절]
       [ Having 그룹절의 조건]
       [ Order By 정렬 ]
select empno,ename,job from emp;
--이름,업무,급여,커미션
select ename,job,sal,comm from emp;
set linesize 150
select * from emp;
set pagesize 30
select * from emp;
quit
sqlplus happy/day
select * from emp;
--sqlplus의 환경설정
C:\oracle\ora92\sqlplus\admin\glogin.sql편집
-----
quit
sqlplus happy/day
select * from emp;
select job from emp;
select distinct job from emp;
select ename,job,sal,deptno from emp;
select ename,job,sal,deptno from emp order by sal;
select ename,job,sal,deptno from emp order by sal desc;
select ename,job,sal,deptno from emp order by deptno;
select ename,job,sal,deptno from emp
   order by deptno,ename;
select ename,job,sal,deptno from emp
   order by deptno desc,sal asc;
select * from emp;
select ename,job,sal,comm from emp;
select ename,job,sal,comm,sal+comm from emp;
select ename 이름,job 업무,sal 급여,comm 커미션,sal+comm 합계
   from emp;
select ename Irum,job "Job" ,sal salary,comm "commission",
   sal+comm "합 계" from emp;


conn happy/day
select ename,job,sal,comm,sal+comm total from emp;
--nvl(표현식,널일경우의 값)
select ename,sal,comm,nvl(comm,-999) from emp;
select ename,sal,comm,nvl(comm,0) from emp;
select ename,sal,comm,nvl(comm,'비었슴') from emp;
select ename,job,sal,nvl(comm,0),sal+nvl(comm,0) total
   from emp;
--where
select empno,ename,job,sal from emp
   where job='SALESMAN';
select empno,ename,job,sal from emp where job='sALESMAN';
select empno,ename,job,sal from emp where sal<=1500;
select empno,ename,job,sal from emp
   where job!='SALESMAN';
select empno,ename,job,sal from emp
   where job<>'SALESMAN';
select empno,ename,job,sal from emp
   where sal>=2000  and sal<=3000;
--between A and B
select empno,ename,job,sal from emp
   where sal between 2000 and 3000;
select empno,ename,job,sal,comm from emp
   where comm=null;
select empno,ename,job,sal,comm from emp
   where comm is null;
select empno,ename,job,sal,comm from emp
   where comm is not null;
--
select empno,ename,job,sal from emp
   where job='MANAGER' or job='ANALYST' or job='PRESIDENT';
select empno,ename,job,sal from emp
   where job in ('MANAGER','ANALYST','PRESIDENT');
--like 연산자
--_:문자하나,%:문자여러개
select empno,ename,job,sal from emp where ename like 'A%';
select empno,ename,job,sal from emp where ename like '%N';
select empno,ename,job,sal from emp where ename like '_L%';
select empno,ename,job,sal from emp where ename like '%LA%';
insert into emp (empno,ename) values (1,'tom_jerry');
insert into emp (empno,ename) values (2,'java_web');
insert into emp (empno,ename) values (3,'tazan%ah');
select * from emp;
select empno,ename,job,sal from emp
   where ename like '%_%';
select empno,ename,job,sal from emp
   where ename like '%?_%' escape '?';
select empno,ename,job,sal from emp
   where ename like '%*%%' escape '*';
select ename,job from emp;
rollback;
select ename,job from emp;
select ename||job 이름과업무 from emp;
select ename||'의 업무는 '||job||'입니다.' 이름과업무 from emp;
==
select * from tab;
create table d1
   as
   select * from dept;
create table d2
   as
   select * from dept where deptno>20;
select * from tab;
select * from d1;
select * from d2;
insert into d2 values (50,'기획부','서울');
select * from d1;
select * from d2;
select * from d1
   union
   select * from d2;
select * from d1
   intersect
   select * from d2;
select * from d1
   minus
   select * from d2;
select * from d1
   union all
   select * from d2;
--
select ename,hiredate from emp;
alter session
   set nls_date_format='yy-mm-dd hh:mi:ss am';
select ename,hiredate from emp;
alter session
   set nls_date_format='yy-mm-dd dy hh:mi:ss am bc';
select ename,hiredate from emp;
alter session
   set nls_date_format='yy-mm-dd day hh:mi:ss am bc';
select ename,hiredate from emp;
alter session
   set nls_date_format='yy-mm-dd';
select ename,hiredate from emp;
select empno,ename,deptno from emp where deptno=30;
--
spool D:\LECTURE\101\10-28.sql
select * from tab;
desc emp;
select * from emp;
spool off
--D:\LECTURE\101\10-28.sql 파일 열어서 내용확인
select table_name,tablespace_name from user_tables;
col table_name format a15
/
select empno,ename,job,sal,comm from emp;
col ENAME format a20
col sal format a5
/
col sal format 999,999
col empno format 000,000
/

conn happy/day
select ename,lower(ename),upper(ename),initcap(ename)
   from emp;
select concat(ename,job),ename||job from emp;
select length('abcdef'),length('무궁화꽃') from dual;
select lengthb('abcdef'),lengthb('무궁화꽃') from dual;
select 10/7 from emp;
select 10/7 from dual;
select 'abcdefghijklmn',substr('abcdefghijklmn',3),
   substr('abcdefghijklmn',3,6) from dual;
--nvl(표현식,널일경우의 값)
--nvl2(표현식,널이아닐경우의 값,널일경우의 값)
select ename,comm,nvl(comm,-999),nvl2(comm,999,-999)
   from emp;
select ename,lpad(ename,10,'!'),rpad(ename,10,'#')
   from emp;
select 10/7,round(10/7,2),trunc(10/7,2) from dual;
select sign(88),sign(0),sign(-7) from dual;
select mod(13,5),power(3,4) from dual;
--
날짜+숫자=날짜
날짜+숫자/24=날짜에 시간을 더함
날짜+숫자/1440=날짜에 분을 더함
날짜-날짜=숫자
select sysdate,sysdate+3,sysdate+3/24,sysdate+3/1440 from dual;
select sysdate-to_date('05-01-01','yy-mm-dd') from dual;
select add_months(sysdate,3),next_day(sysdate,'금') from dual;
select months_between('06-09-19',sysdate) from dual;
--
select count(ename) from emp;
select count(*) from emp;
select sum(sal),sum(comm) from emp;
select max(sal),min(sal) from emp;
select deptno,avg(sal) from emp where deptno=10;
select deptno,avg(sal) from emp group by deptno;
select job,min(sal),max(sal),avg(sal) from emp
   group by job;
select ename,job,sal,deptno from emp order by deptno,ename;
--group by a,b=>(a,b)
select deptno,job,min(sal),max(sal),avg(sal) from emp
   group by deptno,job;
--group by rollup(a,b)=>(a,b) (a) ()
select deptno,job,min(sal),max(sal),avg(sal) from emp
   group by rollup(deptno,job);
--group by cube(a,b)=>(a,b) (a) (b) ()
select deptno,job,min(sal),max(sal),avg(sal) from emp
   group by cube(deptno,job);
--계층쿼리
select empno,ename,job,mgr from emp;
select rowid,rownum,level,empno,ename,job,mgr from emp;
select level,ename,job from emp
   start with ename='KING'
   connect by prior empno=mgr;
select lpad(' ',level*2)||ename irum,job from emp
   start with ename='KING'
   connect by prior empno=mgr;
col irum format a15
/
select lpad(' ',level*2)||ename irum,job from emp
   start with ename='JONES'
   connect by prior empno=mgr;
--가지치기
select lpad(' ',level*2)||ename irum,job from emp
   start with ename='KING'
   connect by prior empno=mgr and ename!='BLAKE';


select * from emp;
select * from dept;
--사번,성명,직무,급여,부서명
--카테시안조인
select empno,ename,job,sal,dname from emp,dept;
--Equi-조인
select empno,ename,job,sal,dname from emp,dept
   where emp.deptno=dept.deptno;
--테이블 알리아스 주기
select empno,ename,job,sal,dname from emp e,dept d
   where e.deptno=d.deptno;
select empno,ename,job,sal,dname
   from emp e join dept d
       on e.deptno=d.deptno;
select * from tab;
desc student;
--번호,이름,출신학교,수능,내신
select student.bunho,irum,school,s1,s2 from student, score
   where student.bunho=score.bunho;
select st.bunho,irum,school,s1,s2
   from student st , score sc where st.bunho=sc.bunho;
--번호,이름,출신학교,학과이름
select bunho,irum,schoo,partname from student s, part p
   where s.partcd=p.partcd;
--번호,이름,출신학교,학과이름,수능,내신,총점
select st.bunho,irum,school,partname,s1,s2,s1+s2 total
   from student st, part p,score sc
   where st.partcd=p.partcd and st.bunho=sc.bunho;
select st.bunho,irum,school,partname,s1,s2,s1+s2 total
   from student st join part p on st.partcd=p.partcd
                          join score sc on st.bunho=sc.bunho;
--번호,이름,출신학교,학과이름,수능,내신,면접,체력장,총점
select st.bunho,irum,school,partname,s1,s2,s3,s4,
   s1+s2+s3+s4 total
   from student st, part p,score sc,tech t
   where st.partcd=p.partcd and st.bunho=sc.bunho and
       st.bunho=t.bunho;
select st.bunho,irum,school,partname,s1,s2,s3,s4,
   s1+s2+s3+s4 total
   from student st join part p on st.partcd=p.partcd
                          join score sc on st.bunho=sc.bunho
                          join tech t on st.bunho=t.bunho;
--Non-Equi 조인
select * from salgrade;
--사번,이름,직무,급여,등급
select empno,ename,job,sal,GRADE
   from emp e, salgrade s
   where sal between LOSAL  and  HISAL;
--outer 조인
insert into emp (empno,ename) values (88,'주상현');
insert into emp (empno,ename) values (99,'오영광');
insert into dept values (1,'기획부','서산');
select * from emp;
select * from dept;
select empno,ename,sal,dname from emp e,dept d
   where e.deptno=d.deptno;
select empno,ename,sal,dname from emp e,dept d
   where e.deptno=d.deptno(+);
select empno,ename,sal,dname from emp e,dept d
   where e.deptno(+)=d.deptno;
select empno,ename,sal,dname from emp e,dept d
   where e.deptno(+)=d.deptno(+);
--
select empno,ename,sal,dname
   from emp e right outer join dept d on e.deptno=d.deptno;
select empno,ename,sal,dname
   from emp e left outer join dept d on e.deptno=d.deptno;
select empno,ename,sal,dname
   from emp e full outer join dept d on e.deptno=d.deptno;
--self 조인
--이름,업무,급여,매니져명
rollback;
select * from emp;
select e1.ename,e1.job,e1.sal,e2.ename manager
   from emp e1,emp e2
   where e1.mgr=e2.empno;
==서브쿼리
--JONES보다 급여를 많이 받는 사원의 이름과 급여,업무출력
select sal from emp where ename='JONES';
select ename,sal,job from emp
   where sal>(select sal from emp where ename='JONES');
--사원의 평균급여보다 적게 받는 사람의 이름과 급여,업무출력
select avg(sal) from emp;
select ename,sal,job from emp
   where sal <(select avg(sal) from emp);
--10번부서의 최대급여보다 많이 받는 사원의 이름과 급여,업무출력
select max(sal) from emp
   group by deptno having deptno=10;
select ename,sal,job from emp
   where sal >(select max(sal) from emp
                   group by deptno having deptno=10);
--MANAGER업무를 진행하는 사원보다
--급여를 많이 받는 사원의 이름과 급여, 업무출력
select sal from emp where job='MANAGER';
select ename,sal,job from emp
   where sal > all(select sal from emp where job='MANAGER');
select ename,sal,job from emp
   where sal > any(select sal from emp where job='MANAGER');        
--20번부서의 업무와 같은 일을 하는 사람
select job from emp where deptno=20;
select ename,sal,job from emp
   where job in (select job from emp where deptno=20);


create table juso(
   no number,
   irum varchar2(20),
   city varchar2(20),
   tel varchar2(15),
   hobby varchar2(15)
);
select * from juso;
insert into juso values (1,'김학민','마산','123','음악감상');
insert into juso values (1,'주상현','부산','333','독서');
select * from juso;
insert into juso values (2,'오영광');
insert into juso (no,irum) values (2,'오영광');
select * from juso;
insert into juso values (3,'강동훈',null,'','등산');
select * from juso;
--
create table addr(
   no number primary key,
   irum varchar2(20),
   city varchar2(10) check (city in ('서울','부산','대전','광주','속초')),
   tel varchar2(10) not null,
   hobby varchar2(10) default '독서'
);
insert into addr values (1,'김민수','대구','777','낚시');--err
insert into addr values (1,'김민수','서울','777','낚시');
select * from addr;
insert into addr values (2,'황건','대전','777','잠자기');
select * from addr;
insert into addr (no,irum,tel) values (3,'유주완','666');
select * from addr;
insert into addr values (4,'서정은',null,null,null);
select * from addr;
insert into addr values (4,'서정은',null,'222',null);
select * from addr;
insert into addr values (4,'오명일',null,'111','');
select * from addr;
insert into addr values (5,'오명일',null,'111','');
select * from addr;
--
select * from emp where deptno=&d;
/
select * from emp where sal>&&s;
/
define
undefine s
define/
accept age prompt '당신의 나이는?'
--
create table ACCOUNTING(
   empno NUMBER(4),
   ENAME VARCHAR2(10),
   sal NUMBER(7,2),
   bonus NUMBER(7,2)
);
create table RESEARCH(
   empno NUMBER(4),
   ENAME VARCHAR2(10),
   sal NUMBER(7,2),
   bonus NUMBER(7,2)
);
create table SALES(
   empno NUMBER(4),
   ENAME VARCHAR2(10),
   sal NUMBER(7,2),
   bonus NUMBER(7,2)
);
create table OPERATIONS(
   empno NUMBER(4),
   ENAME VARCHAR2(10),
   sal NUMBER(7,2),
   bonus NUMBER(7,2)
);
==
create table high_grade(
   empno NUMBER(4),
   ENAME VARCHAR2(10),
   sal NUMBER(7,2),
   grade number
);
create table high_tax(
   empno NUMBER(4),
   ENAME VARCHAR2(10),
   sal NUMBER(7,2),
   tax number(7,2)
);
insert all
   into high_grade values (empno,ename,sal,1)
   into high_tax values (empno,ename,sal,sal*0.1)
   select empno,ename,sal from emp where sal>2000;
select * from high_grade;
select * from high_tax;
insert all
   when deptno=10 then
       into ACCOUNTING values (empno,ename,sal,sal*0.01)
   when deptno=20 then
       into RESEARCH values (empno,ename,sal,sal*0.03)    
   when deptno=30 then
       into SALES values (empno,ename,sal,sal*0.05)    
   when deptno=40 then
       into OPERATIONS values (empno,ename,sal,sal*0.07)    
   select * from emp;
select * from accounting;
select * from research;
select * from sales;
select * from operations;
==update
update emp set ename='변강쇠',sal=3000;
select * from emp;
update emp set ename='김동현' where deptno=20;
select * from emp;
delete from emp where job!='SALESMAN';
select * from emp;
delete from emp;
select * from emp;
==merge
create table d1
   as
   select * from dept;
create table d2
   as
   select * from dept where deptno>20;
select * from d1;
select * from d2;
update d2 set dname='관리부',loc='제주' where deptno=30;
update d2 set dname='경리부',loc='울릉도' where deptno=40;
select * from d2;
merge into d1 using d2
   on (d1.deptno=d2.deptno)
   when matched then
       update set
--        d1.deptno=d2.deptno,
           d1.dname=d2.dname,
           d1.loc=d2.loc
   when not matched then
       insert values (d2.deptno,d2.dname,d2.loc);
select * from d1;
==
DDL->Oracle db,tablespace,segment,object
를 생성,수정,삭제 할수 있게 해주는 명령어
=>Create->생성
  ,Alter->수정
  ,Drop ->삭제
  ,Truncate ->잘라내기
  ,Rename->이름변경
=>AutoCommit 수행
DML->Table에 data를 입력,수정,삭제,병합할수
있게 해주는 명령어
=>Insert ->입력
  ,Update ->수정
  ,Delete ->삭제
  ,Merge(9i에서 추가) ->병합
=>Transaction의 영향을 받는다.
DCL->Oracle User의 권한을 부여,박탁
=>Grant->권한의 부여
  ,Revoke->권한의 박탈
=>Autocommit 수행
Transaction:process가 처리되는 최소단위
=>Commit ->Trans정상적인 처리
  ,Rollback ->Trans의 이전단계로 Rollback
  ,(Savepoint) ->지정한 특별한 시점으로 Rollback
==
commit;
select * from dept;
update dept set dname='관리부';
insert into dept values (99,'영업부','인천');
select * from dept;
rollback;
select * from dept;
--세션2
select * from dept;
--세션1
insert into dept values (77,'경영과','양산');
select * from dept;
--세션2
select * from dept;
--세션1
commit;
--세션2
select * from dept;
update dept set loc='독도' where deptno=30;
select * from dept;
--세션1
update dept set dname='인사과';
--세션2
commit;
delete dept where deptno<30;
--세션1
rollback;
conn /as sysdba
drop user happy cascade;
create user happy
   identified by day
   default tablespace java;
grant connect,resource to happy;
conn happy/day
select * from tab;
--36페이지 스크립트 실행
@C:\oracle\ora92\sqlplus\demo\demobld.sql
sqlplus happy/day
select * from tab;
alter table dept
   add primary key(deptno);
--214~219

데이터베이스 생성
1.db이름결정(1-8자):java11
--D:\java11폴더 생성

2.pfile 생성
C:\oracle\admin\MTDI\pfile\init.ora.xxxxxxx파일을
C:\oracle\ora92\database\initJVA11.ora로 복사후 화일명 변경

--JAVA11.ora화일 내용 편집
###########################################
# Database Identification
###########################################
db_domain=""
db_name=JAVA11


###########################################
# Instance Identification
###########################################
instance_name=JAVA11



###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=C:\java11th\bdump
core_dump_dest=C:\java11th\cdump
timed_statistics=TRUE
user_dump_dest=C:\java11th\udump


###########################################
# File Configuration
###########################################
control_files=("C:\java11th\CONTROL01.CTL", "C:\java11th\CONTROL02.CTL", "C:\java11th\CONTROL03.CTL")

3. 패스워드 파일 생성(C:\oracle\ora92\database\에 생성되어야 함)
--도스 명령창에서 orapwd file=C:\oracle\ora92\database\pwdJAVA11.ora password=test entries=5 입력
  처리후 C:\oracle\ora92\database\에 wdJVA11.ora가 생성이 되었는지 확인

4. 서비스 등록
--도스 명령창에서
oradim -new -sid JAVA11
if)서비스 잘못 생성시
oradim -delete -sid JAVA11

5. 기본데이터베이스 변경
--영구변경
--일시변경 : 도스명령창에서 set oracle_sid=JAVA11

6.spfile생성
sqlplus "/as sysdba"
create spfile from pfile;

7. db생성
startup nomount;
show parameter db_name;

if)db이름이 java11이 아닌경우 quit 5번부터 다시 수행

create database JAVA11
datafile 'C:\java11th\system01.dbf' size 200m
maxdatafiles 200
maxinstances 1
logfile
'C:\java11th/redo01.log' size 10m,
'C:\java11th/redo02.log' size 10m
maxlogfiles 4
maxlogmembers 3
character set ko16ksc5601
undo tablespace undotbs1
datafile 'C:\java11th/undo.dbf' size 100m
default temporary tablespace temp
tempfile 'C:\java11th/temp01.dbf' size 100m;

8.data dictionary생성
@C:\oracle\ora92\rdbms\admin\catalog.sql
@C:\oracle\ora92\rdbms\admin\catproc.sql


select username from dba_users;

create tablespace users
datafile 'C:\java11th\users.dbf' size 10m;
create user scott
identified by tiger
default tablespace users;
@C:\oracle\ora92\rdbms\admin\scott.sql
--테이블 스페이스 mouse를 생성
create tablespace mouse
datafile 'C:\java11th\mouse.dbf' size 10m;
--사용자 tom/jerry를 생성하고 기본테이블스페이스로 mouse지정
create user tom
identified by jerry
default tablespace mouse;
--tom사용자에게 36페이지 데이터 입력
@C:\oracle\ora92\sqlplus\demo\demobld.sql

데이터베이스 생성
1.db이름결정(1-8자):java11
--D:\java11폴더 생성

2.pfile 생성
C:\oracle\admin\MTDI\pfile\init.ora.xxxxxxx파일을
C:\oracle\ora92\database\initJVA11.ora로 복사후 화일명 변경

--JAVA11.ora화일 내용 편집
###########################################
# Database Identification
###########################################
db_domain=""
db_name=JAVA11


###########################################
# Instance Identification
###########################################
instance_name=JAVA11



###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=C:\java11th\bdump
core_dump_dest=C:\java11th\cdump
timed_statistics=TRUE
user_dump_dest=C:\java11th\udump


###########################################
# File Configuration
###########################################
control_files=("C:\java11th\CONTROL01.CTL", "C:\java11th\CONTROL02.CTL", "C:\java11th\CONTROL03.CTL")

3. 패스워드 파일 생성(C:\oracle\ora92\database\에 생성되어야 함)
--도스 명령창에서 orapwd file=C:\oracle\ora92\database\pwdJAVA11.ora password=test entries=5 입력
  처리후 C:\oracle\ora92\database\에 wdJVA11.ora가 생성이 되었는지 확인

4. 서비스 등록
--도스 명령창에서
oradim -new -sid JAVA11
if)서비스 잘못 생성시
oradim -delete -sid JAVA11

5. 기본데이터베이스 변경
--영구변경
--일시변경 : 도스명령창에서 set oracle_sid=JAVA11

6.spfile생성
sqlplus "/as sysdba"
create spfile from pfile;

7. db생성
startup nomount;
show parameter db_name;

if)db이름이 java11이 아닌경우 quit 5번부터 다시 수행

create database JAVA11
datafile 'C:\java11th\system01.dbf' size 200m
maxdatafiles 200
maxinstances 1
logfile
'C:\java11th/redo01.log' size 10m,
'C:\java11th/redo02.log' size 10m
maxlogfiles 4
maxlogmembers 3
character set ko16ksc5601
undo tablespace undotbs1
datafile 'C:\java11th/undo.dbf' size 100m
default temporary tablespace temp
tempfile 'C:\java11th/temp01.dbf' size 100m;

8.data dictionary생성
@C:\oracle\ora92\rdbms\admin\catalog.sql
@C:\oracle\ora92\rdbms\admin\catproc.sql


select username from dba_users;

create tablespace users
datafile 'C:\java11th\users.dbf' size 10m;
create user scott
identified by tiger
default tablespace users;
@C:\oracle\ora92\rdbms\admin\scott.sql
--테이블 스페이스 mouse를 생성
create tablespace mouse
datafile 'C:\java11th\mouse.dbf' size 10m;
--사용자 tom/jerry를 생성하고 기본테이블스페이스로 mouse지정
create user tom
identified by jerry
default tablespace mouse;
grant connect, resource to tom;
--tom사용자에게 36페이지 데이터 입력
@C:\oracle\ora92\sqlplus\demo\demobld.sql
----------------------------------------------------------------------------------------------------------------------------
conn system/manager
@C:\oracle\ora92\sqlplus\admin\PUPBLD.SQL

테이블스페이스 생성
create tablespace spring
datafile 'c:\java11th\spring01.dbf' size 2m,
'c:\java11th\spring02.dbf' size 2m;

desc dba_data_files;
select  FILE_NAME, FILE_ID, TABLESPACE_NAME, BYTES from dba_data_files;

데이터 파일 추가
alter tablespace spring
add datafile 'c:\java11th\spring03.dbf' size 2m;

데이터파일 크기 재정 조정
create tablespace summer
datafile 'c:\java11th\summer.dbf' size 2m;

alter database datafile 'c:\java11th\summer.dbf' resize 10m;

--자동증가 모드(테이블스페이스의 DB가 용량 초과시 자동으로 용량 증가)
alter database
datafile 'c:\java11th\users.dbf'
autoextend on next 2m maxsize 20m;

--테이블스페이스의 이동(이동시 테이블이 사용되고 있으면 테이블 스페이스 모드 변경후 이동)
-테이블 스페이스의 이동을 위해 오프라인 모드변경
alter tablespace summer offline;
-테이블 스페이스 이동
host move c:\java11th\summer.dbf c:\data
exit

-테이블스페이스 데이터 파일 위치 변경(온라인 전에 이동된 테이블의 위치 지정)
alter tablespace summer
rename datafile 'c:\java11th\summer.dbf' to 'c:\data\summer.dbf';

-테이블 이동후 테이블 온라인 모드변경
alter tablespace summer online;

--테이블스페이스의 삭제
drop tablespace spring;

-데이터가 있는 테이블 스페이스의 삭제[옵션으로 including contents를 추가해주면 된다]
drop tablespace users including contents;

-폴더상의 파일까지 삭제[옵션에 datafiles을 추가해주면 된다]
drop tablespace summer including contents and datafiles;


==실습
1.테이블 스페이스 autumn 생성
-데이터 파일 autumn01.dbf(10m)
2.autumn에 데이터 파일 추가
-데이터 파일 autumn01.dbf(10m) 자동확장 모드 100kb단위,최대 50M
3.데이터딕셔너리에서 정보를 표시
4.autumn테이블 스페이스에 36페이지 쿼리1 수행
create user tom
identified by jerry
default tablespace mouse;
--tom사용자에게 36페이지 데이터 입력
@C:\oracle\ora92\sqlplus\demo\demobld.sql
5.autumn테이블 스페이스 및 연관된 파일 삭제후 결과 확인

--data dictionary
select count(*) from dictionary;
desc dictionary
select table_name from dictionary
where table_name like '%user%';

select * from dba_tables;
select * from user_tables;


==데이터 베이스 모드 변경
--데이터 베이스 모드 확인
archive log list;
--데이터 베이스 모드 변경(archive모드로 변경)
(모드변경은 Mount에서만 가능 하므로 데이타 베이스를 shutdown후 mount까지 다시 startup을 해야함)
shutdown immediate;
startup mount;
alter database archivelog;
archive log list;
--archive프로세스의 활성화
alter database open;(데이타베이스 오픈 단계로 올라감)

-변경된 사항을 OS파일로 만들어줘야 함
-1.파일명의 결정
show parameter log_archive_format

-2.파일저장위치결정
alter system set log_archive_dest_1='location=C:\java11th\archive1' scope=spfile;
alter system set log_archive_dest_2='location=C:\java11th\archive2' scope=spfile;
alter system set log_archive_dest_3='location=C:\java11th\archive3' scope=spfile;

-3.아카이브프로세스자동활성화
alter system set log_archive_start=true scope=spfile;
shutdown immediate;
create pfile from spfile;
startup

-4. startup이 안되는 경우
shutdown immediate;
pfile을 편집
C:\oracle\ora92\database\initJAVA11.ora


create table aa(a number,b varchar2(20),c date default sysdate);
begin
for z in 1..100000 loop
insert into aa values(z,'babo'||z,sysdate);
end loop;
end;
/


==우편번호 자료생성
--1. 테이블 생성
@C:\java11th\zipcode_20040517_oracle\oracle_scheme5.sql;
--2.데이터 생성
@C:\java11th\zipcode_20040517_oracle\zipcode_oracle5.sql;
commit;
--3.데이터 확인
select count(*) from zipcode;
--동이름에 '구서'가 들어가는 주소 출력

9장 테이블
==테이블 생성
create table juso(no number(3),irum varchar2(10),city varchar(10));
--컬럼의 추가
alter table juso
add(tel varchar2(20));
--컬럼의 수정
alter table juso
modify irum varchar2(20);
--컬럼명 변경
alter table juso
rename column tel to hp;
-- 컬럼의 삭제
alter table juso
drop column city;

desc juso;

==데이타타입
create table numtest(a number,b number(5),c number(5,2));
insert into numtest values(10/7,10/7,10/7);
insert into numtest values(100/7,100/7,100/7);
insert into numtest values(1000/7,1000/7,1000/7);
insert into numtest values(10000/7,10000/7,10000/7);
select * from numtest;

create table datetest(a date,b timestamp,c timestamp(0),d timestamp(9),e timestamp with time zone);
insert into datetest values(sysdate,sysdate,sysdate,sysdate,sysdate);
select * from datetest;

==제약명작성 규칙:테이블명_컬럼명_제약종류(p,c,n,u,f)
create table product(
pno number(3) constraint product_pno_p primary key,
pname varchar2(20) constraint product_pname_n not null,
price number(10) constraint product_price_c check (price>=10000));

--제약사항 조건 조회
desc user_constraints;
select table_name from dictionary where table_name like '%CONS%';

desc user_constraints;
select CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME,SEARCH_CONDITION from user_constraints;

create table company(
cno number(3) primary key,
cname varchar2(20) not null,
tel varchar2(20) unique,
ceo varchar2(20),
trade number check (trade>=100));

--제약의 삭제
alter table product
drop constraint product_price_c;

--거래처 테이블의 거래량의 제약삭제
alter table company
drop constraint SYS_C001453;

--제약의 수정(기본적으로 수정이 안되기에 삭제후 재생성을 해줘야 한다)
alter table product
drop constraint PRODUCT_PNAME_N;
alter table product
add constraint product_pname_u unique(pname);
alter table product add cno number(3);
desc product
--외래키제약의 추가
alter table product add constraint product_cno_f foreign key(cno) references company(cno);

insert into company (cno,cname) values (1,'삼성');
insert into company (cno,cname) values (2,'삼보');
insert into company (cno,cname) values (3,'엘지');
insert into company (cno,cname) values (4,'신일');

insert into product values (100,'냉장고',100000,1);
insert into product values (101,'냉동고',120000,3);
insert into product values (102,'김치냉장고',150000,5);
update product set cno=2 where pno=101;
delete company where cno=3;
delete company where cno=2;[에러]

--------
입력수정삭제부모○자식데이터유무확인자식부모데이터유무확인부모데이터유무확인○

== 테이블 레벨의 제약
create table book(
bno number(3),
irum varchar2(20),
price number(10),
publisher varchar2(20),
author varchar2(20),
pubdate date,
constraint book_bno_p primary key(bno),
--constraint book_irum_n not null(irum),
--not null제약은 컬럼레벨만 가능
constraint book_price_c check(price>5000),
constraint book_pubdate_u unique(pubdate));

==EXTERNAL테이블
1.외부데이터생성
2.디렉토리 생성
-디렉토리 생성 권한부여 및 디렉토리 생성
conn /as sysdba;
grant dba to tom;
conn tom/jerry
create directory exdir as 'C:\java11th\exdir';
-테이블생성
create table exjuso(
no number, irum varchar2(20),tel varchar2(20),city varchar2(20))
organization external(type oracle_loader default directory exdir location('11-8-1.dat','11-8-2.dat'));
3.일반테이블 변환(데이터 입력을 하기 위해서는 일반 테이블로 변환)
create table address
as select * from exjuso;
insert into address values(77,'오명일','555-6666','정선');

10장 인덱스와 기타객체
==시쿼스
-테이블 생성
create table goods(
no number constraint goods_no_p primary key,
irum varchar2(20) constraint goods_irum_n not null,
maker varchar2(20));
-넘버를 자동으로 증가
create sequence goods_seq;
-데이타 추가
insert into goods values (goods_seq.nextval,'볼펜','모나미');
insert into goods values (goods_seq.nextval,'지우개','바른손');
insert into goods values (goods_seq.nextval,'샤프','파카');
insert into goods values (goods_seq.nextval,'공책','양지노트');
-현재 생성된 시퀀스의 값
select goods_seq.currval from dual;
-현재 시퀀스의 딕셔너리 확인
select * from user_sequences;
-시퀀스 삭제
drop sequence goods_seq;

==뷰
-뷰테이블 만들기
create view v_employee
as select empno,ename,job,dname from emp,dept where emp.deptno=dept.deptno;

-뷰의 수정
create or replace view v_employee
as select empno,ename,job,dname,sal from emp,dept where emp.deptno=dept.deptno;

==Top-N query
select rownum,empno,ename,sal from emp;
-급여순서대로 출력
select rownum,empno,ename,sal from emp order by sal desc;
-월급을 많이 받는 5명만 출력
select empno,ename,sal from (select empno,ename,sal from emp order by sal desc) where rownum<=5;
-가장 오래된 직원 3사람의 이름,입사일 출력
select ename,hiredate from (select ename,hiredate from emp order by hiredate) where rownum<=3;

-테이블 스페이스 확인
select tablespace_name,file_name from dba_data_files;
-테이블 스페이스 생성
create tablespace hat datafile 'c:\java11th\hat01.dbf' size 2m;
--현재 유저의 테이블 스페이스 정보
select table_name,tablespace_name from user_tables;

-다른 테이블 스페이스에 데이타 파일 만들기
create table ee(a number, b varchar2(20)) tablespace hat;

==프로파일:오라클 자원의 제한에 대한 지정
--프로파일 확인을 위해 시스템 관리자로 접속
conn /as sysdba
--프로파일 확인 쿼리
select * from dba_profiles;
--프로파일 생성
create profile protest limit
FAILED_LOGIN_ATTEMPTS 5 (비번 5회이상 틀릴시 계정잠김)
SESSIONS_PER_USER 3 (동시접속자 지정 3명 이상 접속시 접속불가)
IDLE_TIME 10
CONNECT_TIME 3;
--정렬해서 보기
select * from dba_profiles order by profile;
--프로파일과 사용자 연결
alter user tom profile protest;
--잠긴 계정 풀기
alter user tom account unlock;

프로파일에서 커널의 자원은 시스템의 파라미터값을 변경해야만 적용된다
--시스템의 파라미터값을 적용
alter system set resource_limit=true;
==패스워드 함수 만들기
C:\oracle\ora92\rdbms\admin\utlpwdmg.sql 편집하여 11-9.sql로 저장후 실행
--함수생성
@C:\java11th\11-9.sql
--프로파일 확인
select * from dba_profiles order by profile;
--프로파일에 패스워드 함수 적용
alter profile protest limit PASSWORD_VERIFY_FUNCTION tom_function;
--사용자로부터 프로파일 제거
alter user tom profile default;
--프로파일 삭제
drop profile protest;

실습)))
1. 사용자 MOUSE/KEYBOARD 생성
create user mouse identified by keyboard;
grant connect,resource to mouse;

2. 패스워드 함수 생성(MOUSE_FUNCTION)
@C:\java11th\11-9.sql

3. 프로파일 PROTEST2 생성
create profile protest2 limit
PASSWORD_LIFE_TIME 3
SESSIONS_PER_USER 5
FAILED_LOGIN_ATTEMPTS 2;
패스워드 함수(MOUSE_FUNCTION 사용)
alter user tom profile protest;

4. PROTEST2를 사용자 MOUSE에게 적용
alter profile protest2 limit PASSWORD_VERIFY_FUNCTION mouse_function;

5. 사용자의 암호변경
alter user mouse identified by abcd;

6. 사용자로부터 프로파일 제거
alter user tom profile default;

7. PROTEST2 프로파일 삭제
drop profile protest;

==인덱스
-사용자 삭제
//////////////////////////////////////////////////////////////////////////////////
drop user tom cascade;
-사용자 생성
create user happy
identified by day default tablespace winter;
grant connect,resource to happy;
create tablespace indx datafile 'c:\java11th\indx.dbf' size 10m;
//////////////////////////////////////////////////////////////////////////////////
-해피유저로 접속
conn happy/day
select * from tab;
-테이블 생성
create table product(
pno number(3) constraint product_pno_p primary key,
irum varchar2(10) constraint product_irum_n not null,
barce varchar2(10) constraint product_barcd_u unique,
price number(10) constraint product_price_c check(price>10000),
maker varchar2(10));

-유저에 대한 객체보기
desc user_objects;
-객체에 대한 내용 보기
select OBJECT_NAME,OBJECT_TYPE from user_objects;
-인덱스 생성
create index i_product_price on product(price);

==생성된 인덱스가 위치한 테이블스페이스 정보 확인
desc user_indexes;
select INDEX_NAME,TABLE_NAME,TABLESPACE_NAME from user_indexes;
-테이블 위치 확인
select TABLE_NAME,TABLESPACE_NAME from user_tables;
select INDEX_NAME,TABLE_NAME,TABLESPACE_NAME from user_indexes union
select '',TABLE_NAME,TABLESPACE_NAME from user_tables;

-테이블 만들기(테이블과 인덱스를 따로 만드는걸 원칙으로 한다)
create table customer(cno number constraint customer_cno_p primary key
using index(create index i_customer_cno on customer(cno) tablespace indx),
cname varchar2(10),ceo varchar2(10));

-ceo만 따로 인덱스를 만들때
create index i_customer_ceo on customer(ceo) tablespace indx;

-기본키 지우기
alter table customer
drop constraint customer_cno_p;

--함수 기반 인덱스(함수 기반의 인덱스는 일반사용자가 못 만든다)
-권한
conn /as sysdba
grant dba to happy;
conn happy/day
-인덱스 생성
create index i_customer_cname
on customer(upper(cname));

==사용자와 권한
conn /as sysdba
-사용자 생성
create user m1 identified by m1;
create user m2 identified by m2;
create user m3 identified by m3;
-시스템 권한자 조회
select * from dba_sys_privs;

--권한부여
-데이터 베이스 접속과 테이블을 생성할수 있는 권한 부여(다른 유저에게 권한 부여 불가)
grant create session,create table to m1;
-데이터 베이스 접속과 테이블을 생성할수 있는 권한 부여와 어드민 옵션을 포함하여 다른 유저에게 권한 부여가능
grant create session,create table to m2 with admin option;

-권한 회수
revoke create session,create table from m2;

--롤생성(수없이 많은 권한을 하나의 객체로 만드는 방법)
create role role1;
--롤에 권한 부여
grant create session,alter session,create table,create user to role1;
--롤의 사용자 부여
grant role1 to m1;
--롤 회수
revoke role1 from m1;
--롤의 삭제
drop role role1;

-권한 생성
grant connect,resource to m1;
grant connect,resource to m2;
grant connect,resource to m3;

-m2사용자가 m1사용자의 셀렉트와 인서트를 할수 있도록 권한 부여
grant select,insert on dept to m2;
grant select on emp to public;

-권한 부여후 실습
revoke select,insert on dept from m1;
revoke select on emp from public;

--객체롤의 생성
create role objrole;
conn m1/m1
grant select,update on dept to objrole;
grant select on emp to objrole;
-권한은 시스템 관리자로 재로긴후 설정
grant objrole to m3;
update m1.dept set loc='서울';
select * from m1.dept;


==JDBC환경설정
--오라클은 스타드업상태
1. C:\jdbc/ojdbc14.jar 다운로드
2. classpath 지정
C:\jdbc/ojdbc14.jar
3. 확인-명령프로프트 창에서
echo %classpath%

==데이터베이스 전체백업
host
copy C:\java11TH\*.* C:\java11th\backup
exit
conn happy/day
select * from tab;
select tablespace_name,table_name from user_tables;
create table tt(
   a number,
   b varchar2(20),
   c date
);
begin
   for z in 1..100000 loop
       insert into tt values (z,'babo'||z,sysdate);
   end loop;
end;
/
select count(*) from tt;
select tablespace_name,table_name from user_tables;
conn /as sysdba
select tablespace_name,file_name from dba_data_files;
경우1)startup시 데이터파일의 손상이 있는 경우
shutdown immediate;
--데이터파일손상(C:\JAVA11TH\WINTER01.DBF확장자변경)
startup
--데이터파일복원
host
copy C:\java11th\backup\winter01.dbf d:\java11
exit
--데이터파일복구
recover database
AUTO
alter database open;
conn happy/day
select count(*) from tt;
경우2)db사용중 데이터파일의 손상
conn happy/day
begin
   for z in 1..3000 loop
       insert into tt values (z,'kkk'||z,sysdate);
   end loop;
end;
/
commit;
select count(*) from tt;
conn /as sysdba
alter tablespace winter offline;
--데이터파일손상(C:\JAVA11th\WINTER01.DBF확장자변경)
alter tablespace winter online;  ==>err
--데이터파일복원
host
copy C:\java11th\backup\winter01.dbf d:\java11
exit
--데이터파일복구
recover tablespace winter;
auto
alter tablespace winter online;
conn happy/day
select count(*) from tt;

경우3)startup시 데이터파일의 손상이 있는 경우(먼저startup후 복구)
conn happy/day
delete tt where a<5000;
commit;
select count(*) from tt;
conn /as sysdba
shutdown immediate;
--데이터파일손상
startup
alter tablespace winter offline;
--데이터파일 모드변경
alter database datafile 'C:\JAVA11TH\WINTER01.DBF' offline;
또는
alter database datafile 3 offline;
alter database open;
--데이터파일복원
host
copy C:\java11th\backup\winter01.dbf d:\java11
exit
--데이터파일복구
recover datafile 3
또는
recover datafile 'C:\JAVA11TH\WINTER01.DBF'
--데이터파일 모드변경
alter database datafile 'C:\JAVA11TH\WINTER01.DBF' online;
또는
alter database datafile 3 online;
conn happy/day
select count(*) from tt;

경우4)백업본이 없는 경우
conn /as sysdba
create tablespace summer
   datafile 'C:\java11th\summer.dbf' size 10m;
conn happy/day
create table yy(
a number,
b varchar2(20),
c date
)
tablespace summer;
begin
   for z in 1..1000 loop
       insert into yy values (z,'aaa'||z,sysdate);
   end loop;
end;
/
commit;
select count(*) from yy;
select table_name,tablespace_name from user_tables;
conn /as sysdba
alter tablespace summer offline;
--데이터파일손상
alter tablespace summer online;
--데이터파일경로변경
alter database
   create datafile 'C:\JAVA11TH\summer.DBF' as
        'C:\JAVA11TH\summer01.DBF';
--데이터파일 복구
recover datafile  'C:\JAVA11TH\summer01.DBF';
alter tablespace summer online;
conn happy/day
select count(*) from yy;

==과제==
1)테이블스페이스 rain을 만들고 스콧사용자 생성후 기본 테이블
create tablespace rain
   datafile 'c:\java11th\rain.dbf' size 10M;
스페이스로 rain을 사용하도록 지정
alter user scott
identified by tiger
default tablespace rain;
2)36페이지 스크립트를 돌려서 스콧사용자에게
테이블과 데이터 입력
grant connect,resource to scott;
@C:\oracle\ora92\sqlplus\demo\demobld.sql
3)데이터베이스 shutdown
4)테이블스페이스 rain이 사용하는 데이터파일 손상
5)startup+
6)복원시켜서 스콧사용자의 테이블과 데이터 확인
alter database datafile 'C:\JAVA11TH\rain.DBF' offline;
alter database open;
alter database create datafile 'C:\JAVA11TH\rain.DBF' as 'C:\JAVA11TH\rain01.DBF';
recover tablespace rain;
alter database datafile 'C:\JAVA11TH\rain01.DBF' online;

--컨트롤 파일 생성(trc파일에서 불러옴)
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "JAVA11" NORESETLOGS  ARCHIVELOG
--  SET STANDBY TO MAXIMIZE PERFORMANCE
   MAXLOGFILES 4
   MAXLOGMEMBERS 3
   MAXDATAFILES 200
   MAXINSTANCES 1
   MAXLOGHISTORY 226
LOGFILE
  GROUP 1 'C:\JAVA11TH\REDO01.LOG'  SIZE 10M,
  GROUP 2 'C:\JAVA11TH\REDO02.LOG'  SIZE 10M
-- STANDBY LOGFILE
DATAFILE
  'C:\JAVA11TH\SYSTEM01.DBF',
  'C:\JAVA11TH\UNDO.DBF',
  'C:\JAVA11TH\WINTER01.DBF',
  'C:\JAVA11TH\SHOP01.DBF',
  'C:\JAVA11TH\HAT01.DBF',
  'C:\JAVA11TH\INDX.DBF',
  'C:\JAVA11TH\PROFESSIONAL.DBF',
  'C:\JAVA11TH\SUMMER.DBF',
  'C:\JAVA11TH\RAIN01.DBF'
CHARACTER SET KO16KSC5601;

==컨트롤 파일 생성여부 확인
alter database open;