役立つSQL技 for オラクル
索引
・【テーブル構造取得】
・【インデックス情報を取得】
・【ビューの定義情報取得】
・【マテリアライズド・ビューの定義情報取得】
・【基本的な再帰(ANSI SQL)】
・【連続レコード発生】
・【CSV形式のデータを各フィールドに分解】
・【カラムのコメントを取得】
・【全角文字を半角文字に変換】
・【SID 取得】
・【ユーザー名 取得】
・【アプリケーション 取得】
・【セッション情報 取得】
・【実行中のSQL取得（のぞき見）】
・【実行中のSQL取得（集約）】
・【実行中のSQL取得（集約）古いやり方Oracleの制約により完全な復元が困難】
・【セッション実行状況取得】
・【実行中のSQLの実行計画を取得】
・【実行プラン取得(コミットコントロールの元で実施がおすすめ、取得完了後にロールバックが必要、消し忘れを防ぐ)】

索引へ

【テーブル構造取得】 

SELECT distinct
/* テーブル構造取得 */
 a.TABLE_NAME 
,a.COLUMN_ID 
,a.COLUMN_NAME 
,a.DATA_TYPE 
,a.DATA_LENGTH 
,a.DATA_PRECISION 
,a.DATA_SCALE 
,case when a.NULLABLE = 'Y' then 'NULL' when a.NULLABLE = 'N' then 'NOT NULL' end as NULLS 
,a.CHARACTER_SET_NAME 
,cast(substr(b.COMMENTS,1,128) as varchar2(255)) as COMMENTS 
FROM ALL_TAB_COLUMNS a 
left outer join USER_COL_COMMENTS b 
on a.TABLE_NAME = b.TABLE_NAME 
and a.COLUMN_NAME = b.COLUMN_NAME 
WHERE 1=1
--and   a.TABLE_NAME like 'xxxxxxxxxx' 
ORDER BY a.TABLE_NAME,a.COLUMN_ID

TABLE_NAMEに指定したテーブルの構造を取得

索引へ

【インデックス情報を取得】 

SELECT
/* インデックス情報を取得 */
 ALL_IND_COLUMNS.TABLE_NAME
,ALL_IND_COLUMNS.INDEX_NAME
,ALL_IND_COLUMNS.COLUMN_POSITION
,SUBSTR(ALL_IND_COLUMNS.COLUMN_NAME,1,64) as COLUMN_NAME
,ALL_INDEXES.UNIQUENESS
from ALL_IND_COLUMNS
left outer join ALL_INDEXES
on  ALL_INDEXES.OWNER      = ALL_IND_COLUMNS.INDEX_OWNER
and ALL_INDEXES.INDEX_NAME = ALL_IND_COLUMNS.INDEX_NAME
where 1=1
--and TABLE_NAME = 'xxxxxxxxxx'
ORDER BY
 ALL_IND_COLUMNS.INDEX_OWNER
,ALL_IND_COLUMNS.TABLE_NAME
,ALL_IND_COLUMNS.INDEX_NAME
,ALL_IND_COLUMNS.COLUMN_POSITION

TABLE_NAMEと関係のあるインデックス情報を取得します

索引へ

【ビューの定義情報取得】 

select
/* ビューの定義情報取得 */
 OWNER
,VIEW_NAME
,TEXT
,TEXT_VC
,TEXT_LENGTH
,length(TEXT_VC)
,ORIGIN_CON_ID
from ALL_VIEWS
where 1=1
order by
 OWNER
,VIEW_NAME

ビューの定義情報を取得します、
4000バイト以下であれば「TEXT_VC」の内容でOK
4001バイト以上かつ「ORIGIN_CON_ID=3」であれば「TEXT」の内容を使うのがおすすめ

索引へ

【マテリアライズド・ビューの定義情報取得】 

select
/* マテリアライズド・ビューの定義情報取得 */
 OWNER
,MVIEW_NAME
,QUERY
from ALL_MVIEWS
where 1=1

マテリアライズド・ビューの定義情報を取得します、

索引へ

【基本的な再帰(ANSI SQL)】 

--select
/* 基本的な再帰(ANSI SQL) */
with ListNo(p)
as
(
  select 1 as p from dual
  union all
  select p+1 from ListNo
  where p < 5
)
select p from ListNo

Oracle Database 11g R2　以降でのバージョンでのみ実行可能（らしい？）

索引へ

【連続レコード発生】 

select
 RowNum 
from dual
connect by Level <= 100

1〜100の番号を持つレコードを発生させています
年間の日付を作る時などに便利
↓↓↓こんな感じ↓↓↓
select
/* 連続レコード発生 */
cast('20120101' as date) + RowNum-1 as 日付 
from dual
where cast('20120101' as date) + RowNum-1 < cast('20130101' as date)
connect by Level <= 400

索引へ

【CSV形式のデータを各フィールドに分解】 

