{"id":447,"date":"2019-02-21T11:56:23","date_gmt":"2019-02-21T02:56:23","guid":{"rendered":"http:\/\/localhost:8000\/?p=447"},"modified":"2021-01-23T11:56:57","modified_gmt":"2021-01-23T02:56:57","slug":"oracle-tips","status":"publish","type":"post","link":"http:\/\/localhost:8000\/2019\/02\/oracle-tips.html","title":{"rendered":"Oracle\u306eTips"},"content":{"rendered":"<p>Oracle\u95a2\u4fc2\u306e\u30e1\u30e2\u3002<\/p>\n<h2>\u518d\u8d77SQL\u3067\u30c7\u30fc\u30bf\u8907\u88fd<\/h2>\n<pre><code class=\"language-sql\">-- hoge\u30c6\u30fc\u30d6\u30eb\u306eid = 1\u30921000\u4ef6\u307e\u3067\u8907\u88fd\u3059\u308bSQL\ninsert into hoge (\nselect * from (\n  with hoge_cal (id, name) as (\n    select\n      id, name\n    from\n      hoge\n    where\n      id = 1 -- \u8907\u88fd\u5143\u3092\u6307\u5b9a\n    union all\n    select\n      id + 1\n      , name || id + 1\n    from\n      hoge_cal\n    where\n      id &lt;= 1000 -- 1000\u4ef6\u307e\u3067\n  )\n  select\n    id, name\n  from\n    hoge_cal\n  where\n    id &lt;&gt;  1 -- \u8907\u88fd\u5143\u306f\u9664\u304f\n));<\/code><\/pre>\n<h2>explain plan<\/h2>\n<pre><code class=\"language-sql\"># sqlplus\u5185\u3067\u4ee5\u4e0b\u3092\u5b9f\u884c\u3059\u308b\n-- \u307e\u305aplan table\u3092\u4f5c\u6210\u3059\u308b\n@\/apps\/oracle\/product\/10.2.0\/db_1\/rdbms\/admin\/utlxplan.sql\n\n-- explain plan\u3092\u53d6\u5f97\u3059\u308b\nset linesize 300\ntruncate table plan_table;\nexplain plan for select aaa,bbb from ccc where ddd = &#039;eee&#039;;\nselect operation || &#039; options= &#039; || options || &#039; object= &#039;  || object_name || &#039; position= &#039; || position || &#039; cost= &#039; || cost from plan_table;\n\n-- SQL\u5b9f\u884c\u6642\u9593\u8a08\u6e2c\nset timing on\nselect * from hoge;\nElapsed: 00:00:01.23<\/code><\/pre>\n<h2>kill session<\/h2>\n<pre><code class=\"language-sql\"># sqlplus\u5185\u3067\u4ee5\u4e0b\u3092\u5b9f\u884c\u3059\u308b\n\n-- sid\u3068serial#\u3092\u53d6\u5f97\nselect sid, serial#, username, program, status from v$session where username = &#039;hoge&#039;;\nID       SERIAL#  USERNAME  STATUS\n1256  45167      hoge              XXXX\n\nalter system kill session &#039;1256,45167&#039; immediate;<\/code><\/pre>\n<h2>\u30c6\u30fc\u30d6\u30eb\u5225\u306e\u5bb9\u91cf\u78ba\u8a8d<\/h2>\n<pre><code class=\"language-sql\">select\n  at.table_name\n  , trunc(us.bytes\/1024\/1024,0) tablesize_m\n  from\n    all_tables \n    join user_segments us on us.segment_name = at.table_name\n  where\n    owner = &#039;hoge&#039;\n  order by\n    tablesize_m desc<\/code><\/pre>\n<h2>\u65e2\u5b58\u306e\u30c6\u30fc\u30d6\u30eb\u5c5e\u6027\u3092\u7d99\u627f\u3057\u305f\u307e\u307e\u3001\u540c\u4e00\u8868\u9818\u57df\u4e0a\u306b\u65b0\u3057\u3044\u30bb\u30b0\u30e1\u30f3\u30c8\u3092\u78ba\u4fdd<\/h2>\n<ul>\n<li>\u3059\u3079\u3066\u306e\u30c7\u30fc\u30bf\u3092\u518d\u914d\u7f6e\u3057\u3066\u73fe\u5728\u4f7f\u7528\u4e2d\u306e\u30bb\u30b0\u30e1\u30f3\u30c8\u306f\u89e3\u653e\u3059\u308b<\/li>\n<li>\u305f\u3060\u3053\u308c\u3092\u3084\u308b\u3068INDEX\u304c\u58ca\u308c\u308b\u6a21\u69d8\u3002index\u518d\u751f\u6210\u304c\u5fc5\u8981\n<pre><code class=\"language-sql\">alter table hoge move [ online ]<\/code><\/pre>\n<\/li>\n<\/ul>\n<h2>\u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u3092\u518d\u751f\u6210<\/h2>\n<pre><code class=\"language-sql\">alter index index_name rebuild<\/code><\/pre>\n<h2>\u6a29\u9650\u5468\u308a<\/h2>\n<pre><code class=\"language-sql\">-- \u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u6a29\u9650\u4ed8\u4e0e\ngrant select, update, delete, insert, \non hoge_schema.fuga_table\nto sample_user;\n\n-- \u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u6a29\u9650\u78ba\u8a8d\nselect *\nfrom\n  dba_tab_privs\nwhere\n  table_name = &#039;HOGE&#039;\n  and owner = &#039;FUGA&#039;<\/code><\/pre>\n<h2>sqlplus<\/h2>\n<pre><code class=\"language-sh\">-- \u6298\u8fd4\u3057\u8868\u793a\u3092\u898b\u3084\u3059\u304f\u3059\u308b\nset linesize 1000\nset pagesize1000<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"<p>Oracle\u95a2\u4fc2\u306e\u30e1\u30e2\u3002 \u518d\u8d77SQL\u3067\u30c7\u30fc\u30bf\u8907\u88fd &#8212; hoge\u30c6\u30fc\u30d6\u30eb\u306eid = 1\u30921000\u4ef6\u307e\u3067\u8907\u88fd\u3059\u308bSQL insert into hoge ( select * from ( with hoge_cal (id, name) as ( select id, name from hoge where id = 1 &#8212; \u8907\u88fd\u5143\u3092\u6307\u5b9a union all select id + 1 , name || id + 1 from hoge_cal where id &lt;= 1000 &#8212; 1000\u4ef6\u307e\u3067 ) select id, name from hoge_cal where id &lt;&gt; 1 &#8212; \u8907\u88fd\u5143\u306f\u9664\u304f )); explain plan # sqlplus\u5185\u3067\u4ee5\u4e0b\u3092\u5b9f\u884c\u3059\u308b &#8212; \u307e\u305aplan table\u3092\u4f5c\u6210\u3059\u308b @\/apps\/oracle\/product\/ <a href=\"http:\/\/localhost:8000\/2019\/02\/oracle-tips.html\" class=\"read-more button-fancy -red\"><span class=\"btn-arrow\"><\/span><span class=\"twp-read-more text\">Continue Reading<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[34],"tags":[],"_links":{"self":[{"href":"http:\/\/localhost:8000\/wp-json\/wp\/v2\/posts\/447"}],"collection":[{"href":"http:\/\/localhost:8000\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/localhost:8000\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/localhost:8000\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/localhost:8000\/wp-json\/wp\/v2\/comments?post=447"}],"version-history":[{"count":0,"href":"http:\/\/localhost:8000\/wp-json\/wp\/v2\/posts\/447\/revisions"}],"wp:attachment":[{"href":"http:\/\/localhost:8000\/wp-json\/wp\/v2\/media?parent=447"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/localhost:8000\/wp-json\/wp\/v2\/categories?post=447"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/localhost:8000\/wp-json\/wp\/v2\/tags?post=447"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}