{"id":737,"date":"2013-02-26T12:03:13","date_gmt":"2013-02-26T04:03:13","guid":{"rendered":"http:\/\/www.yallonking.com\/?p=737"},"modified":"2013-02-26T19:16:24","modified_gmt":"2013-02-26T11:16:24","slug":"scripts%e6%9f%a5%e7%9c%8b%e5%a4%96%e9%94%ae%e7%b4%a2%e5%bc%95%e6%98%af%e5%90%a6%e5%ad%98%e5%9c%a8","status":"publish","type":"post","link":"http:\/\/www.yallonking.com\/?p=737","title":{"rendered":"scripts:\u67e5\u770b\u5916\u952e\u7d22\u5f15\u662f\u5426\u5b58\u5728"},"content":{"rendered":"

\u4ee5\u4e0b\u9002\u7528oracle10g<\/strong>
\n\u6ce8\u610f\uff1a\u8be5sql\u5bf9\u8868\u7684\u5916\u952e\u7684\u5217\u6570\u6709\u9650\u5236\u3002
\n
\ncolumn columns format a30 word_wrapped
\ncolumn tablename format a15 word_wrapped
\ncolumn constraint_name format a15 word_wrapped<\/p>\n

select table_name,
\n constraint_name,
\n cname1 || nvl2(cname2, ',' || cname2, null) ||
\n nvl2(cname3, ',' || cname3, null) ||
\n nvl2(cname4, ',' || cname4, null) ||
\n nvl2(cname5, ',' || cname5, null) ||
\n nvl2(cname6, ',' || cname6, null) ||
\n nvl2(cname7, ',' || cname7, null) ||
\n nvl2(cname8, ',' || cname8, null) columns
\n from (select b.table_name,
\n b.constraint_name,
\n max(decode(position, 1, column_name, null)) cname1,
\n max(decode(position, 2, column_name, null)) cname2,
\n max(decode(position, 3, column_name, null)) cname3,
\n max(decode(position, 4, column_name, null)) cname4,
\n max(decode(position, 5, column_name, null)) cname5,
\n max(decode(position, 6, column_name, null)) cname6,
\n max(decode(position, 7, column_name, null)) cname7,
\n max(decode(position, 8, column_name, null)) cname8,
\n count(*) col_cnt
\n from (select substr(table_name, 1, 30) table_name,
\n substr(constraint_name, 1, 30) constraint_name,
\n substr(column_name, 1, 30) column_name,
\n position
\n from user_cons_columns) a,
\n user_constraints b
\n where a.constraint_name = b.constraint_name
\n and b.constraint_type = 'R'
\n group by b.table_name, b.constraint_name) cons
\n where col_cnt > ALL (select count(*)
\n from user_ind_columns i
\n where i.table_name = cons.table_name
\n and i.column_name in (cname1,
\n cname2,
\n cname3,
\n cname4,
\n cname5,
\n cname6,
\n cname7,
\n cname8)
\n and i.column_position <= cons.col_cnt\n group by i.index_name);\n<\/coolcode><\/p>\n

\u4ee5\u4e0b\u9002\u7528\u4e8eoracle11g<\/strong>
\n
\nselect case
\n when ind.index_name is not null then
\n case
\n when ind.index_type in ('BITMAP') then
\n '**Bitmap idx**'
\n else
\n 'indexed'
\n end
\n else
\n '**Check idx**'
\n end checker,
\n ind.index_type,
\n cons.owner,
\n cons.table_name,
\n ind.index_name,
\n cons.constraint_name,
\n cons.cols
\n from (select c.owner,
\n c.table_name,
\n c.constraint_name,
\n listagg(cc.column_name, ',') within group(order by cc.column_name) cols
\n from dba_constraints c, dba_cons_columns cc
\n where c.owner = cc.owner
\n and c.owner = upper('&&schema')
\n and c.constraint_name = cc.constraint_name
\n and c.constraint_type = 'R'
\n group by c.owner, c.table_name, c.constraint_name) cons
\n left outer join (select table_owner,
\n table_name,
\n index_name,
\n index_type,
\n cbr,
\n listagg(column_name, ',') within group(order by column_name) cols
\n from (select ic.table_owner,
\n ic.table_name,
\n ic.index_name,
\n ic.column_name,
\n ic.column_position,
\n i.index_type,
\n connect_by_root(ic.column_name) cbr
\n from dba_ind_columns ic, dba_indexes i
\n where ic.table_owner = upper('&&schema')
\n and ic.table_owner = i.table_owner
\n and ic.table_name = i.table_name
\n and ic.index_name = i.index_name
\n connect by prior ic.column_position - 1 =
\n ic.column_position
\n and prior ic.index_name = ic.index_name)
\n group by table_owner,
\n table_name,
\n index_name,
\n index_type,
\n cbr) ind
\n on cons.cols = ind.cols
\n and cons.table_name = ind.table_name
\n and cons.owner = ind.table_owner
\n order by checker, cons.owner, cons.table_name;
\n<\/coolcode><\/p>\n","protected":false},"excerpt":{"rendered":"

\u4ee5\u4e0b\u9002\u7528oracle10g \u6ce8\u610f\uff1a\u8be5sql\u5bf9\u8868\u7684\u5916\u952e\u7684\u5217\u6570\u6709\u9650\u5236\u3002 column columns format […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[9],"tags":[],"_links":{"self":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts\/737"}],"collection":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=737"}],"version-history":[{"count":5,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts\/737\/revisions"}],"predecessor-version":[{"id":747,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=\/wp\/v2\/posts\/737\/revisions\/747"}],"wp:attachment":[{"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=737"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=737"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.yallonking.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=737"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}