--select
/* CSV形式のデータを各フィールドに分解 */
with dt as (
select '123,456,789,"ABC","E,"","",FG","HIJ",0' as csv from dual union all
select '000,456,789,"ABC","E,"","",FG","HIJ",0' as csv from dual union all
select '999,456,789,"ABC","E,"","",FG","HIJ",0' as csv from dual union all
select '789,456,789,"ABC","E,"","",FG","HIJ",0' as csv from dual union all
select '123,456,789,"ABC","E,"","",FG","HIJ",0' as csv from dual
),
dta as (
  select
   r 
  ,csv
  ,no
  ,substr(csv,no,1) as c
  from (select RowNum as r,','||csv||',' as csv from dt) dt
  left outer join (select RowNum as no from dual connect by Level <= 400) b
  on no <= length(csv)
)
select
 R
,CSV
,max(case when G_SEQ = 001 then CSV_ZZZ end) as val001
,max(case when G_SEQ = 002 then CSV_ZZZ end) as val002
,max(case when G_SEQ = 003 then CSV_ZZZ end) as val003
,max(case when G_SEQ = 004 then CSV_ZZZ end) as val004
,max(case when G_SEQ = 005 then CSV_ZZZ end) as val005
,max(case when G_SEQ = 006 then CSV_ZZZ end) as val006
,max(case when G_SEQ = 007 then CSV_ZZZ end) as val007
,max(case when G_SEQ = 008 then CSV_ZZZ end) as val008
,max(case when G_SEQ = 009 then CSV_ZZZ end) as val009
,max(case when G_SEQ = 010 then CSV_ZZZ end) as val010
,max(case when G_SEQ = 011 then CSV_ZZZ end) as val011
,max(case when G_SEQ = 012 then CSV_ZZZ end) as val012
,max(case when G_SEQ = 013 then CSV_ZZZ end) as val013
,max(case when G_SEQ = 014 then CSV_ZZZ end) as val014
,max(case when G_SEQ = 015 then CSV_ZZZ end) as val015
from (
  select
   R
  ,CSV
  ,NO
  ,ROW_NUMBER() OVER (PARTITION BY R,CSV ORDER BY NO) as G_SEQ
  ,CSV_ZZZ
  ,NEXT_NO
  from (
    select
     R
    ,CSV
    ,NO
    ,ROW_NUMBER() OVER (PARTITION BY R,CSV,(no + length(CSV_ZZZ)) ORDER BY NO) as g_seq
    ,cast(replace(replace(replace(substr(CSV_ZZZ,2),'""','^%%^'),'"',''),'^%%^','"') as varchar(50)) as CSV_ZZZ
    ,no + length(CSV_ZZZ) as next_no
    from (
      select 
       R
      ,substr(CSV,2,length(CSV)-2) as CSV
      ,NO
      ,case
       when substr(csv,no,2) = ',"' then substr(csv,no,instr(replace(substr(csv,no),'""','^^'),'",')) 
       else                              substr(csv,no,instr(replace(substr(csv,no+1),'""','^^'),','))
       end as CSV_zzz
      from dta a
      where substr(csv,no,1) = ','
    ) a
  ) a
  where g_seq = 1
)
group by
 R
,CSV
order by 1,2,3

１つのフィールドに入っているＣＳＶ形式のデータを個別のフィールドに分解し格納
上記のＳＱＬはそのまま実行できます。
上記SQL　with句の「dt」ビューを実際のテーブルに置き換えればフィールド分割可能

索引へ

【カラムのコメントを取得】 

SELECT distinct 
/* カラムのコメントを取得 */
 a.TABLE_NAME 
,a.COLUMN_NAME 
,cast(substr(b.COMMENTS,1,128) as varchar2(255)) as COMMENTS 
FROM ALL_TAB_COLUMNS a 
left outer join USER_COL_COMMENTS b 
on  a.TABLE_NAME  = b.TABLE_NAME 
and a.COLUMN_NAME = b.COLUMN_NAME 
WHERE a.TABLE_NAME like '%xxxxx%' 
ORDER BY
 a.TABLE_NAME
,a.COLUMN_ID 







索引へ



【全角文字を半角文字に変換】



















