{"id":3542,"date":"2018-02-04T22:05:00","date_gmt":"2018-02-04T13:05:00","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=3542"},"modified":"2018-02-04T22:08:03","modified_gmt":"2018-02-04T13:08:03","slug":"ddl-wait-%ea%b8%b0%eb%8a%a5-oracle-11g","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=3542","title":{"rendered":"DDL Wait \uae30\ub2a5 (oracle 11g)"},"content":{"rendered":"

DDL Wait<\/strong><\/p>\n

 <\/p>\n

DDL wait \uae30\ub2a5\uc740 \ub370\uc774\ud130\ubca0\uc774\uc2a4 object\uc5d0 DDL\ubb38\uc7a5\uc744 \uc218\ud589\ud560 \ub54c, \ud544\uc694\ud55c Exclusive Lock\uc744 \uc5bb\uc9c0 \ubabb\ud558\ub294 \uacbd\uc6b0 \uc815\ud574\uc9c4 \uc2dc\uac04\ub9cc\ud07c \ub300\uae30\ud558\ub294 \uae30\ub2a5\uc774\ub2e4.<\/p>\n

\uc608\ub97c\ub4e4\uc5b4, \ud14c\uc774\ube14\uc5d0 column\uc744 \ucd94\uac00\ud560 \uacbd\uc6b0<\/p>\n

 <\/p>\n

\uc77c\ubc18 \uc5c5\ubb34 \uc2dc\uac04\uc5d0\ub294 DML\uc774 Table\uc5d0 \ub300\ud55c Shared Lock \ub97c \uc7a1\uace0 \uc788\uc73c\ubbc0\ub85c DDL\ubb38\uc7a5\uc744 \uc218\ud589\ud560 \uacbd\uc6b0, \u201cORA-54 resource busy \u2026\u201d \uc5d0\ub7ec\ub97c \uc720\ubc1c<\/strong><\/span>\ub418\ub294 \uacbd\uc6b0\uac00 \ub9ce\ub2e4.<\/p>\n

 <\/p>\n

\ub530\ub77c\uc11c \uae30\ub2e4\ub838\ub2e4\uac00 \ub2e4\uc2dc \uc2e4\ud589\ud558\uac70\ub098, \uc5c5\ubb34\uac00 \uc911\ub2e8\ub41c \uc2dc\uc810\uc5d0 \uc218\ud589\ud574\uc57c\ub9cc \ud558\ub294 \ubd88\ud3b8\ud568\uc774 \uc788\uc5c8\ub2e4.<\/p>\n

 <\/p>\n

DDL Wait \uae30\ub2a5\uc744 \uc0ac\uc6a9\ud558\uba74 \uc9c0\uc815\ud55c \ub300\uae30\uc2dc\uac04 \ub9cc\ud07c \uae30\ub2e4\ub9ac\uba74\uc11c \uc5f0\uc18d\uc801\uc73c\ub85c DDL\uc744 \uc2dc\ub3c4 \ud558\ubbc0\ub85c DDL\uc758 \uc131\uacf5\ud655\ub960\uc744 \ub192\uc774\uace0 \uad00\ub9ac\uc790\uc758 \ubd88\ud3b8\ud568\uc774 \ud574\uc18c\ub420 \uc218 \uc788\ub2e4.<\/p>\n

\u00a0<\/span><\/p>\n

 <\/p>\n

\u00a0<\/span><\/p>\n

\u00a0<\/span><\/p>\n

DDL Wait Commands<\/strong><\/span><\/p>\n

\u00a0<\/span><\/p>\n

\u00a0<\/span><\/p>\n

DDL Wait<\/span>\uc744\u00a0\uc0ac\uc6a9\ud558\ub294\u00a0\ubc29\ubc95\uc740 DDL_LOCK_TIMEOUT Parameter\ub85c\u00a0\uc9c0\uc815\ud568\uc73c\ub85c\uc368\u00a0\uc124\uc815\ud560\u00a0\uc218\u00a0\uc788\uace0 session \ub610\ub294 system level\ub85c\u00a0\uc124\uc815\uac00\ub2a5\ud558\ub2e4.\u00a0<\/span>\ub2e8\uc704\uc740\u00a0\ucd08(sec)\uc774\ub2e4<\/span>.<\/span><\/p>\n

\u00a0<\/span><\/p>\n

\uba85\ub839\uc5b4\r\n\r\nSQL> alter session set DDL_LOCK_TIMEOUT=100 ; \r\n\r\nSQL> alter system set DDL_LOCK_TIMEOUT=100 ;<\/pre>\n
\u00a0<\/span><\/p>\n
DDL_LOCK_TIMEOUT<\/strong><\/span><\/p>\n
 <\/p>\n\n\n\n\n\n
Property<\/td>\nDescription<\/td>\n<\/tr>\n
Default Value<\/td>\n0\u00a0 (nowait)<\/td>\n<\/tr>\n
Range of Values<\/td>\n0 ~ 1,000,000 (in seconds)
\n1,000,000 \uc740 lock\uc744 \uc5bb\uc744 \ub54c \uae4c\uc9c0 \ubb34\ud55c Wait<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
 <\/p>\n
 <\/p>\n
\u00a0<\/span>\u00a0<\/span><\/strong><\/p>\n
DDL Wait \uc7a5\uc810<\/span><\/strong><\/p>\n
 <\/p>\n
\ubaa8\ub4e0 DDL\uc744\u00a0\uc5c5\ubb34\u00a0\uc911\uc5d0\u00a0\uc218\ud589\ud560\u00a0\uc218\ub294\u00a0\uc5c6\uaca0\uc9c0\ub9cc, \uc5c5\ubb34\uc5d0\u00a0\uc601\ud5a5\uc774\u00a0\ub9ce\uc9c0\u00a0\uc54a\uc740 DDL\uc5d0\u00a0\ud55c\ud574\uc11c\ub294\u00a0\uc5c5\ubb34\uc2dc\uac04\uc5d0\u00a0\uc131\uacf5\uc801\uc73c\ub85c\u00a0\uc218\ud589\ud560\u00a0\uc218\u00a0\uc788\uac8c\u00a0\ub418\uc5c8\ub2e4.<\/span><\/strong><\/span><\/p>\n
 <\/p>\n
\uc5c5\ubb34\uc911\uc5d0\ub3c4 DDL \uc218\ud589\uc758\u00a0\uc131\uacf5\u00a0\ud655\ub960\uc774\u00a0\ub192\uc544\uc84c\ub2e4.<\/span><\/strong><\/span><\/p>\n
 <\/p>\n
DDL\uc218\ud589\uc744\u00a0\uc704\ud55c\u00a0\uc9c0\uc18d\uc801\uc778\u00a0\ubc18\ubcf5\uc218\ud589\u00a0\ub4f1\uc758\u00a0\ubd88\ud3b8\ud568\uc774\u00a0\ud574\uc18c\u00a0\ub418\uc5c8\ub2e4.<\/span><\/strong><\/span><\/p>\n
 <\/p>\n
\u00a0<\/span><\/p>\n
\u00a0<\/span><\/p>\n
DDL Wait \uc635\uc158<\/strong><\/span><\/p>\n
\u00a0<\/span><\/p>\n
\u00a0<\/span><\/p>\n
\uc608\ub97c<\/span>\u00a0\ub4e4\uc5b4<\/span> DBA\ub294\u00a0\ucffc\ub9ac\ub97c\u00a0\ud1b5\ud574 SALES \uc640\u00a0\uac19\uc740\u00a0\ud14c\uc774\ube14\uc5d0 TAX_CODE\ub77c\ub294\u00a0\uceec\ub7fc\uc744\u00a0\ucd94\uac00\ud558\ub294\u00a0\uc791\uc5c5\uc744\u00a0\uc9c4\ud589\u00a0\ud558\uace0\uc790\u00a0\ud560\u00a0\ub54c\u00a0\uc544\ub798\uc640\u00a0\uac19\uc774 SQL \uba85\ub839\uc744\u00a0\uc2e4\ud589\ud569\ub2c8\ub2e4<\/span>:<\/span><\/p>\n
SQL> alter table sales add (tax_code varchar2(10));<\/pre>\n
\u00a0<\/span><\/p>\n
\ud558\uc9c0\ub9cc<\/span> “Table altered”\ub77c\ub294\u00a0\uacb0\uacfc\u00a0\ub300\uc2e0, \uc544\ub798\uc640\u00a0\uac19\uc740\u00a0\uc5d0\ub7ec\ub97c\u00a0\ud655\uc778\ud569\ub2c8\ub2e4<\/span>.<\/span><\/p>\n
 <\/p>\n
alter table sales add (tax_code varchar2(10))\r\n\r\n*\r\n\r\nERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired<\/pre>\n
\u00a0<\/span><\/p>\n
\u00a0<\/span><\/p>\n
\uc5d0\ub7ec \uba54\uc2dc\uc9c0\uac00 \ubaa8\ub4e0 \uac83\uc744 \ub9d0\ud574 \uc90d\ub2c8\ub2e4. \ud14c\uc774\ube14\uc774 \ub2e4\ub978 \ud2b8\ub79c\uc7ad\uc158\uc5d0 \uc758\ud574 \uc0ac\uc6a9\ub418\uace0 \uc788\uc5b4, \ud14c\uc774\ube14\uc5d0 Exclusive Lock\uc744 \uc124\uc815\ud558\ub294 \uac83\uc774 \ubd88\uac00\ub2a5\ud558\ub2e4\ub294 \ub0b4\uc6a9\uc785\ub2c8\ub2e4. \ubb3c\ub860 \ud14c\uc774\ube14\uc758 \ub85c\uc6b0\uc5d0 \uac78\ub9b0 \ub77d\uc740 \uc5b8\uc820\uac00\ub294 \ud480\ub9ac\uac8c \ub9c8\ub828\uc785\ub2c8\ub2e4.<\/p>\n
\uc138\uc158\uc774 \ucee4\ubc0b\uc744 \uc2e4\ud589\ud558\uba74 \ub85c\uc6b0\uc5d0 \ub300\ud55c \ub77d\uc740 \ud574\uc81c\ub429\ub2c8\ub2e4. \ud558\uc9c0\ub9cc \uadf8 \uc804\uc5d0 \ub2e4\ub978 \uc138\uc158\uc774 \ud14c\uc774\ube14\uc758 \ub2e4\ub978 \ub85c\uc6b0\uc5d0 \uc5c5\ub370\uc774\ud2b8\ub97c \uc2dc\uc791\ud560 \uc218 \uc788\uc73c\uba70, \uc5b4\uca4c\uba74 \ud14c\uc774\ube14\uc5d0 Exclusive Lock\uc744 \uc124\uc815\ud560 \uc218 \uc788\ub294 \uae30\ud68c\ub97c \uc601\uc601 \uc5bb\uc744 \uc218 \uc5c6\uc744 \uc9c0\ub3c4 \ubaa8\ub985\ub2c8\ub2e4.<\/p>\n
\uc77c\ubc18\uc801\uc778 \ube44\uc988\ub2c8\uc2a4 \ud658\uacbd\uc5d0\uc11c \ud14c\uc774\ube14 \ub77d\uc744 \uc124\uc815\ud560 \uc218 \uc788\ub294 \uae30\ud68c\ub294 \uc8fc\uae30\uc801\uc73c\ub85c \ucc3e\uc544 \uc624\uc9c0\ub9cc, DBA\uac00 \uc774 \uc2dc\uac04\ub300\uc5d0 alter \ucee4\ub9e8\ub4dc\ub97c \uc218\ud589\ud560 \uc218 \uc788\uc73c\ub9ac\ub77c\ub294 \ubcf4\uc7a5 \ud560 \uc218 \uc5c6\uc2b5\ub2c8\ub2e4.<\/p>\n
\ubb3c\ub860 DBA\ub294 alter table ~ \uba85\ub839\uc5b4\ub85c Exclusive Lock\uc744 \uc124\uc815\ud560 \uc218 \uc788\uc744 \ub54c\uae4c\uc9c0 \ucee4\ub9e8\ub4dc\ub97c \uc26c\uc9c0 \uc54a\uace0 \uc785\ub825\ud558\uac70\ub098, \ub610\ub294 \ubbf8\uccd0 \ubc84\ub9ac\uac70\ub098 \ub458 \uc911\uc758 \ud55c \uac00\uc9c0\ub97c \uc120\ud0dd\ud560 \uc218\ub3c4 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n
Oracle Database 11g\uc740 \ub354 \ubc14\ub78c\uc9c1\ud55c \ub300\uc548\uc744 \uc81c\uacf5\ud569\ub2c8\ub2e4.<\/p>\n
DDL Wait \uc635\uc158\uc774 \ubc14\ub85c \uadf8\uac83\uc785\ub2c8\ub2e4.<\/p>\n
 <\/p>\n
\uc774\ubc88\uc5d0\ub294 DBA\ub294 \uc544\ub798\uc640 \uac19\uc774 \uba85\ub839\uc744 \uc2e4\ud589\ud569\ub2c8\ub2e4 :<\/p>\n
SQL> alter session set ddl_lock_timeout = 10;\r\nSession altered.<\/pre>\n
 <\/p>\n
\uc774\uc81c\ubd80\ud130 \uc2e4\ud589\ub418\ub294 \uc138\uc158\uc758 DDL \uad6c\ubb38\uc774 Exclusive Lock\uc744 \uc124\uc815\ud560 \uc218 \uc5c6\ub294 \uacbd\uc6b0\ub77c\ub3c4 \uc5d0\ub7ec\ub294 \ubc1c\uc0dd\ud558\uc9c0 \uc54a\uc2b5\ub2c8\ub2e4.<\/p>\n
 <\/p>\n
\uadf8 \ub300\uc2e0, DBA\uac00 \uc218\ud589\ud55c \uc138\uc158\uc740 10 \ucd08\uac04\uc744 \ub300\uae30\ud569\ub2c8\ub2e4. \uc774 10 \ucd08 \ub3d9\uc548, \uc138\uc158\uc740 \uba85\ub839\uc774 \uc131\uacf5\ud558\uac70\ub098 \ub610\ub294 \uc81c\ud55c \uc2dc\uac04\uc774 \uacbd\uacfc\ud560 \ub54c\uae4c\uc9c0 DDL \uc791\uc5c5\uc744 \uc5f0\uc18d\uc801\uc73c\ub85c \uc2dc\ub3c4\ud569\ub2c8\ub2e4. \uc774\uc81c DBA\ub294 \uc544\ub798\uc640 \uac19\uc774 \uc2e4\ud589\ud569\ub2c8\ub2e4.<\/p>\n
 <\/p>\n
SQL> alter table sales add (tax_code varchar2(10));<\/pre>\n
\uc774\ubc88\uc5d0\ub294 \uc5d0\ub7ec\uac00 \ubc1c\uc0dd\ud558\uc9c0 \uc54a\uc2b5\ub2c8\ub2e4. Exclusive Lock\uc744 \uc5bb\uc744 \uc218 \uc788\uc744 \ub54c\uae4c\uc9c0 \ubc18\ubcf5\uc801\uc73c\ub85c \uba85\ub839\uc744 \uc785\ub825\ud558\ub294 \ub300\uc2e0, \ucffc\ub9ac\ub294 Database 11g\uc758 \ubc18\ubcf5 \uc2dc\ub3c4 \uae30\ub2a5\uc744 \uc0ac\uc6a9\ud569\ub2c8\ub2e4.<\/p>\n
\uc774 \uae30\ub2a5\uc740 \ub9c8\uce58 \ud1b5\ud654 \uc911\uc778 \uc804\ud654\ubc88\ud638\uc5d0 \ub300\ud574 \ubc18\ubcf5 \uc5f0\uacb0 \uc2dc\ub3c4\ud558\ub294 \uc804\ud654\uae30\ucc98\ub7fc \ub3d9\uc791\ud569\ub2c8\ub2e4.<\/p>\n
DBA\ub294 \uc774 \uae30\ub2a5\uc5d0 \ub108\ubb34\ub3c4 \ub9cc\uc871\ud574 \ud558\uba74\uc11c \ub2e4\ub978 DBA \ub4e4\uc5d0\uac8c \uc774 \uc815\ubcf4\ub97c \uacf5\uc720\ud569\ub2c8\ub2e4.<\/p>\n
 <\/p>\n
\uc6b4\uc601 \uc911\uc778 \uc2dc\uc2a4\ud15c\uc5d0\uc11c DDL \uba85\ub839\uc744 \ub0b4\ub824 \ubcf8 \uc77c\uc774 \uc788\ub294 \ub370\uc774\ud130\ubca0\uc774\uc2a4 \uad00\ub9ac\uc790\ub77c\uba74 \ub204\uad6c\ub4e0 \uc774 \uc0c8\ub85c\uc6b4 \uae30\ub2a5\uc744 \ubc18\uac00\uc6b4 \uc18c\uc2dd\uc73c\ub85c \ubc1b\uc544\ub4e4\uc77c \uac83\uc785\ub2c8\ub2e4.<\/p>\n
 <\/p>\n
\uc774 \ub3d9\uc791 \ubc29\uc2dd\uc744 \ub514\ud3f4\ud2b8\ub85c \uc124\uc815\ud574\uc11c \ub9e4\ubc88 ALTER SESSION \uad6c\ubb38\uc744 \ubc18\ubcf5\uc801\uc73c\ub85c \uc2e4\ud589\ud560 \ud544\uc694\uac00 \uc5c6\uac8c \ub9cc\ub4e0\ub2e4\uba74 \ub354 \uc88b\uc9c0 \uc54a\uc744\uae4c\uc694?<\/strong><\/p>\n
\uac00\ub2a5\ud569\ub2c8\ub2e4.<\/p>\n
 <\/p>\n
“ALTER SYSTEM SET DDL_LOCK_TIMEOUT = 10″\uacfc \uac19\uc774 \uc2e4\ud589\ud558\ub294 \uacbd\uc6b0 \uc138\uc158\uc740 DDL \uc791\uc5c5\uc774 \uc9c4\ud589\ub418\ub294 \ub3d9\uc548 \uc790\ub3d9\uc73c\ub85c DDL WAIT \uc635\uc158\uc744 \uc0ac\uc6a9\ud569\ub2c8\ub2e4.<\/p>\n
 <\/p>\n
\ub2e4\ub978 ALTER SYSTEM \uad6c\ubb38\uacfc \ub9c8\ucc2c\uac00\uc9c0\ub85c, \uc774 \uc635\uc158\uc740 ALTER SESSION \uad6c\ubb38\uc5d0 \uc758\ud574 \ubb34\uc2dc(override) \ub420 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
DDL Wait   DDL wait \uae30\ub2a5\uc740 \ub370\uc774\ud130\ubca0\uc774\uc2a4 object\uc5d0 DDL\ubb38\uc7a5\uc744 \uc218\ud589\ud560 \ub54c, \ud544\uc694\ud55c Exclusive Lock\uc744 \uc5bb\uc9c0 \ubabb\ud558\ub294 \uacbd\uc6b0 \uc815\ud574\uc9c4 \uc2dc\uac04\ub9cc\ud07c \ub300\uae30\ud558\ub294 \uae30\ub2a5\uc774\ub2e4. \uc608\ub97c\ub4e4\uc5b4, \ud14c\uc774\ube14\uc5d0 column\uc744 \ucd94\uac00\ud560 \uacbd\uc6b0   \uc77c\ubc18 \uc5c5\ubb34 \uc2dc\uac04\uc5d0\ub294 DML\uc774 Table\uc5d0 \ub300\ud55c Shared Lock \ub97c \uc7a1\uace0 \uc788\uc73c\ubbc0\ub85c DDL\ubb38\uc7a5\uc744 \uc218\ud589\ud560 \uacbd\uc6b0, \u201cORA-54 resource busy \u2026\u201d \uc5d0\ub7ec\ub97c \uc720\ubc1c\ub418\ub294 \uacbd\uc6b0\uac00 \ub9ce\ub2e4.   \ub530\ub77c\uc11c \uae30\ub2e4\ub838\ub2e4\uac00 \ub2e4\uc2dc \uc2e4\ud589\ud558\uac70\ub098, \uc5c5\ubb34\uac00 […]<\/p>\n","protected":false},"author":1,"featured_media":2875,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[9],"tags":[995,994,998,997,996],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/2018\/02\/1-know-how.jpg?fit=937%2C450","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/3542"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=3542"}],"version-history":[{"count":6,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/3542\/revisions"}],"predecessor-version":[{"id":3548,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/3542\/revisions\/3548"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/2875"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=3542"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=3542"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=3542"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}