I. 설치하기
설치과정은 아래 링크 참조
http://blog.naver.com/hhl0517?Redirect=Log&logNo=60137929642
II. SQL Admin 관련 모음
* 테이블스페이스 dbf파일 확인
select * from DBA_DATA_FILES;
1. 해당 데이터파일의 크기를 재조정
ALTER DATABASE DATAFILE 'D:\ORACLE\ORADATA\KIRIO1\A1_CLON\A1.DBF'
RESIZE 1100M;
2. 해당 테이블 스페이스에 새로운 데이터파일 추가
ALTER TABLESPACE DATA ADD DATAFILE 'd:\oracle\oradata\kirio1\data01.dbf'
SIZE 100M;
3. 해당 데이터파일에 AUTOEXTEND ON 설정
ALTER DATABASE DATAFILE 'D:\ORACLE\ORADATA\KIRIO1\A1_CLON\A1.DBF'
AUTOEXTEND ON;
------------------
-- 테이블 스페이스 생성
create tablespace hiplaza_TS datafile 'D:\oradata\ora9i\hiplaza_TS_01.dbf' size 200M
AUTOEXTEND ON NEXT 100M MAXSIZE 2000M
-- 인덱스 테이블 스페이스 생성
create tablespace hiplaza_IDXTS datafile 'D:\oradata\ora9i\hiplaza_IDXTS_01.dbf' size 100M
AUTOEXTEND ON NEXT 50M MAXSIZE 1000M
-- 임시 테이블 스페이스 생성
create temporary tablespace hiplaza_TMPTS
tempfile 'D:\oradata\ora9i\hiplaza_TMPTS_01.dbf' size 50M
AUTOEXTEND ON NEXT 10M MAXSIZE 500M;
-- 사용자 생성
create user hiplaza identified by HIPLAZA
default tablespace hiplaza_TS
temporary tablespace hiplaza_TMPTS;
-- 사용자에 권한 부여
GRANT connect, resource TO hiplaza;
-- 사용자 삭제
-- Drop user hiplaza cascade
-- 테이블 스페이스 삭제
-- drop tablespace hiplaza_TS including content
-- drop tablespace hiplaza_IDXTS including content
-- drop tablespace hiplaza_TMPTS including content
-- 오라클 종합 요약
--nvl(comm,0) --> comm이 널이면 0으로 대체
--nvl2(comm, 수식1, 수식2) comm이 널이 아니면 수식1을, 널이면 수식2를 반환
--coalesce(수식1, 수식2, 수식3, ...) 수식1이 널이 아닐 경우에 반환하고 널이면 수식2를, 수식2도 널이
면 수식3을.... 반환
--|| --> 두 컬럼을 한컬럼으로 출력 (문자/숫자 구분없이 결합함)
--distinct --> 중복열을 제거하여 오름정렬 후 출력
--chr(65) --> 아스키65값을 문자A로 표시
--ascii('A') --> 문자 A 를 아스키값 65로 표시]
alter session set nls_date_format= -- sssss : 오늘 하루를 초로 변환하여 나타냄...
'yyyy-mm-dd hh:mi:ss sssss am'; -- hh : 12시간제, hh24 : 24시간제로 나타냄... (현재 세션에서만 적용됨)
select value from v$parameter where name='db_block_size'; --환경정보확인(블럭사이즈)
show parameter db_block_size; -- 기본환경정보 확인 (블럭사이즈)
***문자처리함수***
--upper(' ') --> 대문자로 변환
--lower(' ') --> 소문자로 변환
--initcap(' ') --> 첫 문자만 대문자로 변환, 나머지는 소문자로 변환
--to_char(sysdate, 'yyyy-mm-dd') --> 날짜를 포맷형식으로 출력하라...
--sysdate --> 현재 시간/날짜
--current_date --> sysdate와 같다...
--like에서 _는 글자 하나, %는 모든것..
--substr('abcde',3,2) --> 문자열중 3번째부터 2글자 출력 (한글도 1을 1글자로 인식)
--substr('abcde',3) --> 문자열 중에서 3번째부터 끝까지...
--concat('ab','cd') --> 두 문자열을 결합함...
--length('aa') 또는 langthb('aa') --> 문자열의 길이 또는 바이트 수 구하기
--instr('abcdabcd','c',1,2) --> 문자열에서 c가 첫문자부터 세어서 두번째 나오는 위치값 출력
--lpad('korea', 10, '*') --> 전체 10자리로 맞추고 빈칸은 왼쪽으로 *로 채운다.
--rpad('korea', 10, '-') --> 전체 10자리로 맞추고 빈칸은 오른쪽으로 -로 채운다.
--ltrim('aaaabaaba','a') --> 왼쪽에서 반복되는 문자열을 지워라... (최초로 반복되는 문자열까지만...)
지울 문자열을 생략하면 공백을 지운다.
--rtrim('aaaabaaba','a') --> 오른쪽에서 반복되는 문자열을 지워라... (최초로 반복되는 문자열까지
만...) 지울 문자열을 생략하면 공백을 지운다.
--translate('hallp','ap','eo') --> a를 e로, p를 o로 변환하여 hello를 출력
--replace('orahome','ora','오라') --> ora를 오라로 변환 대치하여 출력...
--reverse('oracle') --> 거꾸로 출력함....
***숫자처리함수***
--floor(2.9) --> 실수에서 작은 정수값 취함
--ceil(2.9) ---> 실수에서 큰 정수값 취함
--mod(5, 2) ---> 5/2에서 나머지값을 취함
--round(321.123, 2) --> 소수 3자리에서 반올림하여 2자리로 표시함
--truncate(12.34, 1) --> 소수 첫째자리만 나타냄(버림)
--power(2,3) --> 2의 3승 값
--sqrt(3) --> 루트3의 값
***날짜처리함수***
--months_between(sysdate, sysdate) --> 날짜 차이를 월단위로 구함...
--add_months(sysdate, 4) --> 4개월을 더한 날짜를 출력...
--next_day(sysdate,'월요일') --> 현재날짜로부터 가장 빠른 월요일 날짜를 구하기...
--last_day(sysdate) --> 현재달의 마지막 날을 구함...
--to_char(sysdate,'yyyy') --> 연도 네자리만 출력함 mm, dd, hh, mi, ss 등 가능함...
--to_date('2005-12-12', 'yyyy-mm-dd') 문자열을 날짜및 시간형식으로 변환
--extract(year from sysdate) --> sysdate에서 년도/월/일 만 추출함...
--extract(month from sysdate)
--extract(day from sysdate)
***to_char 변환옵션들...***
q : 분기표시 1 2 3 4
ww: 올해 1월1일부터 계산해서 몇주째인지..
w : 현재 달에서 몇주째인지..
d : 현재 몇요일인지 일(1), 월(2), ... , 토(7)
dd: 현재 달에서 몇일인지...
ddd: 올해 1월1일부터 현재 몇일째인지..
day: 몇요일인지...
dy: 몇요일인지.. 약자로..
mon:
month:
year:
***변환형함수***
----------------------------------------|
-- to_date <------ to_char |
--날짜 <-------> 문자 <--------> 숫자 |
-- to_char -------> to_number |
----------------------------------------|
--to_char(hiredate, 'fmyyyy*mmfm*dd') --> fm~fm: 숫자앞에0을 빼버림
--to_char(hiredate, 'year-mmsp-ddspth') --> 모두 영문으로 표기함..
--to_char(sal, 'L9,999') -->통화기호 및 컴마 표시
--to_dsinterval('007 14:30:10') --> 일 시:분:초 (sysdate와의 계산식에 사용)
--to_yminterval('01-03') --> 년-월 ( " )
***rownum***
결과에 로우컬럼을 추가하여 하나씩 증가시킴...
***case***
select case 3 when 5-2 then 'a'
when 2-2 then 'b'
else '몰라'
end "case 연습"
from dual;
***case 부등호 조건 쓰기***
case when sal > 5000 then .....
***decode***
select decode(2, 5-2, '5-2',
2-2, '2-2',
'몰라')
from dual;
***inline view*****
select T.* -- select 문 자체를 뷰화 하여 쿼리함..
from (select * from emp) T;
*** ***
select deptno, job, sum(sal)
from emp
group by rollup(deptno, job)
--rank() over(order by sal desc) --> sal컬럼에 대한 석차구하기 (공동등수일때 다음 등수 건너뛰기)
--rank() over(partition by deptno order by sal desc) --> deptno에 따라서 sal컬럼에 대한 석차 구하기
--dense_rank() over(order by sal desc) --> 공동석차일 경우 다음 순위에 공동순위만큼 등수 건너뛰지 않음
***조인***
--> 조인시 from절에서 로우값이 많은 테이블을 먼저 쓸것.
--> from에서 가장 끝에 기술 된 테이블이 구동테이블로 잡히고 무조건 테이블 full-scan되어진다.
(로우값이 작은 테이블을 구동시키고 로우가 큰 테이블을 참조시킴)
--> 한쪽만 인덱스가 있다면 from절에서 위치 상관 없이 인덱스 없는쪽이 자동으로 구동테이블이 된다.
(기본 블럭사이즈 = 8KB)
block -> extent -> segment(table/index) -> tablespace -> database -> dataware-house
--equi join
inner join과 같음.... 일반적인 쪼인....
--non equi join
select E.ename, E.sal, G.grade, G.losal, G.hisal
from emp E, salgrade G
where E.sal between G.losal and G.hisal;
--left outer join
select D.department_id, E.first_name, E.last_name, E.salary
from hr.employees E, hr.departments D
where E.department_id=D.department_id(+);
--right outer join
select D.department_id, E.first_name, E.last_name, E.salary
from hr.employees E right join hr.departments D
on E.department_id = D.department_id;
--full outer join
select D.department_id, E.first_name, E.last_name, E.salary
from hr.employees E full join hr.departments D
on E.department_id = D.department_id;
--self join
select A.empno, A.ename, A.job, A.mgr, B.ename, B.job
from emp A, emp B
where A.mgr = B.empno(+);
***union / intersect / minus***
--union : 두 쿼리문을 같은 컬럼으로 로우붙이기 하여 이어서 보여줌...
select * from jumunold
union
select * from jumun;
--차이점 ( union / union all )
--첫번째컬럼으로 자동 asc sort ( O / X )
--결합시 중복되는 로우는 제거 ( O / X )
--intersect : 교집합
--minus : 차집합
*** rank (row number) ***
*** 쿼리출력을 새로운 테이블로...***
--oracle
create table jumunold
as select * from jumun
--ms sql
select * into jumunold
from jumun
*** exists & 상관서브쿼리 ***
in()을 사용하는것 보다 처리 속도가 빠름...
------------------------------------------------------------------------
select * from user_users --계정정보(디폴트테이블스페이스)
select * from user_tablespaces --테이블스페이스정보
select * from USER_SYS_PRIVS --시스템 권한 부여 확인
select * from USER_TABLES --해당 계정 테이블에 대한 정보 확인
select * from USER_ROLE_PRiVS --자기 롤 확인
select * from USER_TS_QUOTAS --자기 테이블스페이스 쿼타 확인
select * from user_free_space --사용 할 수 있는 빈 공간 테이블 스페이스 크기
select * FROM user_SEGMENTS --자기 세그먼트들을 확인(테이블/인덱스)
SELECT * FROM USER_CONS_COLUMNS --제약조건들 목록을 확인
SELECT * FROM USER_INDEXES -- 자기 인덱스 확인하기....
SELECT * FROM USER_IND_COLUMNS -- 자기 인덱스 확인하기....
SELECT * from user_tab_columns --테이블 컬럼 구조 확인 ..
SELECT * FROM USER_CONSTRAINTS --제약조건 검색
select * from user_tab_comments --현재 계정의 모든 테이블에 주석문을 보여준다...
select * from user_col_comments --컬럼 주석문 검색하기...
select * from role_sys_privs --현재 사용자에게 적용된 시스템 role 검색
select * from dba_roles --시스템 기본 role 검색하기....
select * from dba_data_files --sys계정이 database files 정보 확인하기
select * from dict_columns where table_name = 'DBA_DATA_FILES' --dba_data_files 테이블의 정보 검색
select * from user_catalog --자신의 소유의 존재하는 테이블 및 뷰 검색하기....
SELECT * FROM SYSTEM_PRIVILEGE_MAP --SYSTEM PRIVILEGE 종류 검색....
SELECT * FROM TABLE_PRIVILEGE_MAP --TABLE PRIVILEGE 종류 검색...
--오브젝트 검색할 수 있는 테이블 ---> user_tables < user_catalog = tab = cat < user_objects
--현재 사용중인 테이블스페이스 파일 공간 확인하기....
select a.tablespace_name,
a.file_name,
a.bytes,
b."free byte",
(a.bytes - b."free byte") "사용중인 공간"
from dba_data_files a, (select tablespace_name, sum(bytes) "free byte"
from dba_free_space
group by tablespace_name
) b
where a.tablespace_name = b.tablespace_name
---------------------
--제약조건 추가.....
---------------------
--제약조건 검색하기...
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME='BUSEO'
--디폴트옵션도 조회하여 모두 복사함....
SELECT COLUMN_NAME, DATA_DEFAULT
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = 'BUSEO'
-- primary key 부여하기....
ALTER TABLE BUSEO
ADD CONSTRAINT BUSEO_BUNO_PK PRIMARY KEY(BUNO)
--참조키 해제하기...
ALTER TABLE SAWON --테이블을 드롭하기 위하여 참조키를 해제함
DROP CONSTRAINT SAWON_DEPTNO_FK
-- NOT NULL 해제하기....
ALTER TABLE BUSEO
DROP CONSTRAINT SYS_C003051 --> constraint 이름
--또는
ALTER TABLE BUSEO
MODIFY ZIPCODE VARCHAR2(7) NULL
-- DEFAULT 부여하기....
ALTER TABLE BUSEO
MODIFY ZIPCODE DEFAULT ' '
-- NOT NULL 부여하기
ALTER TABLE BUSEO
MODIFY BUNO CONSTRAINT BUSEO_BUNO_NN NOT NULL
--컬럼 추가하기...
alter table sawon --8i 이상에서는 이렇게 쉽게 가능함..
add email varchar2(30);
--또는
ALTER TABLE BUSEO --컬럼 추가하기 ( NOT NULL 옵션 동시에 걸어주기.)....
ADD ZIPCODE VARCHAR2(7) DEFAULT ' ' CONSTRAINT BUSEO_ZIPCODE_NN NOT NULL
--컬럼 이름 변경하기....
alter table sawon
rename column addr to juso
--컬럼 삭제하기....
alter table sawon
drop (email)
--컬럼 UNUSED 하기
ALTER TABLE SAWON SET UNUSED(EMAIL)
--또는
ALTER TABLE SAWON SET UNUSED COLUMN EMAIL
--UNUSED상태에 있는 컬럼명 조회
SELECT *
FROM USER_UNUSED_COL_TABS
--실제로 UNUSED 상태의 컬럼을 삭제하기.....
ALTER TABLE SAWON
DROP UNUSED COLUMNS
--테이블 이름 변경
RENAME BUSEOCP TO BUSEO --BUSEOCP --> BUSEO 이름 변경
--주석문 달기
comment on table sawon is '우리회사 사원들의 테이부르'
comment on column buseo.jitel is '지역전화번호'
select * from user_tab_comments
select * from user_col_comments
--dept 테이블을 다른 스페이스(users)로 옮기기
ALTER TABLE SCOTT.DEPT MOVE TABLESPACE USERS --하나하나 옮기기
-- 일반 사용자가 만든 세그먼트들 검색....
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME = 'SYSTEM'
AND OWNER IN ( SELECT USERNAME --USER_ID가 60이상이면 일반사용자임...
FROM DBA_USERS
WHERE USER_ID > 60 OR USERNAME = 'SCOTT' )
--------------------------------------
--권한부여의 종류와 특정 사용자에게 권한 부여하기
select * from table_privilege_map
grant select on sawon to scott
-----------------------------------------------------------
--사용자 계정 생성하기.....
--계정생성 순서
-> 계정생성 -> 세션(로긴)허용 -> 해당테이블스페이스 쿼터 할당
-----------------------------------------------------------
CREATE USER ORAUSER2 IDENTIFIED BY TPDLF
DEFAULT TABLESPACE SALESTBS
TEMPORARY TABLESPACE TEMP
QUOTA 5M ON SALESTBS --SALESTBS 테이블스페이스는 5MB 할당
QUOTA UNLIMITED ON TEMP --임시 테이블스페이스는 모두 할당
GRANT CREATE SESSION TO ORAUSER2 --로긴 권한 부여
GRANT CREATE TABLE TO ORAUSER2 --테이블 생성 권한 부여
----------------------------------------------------------------------
--사용자 계정 삭제하기...(해당 계정소유의 오브젝트가 있다면 바로 삭제가 불가능함..)
----------------------------------------------------------------------
DROP USER ORAUSER1
DESC DBA_OBJECTS
SELECT * --삭제할 계정소유의 오브젝트 확인하기...
FROM DBA_OBJECTS
WHERE OWNER='ORAUSER1'
DROP TABLE ORAUSER1.MYTAB --삭제할 계정소유의 테이블 먼저 삭제함...
--또는 삭제할 계정을 삭제할 때 그 소유의 오브젝트를 모두 지우고 삭제함..
DROP USER ORAUSER1 CASCADE
-----------------------------
--뷰생성 --view---
-----------------------------
create or replace view v_emp10 --새로 생성하거나 기존의 뷰를 재정의 할 때 쓰여짐.....
as
select empno, ename, sal, deptno
from emp
where deptno = 10
--create or replace trigger
--create or replace procedure
--truncate 는 DDL문이므로 rollback 이 불가함....
truncate table sawon --> 사원테이블의 모든 입력값을 지우고 high water mark 까지도 초기화함...
delete table_name -----> 이 역시 모든 입력값이 지워지지만 H.W.M 는 초기화되지 못한다.
** commit 은 DML문에서만 가능하고 그 이외의 명령문은 auto commit 이다.
** DML이외의 명령문이 샐행 되어지면 commit이 실행되므로 그 이전에 dml문까지도 commit이 실행되어져
rollback 불가함
------------------------------------------------------------------
**MS-SQL에서는 BEGIN TRAN <-----> ROLLBACK TRAN 사이에 넣은 문들은
DML,DDL,DCL등 관계없이 모두 ROLLBACK 되어짐......
------------------------------------------------------------------
--암호재정의
alter user user_ identified by password_
-----------------------------
--external table 생성하기....
-----------------------------
--My-Sql 에서 데이타베이스 내용 추출하기...
C:\> bcp pubs.dbo.stores out c:\ext_data\stores.txt -c -t"," -r\n -Usa -P****** -Sclass-b108
--오라클에서 external table로 사용 할 디렉토리 정의
create or replace directory stores_dir AS 'c:\ext_data\'
--external table 생성
--------------------------------------------------------------------------------
create table scott.stores_ext --external 테이블 정의
( stor_id varchar2(4) --external 테이블 정의
, stor_name varchar2(40) --external 테이블 정의
, stor_address varchar2(40) --external 테이블 정의
, city varchar2(20) --external 테이블 정의
, state varchar2(2) --external 테이블 정의
, zip varchar2(5) --external 테이블 정의
)
organization external --위에서 정의 한 테이블을 external로 정의
( type oracle_loader
default directory stores_dir --생성되는 external 테이블 디렉토리
access parameters
( records delimited by newline --개행문자'\n'에 의한 레코드 구분 정의
badfile 'stores_bad.bad' --데이타형식이 틀려서 입력 안된 로우가 기록되는 파일
logfile 'stores_log.log' --기본 로그파일 정의
fields terminated by ',' --컬럼 구분자를 정의
( stor_id char -- 컬럼 구조 정의
, stor_name char -- 컬럼 구조 정의
, stor_address char -- 컬럼 구조 정의
, city char -- 컬럼 구조 정의
, state char -- 컬럼 구조 정의
, zip char) -- 컬럼 구조 정의
)
location('stores.txt') --외부에서 추출한 데이타파일
)
reject limit unlimited --bad파일 로우 제한 설정
---------------------------------------------------------------------------------
desc scott.stores_ext
select * from scott.stores_ext
SELECT * FROM DICT WHERE UPPER(COMMENTS) LIKE '%EXTERNAL%'
USER_EXTERNAL_TABLES
USER_EXTERNAL_LOCATIONS
