๋ณธ๋ฌธ ๋ฐ”๋กœ๊ฐ€๊ธฐ

oracle64

[Oracle] ์˜ค๋ผํด ์ ‘์† tnsnames.ora ์„ค์ • Easy Connect, ๋กœ์ปฌ ์ด๋ฆ„ ์ง€์ • ๋ฐฉ์‹ ์˜ค๋ผํด ์ ‘์† ๋ฐฉ๋ฒ• ์ด์ „ ๊ธ€์—์„œ Listener์™€ ๊ด€๋ จ๋œ ์‹ค์Šต์„ ์ง„ํ–‰ํ–ˆ์Šต๋‹ˆ๋‹ค. ์ด๋ฒˆ ๊ธ€์—์„œ๋Š” Oracle ์ ‘์† ๋ฐฉ๋ฒ• ์ค‘์—์„œ ๊ฐ€์žฅ ๋งŽ์ด ์‚ฌ์šฉํ•˜๋Š” Easy Connect์™€ ๋กœ์ปฌ ์ด๋ฆ„ ์ง€์ • ๋ฐฉ์‹์— ๋Œ€ํ•ด์„œ ์•Œ์•„๋ณด๊ฒ ์Šต๋‹ˆ๋‹ค. ์‹ค์Šต ํ™˜๊ฒฝ OS : OEL 5.4 DB : Oracle 12.1.0.2 SID: newdb, PROD(1๊ฐœ ์„œ๋ฒ„์•ˆ 2๊ฐœ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค) hostname : host01 listener ์‚ฌ์ „ ๊ตฌ์ถ• ์™„๋ฃŒ 1521ํฌํŠธ ์‚ฌ์šฉ Easy Connect ๋ฐฉ์‹ ๊ฐ€์žฅ ์‰ฝ๊ฒŒ ์—ฐ๊ฒฐํ•  ์ˆ˜ ์žˆ๋Š” ๋ฐฉ์‹์ž…๋‹ˆ๋‹ค. ํ•„์š”ํ•œ ์ •๋ณด๋“ค์„ ๋‚˜์—ดํ•ด์„œ ์—ฐ๊ฒฐํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. [oracle@host01 ~]$ sqlplus system/oracle@host01:1521/PROD sqlplus๋ฅผ ์ด์šฉํ•ด์„œ ์ ‘์†ํ–ˆ์Šต๋‹ˆ๋‹ค. /@:/ ํ˜•ํƒœ๋กœ ์—ฐ๊ฒฐํ•ด์ฃผ๋ฉด.. 2020. 7. 11.
[Oracle] Listener ๋™์  ๋“ฑ๋ก. UNKNOWN๊ณผ READY์˜ ์ฐจ์ด -Oracle Listener ๋™์  ๋“ฑ๋ก ์‹ค์Šตํ™˜๊ฒฝ OS : Red Hat Enterprise Linux Server release 6.10 DB : Oracle 11.2.0.1 SID: newdb, PROD(1๊ฐœ ์„œ๋ฒ„์•ˆ 2๊ฐœ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค) hostname : 601d2fce71dc Oracle Listener๋ฅผ ๋“ฑ๋กํ•˜๋Š” ๋ฐฉ๋ฒ•์—๋Š” ๋‘๊ฐ€์ง€๊ฐ€ ์žˆ์Šต๋‹ˆ๋‹ค. ๋™์ ๋“ฑ๋ก๊ณผ ์ •์ ๋“ฑ๋ก. ์ •์ ๋“ฑ๋ก์€ listener.oraํŒŒ์ผ์— ์ง์ ‘ ์„œ๋น„์Šค๋ฅผ ๋“ฑ๋กํ•ด์ฃผ๋Š” ๋ฐฉ๋ฒ• ์ž…๋‹ˆ๋‹ค. ์ž์„ธํ•œ ๋‚ด์šฉ์€ ์•„๋ž˜ ๋งํฌ๋ฅผ ํ†ตํ•ด ํ™•์ธํ•ฉ๋‹ˆ๋‹ค. [Oracle] Listener ๋„คํŠธ์›Œํฌ ๊ตฌ์„ฑ ์ •์  ๋“ฑ๋ก listener.ora ์ƒ์„ฑ Oracle ๋„คํŠธ์›Œํฌ ํ†ต์‹  ๋ฐฉ๋ฒ• ์™ธ๋ถ€์—์„œ Oracle์„œ๋ฒ„๋ฅผ ํ†ต์‹ ํ•˜๊ธฐ ์œ„ํ•ด์„œ๋Š” Listener๋ฅผ ํ†ตํ•ด ํ†ต์‹ ์„ ํ•˜๊ฒŒ๋ฉ๋‹ˆ๋‹ค. Listen.. 2020. 7. 11.
[Oracle] Listener ๋„คํŠธ์›Œํฌ ๊ตฌ์„ฑ ์ •์  ๋“ฑ๋ก listener.ora ์ƒ์„ฑ Oracle ๋„คํŠธ์›Œํฌ ํ†ต์‹  ๋ฐฉ๋ฒ• ์™ธ๋ถ€์—์„œ Oracle์„œ๋ฒ„๋ฅผ ํ†ต์‹ ํ•˜๊ธฐ ์œ„ํ•ด์„œ๋Š” Listener๋ฅผ ํ†ตํ•ด ํ†ต์‹ ์„ ํ•˜๊ฒŒ๋ฉ๋‹ˆ๋‹ค. Listener๊ฐ€ ํ•ด์ฃผ๋Š” ์ž‘์—…์€ Connection์„ ๋งŒ๋“ค์–ด์ฃผ๋Š” ๊ฒƒ ์ž…๋‹ˆ๋‹ค. ์„œ๋ฒ„ ๊ตฌ์„ฑ ๋ฐฉ์‹์—๋”ฐ๋ผ ์กฐ๊ธˆ ๋‹ค๋ฅด์ง€๋งŒ ๊ธฐ๋ณธ Dedicated Server์—์„œ Connection์ด๋ž€ User Process์™€ Server Process๋ฅผ ์—ฐ๊ฒฐํ•ด์ฃผ๋Š” ๊ฒƒ์ž…๋‹ˆ๋‹ค. ์ฆ‰, ํ•œ๋ฒˆ ์ธ์ฆ ๋œ ์™ธ๋ถ€์‚ฌ์šฉ์ž๊ฐ€ Oracle Database์—์„œ์˜ ์ž‘์—…์„ ์ง€์ •๋œ Server Process๋ฅผ ํ†ตํ•ด ํ•  ์ˆ˜ ์žˆ๋„๋ก ์—ฐ๊ฒฐํ•ด์ค๋‹ˆ๋‹ค. ์ตœ์ดˆ์˜ ํ•œ๋ฒˆ Connection์ž‘์—…์„ ํ†ตํ•ด ์„ธ์…˜์„ ์ƒ์…ฉํ•ด์ค๋‹ˆ๋‹ค. Database์™€์˜ ํ†ต์‹  ๊ตฌ์กฐ๋ฅผ ๋ดค์„๋•Œ ์ง์ ‘ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋กœ ํ†ต์‹ ํ•˜๋Š” 2 Tier ๋ฐฉ์‹์ด ์žˆ๊ณ , ๋Œ€๋ถ€๋ถ„ ์‚ฌ์šฉ๋˜๋Š” ๋ฐฉ์‹์œผ๋กœ App๋ฅผ ํ•œ๋ฒˆ .. 2020. 7. 11.
[Oracle] ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์ƒ์„ฑ ํ›„์† ์ž‘์—…, Data Dictionary ์กฐํšŒ ์•ˆ๋˜๋Š” ๊ฒƒ ๋ฌธ์ œ ํ•ด๊ฒฐ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์ƒ์„ฑ ํ›„์† ์ž‘์—… ์ด์ „ ๊ธ€์—์„œ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์ˆ˜๋™์ƒ์„ฑํ•˜๋Š” ์ž‘์—…์„ ์ง„ํ–‰ํ–ˆ์Šต๋‹ˆ๋‹ค. ์ˆ˜๋™์œผ๋กœ ์ž‘์—…์„ ์ง„ํ–‰ ํ–ˆ์„ ๋•Œ ๋ฌธ์ œ์—†์ด ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋ฅผ ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ์ง€๋งŒ, ๋ช‡๊ฐ€์ง€ ์•ˆ๋˜๋Š” ๊ธฐ๋Šฅ๋“ค์ด ์žˆ์–ด ํ›„์† ์ž‘์—…์„ ์ง„ํ–‰ํ•ด์ฃผ์–ด์•ผ ํ•ฉ๋‹ˆ๋‹ค. dbca๋กœ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋ฅผ ์ƒ์„ฑํ–ˆ์„ ๋•Œ ์ž๋™์œผ๋กœ ํ•ด์ฃผ๋Š” ํ›„์†์ž‘์—…์„ ๋”ฐ๋กœ ํ•ด์ฃผ๋Š” ๊ฒƒ์ž…๋‹ˆ๋‹ค. Database ์ˆ˜๋™ ์ƒ์„ฑํ•˜๋Š” ๋ฐฉ๋ฒ•์€ ์•„๋ž˜ ๋งํฌ๋ฅผ ํ†ตํ•ด์„œ ํ™•์ธํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. [Oracle] ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์ƒ์„ฑ SID ์ถ”๊ฐ€ CREATE DATABASE ๋ช…๋ น์–ด ์‚ฌ์šฉ OS : Red Hat Enterprise Linux Server 6.10 DB : Oracle 11.2.0.1 Oracle Database์—์„œ DBCA๋ฅผ ์ด์šฉํ•˜์ง€ ์•Š๊ณ  ์ˆ˜๋™์œผ๋กœ CREATE DATABASE ๋ช…๋ น์–ด๋ฅผ ์‚ฌ์šฉํ•ด์„œ ๋ฐ์ดํ„ฐ๋ฒ ์ด.. 2020. 7. 11.
[Oracle] ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ์ƒ์„ฑ SID ์ถ”๊ฐ€ CREATE DATABASE ๋ช…๋ น์–ด ์‚ฌ์šฉ OS : Red Hat Enterprise Linux Server 6.10 DB : Oracle 11.2.0.1 Oracle Database์—์„œ DBCA๋ฅผ ์ด์šฉํ•˜์ง€ ์•Š๊ณ  ์ˆ˜๋™์œผ๋กœ CREATE DATABASE ๋ช…๋ น์–ด๋ฅผ ์‚ฌ์šฉํ•ด์„œ ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋ฅผ ์ƒ์„ฑํ•˜๋Š” ์‹ค์Šต์„ ํ•˜๋ ค๊ณ ํ•ฉ๋‹ˆ๋‹ค. ์ƒˆ๋กœ์šด ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋ฅผ ์ƒ์„ฑํ•˜๋ฉด ์ƒˆ๋กœ์šด SID๋ฅผ ์ง€์ •ํ•˜์—ฌ ์—…๋ฌด๋ฅผ ๋‚˜๋ˆ  ๊ด€๋ฆฌํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. 1. ํ™˜๊ฒฝ๋ณ€์ˆ˜ ์ˆ˜์ • oraenv ์‚ฌ์šฉ Oracle ์„ค์น˜ํ•˜๋Š” ๊ณผ์ •์—์„œ ORACLE_SID, ORACLE_HOME ํ™˜๊ฒฝ๋ณ€์ˆ˜๋ฅผ ์„ค์ •ํ•˜๋Š” ์ž‘์—…์„ ์ง„ํ–‰ํ–ˆ์„ ๊ฒƒ ์ž…๋‹ˆ๋‹ค. Oracle Instance๋ฅผ ๊ธฐ๋™ํ• ๋•Œ ์–ด๋–ค ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋กœ ์—ฐ๊ฒฐํ•ด์•ผํ•˜๋Š”์ง€ ์•Œ๋ ค์ฃผ๋Š” ๋ณ€์ˆ˜๊ฐ’์ด๊ธฐ ๋•Œ๋ฌธ์— ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋ฅผ ํ•˜๋‚˜ ๋” ์ƒ์„ฑํ•œ๋‹ค๋ฉด ๊ฐ„๋‹จํ•˜๊ฒŒ ์ด ๋‘๊ฐ€์ง€ ํ™˜๊ฒฝ๋ณ€์ˆ˜๋ฅผ ์ˆ˜์ •ํ•˜๊ณ  ๋‹ค์‹œ Instance.. 2020. 7. 9.
[ORACLE] Shared Pool ์—ญํ•  ๋ฐ ๊ตฌ์„ฑ Shared Pool Shared Pool์˜ ๊ฐ€์žฅ ํฐ ์—ญํ• ์„ Parsing์ž‘์—…์„ ํ•˜๊ธฐ์œ„ํ•ด ์‚ฌ์šฉ๋œ๋‹ค๋Š” ๊ฒƒ ์ž…๋‹ˆ๋‹ค. Parsing๋œ ์ •๋ณด๋ฅผ ์ €์žฅํ•˜๋Š” Library Cache์™€ ์ด๋Ÿฌํ•œ Parsing ์ž‘์—…์„ ์ง„ํ–‰ํ•˜๋Š”๋ฐ ํ•„์š”ํ•œ Data Dictionary์˜ ์ •๋ณด๋ฅผ ๋ฉ”๋ชจ๋ฆฌ์ƒ์— ์˜ฌ๋ ค ์ €์žฅํ•˜๋Š” Data Dictionary Cache๊ฐ€ Shared Pool์˜ ํ•ต์‹ฌ์ด๋ผ๊ณ  ๋ณผ ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. Shared Pool์˜ ๋ชฉ์  Shared Pool๊ณผ Parsing์— ๋Œ€ํ•œ ๋‚ด์šฉ์„ ์–ธ๊ธ‰ํ–ˆ์Šต๋‹ˆ๋‹ค. Shared Pool์˜ ๋ชฉ์ ์€ ๋น ๋ฅธ Parsing์„ ์œ„ํ•ด Hard Parsing์„ ์ค„์ด๊ณ  Soft Parsing์˜ ๋น„์œจ์„ ๋†’์—ฌ Oracle Database์˜ ์„ฑ๋Šฅ์„ ๋†’ํžˆ๋Š” ๊ฒƒ ์ž…๋‹ˆ๋‹ค. Soft Parsing : Library Cache์— ์ €์žฅ๋˜.. 2020. 7. 5.
[ORACLE] Reserved Pool ์—ญํ•  ๋ฐ ํ™•์ธ Reserved Pool Oracle Reserved Pool์€ Shared Pool์— ํฌ๊ธฐ๊ฐ€ ํฐ ์˜ค๋ธŒ์ ํŠธ๋ฅผ ์ €์žฅํ•ด์•ผํ•˜๋Š” ๊ฒฝ์šฐ ์‚ฌ์šฉ๋˜๋Š” ๋ฉ”๋ชจ๋ฆฌ์ž…๋‹ˆ๋‹ค. Parsing๋œ SQL์ด๋‚˜ PL/SQL ๋ฌธ์žฅ ๋ฐ ์‹คํ–‰๊ณ„ํš๋“ค์€ Library Cache์— ์ €์žฅ์„ ํ•ฉ๋‹ˆ๋‹ค. ๊ทธ๋Ÿฐ๋ฐ ์ด๋•Œ ๋‚ด๋ถ€์ ์œผ๋กœ Chunk๋ผ๋Š” 1KB or 4KB ํฌ๊ธฐ์˜ ์กฐ๊ฐ์œผ๋กœ ์˜ค๋ธŒ์ ํŠธ๋ฅผ ์ €์žฅํ•˜๋Š”๋ฐ ๋งŒ์•ฝ ์ด๋ณด๋‹ค ํฐ ํฌ๊ธฐ์˜ Chunk๋ฅผ ์‚ฌ์šฉํ•ด์•ผํ•˜๋Š” ๊ฒฝ์šฐ์— Reserved Pool์„ ์‚ฌ์šฉํ•ฉ๋‹ˆ๋‹ค. Reserved Pool ์šฉ๋Ÿ‰ ํ™•์ธ SHOW PARAMETER SHARED_POOL_RESERVED_SIZE Reserved Pool ์ •๋ฆฌ - Parsed๋œ ํฌ๊ธฐ๊ฐ€ ํฐ SQL or PL/SQL ์˜ค๋ธŒ์ ํŠธ๋ฅผ ์ €์žฅํ•˜๋Š” ๊ณต๊ฐ„ 2020. 7. 5.
[ORACLE] Result Cache ์—ญํ•  ๋ฐ ์‹ค์Šต Result Cache Result Cache๋Š” Oracle 11g์—์„œ ์ฒ˜์Œ ์†Œ๊ฐœ๋˜์—ˆ์Šต๋‹ˆ๋‹ค. Result Cache๋Š” ์‚ฌ์šฉ์ž๋“ค์˜ ๋Œ€๊ธฐ ์ƒํ™ฉ์„ ์ค„์—ฌ์ค„ ์ˆ˜ ์žˆ๋Š” ๋Œ€์•ˆ์œผ๋กœ ์‚ฌ์šฉ์ด ๊ฐ€๋Šฅํ•ฉ๋‹ˆ๋‹ค. ์–ด๋–ค ์‚ฌ์šฉ์ž๊ฐ€ SQL ๋ฌธ์žฅ์„ ์งˆ์˜ ํ–ˆ์„๋•Œ Server Process๊ฐ€ ๊ฐ€์žฅ ๋จผ์ €ํ•˜๋Š” ์ผ์€ Parse ์ž‘์—…์„ ํ†ตํ•ด ๋งŒ๋“ค์–ด์ง„ ์‹คํ–‰๊ณ„ํš์œผ๋กœ Database Buffer Cache์— ํ•„์š”ํ•œ ๋ฐ์ดํ„ฐ๊ฐ€ ์กด์žฌํ•˜๋Š”์ง€ ํ™•์ธํ•ฉ๋‹ˆ๋‹ค. ํ•œ๋ช…์˜ ์‚ฌ์šฉ์ž๊ฐ€ ์‚ฌ์šฉํ–ˆ์„ ๋•Œ๋Š” ์ƒ๊ด€์—†์ง€๋งŒ, ๋‹ค์ˆ˜์˜ ์‚ฌ์šฉ์ž๊ฐ€ ๋™์‹œ์— Database Buffer Cache๋ฅผ ์ ‘๊ทผํ•˜๋ ค๊ณ  ํ• ๋•Œ ์ฝ๊ธฐ ์ผ๊ด€์„ฑ์„ ์œ ์ง€ํ•˜๊ธฐ ์œ„ํ•ด Latch๋ผ๋Š” ๋ฉ”๋ชจ๋ฆฌ์— ๋Œ€ํ•œ Lock์„ ํ™•๋ณดํ•˜์—ฌ Latch๋ฅผ ์†Œ์œ ํ•˜๊ณ  ์žˆ๋Š” ์‚ฌ์šฉ์ž๋งŒ ํ•ด๋‹น Block์— ๋Œ€ํ•œ ์ž‘์—… ์ˆ˜ํ–‰์ด ๊ฐ€๋Šฅํ•˜์—ฌ ๋‹ค๋ฅธ ์‚ฌ์šฉ์ž๊ฐ€ ๋Œ€๊ธฐํ•˜๋Š” ํ˜„์ƒ์ด .. 2020. 7. 5.
[ORACLE] Data Dictionary Cache ์—ญํ•  Data Dictionary Cache Data Dictionary Cache๋Š” Data Dictionary Object ์ •๋ณด๋ฅผ ์ €์žฅํ•˜๋Š” ๊ณต๊ฐ„์ด๋‹ค. ์‚ฌ์šฉ์ž๊ฐ€ SQL Query๋ฅผ ์งˆ์˜ ํ–ˆ์„ ๋•Œ Parsing ์ž‘์—…์„ ํ•ฉ๋‹ˆ๋‹ค. Library Cache์— ํ•ด๋‹น Query๊ฐ€ ์กด์žฌํ•˜์ง€ ์•Š์•„ HardParsing ์ž‘์—…์„ ํ•  ๋•Œ Semantic Check ์ž‘์—…์„ ํ†ตํ•ด Query์—์„œ ์‚ฌ์šฉํ•œ ํ…Œ์ด๋ธ”์ด๋‚˜ ์นผ๋Ÿผ ํ˜น์€ ์‚ฌ์šฉ์ž์˜ ๊ถŒํ•œ์„ ํ™•์ธํ•˜๋Š” ์ž‘์—…์„ ์ง„ํ–‰ํ•ฉ๋‹ˆ๋‹ค. ์ด๋•Œ, ํ…Œ์ด๋ธ”์— ๋Œ€ํ•œ ์ •๋ณด๋‚˜ ์‚ฌ์šฉ์ž์˜ ๊ถŒํ•œ์€ ์–ด๋””์„œ ์ฐธ์กฐ๋ฅผ ํ• ๊นŒ์š”? ์ด๋Ÿฌํ•œ ์ •๋ณด๋“ค์€ ๋ฌผ๋ฆฌ์ ์œผ๋กœ๋Š” SYSTEM Tablespace์— ์ €์žฅ๋˜์–ด์žˆ์Šต๋‹ˆ๋‹ค. SQL ๋ฌธ์žฅ์—์„œ DataDictionary์˜ Object๊ฐ€ ์ฐธ์กฐ๋˜๋ฉด Database Buffer Cache๋ฅผ ์ง€๋‚˜ Dat.. 2020. 7. 5.
[ORACLE] Library cache ์—ญํ•  ๋ฐ ํ™•์ธ Oracle Library Cache Library Cache๋Š” Oracle Instance SGA Shared Pool ์˜์—ญ์— ์กด์žฌํ•œ๋‹ค. ์กด์žฌ์˜ ๊ถ๊ทน์ ์ธ ๋ชฉํ‘œ๋Š” Oracle์˜ ์„ฑ๋Šฅ์„ ๋†’์—ฌ์ฃผ๊ธฐ ์œ„ํ•จ์ด๋‹ค. ์‚ฌ์šฉ์ž๊ฐ€ SQL ๋ฌธ์žฅ์„ ์งˆ์˜ ํ–ˆ์„๋•Œ Oracle ๋‚ด๋ถ€์—์„œ๋Š” Parse - Execute - Fetch ๋‹จ๊ณ„๋ฅผ ์ง„ํ–‰ํ•˜์—ฌ ๊ฒฐ๊ณผ๋ฅผ ๋ฐ˜ํ™˜ํ•ด์ฃผ๋Š”๋ฐ Library Cache์— ์ €์žฅ๋˜์–ด ์žˆ๋Š” ์ •๋ณด์— ๋”ฐ๋ผ Parse ๋‹จ๊ณ„๋ฅผ ๊ฑฐ์น˜์ง€ ์•Š๊ณ  ๋ฉ”๋ชจ๋ฆฌ์— ์ €์žฅ๋œ ์ •๋ณด๋ฅผ ๊ทธ๋Œ€๋กœ ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ๋‹ค. Library Cache ๋‚ด๋ถ€์—๋Š” ๊ณต์œ  SQL ์˜์—ญ(Shared SQL Area)๊ณผ ๊ณต์œ  PL/SQL ์˜์—ญ(Shared PL/SQL Area)์ด ์กด์žฌํ•˜๋Š”๋ฐ, ํŒŒ์Šค ๊ณผ์ •์ด ์™„๋ฃŒ๋˜๋ฉด ์‚ฌ์šฉ์ž๊ฐ€ ์‹คํ–‰ํ•œ SQL ๋ฌธ์žฅ๊ณผ ์‹คํ–‰๊ณ„ํš์ด ์ €์žฅ๋˜๋Š” ๊ณต๊ฐ„์ด๋‹ค... 2020. 7. 5.
[Oracle] Rollfoward & Rollback ์„œ๋ฒ„๊ฐ€ ๋น„์ •์ƒ์ ์œผ๋กœ ์ฃฝ์—ˆ์„ ๋•Œ, Checkpoint ํšŒ๋ณต Oracle startup Oracle ์„œ๋ฒ„๋ฅผ ๊ธฐ๋™ํ–ˆ์Šต๋‹ˆ๋‹ค. ์ด๋•Œ ์ˆœ์„œ๋Œ€๋กœ instance started - database mounted - database opened ์ˆœ์„œ๋กœ ์‹คํ–‰๋ฉ๋‹ˆ๋‹ค. instance started Oracle Instance์ธ SGA ๊ณต์œ  ๋ฉ”๋ชจ๋ฆฌ์™€ Background Process๋“ค์„ ๊ธฐ๋™ํ•ฉ๋‹ˆ๋‹ค. database mounted Control File์„ ์ฝ์„ ์ˆ˜ ์žˆ๋Š” ์ƒํƒœ๊ฐ€ mounted ์ž…๋‹ˆ๋‹ค. ์ด ๋‹จ๊ณ„์—์„œ ๋งŒ์•ฝ ์ „์— ์ •์ƒ์ ์œผ๋กœ ๋‚ด๋ ค๊ฐ€์ง€ ์•Š์•˜๋‹ค๋ฉด SMON Background Process๊ฐ€ Instance Recovery ์ž‘์—…์„ ํ•ฉ๋‹ˆ๋‹ค. Instance Recovery ์ž‘์—…์œผ๋กœ Rollfoward์™€ Rollback ์ž‘์—…์„ ํ•ฉ๋‹ˆ๋‹ค. database opened ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค๋ฅผ ์‚ฌ์šฉํ• .. 2020. 4. 4.
[Oracle] ๋ˆ„์  ํ•ฉ๊ณ„ ๊ตฌํ•˜๊ธฐ, WINDOWING ํ–‰ ๊ธฐ์ค€ ๋ฒ”์œ„ ์—ฐ์‚ฐ :: ๋งˆ์ด์ž๋ชฝ ๋ˆ„์  ํ•ฉ๊ณ„ ๊ตฌํ•˜๊ธฐ EMPํ…Œ์ด๋ธ”์—์„œ ์‚ฌ์›์˜ ์ •๋ณด์™€ ๋ถ€์„œ๋ฒˆํ˜ธ๋กœ ์ •๋ ฌ๋˜์–ด ์žˆ์„๋•Œ ๊ธ‰์—ฌ์˜ ๋ˆ„์ ํ•ฉ๊ณ„๋„ ๊ฐ™์ด ์ถœ๋ ฅํ•ด๋ผ. ORACLE SCOTT ๊ณ„์ •์œผ๋กœ ์‹ค์Šต์„ ์ง„ํ–‰ํ•ฉ๋‹ˆ๋‹ค. 1 2 3 4 5 6 7 8 9 10 SELECT DEPTNO ,EMPNO ,ENAME ,SAL ,SUM(SAL) OVER( ORDER BY DEPTNO ROWS UNBOUNDED PRECEDING ) AS SUM_SAL FROM EMP; 1 2 3 4 5 6 7 8 9 10 11 SELECT DEPTNO ,EMPNO ,ENAME ,SAL ,SUM(SAL) OVER( ORDER BY DEPTNO ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS SUM_SAL FROM EMP; ๋ˆ„์  ํ•ฉ๊ณ„๋ฅผ ๊ตฌํ•˜๊ธฐ ์œ„ํ•ด.. 2020. 4. 3.
[Oracle] redo.log ์„œ๋ฒ„ ๋‚ด๋ถ€์  Log Switch ์ž‘๋™ ์›๋ฆฌ :: ๋งˆ์ด์ž๋ชฝ Redo Log Buffer Update ์ฐธ์กฐ๊ธ€ [Oracle] SGA Redo Log Buffer ๋ฆฌ๋‘ ๋ฒ„ํผ ์‚ฌ์šฉ์›๋ฆฌ :: ๋งˆ์ด์ž๋ชฝ Redo Log Buffer ์ด์ „ ๊ธ€๋“ค์—์„œ๋Š” Shared Pool๊ณผ Database Buffer Cache์— ๋Œ€ํ•ด์„œ ํ•˜๋‚˜์˜ SELECT๋ฌธ์„ ํ†ตํ•ด ์–ด๋–ค ์›๋ฆฌ๋กœ ์‚ฌ์šฉ๋˜๋Š”์ง€ ์•Œ์•„๋ดค์Šต๋‹ˆ๋‹ค. ์ด๋ฒˆ ๊ธ€์—์„œ๋Š” UPDATE๋ฌธ์„ ๊ฐ–๊ณ  ๋ณ€๊ฒฝ์‚ฌํ•ญ์ด ์žˆ์„ ๋•Œ Redo Log Bu.. myjamong.tistory.com redo.log ORACLE_BASE ์•„๋ž˜ oradata ๋””๋ ‰ํ† ๋ฆฌ๋ฅผ ๋ณด๋ฉด redo.log ํŒŒ์ผ๋“ค์ด ์žˆ์Šต๋‹ˆ๋‹ค. ์ด ํŒŒ์ผ๋“ค์€ Recovery ์ž‘์—…์„ ํ•˜๊ธฐ ์œ„ํ•ด Oracle์˜ Background Process์ธ LGWR(Log Writer)๊ฐ€ ์ˆ˜์‹œ๋กœ ์ž…๋ ฅํ•ด์ฃผ๊ณ  ์žˆ๋Š” ํŒŒ์ผ์ž…๋‹ˆ๋‹ค. Or.. 2020. 4. 2.
[Oracle] LISTAGG ์ปฌ๋Ÿผ ๊ฒฐ๊ณผ ๋‚˜์—ดํ•˜๊ธฐ :: ๋งˆ์ด์ž๋ชฝ 1 2 3 4 5 SELECT DEPTNO ,ENAME FROM EMP ORDER BY DEPTNO; ORACLE SCOTT๊ณ„์ •์˜ EMPํ…Œ์ด๋ธ”์˜ ๋‚ด์šฉ์ž…๋‹ˆ๋‹ค. RDB์— ์ต์ˆ™ํ•ด์ง€๋ฉด ์œ„์™€๊ฐ™์€ ๊ฒฐ๊ณผ๊ฐ€ ๋ˆˆ์— ์ž˜ ๋“ค์–ด์˜ต๋‹ˆ๋‹ค. ํ•˜์ง€๋งŒ, ์ผ๋ฐ˜ ๋ฌธ์„œ์—์„œ๋Š” ๊ฐ ๋ถ€์„œ๋ณ„๋กœ ์–ด๋–ค ์‚ฌ๋žŒ๋“ค์ด ์žˆ๋Š”์ง€ ','๋ฅผ ๊ตฌ๋ถ„์ž๋กœ ๋ฐ์ดํ„ฐ๋ฅผ ๋ณด๋Š”๊ฒŒ ์ดํ•ดํ•˜๊ธฐ ํŽธํ•  ๊ฒƒ ์ž…๋‹ˆ๋‹ค. LISTAGG ํ•จ์ˆ˜ 1 2 3 4 5 SELECT DEPTNO ,LISTAGG(ENAME, ',') WITHIN GROUP(ORDER BY ENAME) AS EMPS FROM EMP GROUP BY DEPTNO; LISTAGGํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•˜๋ฉด ๊ฐ€๋กœ๋กœ ๋‚˜์—ดํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. GROUP BYํ•œ ๊ฒฐ๊ณผ์— ๋Œ€ํ•ด์„œ GROUP์•ˆ์˜ ์นผ๋Ÿผ์„ ๋‚˜์—ด์‹œํ‚ฌ ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. MULTI INDEX ํ™•์ธ LI.. 2020. 4. 2.
[Oracle] SGA Database Buffer Cache DBC ๋ฐ์ดํ„ฐ๋ฒ ์ด์Šค ๋ฒ„ํผ ์บ์‹œ ์‚ฌ์šฉ์›๋ฆฌ :: ๋งˆ์ด์ž๋ชฝ SGA Shared Pool ๊ด€๋ จ ๊ธ€ [Oracle] SGA Shared Pool ๊ณต์œ ํ’€ ์‚ฌ์šฉ ์›๋ฆฌ :: ๋งˆ์ด์ž๋ชฝ Shared Pool Library Cache : parsing๋œ ์ •๋ณด๋ฅผ ์ €์žฅ Data Dictionary Cache : Data Dictionary์˜ ์ •๋ณด๋ฅผ ์ €์žฅ --> Hard Parse ์ž‘์—…์„ ๋นจ๋ฆฌ ํ•ด์ฃผ๊ธฐ ์œ„ํ•ด Result Cache : ์‹คํ–‰๋œ ๊ฒฐ๊ณผ๋ฅผ ์ €์žฅ (11g new feature) --.. myjamong.tistory.com Database Buffer Cache ์ด์ „ ๊ธ€์—์„œ SGA ๊ตฌ์„ฑ์š”์†Œ Shared Pool์— Parse๋œ ๊ณต์œ  ๋ฐ์ดํ„ฐ์— ๋Œ€ํ•ด์„œ ์•Œ์•„๋ดค์Šต๋‹ˆ๋‹ค. ์ด๋ฒˆ ๊ธ€์—์„œ๋Š” Parse์— ์ด์–ด์„œ Select๋ฌธ ์š”์ฒญ์ด ๋“ค์–ด์™”์„ ๋•Œ Execute ๋‹จ๊ณ„๋ฅผ Database Buffer.. 2020. 4. 1.
[Oracle] SGA Shared Pool ๊ณต์œ ํ’€ ์‚ฌ์šฉ ์›๋ฆฌ :: ๋งˆ์ด์ž๋ชฝ Shared Pool Library Cache : parsing๋œ ์ •๋ณด๋ฅผ ์ €์žฅ Data Dictionary Cache : Data Dictionary์˜ ์ •๋ณด๋ฅผ ์ €์žฅ --> Hard Parse ์ž‘์—…์„ ๋นจ๋ฆฌ ํ•ด์ฃผ๊ธฐ ์œ„ํ•ด Result Cache : ์‹คํ–‰๋œ ๊ฒฐ๊ณผ๋ฅผ ์ €์žฅ (11g new feature) --> ๊ฒฐ๊ณผ๋กœ ์ ์€ ๋‚ด์šฉ์„ ๋ฐ˜ํ™˜ํ•ด์ฃผ๋Š” ๊ฐ’์„ ์ €์žฅํ•ด๋‘๋ฉด ๋ฐ”๋กœ ์ฝ์„ ์ˆ˜ ์žˆ๋‹ค. Shared Pool์€ Libray Cache, Data Dictionary Cache, Server Result Cache 3๊ฐ€์ง€ ๊ตฌ์„ฑ์š”์†Œ๋ฅผ ๊ฐ–๊ณ  ์žˆ์Šต๋‹ˆ๋‹ค. ๋™์ผํ•œ ๋ฌธ์žฅ์ด ๋ฐ˜๋ณต์ ์œผ๋กœ ๋“ค์–ด์™”์„ ๋•Œ Parse ์ž‘์—…์„ ํ•˜์ง€ ์•Š๊ณ  ๋ฐ”๋กœ ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ๋„๋ก ๊ณต์œ ์˜ ๋ชฉ์ ์œผ๋กœ ์‚ฌ์šฉ๋˜๊ณ  ์žˆ์Šต๋‹ˆ๋‹ค. ์ดํ•ด๋ฅผ ๋•๊ธฐ ์œ„ํ•ด ์šฐ๋ฆฌ๊ฐ€ ์ผ๋ฐ˜์ ์œผ๋กœ ์‚ฌ์šฉํ•˜๋Š” SELECT๋ฌธ.. 2020. 3. 31.
[Oracle] ๋ฌธ์žฅ์ˆ˜์ค€ ์ฝ๊ธฐ ์ผ๊ด€์„ฑ Consistent Mode, Current Mode ์ฐจ์ด :: ๋งˆ์ด์ž๋ชฝ Dirty Read Commit๋˜์ง€ ์•Š์€ ๋ฐ์ดํ„ฐ๋ฅผ ๋‹ค๋ฅธ Transaction์—์„œ ์ฝ์„ ์ˆ˜ ์žˆ๋‹ค๋ฉด ์–ด๋–ค ํ˜„์ƒ์ด ๋ฐœ์ƒํ•  ์ˆ˜ ์žˆ์„๊นŒ์š”? ์ตœ์ข… ์—ฐ๋ด‰ ๊ณ„์‚ฐํ•˜๋Š” ์˜ˆ์‹œ๋กœ ๋ฐœ์ƒํ•  ์ˆ˜ ์žˆ๋Š” ํ˜„์ƒ์„ ์•Œ์•„ ๋ณด๊ฒ ์Šต๋‹ˆ๋‹ค. ์ตœ์ข… ์—ฐ๋ด‰์€ ํ˜„์žฌ ์—ฐ๋ด‰ + ํ‡ด์ง๊ธˆ + ์ธ์„ผํ‹ฐ๋ธŒ๋กœ ์ตœ์ข… ๊ฒฐ์ •๋˜๋Š” ๊ฒƒ์œผ๋กœ ๊ฐ€์ •ํ•ด๋ณด๊ฒ ์Šต๋‹ˆ๋‹ค. ๋งŒ์•ฝ Commit๋˜์ง€ ์•Š์€ ๋ฐ์ดํ„ฐ๋ฅผ ๋‹ค๋ฅธ Transaction์—์„œ ์ฝ์„ ์ˆ˜ ์žˆ๋‹ค๋ฉด, ๋ฐ์ดํ„ฐ๋ฅผ ์ฝ๋Š” ์‹œ์ ์— ๋”ฐ๋ผ ๋‹ค๋ฅธ ๊ฒฐ๊ณผ ๊ฐ’์„ ์ฝ์Šต๋‹ˆ๋‹ค. ํ‡ด์ง๊ธˆ๊นŒ์ง€๋งŒ ํ•ฉ์‚ฐํ–ˆ์„ ๋•Œ ๋ฐ์ดํ„ฐ๋ฅผ ์ฝ์œผ๋ฉด 3900์ด๋˜๊ณ  ์ธ์„ผํ‹ฐ๋ธŒ๊นŒ์ง€ ํ•ฉ์‚ฐํ–ˆ์„ ๋•Œ 4200์˜ ๊ฐ’์„ ์ฝ์Šต๋‹ˆ๋‹ค. ์ฆ‰, ์ผ๊ด€์„ฑ ์—†์ด ๋ฐ์ดํ„ฐ๋ฅผ ์ฝ๊ธฐ ๋•Œ๋ฌธ์— ์—ฐ์‚ฐ ๊ณผ์ •์—์„œ ์ตœ์ข…๊ฐ’์ด ์•„๋‹Œ ์ค‘๊ฐ„๊ฐ’์„ ๋ฐ›์•„์˜ค๋Š” ๋ฌธ์ œ๊ฐ€ ๋ฐœ์ƒํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ์ด๋ ‡๊ฒŒ Commit๋˜์ง€ ์•Š์€ ๋ฐ์ดํ„ฐ๋ฅผ ๋‹ค๋ฅธ Transaction์—์„œ.. 2020. 3. 30.
[Oracle] PIVOT, UNPIVOT ํ•จ์ˆ˜ ์‚ฌ์šฉ ํ–‰์—ด ์ „ํ™˜ :: ๋งˆ์ด์ž๋ชฝ ํ–‰์—ด ์ „ํ™˜ ORACLE SCOTT ๊ณ„์ • EMP ํ…Œ์ด๋ธ”์—์„œ 2๊ฐœ์˜ ์นผ๋Ÿผ์„ ์ด์šฉํ•˜์—ฌ GROUP BY ๊ฒฐ๊ณผ๋ฅผ ํ™•์ธํ•ด๋ดค์Šต๋‹ˆ๋‹ค. ํ•˜๊ณ  ์‹ถ์€ ์ž‘์—…์€ GROUPINGํ•œ ์นผ๋Ÿผ์„ ๊ฐ€๋กœ ์„ธ๋กœ์ถ•์œผ๋กœ ๋‘๊ณ  ๋ฐ์ดํ„ฐ๋ฅผ ์ถœ๋ ฅํ•˜๋ ค๊ณ  ํ•ฉ๋‹ˆ๋‹ค. CASE ํ‘œํ˜„์‹์ด๋‚˜ DECODE ํ•จ์ˆ˜๋ฅผ ์ด์šฉํ•ด์„œ ๊ฒฐ๊ณผ๋ฅผ ์ถœ๋ ฅํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. [Oracle] DECODE ํ•จ์ˆ˜ ์‚ฌ์šฉ ์ฟผ๋ฆฌ ๊ฒฐ๊ณผ ๊ฐ€๋กœ๋กœ ๋ณด๊ธฐ :: ๋งˆ์ด์ž๋ชฝ DECODE ํ•จ์ˆ˜ DECODE ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•˜์—ฌ ์ฟผ๋ฆฌ๋‚ด์˜ ์กฐ๊ฑด๋ฌธ ์ฒ˜๋Ÿผ ์‚ฌ์šฉํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ์ฒซ๋ฒˆ์งธ ์ธ์ž๋กœ ํ™•์ธํ•  ๋Œ€์ƒ์„ ๋„ฃ์–ด์ฃผ๊ณ  2๋ฒˆ์งธ ์ธ์ž์— ํ™•์ธํ•  ๋Œ€์ƒ์˜ ์˜ˆ์ธก ๊ฐ’์ด ์žˆ์œผ๋ฉด 3๋ฒˆ์งธ ์ธ์ž๋ฅผ ์—†์œผ๋ฉด 4๋ฒˆ์งธ ์ธ์ž์˜ ๊ฐ’์„ ๋ฐ˜.. myjamong.tistory.com DECODE ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•˜๋ฉด ๊ฐ™์€ ํ•จ์ˆ˜๋ฅผ ๊ณ„์† ์‚ฌ์šฉํ•˜๋ฉด์„œ ์ค‘๋ณต๋˜๋Š” ์ž‘์—…์„ ํ•ด์•ผํ•ฉ๋‹ˆ๋‹ค. .. 2020. 3. 30.
[Oracle] ROW_NUMBER, RANK, DENSE_RANK ํ•จ์ˆ˜ ์ˆœ์œ„ ๋งค๊ธฐ๊ธฐ :: ๋งˆ์ด์ž๋ชฝ ์‚ฌ์›๋“ค์˜ ๊ธ‰์—ฌ๋ฅผ ๋งŽ์ด ๋ฐ›๋Š” ์ˆœ์„œ๋Œ€๋กœ ์ˆœ์œ„๋ฅผ ์ถœ๋ ฅํ•˜์‹œ์˜ค. ORACLE SCOTT ๊ณ„์ •์˜ EMPLOYEES ํ…Œ์ด๋ธ”์„ ์ด์šฉํ•ด์„œ ์ถœ๋ ฅํ•œ ๊ฒฐ๊ณผ์ž…๋‹ˆ๋‹ค. ์ˆœ์„œ๋ฅผ ๋งค๊ธฐ๋Š” ๋ฐฉ๋ฒ•์—๋Š” ์—ฌ๋Ÿฌ๊ฐ€์ง€ ๋ฐฉ๋ฒ•์ด ์žˆ์Šต๋‹ˆ๋‹ค. ๋ถ„์„ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•˜์ง€ ์•Š๋Š”๋‹ค๋ฉด ์•„๋ž˜์ฒ˜๋Ÿผ ๊ตฌํ• ๊ฒƒ ์ž…๋‹ˆ๋‹ค. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 SELECT EMPNO ,ENAME ,SAL ,DEPTNO ,ROWNUM AS RNK FROM ( SELECT * FROM EMP ORDER BY SAL DESC ); SELECT ์ ˆ์€ ORDER BY ์ด์ „์— ์‹คํ–‰๋˜๊ธฐ ๋•Œ๋ฌธ์— SUBQUERY๋ฅผ ์ด์šฉํ•ด์„œ ์ˆœ์œ„๋ฅผ ๋งค๊ธธ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ์ „์ฒด ์ˆœ์œ„์ค‘ ์ผ๋ถ€๋ฅผ ์ถœ๋ ฅํ•˜๊ธฐ ์œ„ํ•ด์„œ๋Š” TOP-N์ฟผ๋ฆฌ๋‚˜ ROW LIMIT CLAUSE๋ฅผ ์‚ฌ์šฉํ•ด์„œ ์ถœ๋ ฅ์ด ๊ฐ€๋Šฅํ•ฉ๋‹ˆ๋‹ค. ์ž์„ธํ•œ ๋‚ด์šฉ์€ ์•„.. 2020. 3. 29.
[Oracle] OVER ... PARTITION BY ๋ถ„์„์ ˆ ๋ถ„์„ํ•จ์ˆ˜ ANALYTIC FUNCTION :: ๋งˆ์ด์ž๋ชฝ ์‚ฌ์› ์ •๋ณด๋ฅผ ์ž…๋ ฅํ•˜๋Š”๋ฐ, ์ „์ฒด ๊ธ‰์—ฌ ํ‰๊ท ๊ฐ’๊ณผ ํ•จ๊ป˜ ์ถœ๋ ฅํ•˜์‹œ์˜ค. ORACLE SCOTT ๊ณ„์ • EMP ํ…Œ์ด๋ธ”๋กœ ์ถœ๋ ฅ๋œ ๊ฒฐ๊ณผ์ž…๋‹ˆ๋‹ค. ์œ„ ๋ฌธ์ œ๋ฅผ ํ’€๊ธฐ ์œ„ํ•ด ์–ด๋–ป๊ฒŒ ์ฟผ๋ฆฌ๋ฅผ ์ž‘์„ฑํ•˜์‹ค๊ฑด๊ฐ€์š”? 1 2 3 4 5 6 7 8 SELECT DEPTNO ,EMPNO ,ENAME ,JOB ,SAL ,ROUND((SELECT AVG(SAL) FROM EMP)) AS AVG FROM EMP; SUBQUERY๋ฅผ ์ด์šฉํ•  ์ˆ˜๋„ ์žˆ์ง€๋งŒ... ANALYTIC FUNCTION ๋ถ„์„ํ•จ์ˆ˜๋ฅผ ์‚ฌ์šฉํ•ด์„œ ์ข€ ๋” ํŽธํ•œ ๋ฐฉ๋ฒ•์œผ๋กœ ํ•ด๊ฒฐํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. 1 2 3 4 5 6 7 8 SELECT DEPTNO ,EMPNO ,ENAME ,JOB ,SAL ,ROUND(AVG(SAL) OVER()) AS AVG FROM EMP; ๋ถ„์„ํ•จ์ˆ˜ OVER ์ ˆ์„ ์‚ฌ์šฉํ•ด์„œ ๋ฌธ์ œ.. 2020. 3. 28.