--select
with 変換表
as
(
      select cast('　' as varchar2(2)) as 全角,cast(' '  as varchar2(2)) as 半角 from dual
union select cast('ァ' as varchar2(2)) as 全角,cast('ｧ'  as varchar2(2)) as 半角 from dual
union select cast('ア' as varchar2(2)) as 全角,cast('ｱ'  as varchar2(2)) as 半角 from dual
union select cast('ィ' as varchar2(2)) as 全角,cast('ｨ'  as varchar2(2)) as 半角 from dual
union select cast('イ' as varchar2(2)) as 全角,cast('ｲ'  as varchar2(2)) as 半角 from dual
union select cast('ゥ' as varchar2(2)) as 全角,cast('ｩ'  as varchar2(2)) as 半角 from dual
union select cast('ウ' as varchar2(2)) as 全角,cast('ｳ'  as varchar2(2)) as 半角 from dual
union select cast('ェ' as varchar2(2)) as 全角,cast('ｪ'  as varchar2(2)) as 半角 from dual
union select cast('エ' as varchar2(2)) as 全角,cast('ｴ'  as varchar2(2)) as 半角 from dual
union select cast('ォ' as varchar2(2)) as 全角,cast('ｫ'  as varchar2(2)) as 半角 from dual
union select cast('オ' as varchar2(2)) as 全角,cast('ｵ'  as varchar2(2)) as 半角 from dual
union select cast('カ' as varchar2(2)) as 全角,cast('ｶ'  as varchar2(2)) as 半角 from dual
union select cast('ガ' as varchar2(2)) as 全角,cast('ｶﾞ' as varchar2(2)) as 半角 from dual
union select cast('キ' as varchar2(2)) as 全角,cast('ｷ'  as varchar2(2)) as 半角 from dual
union select cast('ギ' as varchar2(2)) as 全角,cast('ｷﾞ' as varchar2(2)) as 半角 from dual
union select cast('ク' as varchar2(2)) as 全角,cast('ｸ'  as varchar2(2)) as 半角 from dual
union select cast('グ' as varchar2(2)) as 全角,cast('ｸﾞ' as varchar2(2)) as 半角 from dual
union select cast('ケ' as varchar2(2)) as 全角,cast('ｹ'  as varchar2(2)) as 半角 from dual
union select cast('ゲ' as varchar2(2)) as 全角,cast('ｹﾞ' as varchar2(2)) as 半角 from dual
union select cast('コ' as varchar2(2)) as 全角,cast('ｺ'  as varchar2(2)) as 半角 from dual
union select cast('ゴ' as varchar2(2)) as 全角,cast('ｺﾞ' as varchar2(2)) as 半角 from dual
union select cast('サ' as varchar2(2)) as 全角,cast('ｻ'  as varchar2(2)) as 半角 from dual
union select cast('ザ' as varchar2(2)) as 全角,cast('ｻﾞ' as varchar2(2)) as 半角 from dual
union select cast('シ' as varchar2(2)) as 全角,cast('ｼ'  as varchar2(2)) as 半角 from dual
union select cast('ジ' as varchar2(2)) as 全角,cast('ｼﾞ' as varchar2(2)) as 半角 from dual
union select cast('ス' as varchar2(2)) as 全角,cast('ｽ'  as varchar2(2)) as 半角 from dual
union select cast('ズ' as varchar2(2)) as 全角,cast('ｽﾞ' as varchar2(2)) as 半角 from dual
union select cast('セ' as varchar2(2)) as 全角,cast('ｾ'  as varchar2(2)) as 半角 from dual
union select cast('ゼ' as varchar2(2)) as 全角,cast('ｾﾞ' as varchar2(2)) as 半角 from dual
union select cast('ソ' as varchar2(2)) as 全角,cast('ｿ'  as varchar2(2)) as 半角 from dual
union select cast('ゾ' as varchar2(2)) as 全角,cast('ｿﾞ' as varchar2(2)) as 半角 from dual
union select cast('タ' as varchar2(2)) as 全角,cast('ﾀ'  as varchar2(2)) as 半角 from dual
union select cast('ダ' as varchar2(2)) as 全角,cast('ﾀﾞ' as varchar2(2)) as 半角 from dual
union select cast('チ' as varchar2(2)) as 全角,cast('ﾁ'  as varchar2(2)) as 半角 from dual
union select cast('ヂ' as varchar2(2)) as 全角,cast('ﾁﾞ' as varchar2(2)) as 半角 from dual
union select cast('ッ' as varchar2(2)) as 全角,cast('ｯ'  as varchar2(2)) as 半角 from dual
union select cast('ツ' as varchar2(2)) as 全角,cast('ﾂ'  as varchar2(2)) as 半角 from dual
union select cast('ヅ' as varchar2(2)) as 全角,cast('ﾂﾞ' as varchar2(2)) as 半角 from dual
union select cast('テ' as varchar2(2)) as 全角,cast('ﾃ'  as varchar2(2)) as 半角 from dual
union select cast('デ' as varchar2(2)) as 全角,cast('ﾃﾞ' as varchar2(2)) as 半角 from dual
union select cast('ト' as varchar2(2)) as 全角,cast('ﾄ'  as varchar2(2)) as 半角 from dual
union select cast('ド' as varchar2(2)) as 全角,cast('ﾄﾞ' as varchar2(2)) as 半角 from dual
union select cast('ナ' as varchar2(2)) as 全角,cast('ﾅ'  as varchar2(2)) as 半角 from dual
union select cast('ニ' as varchar2(2)) as 全角,cast('ﾆ'  as varchar2(2)) as 半角 from dual
union select cast('ヌ' as varchar2(2)) as 全角,cast('ﾇ'  as varchar2(2)) as 半角 from dual
union select cast('ネ' as varchar2(2)) as 全角,cast('ﾈ'  as varchar2(2)) as 半角 from dual
union select cast('ノ' as varchar2(2)) as 全角,cast('ﾉ'  as varchar2(2)) as 半角 from dual
union select cast('ハ' as varchar2(2)) as 全角,cast('ﾊ'  as varchar2(2)) as 半角 from dual
union select cast('バ' as varchar2(2)) as 全角,cast('ﾊﾞ' as varchar2(2)) as 半角 from dual
union select cast('パ' as varchar2(2)) as 全角,cast('ﾊﾟ' as varchar2(2)) as 半角 from dual
union select cast('ヒ' as varchar2(2)) as 全角,cast('ﾋ'  as varchar2(2)) as 半角 from dual
union select cast('ビ' as varchar2(2)) as 全角,cast('ﾋﾞ' as varchar2(2)) as 半角 from dual
union select cast('ピ' as varchar2(2)) as 全角,cast('ﾋﾟ' as varchar2(2)) as 半角 from dual
union select cast('フ' as varchar2(2)) as 全角,cast('ﾌ'  as varchar2(2)) as 半角 from dual
union select cast('ブ' as varchar2(2)) as 全角,cast('ﾌﾞ' as varchar2(2)) as 半角 from dual
union select cast('プ' as varchar2(2)) as 全角,cast('ﾌﾟ' as varchar2(2)) as 半角 from dual
union select cast('ヘ' as varchar2(2)) as 全角,cast('ﾍ'  as varchar2(2)) as 半角 from dual
union select cast('ベ' as varchar2(2)) as 全角,cast('ﾍﾞ' as varchar2(2)) as 半角 from dual
union select cast('ペ' as varchar2(2)) as 全角,cast('ﾍﾟ' as varchar2(2)) as 半角 from dual
union select cast('ホ' as varchar2(2)) as 全角,cast('ﾎ'  as varchar2(2)) as 半角 from dual
union select cast('ボ' as varchar2(2)) as 全角,cast('ﾎﾞ' as varchar2(2)) as 半角 from dual
union select cast('ポ' as varchar2(2)) as 全角,cast('ﾎﾟ' as varchar2(2)) as 半角 from dual
union select cast('マ' as varchar2(2)) as 全角,cast('ﾏ'  as varchar2(2)) as 半角 from dual
union select cast('ミ' as varchar2(2)) as 全角,cast('ﾐ'  as varchar2(2)) as 半角 from dual
union select cast('ム' as varchar2(2)) as 全角,cast('ﾑ'  as varchar2(2)) as 半角 from dual
union select cast('メ' as varchar2(2)) as 全角,cast('ﾒ'  as varchar2(2)) as 半角 from dual
union select cast('モ' as varchar2(2)) as 全角,cast('ﾓ'  as varchar2(2)) as 半角 from dual
union select cast('ャ' as varchar2(2)) as 全角,cast('ｬ'  as varchar2(2)) as 半角 from dual
union select cast('ヤ' as varchar2(2)) as 全角,cast('ﾔ'  as varchar2(2)) as 半角 from dual
union select cast('ュ' as varchar2(2)) as 全角,cast('ｭ'  as varchar2(2)) as 半角 from dual
union select cast('ユ' as varchar2(2)) as 全角,cast('ﾕ'  as varchar2(2)) as 半角 from dual
union select cast('ョ' as varchar2(2)) as 全角,cast('ｮ'  as varchar2(2)) as 半角 from dual
union select cast('ヨ' as varchar2(2)) as 全角,cast('ﾖ'  as varchar2(2)) as 半角 from dual
union select cast('ラ' as varchar2(2)) as 全角,cast('ﾗ'  as varchar2(2)) as 半角 from dual
union select cast('リ' as varchar2(2)) as 全角,cast('ﾘ'  as varchar2(2)) as 半角 from dual
union select cast('ル' as varchar2(2)) as 全角,cast('ﾙ'  as varchar2(2)) as 半角 from dual
union select cast('レ' as varchar2(2)) as 全角,cast('ﾚ'  as varchar2(2)) as 半角 from dual
union select cast('ロ' as varchar2(2)) as 全角,cast('ﾛ'  as varchar2(2)) as 半角 from dual
union select cast('ヮ' as varchar2(2)) as 全角,cast('ﾜ'  as varchar2(2)) as 半角 from dual
union select cast('ワ' as varchar2(2)) as 全角,cast('ﾜ'  as varchar2(2)) as 半角 from dual
union select cast('ヰ' as varchar2(2)) as 全角,cast('ｲ'  as varchar2(2)) as 半角 from dual
union select cast('ヱ' as varchar2(2)) as 全角,cast('ｴ'  as varchar2(2)) as 半角 from dual
union select cast('ヲ' as varchar2(2)) as 全角,cast('ｦ'  as varchar2(2)) as 半角 from dual
union select cast('ン' as varchar2(2)) as 全角,cast('ﾝ'  as varchar2(2)) as 半角 from dual
union select cast('ヴ' as varchar2(2)) as 全角,cast('ｳﾞ' as varchar2(2)) as 半角 from dual
union select cast('ヵ' as varchar2(2)) as 全角,cast('ｶ'  as varchar2(2)) as 半角 from dual
union select cast('ヶ' as varchar2(2)) as 全角,cast('ｹ'  as varchar2(2)) as 半角 from dual
union select cast('！' as varchar2(2)) as 全角,cast('!'  as varchar2(2)) as 半角 from dual
union select cast('”' as varchar2(2)) as 全角,cast('"'  as varchar2(2)) as 半角 from dual
union select cast('＃' as varchar2(2)) as 全角,cast('#'  as varchar2(2)) as 半角 from dual
union select cast('＄' as varchar2(2)) as 全角,cast('$'  as varchar2(2)) as 半角 from dual
union select cast('％' as varchar2(2)) as 全角,cast('%'  as varchar2(2)) as 半角 from dual
union select cast('＆' as varchar2(2)) as 全角,cast('&'  as varchar2(2)) as 半角 from dual
union select cast('’' as varchar2(2)) as 全角,cast('''' as varchar2(2)) as 半角 from dual
union select cast('（' as varchar2(2)) as 全角,cast('('  as varchar2(2)) as 半角 from dual
union select cast('）' as varchar2(2)) as 全角,cast(')'  as varchar2(2)) as 半角 from dual
union select cast('＊' as varchar2(2)) as 全角,cast('*'  as varchar2(2)) as 半角 from dual
union select cast('＋' as varchar2(2)) as 全角,cast('+'  as varchar2(2)) as 半角 from dual
union select cast('，' as varchar2(2)) as 全角,cast(','  as varchar2(2)) as 半角 from dual
union select cast('−' as varchar2(2)) as 全角,cast('-'  as varchar2(2)) as 半角 from dual
union select cast('．' as varchar2(2)) as 全角,cast('.'  as varchar2(2)) as 半角 from dual
union select cast('／' as varchar2(2)) as 全角,cast('/'  as varchar2(2)) as 半角 from dual
union select cast('０' as varchar2(2)) as 全角,cast('0'  as varchar2(2)) as 半角 from dual
union select cast('１' as varchar2(2)) as 全角,cast('1'  as varchar2(2)) as 半角 from dual
union select cast('２' as varchar2(2)) as 全角,cast('2'  as varchar2(2)) as 半角 from dual
union select cast('３' as varchar2(2)) as 全角,cast('3'  as varchar2(2)) as 半角 from dual
union select cast('４' as varchar2(2)) as 全角,cast('4'  as varchar2(2)) as 半角 from dual
union select cast('５' as varchar2(2)) as 全角,cast('5'  as varchar2(2)) as 半角 from dual
union select cast('６' as varchar2(2)) as 全角,cast('6'  as varchar2(2)) as 半角 from dual
union select cast('７' as varchar2(2)) as 全角,cast('7'  as varchar2(2)) as 半角 from dual
union select cast('８' as varchar2(2)) as 全角,cast('8'  as varchar2(2)) as 半角 from dual
union select cast('９' as varchar2(2)) as 全角,cast('9'  as varchar2(2)) as 半角 from dual
union select cast('：' as varchar2(2)) as 全角,cast(':'  as varchar2(2)) as 半角 from dual
union select cast('；' as varchar2(2)) as 全角,cast(';'  as varchar2(2)) as 半角 from dual
union select cast('＜' as varchar2(2)) as 全角,cast('<'  as varchar2(2)) as 半角 from dual
union select cast('＝' as varchar2(2)) as 全角,cast('='  as varchar2(2)) as 半角 from dual
union select cast('＞' as varchar2(2)) as 全角,cast('>'  as varchar2(2)) as 半角 from dual
union select cast('？' as varchar2(2)) as 全角,cast('?'  as varchar2(2)) as 半角 from dual
union select cast('＠' as varchar2(2)) as 全角,cast('@'  as varchar2(2)) as 半角 from dual
union select cast('Ａ' as varchar2(2)) as 全角,cast('A'  as varchar2(2)) as 半角 from dual
union select cast('Ｂ' as varchar2(2)) as 全角,cast('B'  as varchar2(2)) as 半角 from dual
union select cast('Ｃ' as varchar2(2)) as 全角,cast('C'  as varchar2(2)) as 半角 from dual
union select cast('Ｄ' as varchar2(2)) as 全角,cast('D'  as varchar2(2)) as 半角 from dual
union select cast('Ｅ' as varchar2(2)) as 全角,cast('E'  as varchar2(2)) as 半角 from dual
union select cast('Ｆ' as varchar2(2)) as 全角,cast('F'  as varchar2(2)) as 半角 from dual
union select cast('Ｇ' as varchar2(2)) as 全角,cast('G'  as varchar2(2)) as 半角 from dual
union select cast('Ｈ' as varchar2(2)) as 全角,cast('H'  as varchar2(2)) as 半角 from dual
union select cast('Ｉ' as varchar2(2)) as 全角,cast('I'  as varchar2(2)) as 半角 from dual
union select cast('Ｊ' as varchar2(2)) as 全角,cast('J'  as varchar2(2)) as 半角 from dual
union select cast('Ｋ' as varchar2(2)) as 全角,cast('K'  as varchar2(2)) as 半角 from dual
union select cast('Ｌ' as varchar2(2)) as 全角,cast('L'  as varchar2(2)) as 半角 from dual
union select cast('Ｍ' as varchar2(2)) as 全角,cast('M'  as varchar2(2)) as 半角 from dual
union select cast('Ｎ' as varchar2(2)) as 全角,cast('N'  as varchar2(2)) as 半角 from dual
union select cast('Ｏ' as varchar2(2)) as 全角,cast('O'  as varchar2(2)) as 半角 from dual
union select cast('Ｐ' as varchar2(2)) as 全角,cast('P'  as varchar2(2)) as 半角 from dual
union select cast('Ｑ' as varchar2(2)) as 全角,cast('Q'  as varchar2(2)) as 半角 from dual
union select cast('Ｒ' as varchar2(2)) as 全角,cast('R'  as varchar2(2)) as 半角 from dual
union select cast('Ｓ' as varchar2(2)) as 全角,cast('S'  as varchar2(2)) as 半角 from dual
union select cast('Ｔ' as varchar2(2)) as 全角,cast('T'  as varchar2(2)) as 半角 from dual
union select cast('Ｕ' as varchar2(2)) as 全角,cast('U'  as varchar2(2)) as 半角 from dual
union select cast('Ｖ' as varchar2(2)) as 全角,cast('V'  as varchar2(2)) as 半角 from dual
union select cast('Ｗ' as varchar2(2)) as 全角,cast('W'  as varchar2(2)) as 半角 from dual
union select cast('Ｘ' as varchar2(2)) as 全角,cast('X'  as varchar2(2)) as 半角 from dual
union select cast('Ｙ' as varchar2(2)) as 全角,cast('Y'  as varchar2(2)) as 半角 from dual
union select cast('Ｚ' as varchar2(2)) as 全角,cast('Z'  as varchar2(2)) as 半角 from dual
union select cast('［' as varchar2(2)) as 全角,cast('['  as varchar2(2)) as 半角 from dual
union select cast('￥' as varchar2(2)) as 全角,cast('\'  as varchar2(2)) as 半角 from dual
union select cast('］' as varchar2(2)) as 全角,cast(']'  as varchar2(2)) as 半角 from dual
union select cast('＾' as varchar2(2)) as 全角,cast('^'  as varchar2(2)) as 半角 from dual
union select cast('＿' as varchar2(2)) as 全角,cast('_'  as varchar2(2)) as 半角 from dual
union select cast('‘' as varchar2(2)) as 全角,cast('`'  as varchar2(2)) as 半角 from dual
union select cast('ａ' as varchar2(2)) as 全角,cast('a'  as varchar2(2)) as 半角 from dual
union select cast('ｂ' as varchar2(2)) as 全角,cast('b'  as varchar2(2)) as 半角 from dual
union select cast('ｃ' as varchar2(2)) as 全角,cast('c'  as varchar2(2)) as 半角 from dual
union select cast('ｄ' as varchar2(2)) as 全角,cast('d'  as varchar2(2)) as 半角 from dual
union select cast('ｅ' as varchar2(2)) as 全角,cast('e'  as varchar2(2)) as 半角 from dual
union select cast('ｆ' as varchar2(2)) as 全角,cast('f'  as varchar2(2)) as 半角 from dual
union select cast('ｇ' as varchar2(2)) as 全角,cast('g'  as varchar2(2)) as 半角 from dual
union select cast('ｈ' as varchar2(2)) as 全角,cast('h'  as varchar2(2)) as 半角 from dual
union select cast('ｉ' as varchar2(2)) as 全角,cast('i'  as varchar2(2)) as 半角 from dual
union select cast('ｊ' as varchar2(2)) as 全角,cast('j'  as varchar2(2)) as 半角 from dual
union select cast('ｋ' as varchar2(2)) as 全角,cast('k'  as varchar2(2)) as 半角 from dual
union select cast('ｌ' as varchar2(2)) as 全角,cast('l'  as varchar2(2)) as 半角 from dual
union select cast('ｍ' as varchar2(2)) as 全角,cast('m'  as varchar2(2)) as 半角 from dual
union select cast('ｎ' as varchar2(2)) as 全角,cast('n'  as varchar2(2)) as 半角 from dual
union select cast('ｏ' as varchar2(2)) as 全角,cast('o'  as varchar2(2)) as 半角 from dual
union select cast('ｐ' as varchar2(2)) as 全角,cast('p'  as varchar2(2)) as 半角 from dual
union select cast('ｑ' as varchar2(2)) as 全角,cast('q'  as varchar2(2)) as 半角 from dual
union select cast('ｒ' as varchar2(2)) as 全角,cast('r'  as varchar2(2)) as 半角 from dual
union select cast('ｓ' as varchar2(2)) as 全角,cast('s'  as varchar2(2)) as 半角 from dual
union select cast('ｔ' as varchar2(2)) as 全角,cast('t'  as varchar2(2)) as 半角 from dual
union select cast('ｕ' as varchar2(2)) as 全角,cast('u'  as varchar2(2)) as 半角 from dual
union select cast('ｖ' as varchar2(2)) as 全角,cast('v'  as varchar2(2)) as 半角 from dual
union select cast('ｗ' as varchar2(2)) as 全角,cast('w'  as varchar2(2)) as 半角 from dual
union select cast('ｘ' as varchar2(2)) as 全角,cast('x'  as varchar2(2)) as 半角 from dual
union select cast('ｙ' as varchar2(2)) as 全角,cast('y'  as varchar2(2)) as 半角 from dual
union select cast('ｚ' as varchar2(2)) as 全角,cast('z'  as varchar2(2)) as 半角 from dual
union select cast('｛' as varchar2(2)) as 全角,cast('{'  as varchar2(2)) as 半角 from dual
union select cast('｜' as varchar2(2)) as 全角,cast('|'  as varchar2(2)) as 半角 from dual
union select cast('｝' as varchar2(2)) as 全角,cast('}'  as varchar2(2)) as 半角 from dual
union select cast('￣' as varchar2(2)) as 全角,cast('~'  as varchar2(2)) as 半角 from dual
)
,pos as 
(
select
 RowNum as p 
,RowNum+1 as nx 
from dual
connect by Level <= 300
)
,args as
(
select 'タダsyuu' as in_val from dual
)
select
 cast(replace(VAL,'^','') as varchar(64)) out_val 
from (
  select
   p
  ,nx
  ,ROW_NUMBER() OVER (PARTITION BY in_val ORDER BY p desc) as n
  ,sys_connect_by_path(nvl(半角,substr(in_val,p,1)),'^') as val 
  from args
  cross join pos
  left outer join 変換表
  on 全角 = substr(in_val,p,1)
  where p<=length(in_val)
  start with p=1
  connect by prior nx = p
) a
where n =1



















上記のＳＱＬはそのまま実行できます。
in_valに全角文字を含む文字列を設定すれば半角に変換された文字列が取得できます
UTL_I18N.TRANSLITERATE関数が少々遅いようなのでＳＱＬのみで変換可能にしてみました
iPad等では半角カタカナの入力ができないようなので検索の際半角に変換するのに使えるかも、






索引へ



【SID 取得】



















select LPAD(' ',3-length(SID),' ')||SID||' '||OSUSER as SID from V$SESSION
























索引へ



【ユーザー名 取得】



















select distinct OSUSER as OSUSER from V$SESSION
























索引へ



【アプリケーション 取得】



















select distinct PROGRAM as PROGRAM from V$SESSION
























索引へ



【セッション情報 取得】



















select SID,USERNAME,OSUSER,MACHINE,PROGRAM,LOGON_TIME from V$SESSION
























索引へ



【実行中のSQL取得（のぞき見）】



















select 
 sid 
,piece 
,osuser 
,username 
,MACHINE 
,PROGRAM 
,LOGON_TIME 
,replace(replace(sql_text,CHR(13)||CHR(10),CHR(10)),CHR(10),CHR(13)||CHR(10)) as sql_text 
from v$session s 
left outer join V$SQLTEXT_WITH_NEWLINES t 
on s.sql_address =t.address 
where 1=1
--and sid=  " + SIDStr + " 
and sid=  623 
order by 
 sid 
,address 
,piece 

























索引へ



【実行中のSQL取得（集約）】



















--select 
with sql_T 
as 
( 
  select 
   sid 
  ,piece 
  ,osuser 
  ,username 
  ,MACHINE 
  ,PROGRAM 
  ,LOGON_TIME 
  ,replace(replace(sql_text,CHR(13)||CHR(10),CHR(10)),CHR(10),CHR(13)||CHR(10)) as sql_text 
  from v$session s 
  left outer join V$SQLTEXT_WITH_NEWLINES t 
  on s.sql_address =t.address 
  where 1=1 
  --and sid= " + SIDStr + " 
  --and sid= 748 
) 
SELECT 
 sid 
--,piece 
,osuser 
,username 
,MACHINE 
,PROGRAM 
,LOGON_TIME 
,LISTAGG(sql_text, '') WITHIN GROUP (ORDER BY piece) sql_text 
FROM sql_T 
where sql_text is not null
GROUP by 
 sid 
--,piece 
,osuser 
,username 
,MACHINE 
,PROGRAM 
,LOGON_TIME 

























索引へ



【実行中のSQL取得（集約）古いやり方Oracleの制約により完全な復元が困難】



















--select
with sql_T
as
(
  select 
   sid 
  ,piece 
  ,piece + 1 as Next_piece
  ,osuser 
  ,username 
  ,MACHINE 
  ,PROGRAM 
  ,LOGON_TIME 
  ,replace(replace(replace(sql_text,CHR(13)||CHR(10),CHR(10)),CHR(10),CHR(13)||CHR(10)),'^','@#@') as sql_text 
  from v$session s 
  left outer join V$SQLTEXT_WITH_NEWLINES t 
  on s.sql_address =t.address 
  where 1=1
  --and sid=  " + SIDStr + " 
  --and sid=  748 
)
,cat_sql
as
(
  select
   sid 
  ,piece 
  ,Next_piece
  ,osuser 
  ,username 
  ,MACHINE 
  ,PROGRAM 
  ,LOGON_TIME 
  ,sql_text 
  ,Level
  ,sys_connect_by_path(sql_text,'^#^') as Path
  from sql_T
  start with piece = 0
  connect by
      prior next_piece = piece
  and prior SID = SID
)
select
 replace(replace(PATH,'^#^',''),'@#@','^')
from cat_sql
where exists(
select
 1
from (
  select
    SID
   ,max(PIECE) as max_PIECE
  from sql_T
  group by
   SID
  ) ex
  where ex.SID     = cat_sql.SID
  and ex.max_PIECE = cat_sql.PIECE
)
























索引へ



【セッション実行状況取得】



















select 
 a.SID 
,to_char(cast(b.SOFAR/b.TOTALWORK*100 as number(3,0))         ,   '990')||'%'  as 状況 
,to_char(24*60*60*to_number(b.LAST_UPDATE_TIME - b.START_TIME),'99,990')||'秒' as 経過時間 
,cast(substr(b.MESSAGE,1,255) as varchar2(255)) as 実施内容 
from V$SESSION a 
left outer join V$SESSION_LONGOPS b 
on a.PREV_SQL_ADDR = b.SQL_ADDRESS 
where 1=1
--and  a.SID = " + SIDStr +  L" 
and b.TIME_REMAINING > 0 

























索引へ



【実行中のSQLの実行計画を取得】



















SELECT  
 b.SID 
,b.OSUSER 
,ID 
,cast( 
 substr( 
 LPAD(' ',DEPTH*2,' ') 
 ||case 
 when OPTIMIZER = 'ANALYZED'                             then '<'||OBJECT_NAME||'>' 
 when OPTIMIZER is null      and OBJECT_NAME is not null then '<'||OBJECT_NAME||'>' 
 when OPTIMIZER is null                                  then NULL 
 else null 
 end  
 ||' ' 
 ||case 
 when OPTIMIZER = 'ANALYZED'                         then OPERATION||' '||NVL2( OPTIONS, OPTIONS, null ) 
 when OPTIMIZER is null      and OPTIONS is not null then OPERATION||'('||OPTIONS||')' 
 when OPTIMIZER is null                              then OPERATION 
 else OPERATION 
 end 
 || 
 case 
 when OPTIMIZER <> 'ANALYZED' and OPTIMIZER is not null then '(Optimizer='||OPTIMIZER||')' 
 end 
 ,1,150) 
 as varchar2(100) 
 ) as 計画 
,COST 
,BYTES 
,OPTIMIZER 
FROM v$sql_plan a 
left outer join v$session b 
on  b.SQL_HASH_VALUE = a.hash_value 
and b.SQL_ADDRESS = a.address 
where 1=1 
and a.child_number = 0 
--and b.SID = " + SIDStr + " 
and b.SID is not null
ORDER BY a.ADDRESS, a.HASH_VALUE, a.CHILD_NUMBER, a.ID 
























索引へ



【実行プラン取得(コミットコントロールの元で実施がおすすめ、取得完了後にロールバックが必要、消し忘れを防ぐ)】



















--実行プラン取得の手順１
EXPLAIN PLAN 
SET STATEMENT_ID = (select username||to_char(sysdate,'yyyymmddhhmiss') from user_users)   --ユニークとなるキーを指定
INTO PLAN_TABLE FOR 
 select * from ALL_VIEWS          --実行計画を取得したいSQLを指定
 
--実行プラン取得の手順２
SELECT 
  case when id=0 then '' else LPAD(' ',2*(level-1)) end 
  ||case  
  when OPTIMIZER = 'ANALYZED'                             then '<'||OBJECT_NAME||'>'  
  when OPTIMIZER is null      and OBJECT_NAME is not null then '<'||OBJECT_NAME||'>'  
  when OPTIMIZER is null                                  then NULL  
  else null  
  end   
  ||' '  
  ||case  
  when OPTIMIZER = 'ANALYZED'                         then OPERATION||' '||NVL2( OPTIONS, OPTIONS, null )  
  when OPTIMIZER is null      and OPTIONS is not null then OPERATION||'('||OPTIONS||')'  
  when OPTIMIZER is null                              then OPERATION  
  else OPERATION  
  end  
  ||  
  case  
  when OPTIMIZER <> 'ANALYZED' and OPTIMIZER is not null then '(Optimizer='||OPTIMIZER||')'  
  end as 計画 
FROM plan_table
CONNECT BY PRIOR id = parent_id
             AND PRIOR TIMESTAMP = TIMESTAMP
             AND statement_id    = (select username||to_char(sysdate,'yyyymmddhhmiss') from user_users) --１で指定したキーを指定
START       WITH id              = 0
             AND statement_id    = (select username||to_char(sysdate,'yyyymmddhhmiss') from user_users) --１で指定したキーを指定



























































901