반응형
ADMIN
SYSDBA
- OS에 유저가 ORA_DBA에 속해있으면 sysdba로 접속이 가능하다.
- sysdba 접속
sqlplus /nolog
conn /as sysdba설치파일
DeveloperSuite10g
- 오라클 클라이언트
C:\DevSuiteHome\NETWORK\ADMIN\tnsnames.ora - OracleDB 접속을 위한 서버 환경 정보
로컬 시스템 Hosts 정보 - C:\Windows\System32\drivers\etc\hosts
Oracle Job을 등록하여 프로시저를 실행. 일정 주기로 데이터 가공을 하는 방법
DB 환경설정 정보
- 테이블에 환경설정정보를 저장한다.
- DB명, 로그경로, 이메일..
RAC, HA
- 여러개의 인스턴스를 모아 하나의 서버로 구성한것.
- SID, ServiceName
- SID는 DB하나의 인스턴스
- ServiceName은 여러개의 인스턴스를 모아 하나의 서버 혹은 시스템으로 구성한 것.
ORCL = --> orcl: 설치시에 주었던 이름 SID
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = SISTF176)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = SISTF177)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)데이터베이스 링크
- 접속한 데이터베이스 내에서 다른 데이터베이스 접속을 하기 위한 접속 설정을 정의하는 오라클 객체.
--링크생성
CREATE DATABASE '링크이름'
CONNECT TO SCOTT
IDENTIFIED BY TIGER
USING '서비스이름'; -- tns에 서비스 정보가 있어야함.
--링크 삭제
DROP DATABASE LINK '링크이름';
-- 링크된 서버의 테이블에 데이터 삽입
insert into emp@dev.server.com
select * from emp where name = 'test';
--링크된 서버의 테이블에 데이터 삭제
delete emp@apps.server.com where name ='test';View 테이블
-- v$ ~ Oracle에서 제공하는 View 테이블
SELECT * FROM v$session; -- SESSION에 대한 로그 확인
SELECT * FROM v$version;
SELECT * FROM v$database; -- SID
SELECT * FROM v$thread;
SELECT * FROM v$parameter;DBA 테이블
-- dba ~ Oracle에서 제공하는 DBA 테이블
SELECT * FROM dba_data_files;
SELECT * FROM dba_objects;
..Object 조회
SELECT * FROM ALL_OBJECTS; --OBJECT 조회
SELECT * FROM ALL_TABLES; -- TABLE 조회
SELECT * FROM ALL_SYNONYMS -- SYNONYM 조회
SELECT * FROM ALL_IND_COLUMNS -- INDE
SELECT * FROM ALL_TAB_COLUMNS --COLUMN
SELECT * FROM ALL_TAB_COMMENTS -- COMMENT
SELECT * FROM ALL_COL_COMMENTS -- COMMENT유저, 롤, 권한 설정
- 권한
- 시스템 권한( Create Table, Create View, Insert, Update, Delete...)
- ANY 권한은 자기 소유 외의 오브젝트에 대한 허가.
- 테이블 권한(SELECT, INSERT, UPDATE, DELETE)
- 테이블 생성자가 권한을 부여할 수 있다.
- 시스템 권한( Create Table, Create View, Insert, Update, Delete...)
- SYNONYM
- 네임스페이스 기능
- TABLE_OWNER가 아닌 OWNER는 DML시 네임스페이스 생략됨.
- DDL의 경우 TABLE_OWNER를 명시해야함.
- 패키지 실행 시 패키지 소유자의 권한으로 실행
- APPS로 패키지를 생성하고, APPS에 생성된 테이블에 대한 권한을 주는 이유.
CREATE USER team IDENTIFIED BY 1234; -- 계정 생성
SELECT * FROM all_users; -- 유저
SELECT * FROM dba_users; -- 유저
SELECT * FROM user_sys_privs; -- 유저 시스템 권한
SELECT * FROM user_tab_privs; -- 유저 테이블 권한
SELECT * FROM user_role_privs; -- 유저 롤
SELECT * FROM dba_roles; -- 롤 목록
SELECT * FROM role_role_privs; -- 롤에 대한 롤
SELECT * FROM role_sys_privs; -- 롤 시스템 권한
SELECT * FROM role_tab_privs; -- 롤 테이블 권한
GRANT SELECT, INSERT, DELETE, UPDATE ON [TABLE NAME] TO [USER] [WITH GRANT OPTION]; -- 권한부여
REVOKE SELECT, INSERT, DELETE, UPDATE ON [TABLE NAME] FROM [USER] ; -- 권한취소
**-- ROLE
CREATE ROLE manager; -- Role 생성
GRANT create session, create table TO manager; -- Role 권한 부여
GRANT manager TO scott, test; -- Role 부여**
-- SYNONYM
CREATE SYNONYM XXCRSH.NEW_EMPLOYEE FOR APPS.NEW_EMPLOYEE; -- XXCRSH가 NEW_EMPLOYEE의 소유자가 됨.
-- 모듈별 권한 부여
GRANT SELECT, INSERT, DELETE, UPDATE ON XXCRSH.* TO XXCRSH ; -- 권한부여
GRANT SELECT, INSERT, DELETE, UPDATE ON XXPERH.* TO XXPERH ; -- 권한부여
GRANT SELECT, INSERT, DELETE, UPDATE ON *.* TO APPS ; -- 전체 권한 부여유저에게 특정 테이블에 특정 쿼리를 지정해 줄 수 있다.
View 권한별 조회 가능 데이터 설정
View를 사용하여 권한별로 조회 가능한 데이터를 설정할 수 있다.
해지한 DB와 유효한 테이블을 합쳐서 보여줄 수 있다.
SELECT * FROM user$; -- sys.user$ SELECT * FROM user_tab_privs; -- sys.user$ 의 권한을 확인하여 조회 가능한 데이터만 보여줌.
Concurrent
프로시저(프로그램)를 Concurrent로 등록하여 사용한다. (배치와 스케줄러 개념)
DBMS_JOB.submit : job 등록
DBMS_JOB.remove : 제거
DBMS_JOB.change : 변경
DBMS_JOB.next_date : job의 다음 수행시간 변경
DBMS_JOB.interval : job의 실행 cycle 지정
DBMS_JOB.what : job 수행 으로 등록된 object 를 변경
DBMS_JOB.run : job을 수동으로 실행I/F
영향도분석
- 문자열 검색을 통해서 DB Object에서의 영향도 분석을 진행할 수 있음.
- 해당 DB Object 변경 시 연계되는 변경사항들을 분석.
클로닝
운영 DB의 데이터를 개발 DB에 반영하는 작업
- 주기적인 클로닝을 통해 운영환경과 같은 데이터를 기반으로 개발을 진행할 수 있다.
오라클 데이터 복구
SELECT *
FROM TEST_TABLE AS OF TIMESTAMP(SYSTIMESTAMP-INTERVAL '10' MINUTE)
WHERE ID = '1234'lock
### lock
```sql
-- lock 걸린 개체 확인
SELECT OBJECT_ID
, SESSION_ID -- SID
, ORACLE_USERNAME
, OS_USER_NAME
FROM V$LOCKED_OBJECT
;
-- lock 걸린 개체명 확인
SELECT A.SID
, A.SERIAL#
, object_name
, A.SID || ', ' || A.SERIAL# AS KILL_TASK
FROM V$SESSION A
INNER JOIN V$LOCK B
ON A.SID = B.SID
INNER JOIN DBA_OBJECTS C
ON B.ID1 = C.OBJECT_ID
WHERE B.TYPE = 'TM'
;
-- 세션 해제
ALTER SYSTEM KILL SESSION '401, 12761'
;
-- lock 걸린 개체 및 sql 확인
SELECT DISTINCT T1.SESSION_ID
, T2.SERIAL#
, T4.OBJECT_NAME
, T2.MACHINE
, T2.TERMINAL
, T2.PROGRAM
, T3.ADDRESS
, T3.PIECE
, T3.SQL_TEXT
FROM V$LOCKED_OBJECT T1
, V$SESSION T2
, V$SQLTEXT T3
, DBA_OBJECTS T4
WHERE 1=1
AND T1.SESSION_ID = T2.SID
AND T1.OBJECT_ID = T4.OBJECT_ID
AND T2.SQL_ADDRESS = T3.ADDRESS
ORDER BY T3.ADDRESS, T3.PIECE
;
```
DDL
테이블 생성 프로세스
- 테이블
- 테이블 스페이스
- 키 값 설정
- 시퀀스 생성
- 인덱스 생성
- 코맨트 추가
- 권한 설정
- Synonym 설정
CREATE TABLE team (
team_id NUMBER NOT NULL,
name VARCHAR(255) NOT NULL,
CONSTRAINT team_pk PRIMARY KEY(team_id),
--CONSTRAINT ticket_rank_fk FOREIGN KEY(event_id) REFERENCES ticket_event(event_id)
);
-- 시퀀스 생성
CREATE SEQUENCE ticket_user_seq
START WITH 1
INCREMENT BY 1
MAXVALUE 9999999999
NOCACHE;
-- 인덱스 생성
CREATE INDEX team ON team(team_id);
-- 주석
COMMENT ON COLUMN team.team_id IS '팀아이디';
COMMENT ON COLUMN team.name IS '팀명';
-- 소유자 부여
CREATE SYNONYM apps.team FOR team;
-- 권한 부여
GRANT SELECT, INSERT, DELETE, UPDATE ON apps.team TO apps;Extent 란? 테이블 스페이스 생성
create tablespace info_data
datafile '/DATA/oracle/oradata/ORCL/infodata.dbf'
size 200m
pctfree 10 -- extent 여유공간 10% 확보
initrans 2 -- extent header의 transaction slot 크기 지정
maxtrans 255
default storage(
initial 80k -- 초기 크기
next 80k -- 확장 크기
minextents 1 -- 갯수
maxextents 121 -- 갯수
pctincrease 80 -- Next Extent = Next * (1+Pctincrease/100) 증가율
)online;DML
NULL 비교
null은 비교가 안된다.
IF NVL(a, '|') = NVL(b, '|') THEN
NULL;
END IF;Empty String 과 NULL
오라클의 경우 Empty String과 NULL을 동일 시 한다.
유저변수
select userenv('1')
from dual;XML 범위 이스케이프
<![CDATA[
-- 해당 범위에 있는 특수기호는 문자열로 취급
]]>with 키워드
하위쿼리를 with로 추출하여 정의하면 좀 더 깔끔하게 쿼리를 작성할 수 있다.
WITH suppliers
(SELECT *
FROM ap_suppliers)
SELECT *
FROM suppliers s
,ap_supplier_sites_all assa
WHERE s.vendor_id = assa.vendor_id;MERGE
충돌나지 않게 합친다는 개념.
SVN에서 MERGE는 소스들의 충돌을 방지하고 적절하게 누락되지 않게 통합하기 위해 사용함.
오라클에서 MERGE 또한 같은 개념이다. TABLE에 존재 하지 않는 데이터는
그대로 변경만 하고 없는 데이터는 삽입을 하여 적절하게 통합하기 위한 예약어이다.
즉, 대상 테이블의 해당 KEY에 맞는 데이터가 이미 있으면 UPDATE, 존재하지 않으면 INSERT를 하여,
ROW 가충돌나지 않게 한번에 작업 할 수 있게 해준다.
오라클 9i 버전부터 가능하며, 오라클 10g 버전부터는 DELETE 구문도 가능.
- 대상 테이블에서 조건에 따라 행을 삽입 또는 업데이트합니다.
- 대상 테이블에 행이 존재하는 경우 하나 이상의 열을 업데이트하고, 그렇지 않으면 새 행에 데이터를 삽입합니다.
- 두 테이블을 동기화합니다.
- 원본 데이터와의 차이점에 따라 대상 테이블에서 행을 삽입, 업데이트 또는 삭제합니다.
MERGE INTO 테이블명 별칭
USING 대상테이블/뷰 별칭
ON 조인조건
WHEN MATCHED THEN
UPDATE SET
컬럼1=값1
컬럼2=값2
WHEN NOT MATCHED THEN
INSERT (컬럼1,컬럼2,...)
VALUES(값1,값2,...);START WITH
connect by prior 컬럼, connect 뒤에 나오는 컬럼이 뒤로 나옴.
connect by prior 부모 → Top Down
connect by piror 자식 → Bottom Up
select * from fnd_menus_entries start with menu_name = 'XXHR_SSHR_TOP_MENU' connect by prior sub_menu_id = menu_id
OVER(PARTITION BY, ORDER BY)
PARTITION BY 기준으로 그룹을 나누고 ORDER BY로 정렬을 한다.
-- partition by 기준으로 그룹핑을 하고 order by 기준으로 순위를 매김
SELECT MAX() OVER (PARTITION BY organization_id ORDER BY inventory_item_id)
,AVG() OVER (PARTITION BY organization_id ORDER BY inventory_item_id)
,SUM() OVER (PARTITION BY organization_id ORDER BY inventory_item_id)
,COUNT() OVER (PARTITION BY organization_id ORDER BY inventory_item_id)
,ROW_NUMBER() (PARTITION BY organization_id ORDER BY inventory_item_id)
,RANK() (PARTITION BY organization_id ORDER BY inventory_item_id)
FROM mtl_system_items_b msi;
-- 출력 기준으로 순위를 매김
SELECT rownum
FROM mtl_system_items_b msi;GROUP BY, ORDER BY를 이용한 서브쿼리를 개선하기 위해 나온 함수
쿼리에 GROUP BY는 하나만 사용할 수 있지만 OVER을 사용할 경우 각 컬럼마다 그룹핑 후 집계함수를 사용할 수 있다.
SELECT project_type_code
,COUNT(1) OVER () -- 전체 행수
,SUM(1) OVER (PARTITION BY project_type_code) -- project_type_code로 그룹핑
FROM xxotlh_project;쿼리 임시테이블
select *
from ( select '1',
,'전승규'
,'30'
from dual
union
select '2'
,'전진규'
,'28'
from dual);LIKE 키워드 %, _
LIKE '%target%' 같이 %는 많이 사용해봤지만 LIKE '_target'이 있는줄은 몰랐다. _의 이미는 어떤 문자든 한자리를 의미 %는 자릿수 상관없이 모든 문자를 의미한다.
FOR UPDATE
FOR UPDATE 키워드는 테이블의 모든 ROW에 LOCK 걸기 때문에 다른 유저가 접근이 불가능하다. 그러므로 사용을 지양해야 하며 PL/SQL DEVELOPER UI 수정을 할 경우 ROWID 키워드로 ROW ID 정보를 불러오면 수정이 가능하다.
서브쿼리 캐싱
일반적으로 함수는 테이블 로우마다 실행하게 되는데 실행되는 함수를 서브쿼리로 작성하면, 서브쿼리로 조회된 테이블은 캐시에 적재되고 캐싱이 가능하게 된다.
- 서브쿼리를 사용하면 입력값에 따른 결과값이 캐싱
- 같은 입력값이 들어올 경우 서브쿼리를 재실행 하지 않고 캐시된 결과를 재사용
- 함수에 입력되는 값이 중복이 많은 경우 효과적
- 유니크한 값이 입력되는 경우엔 효과 없음
SELECT SUBSTR(FN, 1, 2)
,SUBSTR(FN, 3, 2)
FROM (
SELECT FUNCTION_A() AS FN
FROM TABLE
)
--
SELECT SUBSTR(FN, 1, 2)
,SUBSTR(FN, 3, 2)
FROM (
SELECT (SELECT FUNCTION_A() AS FN FROM DUAL) AS FN
FROM TABLE
)
반응형
