Oracle

 Oracle 10g에서 RBO desupport  http://download-east.oracle.com/docs/cd/B14117_01/server.101/b10763/compat.htm#sthref318

Chapter 4: Summarizing and Grouping Data

Chapter 5: Performing Calculations and using Functions

Chapter 6: Combining SQL Queries

Chapter 8: Hiding Complex SQL with Views

Chapter 11: Users and Security

Chapter 12: Working with Database Objects

Use EXPLAIN PLAN and TKPROF To Tune Your Applications

http://www.dbspecialists.com/presentations/use_explain.html

http://blog.naver.com/prusciante/60028203203

outerjoin : http://blog.naver.com/ddoraizzang/12275671

Proc c : http://blog.naver.com/knbawe/100002483290

 index 관련 자료

http://blog.naver.com/sswhite74/40001441448

DB link : http://blog.naver.com/966138/60002347392

오라클 Datbase에서의 TOP N Query

rollup, cube, grouping function : http://blog.naver.com/knbawe/100002582940

Dictionary:

http://blog.naver.com/966138/60001657505

 Toad

단축키 : http://blog.naver.com/966138/60001106978

TOAD SQL Editor : http://blog.naver.com/wonie777/120001742935

설치

Redhat 9.0 기반에서 오라클 9.2.0 설치하기

리눅스8에오라클9i설치성공기

Trigger

http://blog.naver.com/prof97/633965

Search for Error Messages

ERROR

Oracle Error : ORA-01000

http://www.orafaq.com/wiki/ORA-01000

Index

index 재구성 : alter index player_t_x6 rebuild unrevoerable

Hint

/* +ORDERED */

Partition

html, body {margin:0px; padding:0px; background-color: transparent;}.xed a {cursor: pointer !important;}

ORA-14400 :
DW_II2_STAT_LOG  inserted partition key does not map to any partition
ORA-14400 삽입된 분할영역키와 매핑되는 분할영역이 없음

Oracle 의 partitioned table 에 partition 추가하는 두가지 방법 add 와 split

ALTER TABLE DW_II2_STATS_LOG
 ADD PARTITION P2000905 VALUES LESS THAN ('20090601')
 TABLESPACE TS_SIMS_D001 PCTFREE 5 PCTUSED 80 INITRANS 1 MAXTRANS 255
 STORAGE ( INITIAL 500K NEXT 500K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0)
 LOGGING;

PCTFREE와 PCTUSED

DB link

http://blog.naver.com/hjc426?Redirect=Log&logNo=130036440607

create public database link devdb

connect to udrims

identified by devdgu123

using '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=210.94.220.159)(PORT=1521))(CONNECT_DATA=(SID=ora10dev)))'

튜닝포인트 찾기

/* 과도한 DISK READ를 수행하는 SQL문을 V$SQLAREA 에서 검색해줌.
/* 원인 => 1) SQL문이 최적화 되지 않아 DISK READ를 많이 할 수 밖에 없는 쿼리일경우.
/* (INDEX가 없거나 사용되지 않을때)
/* 2) DB_BLOCK_BUFFERS 또는 SHARED_POOL_SIZE 가 작은 경우. (메모리가 적음)
/*--------------------------------------------------------------------------*/

과다한 DISK READ

SELECT BUFFER_GETS, SQL_TEXT FROM V$SQLAREA
WHERE BUFFER_GETS > 200000
  and PARSING_SCHEMA_NAME ='SIMS'
ORDER BY BUFFER_GETS DESC;

과도한 Logical Read
SELECT DISK_READS, SQL_TEXT FROM V$SQLAREA
WHERE DISK_READS > 10000
  and PARSING_SCHEMA_NAME ='SIMS'
ORDER BY DISK_READS DESC;