{"id":1432,"date":"2019-10-11T23:32:21","date_gmt":"2019-10-11T14:32:21","guid":{"rendered":"https:\/\/oboki.net\/workspace\/?p=1432"},"modified":"2019-10-11T23:34:38","modified_gmt":"2019-10-11T14:34:38","slug":"oracle-diagnosability-in-jdbc-driver%ec%97%90%ec%84%9c-%ec%82%ac%ec%9a%a9%eb%90%98%eb%8a%94-sql-%eb%a1%9c%ea%b9%85","status":"publish","type":"post","link":"https:\/\/oboki.net\/workspace\/java\/oracle-diagnosability-in-jdbc-driver%ec%97%90%ec%84%9c-%ec%82%ac%ec%9a%a9%eb%90%98%eb%8a%94-sql-%eb%a1%9c%ea%b9%85\/","title":{"rendered":"[Oracle] Diagnosability in Jdbc, driver\uc5d0\uc11c \uc0ac\uc6a9\ub418\ub294 SQL \ub85c\uae45"},"content":{"rendered":"<h1>[Oracle] Diagnosability in Jdbc, driver\uc5d0\uc11c \uc0ac\uc6a9\ub418\ub294 SQL \ub85c\uae45<\/h1>\n<p>Oracle jdbc driver (<code>ojdbc<\/code>) \uc911\uc5d0\uc11c <code>_g<\/code> \ub85c \ub05d\ub098\ub294 \ub77c\uc774\ube0c\ub7ec\ub9ac\uac00 \uc788\ub294\ub370 \uc774\uac78 \uc774\uc6a9\ud558\uba74 driver \ub97c \ud1b5\ud574\uc11c \ucffc\ub9ac\ud558\ub294 SQL \uc744 \ubaa8\ub450 \ucd94\uc801\ud560 \uc218\uac00 \uc788\ub2e4.<\/p>\n<h2>Oracle Test DB<\/h2>\n<p>docker hub \uc5d0 \uac04\ub2e8\ud558\uac8c \uc624\ub77c\ud074 \ub370\uc774\ud130\ubca0\uc774\uc2a4\ub97c \ud14c\uc2a4\ud2b8\ud574\ubcfc \uc218 \uc788\ub294 \uc774\ubbf8\uc9c0\uac00 \uc788\uc5b4 \uc774\uac78 \uc774\uc6a9\ud588\ub2e4. \ub4f1\ub85d\ud55c \uc0ac\uc6a9\uc790\ub791 DB \uc2a4\ud0a4\ub9c8\ub97c \ubcf4\ub2c8 owncloud \uc5d0\uc11c repository DB \ub85c oracle XE \ub97c \uc774\uc6a9\ud558\uae30 \uc704\ud574 \ud14c\uc2a4\ud2b8 \ud558\ub294 \uc6a9\ub3c4\ub85c \uc0ac\uc6a9\ud558\ub294 \uac83 \uac19\uc740\ub370, \uae30\ub2a5 \ud14c\uc2a4\ud2b8\ub9cc \ud558\uba74 \ub418\uae30\ub54c\ubb38\uc5d0 \uc0c1\uad00\uc5c6\ub2e4.<\/p>\n<p>\uc544\ub798\uc640 \uac19\uc774 \uac04\ub2e8\ud558\uac8c \uc774\ubbf8\uc9c0\ub97c \ub2e4\uc6b4\ubc1b\uace0 \uc2e4\ud589\ud558\uba74<\/p>\n<pre><code class=\"language-bash\">docker pull deepdiver\/docker-oracle-xe-11g\ndocker run -d -p 49160:22 -p 49161:1521 deepdiver\/docker-oracle-xe-11g<\/code><\/pre>\n<p>\uc544\ub798\uc640 \uac19\uc774 ssh \uc811\uadfc\uc744 \ud560 \uc218\ub3c4 \uc788\uace0<\/p>\n<pre><code class=\"language-bash\">ssh root@localhost -p 49160<\/code><\/pre>\n<p>DB Listener \uac00 \ub5a0 \uc788\ub294 \uac83\ub3c4 \ud655\uc778\ud560 \uc218 \uc788\ub2e4.<\/p>\n<pre><code class=\"language-bash\"># netstat -anp | grep 49161\ntcp6       0      0 :::49161                :::*                    LISTEN      -<\/code><\/pre>\n<h2>Sample Jdbc Source Code<\/h2>\n<p>\uac04\ub2e8\ud788 xe:autotest\/owncloud \ub370\uc774\ud130\ubca0\uc774\uc2a4\uc5d0 \uc811\uc18d\ud574 SELECT SQL \uc744 \uc218\ud589\ud558\ub294 \ucf54\ub4dc\ub97c \uc791\uc131\ud558\uace0<\/p>\n<pre><code class=\"language-java\">import java.sql.Connection;\nimport java.sql.DriverManager;\nimport java.sql.PreparedStatement;\nimport java.sql.ResultSet;\nimport java.sql.SQLException;\n\npublic class ConnTest {\n\n    static String result = null;\n    static Connection conn = null;\n    static PreparedStatement pstmt = null;\n\n    public static void main(String[] argv) {\n\n        try{\n            conn = DriverManager.getConnection(&quot;jdbc:oracle:thin:@localhost:49161:xe&quot;, &quot;autotest&quot;, &quot;owncloud&quot;);\n\n            String sql = &quot;SELECT TABLE_NAME from ALL_TABLES&quot;;\n            pstmt = conn.prepareStatement(sql);\n            ResultSet rs = pstmt.executeQuery();\n\n            while (rs.next()) {\n                String col1 = rs.getString(&quot;TABLE_NAME&quot;);\n                System.out.println(col1);\n            }\n\n            pstmt.close();\n            conn.close();\n        }catch (SQLException e){\n            e.printStackTrace();\n        }\n    }\n}<\/code><\/pre>\n<p>\ucef4\ud30c\uc77c\ud558\uba74 \ud14c\uc2a4\ud2b8 \uc900\ube44 \ub05d.<\/p>\n<pre><code class=\"language-bash\">javac ConnTest.java<\/code><\/pre>\n<h2>Diagnosability in JDBC<\/h2>\n<p><a href=\"https:\/\/docs.oracle.com\/database\/121\/JJDBC\/diagnose.htm\">\ub9c1\ud06c<\/a> \uc5d0 \uac00\uba74 \uc790\uc138\ud55c \uc0ac\uc6a9\ubc95\uc744 \ud655\uc778\ud560 \uc218 \uc788\ub294\ub370 \uae30\ubcf8\uc801\uc778 \uc0ac\uc6a9\ubc95\uc740 \uc544\ub798\uc640 \uac19\ub2e4.<\/p>\n<p>\uc77c\ub2e8\uc740 jdbc\ub97c \uc0ac\uc6a9\ud558\ub294 \uba54\uc778 \ud074\ub798\uc2a4\ub97c \uc2e4\ud589\ud560 \ub54c\uc5d0 <code>-Djava.util.logging.config.file=OracleLog.properties -Doracle.jdbc.Trace=true<\/code> \uc640 \uac19\uc740 \uc635\uc158\uc744 \ucd94\uac00\ud574\uc8fc\uba74 \ub418\ub294\ub370,<\/p>\n<p><code>jdbc.log<\/code> \ub77c\ub294 \ud30c\uc77c\ub85c \ub85c\uadf8\ub97c \ub0a8\uae30\uae30 \uc704\ud574\uc11c <code>OracleLog.properties<\/code> \uc124\uc815 \ud30c\uc77c\uc744 \ub9cc\ub4e4\uc5b4\uc900\ub2e4.<\/p>\n<pre><code class=\"language-properties\">.level=SEVERE\noracle.jdbc.level=FINE\noracle.jdbc.handlers=java.util.logging.FileHandler\njava.util.logging.FileHandler.level=FINE\njava.util.logging.FileHandler.pattern=jdbc.log\njava.util.logging.FileHandler.count=1\njava.util.logging.FileHandler.formatter=java.util.logging.SimpleFormatter<\/code><\/pre>\n<p>\uc774\uc81c \ub2e4\uc74c\uacfc \uac19\uc774 \uc55e\uc11c \ub9cc\ub4e0 \ud504\ub85c\uadf8\ub7a8\uc744 \uc2e4\ud589\ud558\uba74<\/p>\n<pre><code class=\"language-bash\">java \\\n    -cp .:ojdbc8_g-19.3.0.0.jar \\\n    -Djava.util.logging.config.file=OracleLog.properties \\\n    -Doracle.jdbc.Trace=true \\\n    ConnTest<\/code><\/pre>\n<p><code>jdbc.log<\/code> \ub77c\ub294 \ud30c\uc77c\uc774 \uc0dd\uc131\ub418\uace0 \ub2e4\uc74c\uacfc \uac19\uc774 DB \uc5d0 \uc694\uccad\ud55c SQL \uc774 \ubaa8\ub450 \ub85c\uae45\ub418\ub294 \uac83\uc744 \ud655\uc778\ud560 \uc218 \uc788\ub2e4.<\/p>\n<pre><code class=\"language-log\">Oct 11, 2019 11:25:06 PM oracle.jdbc.driver.BlockSource$ThreadedCachingBlockSource$BlockReleaserListener &lt;init&gt;\nINFO: setCollectionUsageThreshold&lt;PS Old Gen&gt;(2479148236)\nOct 11, 2019 11:25:06 PM oracle.jdbc.driver.T4CConnection logon\nINFO: Connection.logon: oracle.jdbc.driver.T4CConnection@1e67b872\nOct 11, 2019 11:25:06 PM oracle.jdbc.driver.T4CConnection logon\nINFO: Operating System Process Identifier (SPID): 213 \nOct 11, 2019 11:25:06 PM oracle.jdbc.driver.T4CConnection logon\nINFO: DRCP Enabled: false\nOct 11, 2019 11:25:07 PM oracle.jdbc.driver.OracleStatement logSQL\nCONFIG: 1FB700EE SQL: SELECT TABLE_NAME from ALL_TABLES<\/code><\/pre>\n<p><code>FINE<\/code> \uc678\uc5d0\ub3c4, \uacf5\uc2dd \ubb38\uc11c\uc5d0 \ub530\ub974\uba74 \ub2e4\uc74c\uacfc \uac19\uc740 LOGLEVEL \uc744 \uc124\uc815\ud560 \uc218 \uc788\ub2e4.<\/p>\n<ul>\n<li>OFF\n<ul>\n<li>Turns off logging.<\/li>\n<\/ul>\n<\/li>\n<li>SEVERE\n<ul>\n<li>Logs SQLExceptions and internal errors.<\/li>\n<\/ul>\n<\/li>\n<li>WARNING\n<ul>\n<li>Logs SQLWarnings and bad but not fatal internal conditions.<\/li>\n<\/ul>\n<\/li>\n<li>INFO\n<ul>\n<li>Logs infrequent but significant events and errors. It produces a relatively low volume of log messages.<\/li>\n<\/ul>\n<\/li>\n<li>CONFIG\n<ul>\n<li>Logs SQL strings that are executed.<\/li>\n<\/ul>\n<\/li>\n<li>FINE\n<ul>\n<li>Logs the entry and exit to every public method providing a detailed trace of JDBC operations. It produces a fairly high volume of log messages.<\/li>\n<\/ul>\n<\/li>\n<li>FINER\n<ul>\n<li>Logs calls to internal methods.<\/li>\n<\/ul>\n<\/li>\n<li>FINEST\n<ul>\n<li>Logs calls to high volume internal methods.<\/li>\n<\/ul>\n<\/li>\n<li>ALL\n<ul>\n<li>Logs all the details. This is the most detailed level of logging.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>[Oracle] Diagnosability in Jdbc, driver\uc5d0\uc11c \uc0ac\uc6a9\ub418\ub294 SQL \ub85c\uae45 Oracle jdbc driver (ojdbc) \uc911\uc5d0\uc11c _g \ub85c \ub05d\ub098\ub294 \ub77c\uc774\ube0c\ub7ec\ub9ac\uac00 \uc788\ub294\ub370 \uc774\uac78 \uc774\uc6a9\ud558\uba74 driver \ub97c \ud1b5\ud574\uc11c \ucffc\ub9ac\ud558\ub294 SQL \uc744 \ubaa8\ub450 \ucd94\uc801\ud560 \uc218\uac00 \uc788\ub2e4. Oracle Test DB docker hub \uc5d0 \uac04\ub2e8\ud558\uac8c \uc624\ub77c\ud074 \ub370\uc774\ud130\ubca0\uc774\uc2a4\ub97c \ud14c\uc2a4\ud2b8\ud574\ubcfc \uc218 \uc788\ub294 \uc774\ubbf8\uc9c0\uac00 \uc788\uc5b4 \uc774\uac78 \uc774\uc6a9\ud588\ub2e4. \ub4f1\ub85d\ud55c \uc0ac\uc6a9\uc790\ub791 DB \uc2a4\ud0a4\ub9c8\ub97c \ubcf4\ub2c8 owncloud \uc5d0\uc11c repository DB [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[162,36,48],"tags":[],"class_list":["post-1432","post","type-post","status-publish","format-standard","hentry","category-docker","category-java","category-oracle"],"_links":{"self":[{"href":"https:\/\/oboki.net\/workspace\/wp-json\/wp\/v2\/posts\/1432","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/oboki.net\/workspace\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/oboki.net\/workspace\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/oboki.net\/workspace\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/oboki.net\/workspace\/wp-json\/wp\/v2\/comments?post=1432"}],"version-history":[{"count":0,"href":"https:\/\/oboki.net\/workspace\/wp-json\/wp\/v2\/posts\/1432\/revisions"}],"wp:attachment":[{"href":"https:\/\/oboki.net\/workspace\/wp-json\/wp\/v2\/media?parent=1432"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/oboki.net\/workspace\/wp-json\/wp\/v2\/categories?post=1432"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/oboki.net\/workspace\/wp-json\/wp\/v2\/tags?post=1432"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}